07. How Row Lock Is Handle When Query On The Table Row

 

How Row Lock Is Handle When Query On The Table Row

Question: 

What will be the final value in a row when multiple transactions attempt to update it concurrently?

Let’s say we have a `student` table where the current value of the `name` column is `"name0"`. We execute the following sequence using two separate transactions:

1. Transaction 1 (T1) starts and updates the name to `"name1"`, but does not commit.

2. Transaction 2 (T2) starts, updates the same row’s name to `"name2"`, and then commits.

3. Transaction 1 (T1) is then rolled back.

Given this scenario, what will be the final value stored in the `name` column of the `student` row? Will it be `"name0"` (the original), `"name1"` (from the rolled-back transaction), or `"name2"` (from the committed transaction)?

 

Important Concepts:

1. Each transaction works with its own version of data (isolation).
2. InnoDB uses row-level locking and undo/redo logs.
3. You cannot update the same row in two transactions at the same time without waiting for the lock.

 

Let's walk through what really happens:

 

Step 1: Transaction 1 (T1) starts

START TRANSACTION;

UPDATE student SET name = 'name1' WHERE id = 1; 

-- Still not committed

 

 The row `id=1` is now locked by T1.

 Change to `name='name1'` is not visible to others yet.

 

 

Step 2: Transaction 2 (T2) tries to update same row

START TRANSACTION;

UPDATE student SET name = 'name2' WHERE id = 1;

COMMIT;

 

Here’s the catch:

 T2 will block (wait) because T1 is holding the lock on that row.

 T2 cannot proceed until T1 either commits or rolls back.

 

Step 3: You ROLLBACK T1

ROLLBACK; -- Now name reverts to 'name0'

Now the lock is released.

 

Step 4: T2 resumes and commits

UPDATE student SET name = 'name2' WHERE id = 1;

COMMIT;

 

T2 executes the update after T1 rolls back.

So the final committed value is: `name = 'name2'`

 

Final Answer:

The final value of `name` is `name2`.

 

Because:

 T2 was waiting for the row lock.

 Only after T1 rolled back, T2 could proceed.

 Then it committed its change.