Mysql transaction lock wait timeout Lock wait timeout exceeded; what to do

Problem Scenario

Environment in which the problem occurs:
1. Insert and update the same piece of data successively within the same transaction;
2. Multiple servers operate the same database;
3. High concurrency occurs instantaneously;

continuously throws exceptions, exception information:

org.springframework.dao.CannotAcquireLockException:

Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

The error may involve com.*.dao.mapper.PhoneFlowMapper.updateByPrimaryKeySelective-Inline

The error occurred while setting parameters

SQL:-----The following is the SQL statement and stack information--------

Cause analysis

at high In the case of concurrency, Spring transactions cause database deadlock, and subsequent operations time out and throw exceptions.

Mysql database adopts InnoDB mode. The default parameter: innodb_lock_wait_timeout sets the lock waiting time to 50s. Once the database lock exceeds this time, an error will be reported.

Solution

1. Use the following statement to find the data for the submitted transaction and kill the thread.

select * from information_schema.innodb_trx

2. Increase the lock waiting time, that is, increase the parameter value of the following configuration item, in seconds (s)

innodb_lock_wait_timeout=500

3. Optimize the stored process and avoid long waiting times for transactions.

Reference information

1. Lock waiting timeout. It is caused by the current transaction waiting for other transactions to release lock resources. You can find tables and statements competing for lock resources, optimize SQL, create indexes, etc. If that still doesn't work, you can appropriately reduce the number of concurrent threads.

2. The transaction times out while waiting to lock a certain table. It is estimated that the table is being locked by another process and has not been released.

You can use SHOW INNODB STATUS/G; to check the lock situation.

3. Search for a solution and add:

TransactionDeadLockDetectionTimeOut=10000 (set to 10 seconds) in the [ndbd default] area of ​​the management node. The default is 1200 (1.2 seconds)

4 , InnoDB will automatically detect deadlock and rollback, or terminate the deadlock situation.

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted , updated, or deleted.

######If the parameters innodb_table_locks=1 and autocommit=0, InnoDB will pay attention to table deadlocks and row-level locks at the MySQL level. Additionally, InnoDB does not detect MySQL Lock Tables commands and other storage engine deadlocks. You should set innodb_lock_wait_timeout to solve this situation. ###innodb_lock_wait_timeout is the timeout for Innodb to give up row-level locks. ###

The above is the detailed content of Mysql transaction lock wait timeout Lock wait timeout exceeded; what to do. For more information, please follow other related articles on the PHP Chinese website!