MySQL Bugs: #21281: Pending write lock is incorrectly removed when its statement being KILLed

Bug #21281	Pending write lock is incorrectly removed when its statement being KILLed
Submitted:	25 Jul 2006 18:07	Modified:	25 Aug 2007 16:07
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	4.1.19, 5.0.40	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any
Tags:	bfsm_2007_05_31, bfsm_2007_06_21, rt_q1_2007

Description:
If a table t1 is locked by an explicit LOCK TABLE t1 READ, and an INSERT ... ON DUPLICATE KEY statement in another thread places a write lock in the queue, statements blocked by the INSERT's lock are not allowed to proceed if the INSERT is killed.

How to repeat:
#THREAD 1
create table foo2 (a int, b int, unique key foo2$a (a));
lock table foo2 read;

#THREAD 2
insert into foo2 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;

#THREAD 3
show table status like 'foo2';

#THREAD 1
Kill the INSERT thread

mysql 4.1.19-max (root) [test]> show processlist;
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                                                         |
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
| 21 | root | localhost | test | Query   |  839 | Locked | insert into foo2 values (1, 2) ON DUPLICATE KEY UPDATE b = 2 |
| 22 | root | localhost | test | Query   |  826 | Locked | show table status like 'foo2'                                |
| 23 | root | localhost | test | Sleep   |   20 |        | NULL                                                         |
| 24 | root | localhost | test | Query   |    0 | NULL   | show processlist                                             |
+----+------+-----------+------+---------+------+--------+--------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql 4.1.19-max (root) [test]> kill 21;
Query OK, 0 rows affected (0.00 sec)

mysql 4.1.19-max (root) [test]> show processlist;
+----+------+-----------+------+---------+------+--------+-------------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                          |
+----+------+-----------+------+---------+------+--------+-------------------------------+
| 22 | root | localhost | test | Query   |  867 | Locked | show table status like 'foo2' |
| 23 | root | localhost | test | Sleep   |   61 |        | NULL                          |
| 24 | root | localhost | test | Query   |    0 | NULL   | show processlist              |
+----+------+-----------+------+---------+------+--------+-------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
Killing the INSERT should allow the SHOW TABLE STATUS to execute -or- the SHOW TABLE STATUS should be able to execute even while the INSERT is blocked.

In 5.0, SHOW TABLE STATUS is not affected by the INSERT.

Actually this problem affects not only INSERT ... ON DUPLICATE KEY UPDATE but any statement that tries to take normal write lock on the table (i.e. write lock which is not low-priority write lock and not concurrent insert write lock). So REPLACE, UPDATE and DELETE statements are affected as well.

To reflect this I have changed bug's synopsis.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31996

ChangeSet@1.2575, 2007-08-02 09:56:00+04:00, dlenev@mockturtle.local +3 -0
  Proposed fix for bug #21281 "Pending write lock is incorrectly removed
  when its statement being KILLed".
  
  When statement which was trying to obtain write lock on then table and
  which was blocked by existing read lock was killed, concurrent statements
  that were trying to obtain read locks on the same table and that were
  blocked by the presence of this pending write lock were not woken up and
  had to wait until this first read lock goes away.
  
  This problem was caused by the fact that we forgot to wake up threads
  which pending requests could have been satisfied after removing lock
  request for the killed thread.
  
  The patch solves the problem by waking up those threads in such situation. 
  
  Questions for reviewers are marked by QQ.
  
  QQ: Should we fix this bug in 5.0 ?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32114

ChangeSet@1.2489, 2007-08-05 13:17:07+04:00, dlenev@mockturtle.local +1 -0
  Fix for bug #21281 "Pending write lock is incorrectly removed when its
  statement being KILLed".
  
  When statement which was trying to obtain write lock on then table and
  which was blocked by existing read lock was killed, concurrent statements
  that were trying to obtain read locks on the same table and that were
  blocked by the presence of this pending write lock were not woken up and
  had to wait until this first read lock goes away.
  
  This problem was caused by the fact that we forgot to wake up threads
  which pending requests could have been satisfied after removing lock
  request for the killed thread.
  
  The patch solves the problem by waking up those threads in such situation.
  
  Test for this bug will be added to 5.1 only as it has much better
  facilities for its implementation. Particularly, by using I_S.PROCESSLIST
  and wait_condition.inc script we can wait until thread will be blocked on
  certain table lock without relying on unconditional sleep (which usage
  increases time needed for test runs and might cause spurious test
  failures on slower platforms).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32116

ChangeSet@1.2576, 2007-08-05 13:55:37+04:00, dlenev@mockturtle.local +2 -0
  Added test for bug #21281 "Pending write lock is incorrectly removed
  when its statement being KILLed". The bug itself was fixed by separate
  patch in 5.0 tree.

Pushed into 5.0.48

Pushed into 5.1.22-beta

Noted in 5.0.48, 5.1.22 changelogs.

Read lock requests that were blocked by a pending write lock request
were not allowed to proceed if the statement requesting the write
lock was killed.