why mysql insert could cause lock wait timeout exception

Question

when execute unit test(mvn test) sometimes there is below exception

org.springframework.dao.CannotAcquireLockException:

### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.foo.dao.mapper.TestMapper.insertSuccesfulPaymentOrder-Inline
### The error occurred while setting parameters
### SQL: insert into order(order_seq,note,user_id,product_id, pay_status) values(uuid(),'',?,?,1)
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

and from mysql document I know

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

So I think only when update some row could have this exception, but my is insert operation, why still have this exception? And how could I reproduce it directly in mysql session?

Answer 1

Regardless the ORM's usage of get_lock() the default value of 50 seconds would imply that there is another long running query which may be undesirable for a variety of reasons. This is especially the case if you are getting a lock wait timeout exceeded on unit tests on a development db instance.

So, you are left to make the problem reproducible and determine which query (not the insert) is culpable and has a lock.

Increase the default timeout to give enough interactive time on the mysql cli. Run the tests and break on the one that gets the lock wait timeout.

SELECT * FROM `information_schema`.`innodb_locks`;

Will show the current locks.

select * from information_schema.innodb_trx where trx_id = [lock_trx_id];

Will show the involved transactions

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where id = [trx_mysql_thread_id];

Will show the involved connection and may show the query whose lock results in the lock wait timeout. Maybe there is an uncommitted transaction.