Mysql deadlock, waiting for resources, transaction lock, lock wait timeout exceeded; try restarting transaction
I have already learned about InnoDB. When the lock wait occurs, it will judge whether the timeout operation is needed according to the configuration of the parameter innodb_lock_wait_timeout. This document describes the viewing and analysis processing when the lock wait occurs.
Before the InnoDB Plugin, the current database request is generally viewed through the show full processlist (it is difficult to find the locked row record problem) and the show engine innodb status command, and then the lock in the current transaction is determined. With the development of mysql, a more convenient way has been provided to monitor the lock wait phenomenon in the database.
There are three tables under the information_schema: INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS (solving the problem method), through which you can more easily monitor the current transaction and analyze possible problems.
More commonly used columns:
Trx_id: the unique transaction ID inside the InnoDB storage engine
Trx_status: the status of the current transaction
Trx_status: the start time of the 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. When a deadlock is found to need to be rolled back, the smaller the weight, the value is rolled back.
Trx_mysql_thread_id: Process ID in MySQL, corresponding to the ID value in show processlist
Trx_query: SQL statement run by the transaction
Kill process ID; encountered a vehicle record in the work, select * from car for update or modify the value of a field, the error: Lock wait timeout exceeded; try restarting transaction solution, and fundamentally from the business logic code optimization For the operation of the database, I have encountered such a situation before. For example, if I have just modified this record and then modified it again, I will report this error and try to avoid it from the code and business level.