Seems like the advantage of your solution is that it's easier to implement and to make it user-friendly. However as you state yourself, for larger systems the other will work better. While the ticket system now is small, in the future I'll be mixing multiple events in one database.

@O'Niel Absolutely, if you have more than one table affected for example, the other solution is the way to go. It was meant to solve exactly this problem.

this is the better way for the larger system!

This is one way to implement optimistic locking. The more common way is a VERSION column that is used similarly, and works for any kind of update, not only of the IS_SCANNED column. However, you will want to spell UPDATE with a "P". ;)

This is exactly the solution I'd have chosen, too. No questions, no trouble, no ifs. How large can a "large" system possibly be? If you actually manage to run 10 concurrent large events country-wide, then I'll bow in awe to you. Assume 10 queues on each, and at least 1 second for the queue guy to take the ticket and scan it, that's 100 transactions per second. So... what? I mean, seriously? Are you using a Raspberry as database backend? This is not a challenge for a database system in any way. Besides, tickets for different events need not be in one database at all, even if that is "cool".

"this is an atomic operation" - reference needed. I somewhat doubt that this is true for all possible databases with all possible lock levels. It certainly isn't specified anywhere in the standard and it also certainly won't hold for more complex statements.

To be honest there are probably enough obscure systems out there that I don't even know their name, much less their ACID properties in combination with single statements. Do you have a specific system in mind, where it would not be atomic?

This is nice to avoid the problem of locking a row and then getting into a situation where the row is never unlocked.

@user3067860 You should always write your software such that transactions are terminated (or committed) when the work is done, even if the client goes away. "Row never unlocks" is a software bug in your code, not a problem with your database.

@nvoigt And that's why you rely on standards and actual written guarantees when designing a secure system, instead of going with "well I don't know if it can fail, so let's just assume it's safe". But since you'll probably need a counter example despite that missing the point: Conceivably on MSSQL without serializable isolation level (which happens to be the default) depending on the data schema.

@ChristopherSchultz I'm quite interested in how you propose to fix something like a connection problem in the client software. I've worked with an application that used Oracle and avoided transactions like the plague, because any small disturbance in the connection (or application crash, of course) would lock up the database for hours without manual intervention.

@ChristopherSchultz If all software was perfectly implemented this whole site could probably go away, right? "What could possibly go wrong with implementing this?" is a question we should always be asking. (Valid user gets locked out and complains loudly on review sites. Security guards are so accustomed to bugs in the system that whenever it says there is a problem they just wave the person through. Etc.)

@Luaan That sounds very weird. At least with MSSQL if the open connection over which the transaction is run closes (which would obviously happen if the application crashed or the connection was lost) the transaction is rolled back. A quick search shows the same should be true for Oracle. I guess you might've used distributed transactions which are an awful idea all around but could lead to such issues.

Is this guaranteed to not run two updates on a distributed database? I think the OPs scenario is silly, but I'm also interested in how a distributed database responds to something like this.

@Steve This is not even guaranteed to work on a single database with the right (or wrong) isolation level.

@Voo Hmm.. I'm not familiar enough with the low level details of a DB to know this sort of transaction parallelism. My gut says if you really care about this sort of microsecond level race-conditions, an RDBMS might not be the right solution.

@Steve It's the same as with normal programming: Most people who want to do lock-free programming just don't know all the pitfalls they're ignoring and a quick test seems to show them that it "works". No I couldn't tell you all the possible ways this could go wrong, but I can think of a few. For us mere mortals using locking is simple, correct and scales just fine. Or as a professor of mine used to say: If it doesn't have to be correct, I can make it as fast as you want.

@Voo Yeah, MS SQL doesn't have that problem, and I hope more modern versions of Oracle don't either. But the details aren't really important here - my point was that this must be done by the database, since your application may not be able to.

@Luaan My point was that using a transaction is the simplest way for an application to make sure no race condition occurs and there's absolutely no reason to avoid them with any modern mainstream DBMS. The whole point of DBMSes is to handle failures such as these gracefully.