MySQL deadlock, wait for resources, transaction lock, lock wait timeout exceeded; Try restarting Transaction solution

The previous understanding of InnoDB on the occurrence of lock waiting, will be based on the configuration of the parameters Innodb_lock_wait_timeout, determine whether to do a timeout operation, this document describes in the presence of lock waiting time to view and analyze processing;

Before InnoDB plugin, it is common to see the current database request through Show full processlist (it is difficult to find the locked line to record the problem) and the show Engine InnoDB status command. It then determines the condition of the lock in the current transaction. With the development of MySQL, more convenient methods have been provided to monitor the lock waiting in the database.

There are three tables under Information_schema : Innodb_trx, Innodb_locks, innodb_lock_waits (Problem solving), through these three sheets, You can more easily monitor your current transactions and analyze possible problems.

Comparison of commonly used columns:

Trx_id:innodb The only thing ID inside the storage engine
Trx_status: Status of the current transaction
Trx_status: Start time of transaction
TRX_REQUESTED_LOCK_ID: The lock ID of the waiting transaction
Trx_wait_started: The start time of the transaction wait
Trx_weight: The weight of the transaction, which reflects the number of rows modified and locked by a transaction, the smaller the weight value is rolled back when a deadlock is found to be rolled back
The process ID in Trx_mysql_thread_id:mysql, corresponding to the ID value in show processlist
Trx_query: SQL statement run by transaction

Kill process ID; A vehicle record was encountered at work, select * from car for update or to modify the value of a field, error: Lock wait timeout exceeded; Try restarting transaction solution, in addition to fundamentally from the Business logic code optimization of the operation of the database, have encountered such a situation before, such as just modified this record, and then modify again, will report this error, from the code and business level to avoid.

MySQL deadlock, wait for resources, transaction lock, lock wait timeout exceeded; Try restarting Transaction solution