skip to Main Content

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


  1. 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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search