Database bugs in code

The idea

Databases can process thousands of queries a second. If two users buy the last ticket at the exact same millisecond, the database runs their transactions concurrently (interleaved). If they both READ the `tickets_left = 1` before either has a chance to WRITE `tickets_left = 0`, they both succeed. This is a Lost Update anomaly.

To fix this, you don't just rely on standard transactions (which often default to weak "Read Committed" isolation). You must explicitly lock the row using a SELECT ... FOR UPDATE. This forces the second transaction to wait until the first one is completely finished.

How it works (Pessimistic Row Locking)

# BAD: Naive read-modify-write
with db.transaction():
    ticket = db.query("SELECT tickets_left FROM events WHERE id=1")
    if ticket.tickets_left > 0:
        # If Tx2 reads before Tx1 reaches here, both buy the ticket!
        db.execute("UPDATE events SET tickets_left = tickets_left - 1")
        return "Purchased!"

# GOOD: SELECT FOR UPDATE
with db.transaction():
    # The database LOCKS this specific row. Tx2 must wait!
    ticket = db.query("SELECT tickets_left FROM events WHERE id=1 FOR UPDATE")
    
    if ticket.tickets_left > 0:
        db.execute("UPDATE events SET tickets_left = tickets_left - 1")
        return "Purchased!"
    else:
        return "Sold out!"