MySQL Row level locks

问题:

I am not sure about how the row level lock work but here is my problem I have a table T (id int , balance int ) ( engine = InnoDB ) that I want to lock rows having ID = 1, so i start a transaction like this :

start transaction ; 
select * from T where ID = 1 FOR UPDATE ; 

Before sending commit, I wanted to try if really the rows are LOCKED. So I started an other session and I typed :

UPDATE T set balance = balance  + 100 where ID = 1 ;

Here I clearly see that I waiting for the lock (timeout after 30 sec).

But when I type :

UPDATE T set balance = balance  + 8500 where ID = 2 ;

I am also waiting for the lock, so how can I just lock the ID = 1 rows and not the table entirely?

回答1:

You need to add an index on the id column to ensure that you get a row-level lock. SELECT ... FOR UPDATE locks all the rows that are read to perform the query, not just the rows that are actually selected. Without an index, it has to perform a full table scan, so every row is locked as a result.

With an index, it just puts a lock in that index entry, it doesn't have to read any other rows, so no other rows will be locked.