[SOLVED] postgres does a row or table locked by one operation can be accessed by another op in a single transaction

postgres does a row or table locked by one operation can be accessed by another op in a single transaction

I am beginning to understand how transaction and locks work in databases.

So in a very higher-level understanding:

BEGIN -- txID: 1234

UPDATE tbl_1 SET field = value WHERE id IN (1); -- for simplicity
-- Locks acquired on rows with ID 1

UPDATE tbl_1 SET field = new_value WHERE id IN (1); -- just for dumb reason updating the same row again.
-- does this operation still wait for the lock from previous operation?

COMMIT -- txID: 1234
-- Locks released on row with ID 1

It's my understanding that all locks within a transaction are held until the transaction commits.

So in that case, how does the 2nd operation proceed on the same row that is locked by 1st op?

Solution

Locks are at the transaction level. The second update will be executed. The result of the transaction being the <new_value> from the second update. See demo.