A special deadlock in MySQL

Looking back at the CASE of online database (5.1.68), a large number of thread_running occurs during the peak period. It is found that the thread is in the deadlock State and only one table is involved, and there is only one row:

Table

('Id' smallint (5) unsigned not null default '0 ',

'Key' varchar (32) not null,

'Value' varchar (32) not null,

'Time' int (10) unsigned not nul) engine = innodb;

The database version is 5.1.68 innodb-plugin. The original logic is:

Lock table awrite; read and update the table; unlocktable;

Later, the logic begin; select id, key, value from a for update; commit;

In the case of a problem, these two logics exist at the same time, resulting in deadlocks at the MySQL Server layer and the Storage layer;

A sequence is as follows:

Session1:

Begin;

Select * from a for update; -- lock everyrow in execlusive mode

Session2:

Lock table a write; -- lock table a in server

Session2:

Update a set key = xxx where id = xxx -- holdserver lock and acquire row lock

Session1:

Same as above; -- hold row lock andacquire server lock

A deadlock occurs because only a part of the RD release code is released and rolled back and forth. Another Solution is to change the table to the MyISAM engine, which breaks the row lock;

However, this problem is solved by introducing MDL in 5.5. When session2 executes lock table t write, it will be blocked on: Waiting for table metadata lock, breaking the deadlock condition.

This article is from "MIKE's old blog" blog, please be sure to keep this source http://boylook.blog.51cto.com/7934327/1298750