I am beginning to understand how transaction and locks work in databases.
So in a very higher-level understanding:
<code>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
</code>
<code>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
</code>
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?