MySQL uses essay one

In the practice project, MySQL row-level locks are not used. Instead, the use of inventory increases and decreases through RABBITMQ queues. Two tables update data by displaying the Create transaction implementation. During the testing process, multiple people operate the order, resulting in a transaction lock timeout, which is used on the MySQL command line

Show full processlist;

There are many update processes in sleep that are found in MySQL. It is possible to determine that the MySQL connection handle could not be closed due to a logical interruption in the transaction or a code error, that is, the transaction lock is not closed.

Related information:

 full processlist;

Used before InnoDB plugin, it is difficult to find the locked line to record the problem, Shou engine InnoDB status can only view the current database requests, as well as the current transaction in the case of locks.

There are currently three tables in the INFORMATION_SCHEMA library available from the MySQL database to solve the problem: Innodb_trx, Innodb_locks, Innodb_lock_waits.

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; The job encountered a query record, select * from the car for update or to modify a field value, 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 uses essay one