You assume there is no lock on the row when it is being read.

While I applaud your intent to make it as good as possible, you are assuming that two people queue in different lines at a festival and arrive at the scanner/register inside of maybe 100 ms. I cannot even queue in the line at the supermarket that is faster. Not even inside of a minute or more.

You could store the name in the DB and make the ticket scanner on the phone show the name of the ticket holder to the security guard.

@mbomb007 Yes, I stated that as possible solution under 'Passport', and also told I was already doing that. However, this solution requires human interaction and the alertness of the guard. I prefer a solution which is human-proof, knowing they are often the weak spot in a (security) system. However, showing up the name is a nice extra. So I'll keep doing that.

Another way to beat this race condition without row locking or other explicit atomic operations is: Rather than just track whether it's been scanned or not, insert a transaction ID for when it's scanned, and do a second read after your insert to ensure that yours txid is still in there. If they arrive within 100ms of each other, the second person will get in, but on human timescales, it's synchronous... In all other cases, the first person gets in. This has the added benefit of logging which gate a person went through, if you use a scanner's MAC address as part of the txid.

Is someone able to register the ticket name after the purchase? Sometimes someone will buy multiple tickets with an intent to give it to friends later without knowing who those friends will be at purchase time.

@TheLoneMilkMan No this at the moment is not possible. However, this is a damn good idea. Thanks for the suggestion! Will take this into account the next developing cycle.

It sounds like you're under the impression that festival attendance is such a large number that normal synchronization mechanisms (just a simple lock, or an atomic transaction) would be prohibitive. It's not; there are many orders of magnitude in between. It might be a problem if you had billions attending, and millions of entry gates, but that's not a realistic scenario.

@R.. True, but I'm also not really concerned about the volume of the database, but about the timespan. Most people buy their ticket the night before the event (around 10-11PM), so then the system has to be able to handle a huge load of requests and database-changes. I'd like to be more safe than sorry. ;)

@O'Niel You need to be realistic about your requirements. Having the software hit a database, and make a syncronous call, before giving ticket the OK, is going to add latency to the whole process. The best solution may be the device itself having a record of scanned tickets, and have that be updated every second. So, worst case is two people getting their ticket scanned within a second. We are talking about a festival here, not entrance into the CIA.

@GregoryCurrie But what if the CIA wants to throw a sick party and use my TicketSystem? ;) Jokes aside, yes I was also thinking about storing the database on the devices itself and update those at an interval... But that'd give the problem you described yourself, and it will also be a lot of data going through the devices (writing/updating the database each time), and the guards also use 4G (mobile data) to scan, so that can become expensive.

@O'Niel: Computers are orders of magnitude faster than you imagine them to be.

This is a classic example of wasting time and money securing the wrong thing. People who want to get into festivals for free will attack the physical security at its weakest point: a gap will be created in a fence, or they'll manage to scale a wall, or there'll be a mechanism for allowing people out and back in again, or there'll be a security guard who's waving people through without scanning tickets... This imagined scenario is only good for situations like the scene in Terminator 2 when they have to turn the key at the same time to open the vault ;-)

This "hack" was proven by a Dutch duo: youtube.com/watch?v=2IaQ07wELQg It works for the time being, but usually the speed of the line varies a lot, and so chances for it actually working are quite slim.

@EdwinLambregts Yes, that video was the reason I asked this question. However, by using row-locks I can prevent the race-condition. Seems the solution for this problem isn't too complicated actualy.

@AaronF I don't agree at all. With that mentality we could also stop using prepared statements and just put PHP-code full of SQLi-vulnerable code 'Why bother wasting time and money on those extra lines of prepared statements??? They could jump over the fence anyway...' The chance of this race condition being exploited is indeed very very small, and if the solution was too complicated I would also think twice about implementing it. But if this can be prevented by just typing 'SELECT FOR UPDATE' instead of 'SELECT' those 2 extra seconds are worth it IMHO.

@AaronF Clients where also happy to hear I found a solution for this problem after it came in the news.. So bonus bonus, not only security/IT-wise but also commercial. Now I can market my small ticketsystem as 'not having this severe security-issue all the big ticketsystems have.'

@EdwinLambregts I don't understand Dutch, but I think the system they're exploiting may be possibly worse, if it checks for scannedness during scanning, but doesn't mark scannedness until the user taps the screen. In that case, the concurrency problem could be easily several seconds, or even minutes if you can distract the scanner. Much easier to exploit and certainly worth considering. But not as simple as "select for update", because if you do that you have to consider situations where the row may not be UNlocked (see Falco's comment).

I think your festival goers aren't going to be very happy when they can't give their ticket to a friend, or sell it when they can't go. Also, you're putting a large burden on all the attendants to now verify the name comes up correctly. Basically, your solution is worse than the problem. You seem to be very concentrated on creating the "perfect" solution, but only to this one limited problem you found. Why is it you think this is the most likely scenario to be attacked? Existing solutions of ACID compliant databases already solve this problem.

Your question poses a scenario and then pokes holes in it. This is called a straw man argument and is a logical fallacy. Instead of examining an actual system and claiming it has vulnerabilities, you have described a system which supposes certain vulnerabilities and then pointed them out. Just because you aren't able to construct a system that is resistant to your scenario doesn't mean it's not possible. Your "question" would have been better-posed as an actual question like "Let's say my system works like this [...]; how do I protected against X?"

@O'Niel well yes of course, your technically clueless clients were probably over the moon that you'd 'solved' the 'problem'. You're engaged in security theatre - you're more interested in giving the impression of security than you are in actually securing things.

With SELECT is_scanned ... WHERE qrcode = 1 followed by a separate UPDATE ... SET is_scanned = 1 there is always a chance of having two updates.