How to concurrently update PostgreSQL 13 table row without locks

Question

I need to update a table with different measures per id and these updates will be run by different transactions.

I need to know if there is a way to unlock the row on the update statement as I do not need to read data at the moment and data consistency would not be a problem.

Thanks in advance.

An UPDATE will not prevent concurrent reads. The only way to "unlock" a row locked by an UPDATE is to either `COMMIT` or `ROLLBACK` your transaction. — a_horse_with_no_name, May 19 '22 at 08:48
So how do I achieve that with a query like this?: ```UPDATE stats SET measureA = 1000 WHERE id = 1;``` — JourneyToJsDude, May 19 '22 at 08:52
Achieve what? If you need to "unlock" that row, then commit the UPDATE — a_horse_with_no_name, May 19 '22 at 08:55
But imagine I have 2 concurrent updates, one for measureA and another for measureB and they need to be committed at the same time — JourneyToJsDude, May 19 '22 at 08:57
Then the second UPDATE will wait until the first one commits (or rolls back). If you need to do it one transaction, then update both columns with the same statement. — a_horse_with_no_name, May 19 '22 at 08:59
Ok then I understand it is not possible to have two parallel update transactions without waits. — JourneyToJsDude, May 19 '22 at 09:03

score 1 · Accepted Answer · answered May 19 '22 at 09:27

1

There is no way for two transactions to update the same row at the same time.

But that is not necessary. Just make sure that your database transactions are as short as possible, then no lock will be held for a long time. You can mode the update of that row towards the end of the transaction to reduce the time the lock is held.

answered May 19 '22 at 09:27

Laurenz Albe

167,868
16
137
184

Cool. I was also wondering if in the case of multiple updates on the same transaction there would be a transaction level that would make it more performant. The official documentation does not help me with this question. Thanks! – JourneyToJsDude May 19 '22 at 09:49

JourneyToJsDude · Answer 2 · 2022-05-31T15:53:01.810

0

Finally I have implemented the different transactions (insert, update) inside a procedure and released the locks with COMMIT after each transaction in order to avoid deadlocks, following this comment: https://stackoverflow.com/a/56768529

edited May 31 '22 at 15:53

answered May 31 '22 at 15:47

JourneyToJsDude

155
2
11

How to concurrently update PostgreSQL 13 table row without locks

2 Answers2