I have a table named Students like:

mysql> select * from students;
| id | name  | classId |
| 1  | Kris  |       7 |
| 2  | Tom   |       7 |
| 3  | Mary  |       2 |
| 4  | Leon  |       2 |
| 5  | Mario |       3 |
5 rows in set (0.00 sec)

I execute the following sql statement, but don't give the commit statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 1; 

After that I will open another session and execute the following sql statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 2;  

Now, the whole shell screen will block and not respond. After a while it will give me an error, similar to this one:

1205 Lock wait timeout exceeded; try restarting transaction

So, I am confused as to why does the InnoDB engine give me a row-level lock on the table? In this case, why does MySQL block my updates to another row?

  • ,Welcome to the StackExchange. What is MySQL storage engine? – Md Haidar Ali Khan Nov 6 '18 at 11:05

I apologize for the other answer you were given that just copied and pasted from the manual without being related to your question.

This is a common misconception- Innodb does not do full table locks to ensure isolation between transactions- however, it does apply row and gap locking by default to assure no phantom reads happen. Because your table is unindexed the only (inefficient) way to do gap locking is to setup individual lock in every row and every gap between ( for example if you try to insert a new row with id 0, it should get blocked too.

You have two options: create an index on id (probably a primary key is the right way) so only a single row is actually locked thanks to the new index, or change your default isolation level to read committed (SET SESSION transaction_isolation='READ-COMMITTED';).

This is a common question here on dba.stackexchange so here you have a more detailed explanation I wrote on another similar question: MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

However, in your case I strongly recommend you to go the route of creating the index.

  • Hi jynus, thank you, I think your reply will be more helpful. YES, I also be told that it will be resolved if i create a index. But unfortunatly in my case the id has already had the index, but still can not work: – Kris Nov 6 '18 at 23:07
  • Sorry, My bad, It work now, the id I am using is varchar, but I wrote it as "where id = 1" , So I missed the index searching at that time. and "where id = '1'" is working for me now, thanks a lots ! – Kris Nov 7 '18 at 7:38
  • Not having and index and not being able to use one have similar results. I had to suppose the first as I had no more data. I find strange however that if the literal it has to be casted as an index it doesn't work- I will try to reproduce it. – jynus Nov 8 '18 at 15:54

As per mysql documentation InnoDB Locking InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.

Shared and Exclusive Locks

  • A shared (S) lock permits the transaction that holds the lock to read a row.
  • An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

  • A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
  • A request by T2 for an X lock cannot be granted immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

For your further Ref here

  • Hi Khan, I was updating the different row in my case, not only for row r, I suppose that there are two rows, one is row R, another is row R1, what will happen in your instructions? – Kris Nov 6 '18 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.