MySQL table lock wait

Today's online business encounters a problem because a table with a simulated self-increment sequence is locked and the business involved in the table is affected.

On-Line conditions:

1. This table has only one ID field.

2. ID field as primary key index

3, the table has only one row of data, record the global maximum ID

4. A business stored procedure operation performs a id+1 operation and gets the value of the current ID, inserting a row of data into another related table

5. The stored procedure is executed, and the final commit is completed.

6, the Thing isolation level is read-committed

7, a business thread 102336 lock the table, using Show Processlist view, Process 102336 is in sleep state

8, occasionally there will be other tread request lock the table, are waiting for timeout

9, after the kill process 102336, the business returned to normal

Normal This table is not locked for a long time, why this process has not freed the lock resource, difficult to understand, normally after the execution of the stored procedure, either commit, or an abnormal exit and rollback.

650) this.width=650; "title=" 1.png "alt=" wkiol1qaekhi0f_vaadgcan6plg937.jpg "src=" http://s3.51cto.com/wyfs02/M00/ 47/d8/wkiol1qaekhi0f_vaadgcan6plg937.jpg "/>

This article is from the "composer" blog, make sure to keep this source http://zuoqujia.blog.51cto.com/9151800/1546672

MySQL table lock wait