postgres: Locking row data using python

Question

I have a postgres( PostgreSQL 12.3) db table which consists of 100k rows and I have a python script which reads from this table and perform some action based on the data. I want to run the same script through multiple machines so that data can be processed faster. But when I run from multiple machines, I want to make sure one row is processed by only one machine at a time, basically achieving it by locking that row.

Can you provide some pointers on how locking row can be achieved through python. I am using psycopg2 module to read and update data from table, but did not find a way to lock the row data.

AdamKG · Accepted Answer · 2020-07-01 18:56:35Z

6

Use SELECT ... FOR UPDATE SKIP LOCKED, which performs a row-level lock on any rows that are returned, and skips rows that are already locked by another transaction.

Session 1:

testdb=# create table tt(x text);
CREATE TABLE
testdb=# insert into tt select 'foo';
INSERT 0 1
testdb=# insert into tt select 'bar';
INSERT 0 1
testdb=# BEGIN;
BEGIN
testdb=# SELECT * FROM tt ORDER BY x LIMIT 1 FOR UPDATE SKIP LOCKED;
  x  
-----
 bar
(1 row)

testdb=# -- SELECT in session 2 is performed now.
testdb=# commit;
COMMIT

Session 2:

testdb=# BEGIN;
BEGIN
testdb=# SELECT * FROM tt ORDER BY x LIMIT 1 FOR UPDATE SKIP LOCKED;
  x  
-----
 foo
(1 row)

testdb=# commit;
COMMIT

You won't need to do anything special with psycopg2 for this, as long as your query already has a reasonably small limit (so the SELECT doesn't lock every row), adding FOR UPDATE SKIP LOCKED should do what you're asking for. See the docs on the locking clause for more details.

answered Jul 1, 2020 at 18:56

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rovaru Over a year ago

AdamKG Over a year ago

In psql, unless you issue a BEGIN, every statement is it's own transaction, so the lock is released as soon as the SELECT finishes. With psycopg2, the transaction is started implicitly and lasts until you issue a connection.commit().

rovaru Over a year ago

Thanks, it works now. Just curious, will there be any chance that two sessions could pick up the same row, and one gets the lock finally? I am just thinking if in that case which session will process the data and wot happens to the other session. In my setup, I am using the same user to access the table in both sessions..

rovaru Over a year ago

Also, for pscopyg2, can we say that the transaction will close if the script exits even if the connect.commit() was not issued, and hence lock is released?

AdamKG Over a year ago

No, if a connection with an open transaction gets a row from a SELECT FOR UPDATE SKIP LOCKED query, no other transaction can return that from from their own SELECT FOR UPDATE until the 1st has either committed or rolled back. And yes, a connection closing without issuing a commit is treated as a rollback, and the lock will be released as soon as PG notices the connection is closed.

Collectives™ on Stack Overflow

postgres: Locking row data using python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related