Deadlock when using SELECT FOR UPDATE with WHERE IN clause

Question

Consider the following example:

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

In this example it's obvious that a deadlock could happen if both transaction are executed concurrently because if T1 locks row with id=1 and then T2 locks row with id=2, both T1 and T2 can not perform the second SELECT FOR UPDATE query and we have a deadlock.

Now, to solve this, we could just perform the SELECT FOR UPDATE queries in the same order:

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

We solved the deadlock problem for this example.

Now, my question is, if you consider the following similar example:

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

My questions:

Is it possible to have a deadlock in the last example?
In other terms: will Postgres lock all rows that match a WHERE condition atomically at the same time?
If yes, can we also say that the WHERE clause order does not count? So that in T1 we could use:

SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;

While in T2 we could use:

SELECT * FROM table1 WHERE id IN (2, 1) FOR UPDATE;

Without risking to cause a deadlock?

Could you please explain why? And also if you attach relevant informations from postrgres documentation I would be grateful — radar155
– radar155, Commented Mar 10, 2023 at 16:29

Erwin Brandstetter · Accepted Answer · 2023-03-10 16:47:38Z

5

The last example is susceptible to deadlocks.

Locks are not taken "atomically" in a sense that they would happen virtually at the same time for the same transaction (or even the same statement). Locks are taken along the way one after the other, and released at the end of the transaction.

The point is that the list of items in the IN clause does not necessarily mandate an order in which rows are locked. You need an ORDER BY clause to do that. Or separate statements like you already successfully tried.

Separate statements are verbose and more expensive. So:

BEGIN;
SELECT FROM table1
WHERE  id IN (1,2)
ORDER  BY id             -- !
FOR    UPDATE;

UPDATE table1 set col1 = 'abcd' WHERE id = 1;
COMMIT;

As long as all writing access to the same table sticks to the same order reliably, there cannot be a deadlock (from this interaction).

The manual:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

With data more or less physically sorted by id on disk, this can even improve performance.

edited Mar 10, 2023 at 16:47

answered Mar 10, 2023 at 16:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

6 Comments

radar155 Over a year ago

so, just by adding an ORDER BY clause I would avoid deadlock in situations where a single SELECT FOR UPDATE statement is locking more rows?

radar155 Over a year ago

Thank you for the explanation. So based on your reply can we say that, when triyng to lock multiple rows with a single SQL statement, postgres doesn't lock them atomically?

Erwin Brandstetter Over a year ago

@radar155: ORDER BY:Yes, that's also what the manual advises. See additions. "Atomically": Locking needs to happen in consistent order. Not sure what you mean by "atomically" in this context. All locks are part of an "atomic" transaction.

radar155 Over a year ago

with "atomically" in this context I mean that, if my WHERE clause is going to select 3 rows, then all 3 rows are locked at the same time. But it looks that this is false. And for this reason we need to ORDER BY, so that ORDER BY is performed before FOR UPDATE. Please, tell me if I understood

Erwin Brandstetter Over a year ago

I added a note on that above.

|

Collectives™ on Stack Overflow

Deadlock when using SELECT FOR UPDATE with WHERE IN clause

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related