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?
2
Answers
It wouldn’t interact. What it’s supposed to be done in those cases is to have order in querying and row access and this would be avoided.
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.