Row Level Locking in Mysql

Question

I have 5 rows in a table (1 to 5). I want row 2 lock for some update and in the meanwhile if someone tries to update row 4, then he should able to update.

I am trying this with code below, but I feel its placing lock on table level rather than row level.

------ session 1

START TRANSACTION;
SELECT * FROM test WHERE t=1 FOR UPDATE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

----- session 2 (which is being blocked)

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

Answer 1

Instead of FOR UPDATE use LOCK IN SHARE MODE. FOR UPDATE prevents other transactions to read the row as well. LOCK IN SHARE MODE allows read, but prevents updating.

Reference: MySQL Manual

------ session 1

START TRANSACTION;
SELECT * FROM test WHERE t=1 LOCK IN SHARE MODE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

----- session 2 (which is not being blocked anymore :) )

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

Update:

Realizing that the table has no index on t, I have the following explanation:

First, transaction T1 locks the row 1 in SELECT * FROM test WHERE t=1 FOR UPDATE

Next, transaction T2 tries to execute UPDATE test SET NAME='irfandd' WHERE t=4. To find out which row(s) are affected, it needs to scan all rows, including row 1. But that is locked, so T2 must wait until T1 finishes. If there is any kind of index, the WHERE t=4 can use the index to decide if row 1 contains t=4 or not, so no need to wait.

Option 1: add an index on test.t so your update can use it.

Option 2: use LOCK IN SHARE MODE, which is intended for putting a read lock only. Unfortunately this option creates a deadlock. Interestingly, T2 transaction executes (updating row 4), and T1 fails (updating row 2). It seems that T1 read-locks row 4 also, and since T2 modifies it, T1 fails because of the transaction isolation level (REPEATABLE READ by default). The final solution would be playing with Transaction Isolation Levels, using READ UNCOMMITTED or READ COMMITTED transaction levels.

The simplest is Option 1, IMHO, but it's up to your possibilities.