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
• Status: Won't fix
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: mysql-6.0-codebase-bugfixing
• OS: Any
• CPU Architecture: Any
• Tags: deadlock

[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".