Mysql lock wait timeout error

Question

I have a scenario in my app where simple INSERT statement under transaction is blocking DELETE statement on different rows in MySQL and eventually, DELETE statement session times out with lock time out error.

I have tried to explain the situation in a simple scenarios below. Note, adding index on the column helps as bit however, if I have 2 columns in WHERE clause of DELETE, I still see lock wait timeout. Also please note that after I start transaction, I need to do few other processing and hence transaction takes few mins to commit or roll back. It is hard to believe that even if I delete different records than what I am trying to insert, MySQL blocks it.

Session 1:-

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;

 +-----------------------+----------------+------------------------+
 | @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
 +-----------------------+----------------+------------------------+
 | READ-COMMITTED        | READ-COMMITTED | READ-COMMITTED         |
 +-----------------------+----------------+------------------------+ 

1 row in set (0.02 sec)` 

mysql> create table testtab(col1 int, col2 int);
Query OK, 0 rows affected (0.53 sec) 
mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec) 
mysql> INSERT INTO testtab values(1,1); 
Query OK, 1 row affected (0.00 sec) 
mysql> INSERT INTO testtab values(2,2); 
Query OK, 1 row affected (0.00 sec)

Session 2:-

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation; 
+-----------------------+----------------+------------------------+ 
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation | 
+-----------------------+----------------+------------------------+ 
| READ-COMMITTED        | READ-COMMITTED | READ-COMMITTED         | 
+-----------------------+----------------+------------------------+
 1 row in set (0.00 sec) 
mysql> DELETE FROM testtab where col1=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Any suggestions ?

Solarflare · Accepted Answer · 2017-07-20 19:15:28Z

This is a result of how InnoDB-locking works. To quote the manual,

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

It's also worth noting that

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

So what happens in your case is that the two insert add two locked rows to the table. Because there is no appropriate index, the delete will now scan the whole table. It has to lock every row it encounters, but to do that it has to wait for the locks created by the insert has been lifted (which doesn't happen in time).

Adding an index on col1 will solve this, as the delete will only have to consider (and lock) rows with col1 = 3 that it can find using the index, and thus it will have no overlap with the rows inserted by the other transaction.

If you have problems finding a good index for your "two-column-where-condition", you should add more details about it, there might be an index that fits that situation.

This will of course not prevent every situation (e.g. if you actually want to modify the same row), so it is usually a good idea to keep your transactions as short as possible, to be prepared to wait that long and/or to be prepared to repeat the transaction if required.

This is by the way not a complete description of InnoDB locks, just the part that explains your situation.

for the similar example if I want to see blocking query ( show full processlist shows query for only session 2 ) , what options do I have. I tried show engine innodb status , full process list etc, however nothing seems to be showing me the INSERT statement which is actually causing transaction to run for longer time.

Collectives™ on Stack Overflow

Mysql lock wait timeout error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related