CODEWITHSUNDEEP
postgresqllocking
Majid Darabi
Majid Darabi

Reputation: 741

lock a transaction (series of selects and updates) in Postgresql

I have a table and several threads (systems) which read a specific column from the table and update it. So my query is as follow:

Begin;
SELECT col FROM tbl WHERE <condition1>
UPDATE tbl SET col = col + 1 WHERE <condition1>
Commit;

I want to make sure at any time only one thread can select and update that column. What is the best solution ?

PS: To the best of my knowledge, using transactions in SQL Server locks all the resources in the transaction but I have no idea about PostgreSQL.

Thank you!

Upvotes: 0

Views: 1145

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324901

kordirko has correctly pointed out that SELECT ... FOR UPDATE is the basic feature used for this.

Note that it will not take a predicate lock. So if you SELECT id FROM sometable WHERE id = 42 FOR UPDATE and there is no row with id = 42, no lock is taken. There's no "reservation" created on ID 42. Somebody else can jump in and create it between your SELECT and a subsequent INSERT.

If you're trying to deal with that, you may wish to look into SERIALIZABLE isolation mode. I think MS SQL Server, which you're used to, defaults to SERIALIZABLE isolation.

You can instead write:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT col FROM tbl WHERE <condition1>
UPDATE tbl SET col = col + 1 WHERE <condition1>
COMMIT;

I strongly suggest setting up some concurrency tests to demonstrate that this works as you expect, of course.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36127

The finest level lock in SQL is a row lock.

A column cannot be locked, the database always places a lock at the whole row (all columns of the row).

You can use a SELECT .. FOR UPDATE clause:

Begin;
SELECT col FROM tbl WHERE <condition1> FOR UPDATE;
UPDATE tbl SET col = col + 1 WHERE <condition1> ;
Commit;

SELECT .. FOR UPDATE works like ordinary select (it retrieves rows from the table), but in addtion it places locks on all retrieved rows, preventing them from being modified by other transactions until the current transaction ends.

You can also use RETURNING clause in UPDATE statement: 

Begin;
UPDATE tbl SET col = col + 1 WHERE <condition1> 
   RETURNING col - 1 AS col;
Commit;

The UPDATE statement always places a write lock on the modified record.

The RETURNING clause causes to return values, like ordinary SELECT, however it returns row values after modification (post-update row values), therefore a col - 1 expression is used in the above example, to compute the column value before it was incremented.

Please take a look at this demo --> http://www.sqlfiddle.com/#!15/4d0f4/3

The advantage of the second way (UPDATE+RETURNING clause) is that the record is accessed only once, in contrast to the first method, when the row must be selected first by the SELECT statement (first access), then it is updated using the UPDATE statement (second access).

Upvotes: 2

Related Questions