CODEWITHSUNDEEP
sqlpostgresqlconcurrencytransactionslocking
Davs
Davs

Reputation: 489

Database row lock during multiple transactions

Hi and Happy New Year :)

I'm using Postgresql and I need a way to enable a worker process to lock a specific row, while that process operates on that row. It is essentially a table of actions which should be executed once and only once. Each process should grab a different row to operate on.

During this 'operation' the worker process will calculate some value and insert into the database in a transaction multiple times (it will alter between calculating and inserting into the database).

I don't know how long each operation will take (it varies) and I need a way to unlock that row, if that process dies/gets killed or the system crashes (so other process can grab that row and finish the operations on it).

As far as I know, Postgresql's row lock last only in one transaction. I was thinking to add some flag, which would indicate if a row is locked or not into the table, but I have trouble figuring out how could I tell if that row is still being operated on, or is it hanging, because the worker process died (in the latter case it should be taken by an other worker process)? (I have a finished flag in that table to signal, that the row is finished/done being processed)

Upvotes: 0

Views: 1855

Answers (1)

Mike Dinescu
Mike Dinescu

Reputation: 55720

Your solution with the flag seems feasible and I think the only thing that is needed is to make the lock expire. Basically the way I would architect the lock is I would write a timestamp when the lock was taken and make it so the process would have to update the lock every so often (i.e. every 30 seconds) while it's still working on the record. If the process dies or otherwise fails to complete the work the lock will expire and other processes can unlock it if more than double the timeout period elapses.

When a process finishes working on a record it would clear the lock flag and mark the record as processed (again another flag).

You will probably want to have two fields: one that would store the timestamp lock flag and another that would indicate which process owns the lock (in case you care about it). I'm assuming that there is some kind of key that can be used to order the records in the table such that the concept of "next action" is meaningful.

You could use a query like this to obtain the next record to process:

 -- find the next available process and "lock" it by updating it's flag
 UPDATE actions_tabe
    SET LockFlag = @timestamp,
        Process = @processname
  WHERE Id IN (SELECT Id
            FROM actions_table
           WHERE LockFlag IS null
             AND IsComplete = '0'
             AND ScheduledTime < now()
           ORDER BY Scheduledtime ASC, Id ASC
           LIMIT 1);

 -- return the Id and Action of the record that was just marked above
 SELECT Id, Action
   FROM actions_table
  WHERE Process = @processname

Sample Fiddle here: http://sqlfiddle.com/#!11/9c120/26/1

Upvotes: 1

Related Questions