Exploiting the delay when a festival ticket is scanned

The vulnerability you're describing is a race condition.

There are several ways to deal with it, but I would go with a SELECT ... FOR UPDATE SQL query, which puts a lock on the selected rows to prevent new writes until the current transaction is committed.

Be sure to check your RDBMS documentation to check how to implement it correctly:

  • PostgreSQL
  • MySQL
  • MariaDB

The other solution here is absolutely right and makes sense for larger systems where it's not as easy.

With the data you have, that is relatively simple, you could go for a non-blocking option:

WHERE TICKET_ID = @ScannedKey 

Now, this is an atomic operation. No two users of the database can issue this and have it update the row. The person who gets returned "1 row affected" (obviously there is a way to find that out in code, do not parse text for this) can go in. Everybody else will get zero rows affected by the statement. If you want to be user friendly, you can now check why it could not be found, whether it was the wrong ID or it's already scanned.

But the important detail is that the statement is atomic. Only one will win, no matter how close they are to zero time difference. Because you no longer have a read and then a write. You have the read and write in one atomic operation.

The downside to this seems to be that a person might get in for free (with a copied ticket).

For small events that is probably correct.
However if you add too much delay, for any reason, you risk more than a person getting in.
The ticket scanners will just let a few extra people through if their devices jam or are too slow... because, heck most of them probably have valid tickets, right?

I watched this happen at a major event this calendar year attended by thousands of fans of musicians that many people have heard of.
The ticket company was a major one (maybe the one you work for?) and it was at a site that was custom built for ticket taking.
My party was one of the ones let through without a scan (and yes... I had a valid/legal ticket).
I had to stand there and watch the ticket takers a few minutes before I could figure out why it had happened.

Don't code for every eventuality when people are involved.
Shoot for two or three nines (99%-99.9%) and call it a day.
Save the nick picking for when only machines are involved... that's when you can get a bunch of 9's.