I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.
The user needs to be able to redeem a coupon, which will insert a row into the log table and mark the coupon as used (by updating the used
column to true
).
Naturally, there's an obvious race condition/security issue here.
I've done similar things in the past in the world of mySQL. In that world, I'd lock both tables globally, do the logic safe in the knowledge that this could only happen once at a time, and then unlock the tables once I was done.
Is there a better way in Postgres to do this? In particular, I'm concerned that the lock is global, but doesn't have to be — I really only need to make sure no one else is trying to enter that particular code, so perhaps some row-level locking would work?
Best Answer
I have heard of concurrency problems like that in MySQL before. Not so in Postgres.
Built-in row-level locks in the default
READ COMMITTED
transaction isolation level are enough.I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you should need that). If you don't need anything from the
coupon
table you can use a transaction with separateUPDATE
andINSERT
statements just as well.It should be a rare thing that more than one transaction tries to redeem the same coupon. They have a unique number, don't they? More than one transaction trying at the same moment in time should be much rarer, yet. (Maybe an application bug or somebody trying to game the system?)
Be that as it may, the
UPDATE
only succeeds for exactly one transaction, no matter what. AnUPDATE
acquires a row level lock on each target row before updating. If a concurrent transaction tries toUPDATE
the same row, it will see the lock on the row and wait till the blocking transaction is finished (ROLLBACK
orCOMMIT
), then being the first in the lock queue:If committed, recheck the condition. If it's still
NOT used
, lock the row and proceed. Else theUPDATE
now finds no qualifying row and does nothing, returning no row, so theINSERT
also does nothing.If rolled back, lock the row and proceed.
There is no potential for a race condition.
There is no potential for a deadlock unless you put more writes into the same transaction or otherwise lock more rows than just the one.
The
INSERT
is care-free. If, by some mistake thecoupon_id
already is in thelog
table (and you have a UNIQUE or PK constraint onlog.coupon_id
), the whole transaction will be rolled back after a unique violation. Would indicate an illegal state in your DB. If the above statement is the only way to write to thelog
table, that should never occur.