Bug #48609 deadlock when running concurrent LOCK TABLE, ALTER TABLE,DELETE LOW_PRIORITY
Submitted: 6 Nov 2009 21:41 Modified: 4 Apr 2011 18:56
Reporter: Matthias Leich Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:mysql-6.0-codebase-bugfixing OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock
Triage: Triaged: D2 (Serious)

[6 Nov 2009 21:41] Matthias Leich 
Description:
Found when running a RQG test with 30 threads and a derivate
of WL5004_sql.yy on mysql-6.0-codebase-bugfixing
revno: 3694 Tue 2009-11-03
============================================================

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id  | User | Host            | db   | Command | Time | State      | Info                                                                                                 |
+-----+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
|   1 | root | localhost:54449 | test | Sleep   | 2604 | NULL       | NULL                                                                                                 |
| 161 | root | localhost:55146 | test | Query   | 2548 | Table lock | LOCK TABLES testdb_S . t1_all_2_S  AS a IN EXCLUSIVE MODE NOWAIT , testdb_S . t1_all_1_S  AS h LOW_P |
| 162 | root | localhost:55156 | test | Query   | 2545 | Table lock | LOCK TABLES testdb_S . t1_all_2_S  AS a IN SHARE MODE  , testdb_S . t1_all_2_S  AS c IN SHARE MODE   |
| 165 | root | localhost:55161 | test | Query   | 2541 | Table lock | DELETE LOW_PRIORITY  FROM testdb_S . t1_all_2_S  WHERE `pk` + SLEEP( 0.5 * 0.28 * 1 ) = 3            |
| 167 | root | localhost:55186 | test | Query   | 2544 | Table lock | ALTER TABLE testdb_S . t1_all_2_S  CHANGE COLUMN my_column `int` INT                                 |
| 422 | root | localhost       | NULL | Query   |    0 | NULL       | SHOW ROCESSLIST                                                                                     |
+-----+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

I killed the server via kill -SIGSEGV <server process> and will
upload the backtrace.

My environment:
- MySQL compiled from source
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)

How to repeat:
not available
[6 Nov 2009 21:42] Matthias Leich 
backtrace

Attachment: k1 (application/octet-stream, text), 32.18 KiB.
[20 Nov 2009 11:56] Philip Stoev 
I am setting this to Verified with the expectation that this deadlock can be debugged based on stack traces alone. If this is not the case, please set the bug back to "Need feedeback". Thank you.
[21 Jan 2010 10:12] Konstantin Osipov 
A transactional lock table issue.
[4 Apr 2011 18:56] Dmitry Lenev 
Since was bug involves WL#3561 "Transactional LOCK TABLES" and this worklog got cancelled I am closing this bug as "Won't fix".