MySQL table lock wait
Today, an online business problem occurs because a table with a simulated auto-incrementing sequence is locked and services related to the table are affected.
Online Information:
1. This table has only one id field.
2. The id field is the primary key index.
3. This table has only one row of data and records the global maximum id.
4. A business stored procedure performs the id + 1 operation, obtains the value of the current id, and inserts a row of data into other related tables.
5. After the stored procedure is executed, commit
6. transaction isolation level: READ-COMMITTED
7. A service thread 102336 locks the table and uses show processlist to check that process 102336 is in sleep state.
8. Occasionally, other tread requests lock the table, waiting for timeout
9. After kill the process 102336, the service will return to normal.
Normally, this table will not be locked for a long time. Why is it hard to understand why the process has never released the lock resources? Normally, after the execution of the stored procedure is completed, it will either be submitted, or an exception occurs and exits and rollback.
-------------------------------------- Split line --------------------------------------
Install MySQL in Ubuntu 14.04
MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF
Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL
Build a MySQL Master/Slave server in Ubuntu 14.04
Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS
Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04
MySQL-5.5.38 universal binary Installation
-------------------------------------- Split line --------------------------------------
This article permanently updates the link address: