20

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?

0

2 Answers 2

31

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 separate UPDATE and INSERT statements just as well.

WITH upd AS (
   UPDATE coupon
   SET    used = true
   WHERE  coupon_id = 123
   AND    NOT used
   RETURNING coupon_id, other_column
   )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

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. An UPDATE acquires a row level lock on each target row before updating. If a concurrent transaction tries to UPDATE the same row, it will see the lock on the row and wait till the blocking transaction is finished (ROLLBACK or COMMIT), 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 the UPDATE now finds no qualifying row and does nothing, returning no row, so the INSERT 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 the coupon_id already is in the log table (and you have a UNIQUE or PK constraint on log.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 the log table, that should never occur.

1
  • It should indeed be a rare thing that more than one transaction tries to redeem the same code, but your suspicions are right in that this is going to exclusively be when someone is trying to game the system. Thanks so much for this — CTEs were a big draw for me in moving to Postgres but I didn't realise that implicit locking would be good enough for this.
    – Rob Miller
    Jul 7, 2015 at 11:54
0

Erwin's answer is state of the art. But for completeness sake I'd like to suggest more options.

Pessimistic Lock

First one is typical - using Pessimistic Lock on the coupon row, it must be taken in the beginning:

select * from coupon where id=? and used=false for update
insert into log(...)

The 2nd transaction will wait until the 1st one is finished. If 1st TX committed, then select will not return the coupon and you can abort. This approach is similar to Erwin's answer but allows to split transaction into multiple statements.

Pessimistic Lock with skip locked

If you don't care which coupon to take - any one that satisfies the price works, then you can simply skip the one that's locked and move on. In such case you never have to abort transactions - they will always succeed:

select * from coupon where used=false for update skip locked limit 1
insert into log(...)

I'm hesitant to call this "pessimistic lock" because in fact it will never block.

Serializable Isolation

Another option is optimistic (in case of PG): using Serializable Isolation. In which case you can insert into log table before you update the used=false. Note that when 2nd TX tries to commit it will notice that 1st TX updated the row that it updated (or even if it only read it) and you'll get:

ERROR:  could not serialize access due to concurrent update

More on isolations in PG.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.