Menu
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?
443
The select ... for update acquires a ROW SHARE LOCK on a table. This lock conflicts with the EXCLUSIVE lock needed for an update statement, and prevents any changes that could happen concurrently. All the locks will be released when the transaction ends.
It is very easy to update multiple columns in PostgreSQL. Here is the syntax to update multiple columns in PostgreSQL. UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];
FOR UPDATE . The Postgres-specific docs are here. Using SELECT FOR UPDATE will lock the selected records for the span of the transaction, allowing you time to update them before another thread can select. before another thread can select is a bit fuzzy, as selecting is still freely possible.
Example: SELECT FOR UPDATE in action A complete transaction that uses SELECT FOR UPDATE on that table could look like this: BEGIN; SELECT * FROM kv WHERE k = 1 FOR UPDATE; UPDATE kv SET v = v + 5 WHERE k = 1; COMMIT ; Working line by line through the statement above: The first line, BEGIN , initiates the transaction.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
