Mysql locking – How to acquire a lock in the DB for each userId

1. Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue

2. Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.

3. Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

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.