Lets say that there are multiple parallel transactions that all do the same query:
SELECT * FROM table1 FOR UPDATE;
Can this result in a deadlock?
To put it in another way. Is the operation "lock all rows" in the above statement atomic or are the locks acquired along the way while the the records are processed?
Yes, it can result in a deadlock.
This is pretty easy to demonstrate. Set up a test table:
CREATE TABLE t AS SELECT i FROM generate_series(1,1000000) s(i);
... and then run these two queries in parallel:
SELECT i FROM t ORDER BY i FOR UPDATE;
SELECT i FROM t ORDER BY i DESC FOR UPDATE;
You can prevent deadlocks by ensuring that all processes acquire their locks in the same order. Alternatively, if you want to lock every record in the table, you can do it atomically with a table lock:
LOCK t IN ROW SHARE MODE;
