PostgreSQL – Synchronizing Batch Delete with Advisory Lock

lockingpostgresql

We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch issue between multiple instances of the app all trying to delete "old" data once a day (basically, "delete all rows since 30 days ago or more").

I am considering using an advisory lock in Postgres as a synchronisation token so that only one instance actually performs the deletion.

So this is how I envision it (assuming all instances run the exact same code): At, say, 4 AM each morning all 3 instances will try to acquire the advisory lock; only one will succeed initially, and it will perform the deletion of old data, while the other app instances will wait on the lock.

Once the first instance is done, the other instances will, in turn, acquire the lock but will find that there a no more eligible rows and return.

Please let me know if you see any issue with the above approach.

Best Answer

Manual locking is probably unnecessary. When the second process to get to a row in order to delete it discovers that it is already in the process of being deleted, it will inherently block until that deleting transaction commits. So this should already yield the behavior you want.

It is possible (but I think unlikely) that the processes will choose different execution plans which delete rows in different orders, and deadlock against each other. In that case, one will get served an ERROR, and the other will proceed. You will still get an acceptable outcome if you retry the ERRORed process, or if you are sure this is the only source of deadlocks you could just ignore deadlock errors and trust that the winner of the deadlock did the job. This is somewhat wasteful to have one process do a lot of work which just gets aborted and repeated, so you it might be worthwhile to use advisory locking to avoid that. But I wouldn't bother until I had a demonstrated problem that needs to be solved.

Depending on the execution plan for the deletions, verifying that no rows need to be deleted might itself be a lot of work. If you want to reduce that work, you could use "try" advisory locks to skip the work for all but the first session, but then if the first session fails before it finishes but after it has warned off the others, then the job just won't get done. And if your app uses advisory locks for other purposes, you would want to make sure you don't get collisions on the lock keyspace.

Related Solutions

PostgreSQL 9.3 – Running Two Instances on Same Port with Different IP Addresses

You must also set separate unix_socket_directories.

PostgreSQL – Locking for UPDATE / INSERT Combination

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.

Best Answer

Related Solutions

PostgreSQL 9.3 – Running Two Instances on Same Port with Different IP Addresses

PostgreSQL – Locking for UPDATE / INSERT Combination

Related Question