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

 

How Row Lock Is Handle When Query On The Table Row

In MySQL, there are 2 mechanisms to lock table row.

01. SELECT * FROM student WHERE id = 1 FOR UPDATE;

• Then this row is getting lock.
• Other transactions cannot update/delete the row until the current transaction commits or rolls back.
• Other transactions can read the row, unless you're using SERIALIZABLE isolation.

START TRANSACTION;

SELECT * FROM student WHERE id = 1 FOR UPDATE;

-- Locks row with id = 1 for update/delete by others

Transaction 2 tries to read student with id=1:

    If isolation is READ COMMITTED → Transaction 2 can read immediately (no lock on read).

    If isolation is SERIALIZABLE → Transaction 2 blocks, waiting for Transaction 1 to finish.

How to check current isolation level in MySQL

SELECT @@transaction_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

02. If we are Update or Delete any records, then that record is getting lock automatically for other transactions until the transaction is commits or rolls back.

In the code level that lock is handle like this:

1. Use Optimistic Locking (with @Version)

@Version

private int version;

This throws an exception if two concurrent updates are based on the same version.

2. Use Pessimistic Locking: Lock the row so only one transaction can update at a time:

@Lock(LockModeType.PESSIMISTIC_WRITE)

@Query("SELECT s FROM Student s WHERE s.id = :id")

Student findForUpdate(@Param("id") Long id);

If isolation is READ COMMITTED → Transaction 2 can read immediately (no lock on read).

Another problem.

1. Both T1 and T2 read the same row when grade = 0.
2. T1 updates grade = 0 + 1 = 1 and commits.
3. The row lock is released after commit.
4. T2 still has the old value (grade = 0) in its transaction snapshot (because it read before T1 committed).
5. T2 updates grade = 0 + 1 = 1 and commits.
6. Final value is 1 instead of 2 — because T2 didn’t see T1’s update.

Solution:

Use Optimistic Locking:  Add a @Version field in your entity.

Use Pessimistic Locking

     @Lock(LockModeType.PESSIMISTIC_WRITE)

    @Query("SELECT s FROM Student s WHERE s.id = :id")

   Student findByIdForUpdate(@Param("id") Long id);

-----------------------------------------------------------------------------------------------------------------------------------------------

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.

Assume this scenario, Student table has int grade column = 0

1. Transaction 1 (T1).

2. Transaction 2 (T2).

3. Thean row is loaded to RAM which it transaction specific.T1 has his own copy of row and T2 also has its own copy.

4. T2 starts, updates the same row’s column to column+1, and then commits.

5. T1 starts, updates the same row’s column to column+1, and then commits.

8. Again DB value become 1, not 2...

How to Avoid Lost Updates?

If handle in In DB level:

START TRANSACTION;

-- This locks the row so no other transaction can read/write it until you're done

SELECT grade FROM student WHERE id = 1 FOR UPDATE;

-- Then do your update

UPDATE student SET grade = grade + 1 WHERE id = 1;

COMMIT;

If handle in code level:

1. Use Optimistic Locking (with @Version)

@Version

private int version;

This throws an exception if two concurrent updates are based on the same version.

2. Use Pessimistic Locking

Lock the row so only one transaction can update at a time:

@Lock(LockModeType.PESSIMISTIC_WRITE)

@Query("SELECT s FROM Student s WHERE s.id = :id")

Student findForUpdate(@Param("id") Long id);