Shulou(Shulou.com)06/01 Report--

Editor to share with you what to do about the Mysql thing lock waiting for timeout Lock wait timeout exceeded;. I hope you will get something after reading this article. Let's discuss it together.

Problem scenario

The environment in which the problem occurs:

1. Insert and update the same data successively within the same transaction

2. Multiple servers operate the same database

3. High concurrency occurs instantly.

There is a constant exception throw, exception message:

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:- is followed by SQL statement and stack information-

Cause analysis

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

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

Solution

1. Use the following statement to find the data that commits the transaction, and kill can drop this thread.

Select * from information_schema.innodb_trx

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

Innodb_lock_wait_timeout=500

3. Optimize the stored procedure to avoid waiting for too long.

Reference information

1. Lock wait timed out. This is caused by the current transaction waiting for other transactions to release lock resources. You can find tables and statements that compete for lock resources, optimize SQL, create indexes, and so on. If it still doesn't work, you can appropriately reduce the number of concurrent threads.

2. The transaction timed out while waiting for a table to be locked. It is estimated that the table is locked by another process and has not been released.

You can use SHOW INNODB STATUS/G; to look at the lock.

3. Search for a solution and add: in the [ndbd default] area of the management node:

TransactionDeadLockDetectionTimeOut=10000 (set to 10 seconds) defaults to 1200 (1.2 seconds)

4. InnoDB automatically detects deadlocks for rollback or terminates deadlocks.

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 parameter innodb_table_locks=1 and autocommit=0, InnoDB pays attention to table deadlocks and row-level locks at the MySQL level. In addition, InnoDB does not detect MySQL's Lock Tables commands and other storage engine deadlocks. You should set up innodb_lock_wait_timeout to solve this situation.

Innodb_lock_wait_timeout is the timeout for Innodb to discard row-level locks.

After reading this article, I believe you have a certain understanding of "Mysql thing lock waiting for timeout Lock wait timeout exceeded;". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.