[Mysql] mysql lock waits for Lock wait timeout exceeded; try restarting transaction

Uncle Cthulhu 2022-11-24 21:24:08 阅读数:378

mysqlmysqllockwaitslock
# Query the global waiting transaction lock timeoutSHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';# Set the global waiting transaction lock timeoutSET GLOBAL innodb_lock_wait_timeout=100;# Query the current session waiting for transaction lock timeoutSHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Problem scenario
Problem occurrence environment:
1. Insert and update the same piece of data successively in the same transaction;
2. Multiple servers operate the same database;
3. InstantaneousThere is a high concurrency phenomenon;

Cause analysis
In the case of high concurrency, Spring transactions cause database deadlock, and subsequent operations time out and throw exceptions.
The Mysql database adopts the 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.

Solutions

1. Run the following command to find the data of the submitted transaction and kill the thread to solve it

select * from information_schema.innodb_trxkill thread_id;

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

innodb_lock_wait_timeout=500

3. Optimize stored procedures and avoid long waiting times for transactions.

As shown in the picture below, the ID is killed

copyright:author[Uncle Cthulhu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/328/202211242120377879.html