| Bug #21281 | Pending write lock is incorrectly removed when its statement being KILLed | ||
|---|---|---|---|
| Submitted: | 25 Jul 2006 20:07 | Modified: | 25 Aug 2007 18:07 |
| Reporter: | Kolbe Kegel | ||
| Status: | Closed | ||
| Category: | Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 4.1.19, 5.0.40 | OS: | Linux (Linux) |
| Assigned to: | Dmitri Lenev | Target Version: | |
| Tags: | bfsm_2007_06_21, rt_q1_2007, bfsm_2007_05_31 | ||
[1 Aug 2007 17:48]
Dmitri Lenev
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.
[1 Aug 2007 17:53]
Dmitri Lenev
To reflect this I have changed bug's synopsis.
[2 Aug 2007 7:51]
Bugs System
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 ?
[5 Aug 2007 11:12]
Bugs System
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).
[5 Aug 2007 11:50]
Bugs System
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.
[9 Aug 2007 15:17]
Bugs System
Pushed into 5.0.48
[9 Aug 2007 15:18]
Bugs System
Pushed into 5.1.22-beta
[25 Aug 2007 18:07]
Paul DuBois
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.
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.