mysql 为何insert的时候会有lock wait timeout 异常

Question

执行单元测试（mvn clean test）时偶尔会遇到下面的异常信息

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

但是像这样的错误 不应该只是update才会有吗？ 见官方文档说明

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

但我明明是insert啊。 并且还只是偶尔会有这种情况， 且稍后就正常了。

所以不明白到底是怎么回事？ 怎么模拟这种情况呢？

Answer 1

I believe you already know that this phenomenon occurs when the table is locked. Lock wait timeout means that the transaction waits too long due to the lock table, and then the transaction is rolled back
I think your question is (why insert The operation will lock the table).

My explanation is as follows:
Generally speaking, this phenomenon will not occur under normal conditions, but it is different if you actively start the transaction (or do not actively submit the transaction), because the transaction operation requires a certain amount of time. time and there will be multiple instructions running. In this case, if another process also starts a transaction and locks the table, and it cannot be completed in a short time (the default is 50 seconds), then the first transaction will report Such an error and rollback.
So he prompts you to try restarting transaction in the error; let you re-execute