Problem phenomenon
The interface response time is too long, and it takes dozens of seconds to return an error message. The error Lock wait timeout exceeded; try restarting transaction appears in the background log.
<-- java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359) at com.trs.components.wcm.publish.element.PublishContentDocumentImpl.setPublishTimeAndURL(PublishContentDocumentImpl.java:851) at com.trs.components.common.publish.domain.publisher.PageGenerator.updateContentPublishTime(PageGenerator.java:236) at com.trs.components.common.publish.domain.publisher.PageGenerator.generateDetail(PageGenerator.java:216) at com.trs.components.common.publish.domain.taskdispatch.PageTaskWorker.executeTask(PageTaskWorker.java:278) at com.trs.components.common.publish.domain.taskdispatch.PageTaskWorker.run(PageTaskWorker.java:153) at com.trs.components.common.publish.domain.taskdispatch.ThreadPool$Worker.run(ThreadPool.java:56)
Cause of the problem
- In the case of high concurrency, Spring transactions cause database deadlocks, 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
Method 1: Adjust timeout parameters
The official mysql documentation is as follows:
When the lock wait times out, the innodb engine reports this error, and the statement that waited for too long is rolled back (not the entire transaction). If you want the SQL statement to wait longer for other transactions to complete, you can increase the value of the parameter innodb_lock_wait_timeout configuration. If there are too many long-running locked transactions, you can reduce the value of innodb_lock_wait_timeout. On particularly busy systems, you can reduce concurrency.
The maximum time (in seconds) that an InnoDB transaction waits for a row-level lock. It will give up after this time. The default value is 50 seconds. A transaction A tries to access a row of data, but this row of data is being locked by another innodb transaction B. At this time, transaction A will wait for transaction B to release the lock. If the wait exceeds the value set by innodb_lock_wait_timeout, an error ERROR will be reported.
1205 (HY000):
innodb_lock_wait_timeout is a dynamic parameter, the default value is 50 seconds, the minimum value is 1 second, and the maximum value is 1073741824;
set innodb_lock_wait_timeout=1500 is equivalent to set session and only affects the current sessio. As a global modification method, set global innodb_lock_wait_timeout=1500 will only affect the session opened after the modification and cannot change the current session.
mysql> set GLOBAL innodb_lock_wait_timeout=1500;
Method 2: Solve the deadlock
View the current process of the database
show processlist will display the list of SQL statements currently being executed and find the ID corresponding to the statement that consumes the most resources.
mysql> show processlist; + --------- + ------ + ------------------- + ---------- -------- + --------- + ------- + ------- + --------------- --- + | Id | User | Host | db | Command | Time | State | Info | + --------- + ------ + ------------------- + ---------- -------- + --------- + ------- + ------- + --------------- --- + | 3205081 | root | 172.19.2.8:50317 | ******** | Sleep | 16485 | | NULL | | 3210354 | root | 172.19.2.8:51066 | information_schema | Sleep | 3569 | | NULL | | 3210630 | root | 172.19.2.12:61845 | ******** | Query | 0 | init | show processlist | + --------- + ------ + ------------------- + ---------- -------- + --------- + ------- + ------- + --------------- --- + 10 rows in set (0.00 sec)
View current locks and transactions
In 5.5, three tables about locks (inndodb engine) were added to the information_schema library:
- innodb_trx ## All currently running transactions
- innodb_locks ## Current locks, view the transaction being locked
- innodb_lock_waits ## Correspondence of lock waits, view transactions waiting for locks
All currently running transactions
mysql> SELECT * FROM information_schema.INNODB_TRX;
:::tips
ps: Manual deletion based on INNODB_TRX
Check the INNODB_TRX transaction table to see if trx_mysql_thread_id is in the sleep thread in show full processlist (the trx_mysql_thread_id field in the INNODB_TRX table corresponds to the Id in show full processlist); if it is, it means that the sleep thread transaction has not been committed or rolled back. Instead, it is stuck and requires us to delete it manually.
Just delete the found trx_mysql_thread_id manually. As shown above, trx_mysql_thread_id is 1816
:::
The current lock
mysql> SELECT * FROM information_schema.INNODB_LOCKs;
Correspondence between lock waits
mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;
Check to see if there is a transaction thread that is being locked, and see if the ID is in the sleep thread in show processlist. If so, it proves that the sleep thread transaction has not been committed or rolled back but has been stuck.
Query the specific SQL that generated the lock
Based on the specific SQL, you can see whether there is a deadlock, and you can determine what business was performed and whether it can be killed;
select
a.trx_id transaction id,
a.trx_mysql_thread_id transaction thread id,
a.trx_query transaction sql
from
INFORMATION_SCHEMA.INNODB_LOCKS b,
INFORMATION_SCHEMA.innodb_trx a
where
b.lock_trx_id=a.trx_id;
Kill deadlock transactions
Query the thread ID corresponding to all locked transactions (note that it is the thread ID, not the transaction ID), generate the statement temporary file of the MySQL connection that needs to be processed through the connection information in the information_schema.processlist table, and then execute the statement generated in the temporary file instruction.
mysql> select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3205081; |
| KILL 3210354; |
| KILL 3210630; |
+------------------------+
18 rows in set (0.00 sec)
If there are too many, you can export them to txt and execute them in batches.
mysql> select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id into outfile '/tmp/kill.txt';
The KILL command allows optional CONNECTION or QUERY modifiers: KILL CONNECTION is the same as KILL without modifiers: it terminates the connection associated with the given thread_id. KILL QUERY terminates the statement currently executing on the connection, but leaves the connection intact. The syntax format of the KILL command is as follows:
KILL [CONNECTION | QUERY] thread_id
Run kill command
mysql> kill 3205081; Query OK, 0 rows affected (0.00 sec) mysql> kill 3210354; Query OK, 0 rows affected (0.00 sec)
Reference
Lock wait timeout exceeded: http://blog.itpub.net/29654823/viewspace-2150471/