| Bug #50821 | Deadlock between LOCK TABLES and ALTER TABLE | ||
|---|---|---|---|
| Submitted: | 2 Feb 2010 9:59 | Modified: | 7 Mar 2010 1:03 |
| Reporter: | Jon Olav Hauglid | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S1 (Critical) |
| Version: | mysql-next-4284 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[2 Feb 2010 12:09]
Jon Olav Hauglid
Repeatable MTR-test case. Execute this with --mysqld=--log-bin=OFF. create table t1(id int); create table t2(id int); connect (con2, localhost, root); start transaction; select * from t1; connection default; --send alter table t1 add column j int; connection con2; --sleep 1 insert into t2 select * from t1; commit; connection default; --reap drop table t1, t2; disconnect con2;
[3 Feb 2010 15:47]
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/99110 3070 Jon Olav Hauglid 2010-02-03 Bug #50821 Deadlock between LOCK TABLES and ALTER TABLE This was a deadlock between ALTER TABLE and another DML statement. ALTER TABLE would wait trying to upgrade its lock to MDL_EXCLUSIVE and the DML statement would wait trying to acquire a TL_READ_NO_INSERT table level lock. This could happen if one connection first acquired a MDL_SHARED_READ lock on a table. In another connection ALTER TABLE is then started. ALTER TABLE eventually blocks trying to upgrade to MDL_EXCLUSIVE, but while holding a TL_WRITE_ALLOW_READ table level lock. If the first connection then tries to acquire TL_READ_NO_INSERT, it will block and we have a deadlock since neither connection can proceed. This patch fixes the problem by allowing TL_READ_NO_INSERT locks to be granted if another connection holds TL_WRITE_ALLOW_READ on the same table. This will allow the DML statement to proceed such that it eventually can release its MDL lock which in turn makes ALTER TABLE able to proceed. Test case added to lock_sync.test.
[4 Feb 2010 9:00]
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/99216 3073 Jon Olav Hauglid 2010-02-04 Bug #50821 Deadlock between LOCK TABLES and ALTER TABLE This was a deadlock between ALTER TABLE and another DML statement (or LOCK TABLES ... READ). ALTER TABLE would wait trying to upgrade its lock to MDL_EXCLUSIVE and the DML statement would wait trying to acquire a TL_READ_NO_INSERT table level lock. This could happen if one connection first acquired a MDL_SHARED_READ lock on a table. In another connection ALTER TABLE is then started. ALTER TABLE eventually blocks trying to upgrade to MDL_EXCLUSIVE, but while holding a TL_WRITE_ALLOW_READ table level lock. If the first connection then tries to acquire TL_READ_NO_INSERT, it will block and we have a deadlock since neither connection can proceed. This patch fixes the problem by allowing TL_READ_NO_INSERT locks to be granted if another connection holds TL_WRITE_ALLOW_READ on the same table. This will allow the DML statement to proceed such that it eventually can release its MDL lock which in turn makes ALTER TABLE able to proceed. Note that TL_READ_NO_INSERT was already partially compatible with TL_WRITE_ALLOW_READ as the latter would be granted if the former lock was held. This patch just makes the opposite true as well. Also note that since ALTER TABLE takes an upgradable MDL lock, there will be no starvation of ALTER TABLE statements by statements acquiring TL_READ or TL_READ_NO_INSERT. Test case added to lock_sync.test.
[4 Feb 2010 10:35]
Jon Olav Hauglid
Pushed to mysql-next-4284.
[4 Feb 2010 10:38]
Jon Olav Hauglid
Setting the bug to Closed since it was not reproducable outside next-4284 which is an internal tree.
[16 Feb 2010 16:47]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20100210211106-nq8ztcq2z9o4csit) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:56]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20100204090036-bu3x1qwj4wjg3xd6) (pib:16)
[6 Mar 2010 11:01]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:03]
Paul DuBois
No changelog entry needed.
Description: Concurrent LOCK TABLE t1 and ALTER TABLE t1 can deadlock. LOCK TABLE: #0 pthread_cond_wait@@GLIBC_2.3.2 () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:261 #1 0x0000000000b57da3 in safe_cond_wait (cond=0x127d408, mp=0x127d088, file=0xd740e3 "thr_lock.c", line=448) at thr_mutex.c:237 #2 0x0000000000b55204 in wait_for_lock (wait=0x127d0f0, data=0x7ffff0001420, in_wait_list=0 '\000') at thr_lock.c:448 #3 0x0000000000b55e49 in thr_lock (data=0x7ffff0001420, owner=0x12860b0, lock_type=TL_READ_NO_INSERT) at thr_lock.c:786 #4 0x0000000000b56917 in thr_multi_lock (data=0x7ffff00014f0, count=1, owner=0x12860b0) at thr_lock.c:1055 #5 0x00000000006a651f in mysql_lock_tables (thd=0x12852b0, tables=0x7ffff00014c0, count=1, flags=32, need_reopen=0x7ffff7e55e5f) at lock.cc:320 #6 0x0000000000712fa1 in lock_tables (thd=0x12852b0, tables=0x7ffff00009e0, count=1, flags=32, need_reopen=0x7ffff7e55e5f) at sql_base.cc:5425 #7 0x0000000000712680 in open_and_lock_tables_derived (thd=0x12852b0, tables=0x7ffff00009e0, derived=false, flags=32, prelocking_strategy=0x7ffff7e56570) at sql_base.cc:5110 #8 0x00000000006c418e in mysql_execute_command (thd=0x12852b0) at sql_parse.cc:3338 #9 0x00000000006ca731 in mysql_parse (thd=0x12852b0, inBuf=0x7ffff0000950 "lock table t1 read", length=18, found_semicolon=0x7ffff7e57a28) at sql_parse.cc:5592 ALTER TABLE: #0 pthread_cond_timedwait@@GLIBC_2.3.2 () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S:220 #1 0x0000000000b57f5d in safe_cond_timedwait (cond=0x122f3e0, mp=0x122f378, abstime=0x7ffff7e95500, file=0xd0acfe "mdl.cc", line=802) at thr_mutex.c:275 #2 0x00000000008d9629 in MDL_context::timed_wait (this=0x122f310, timeout=1) at mdl.cc:802 #3 0x00000000008da45f in MDL_context::acquire_lock_impl (this=0x122f310, mdl_request=0x7ffff7e955f0) at mdl.cc:1489 #4 0x00000000008da952 in MDL_context::upgrade_shared_lock_to_exclusive ( this=0x122f310, mdl_ticket=0x12af650) at mdl.cc:1639 #5 0x000000000070d3ac in wait_while_table_is_used (thd=0x122f240, table=0x12a8320, function=HA_EXTRA_PREPARE_FOR_RENAME) at sql_base.cc:2148 #6 0x000000000082de09 in mysql_alter_table (thd=0x122f240, new_db=0x12bd5f0 "test", new_name=0x1284a40 "t1", create_info=0x7ffff7e96ee0, table_list=0x1284240, alter_info=0x7ffff7e974a0, order_num=0, order=0x0, ignore=false) at sql_table.cc:7303 #7 0x00000000006c200f in mysql_execute_command (thd=0x122f240) at sql_parse.cc:2630 #8 0x00000000006ca731 in mysql_parse (thd=0x122f240, inBuf=0x12a7b50 "alter table t1 engine = memory", length=30, found_semicolon=0x7ffff7e98a28) at sql_parse.cc:5592 is_deadlock() returns false for ALTER TABLE and it just continues to loop, waiting to upgrade the lock. How to repeat: Non-deterministic MTR test case: create table t1(id int); connect (con2, localhost, root); let $try = 10000; while ($try) { connection default; --send alter table t1 engine = memory connection con2; lock table t1 read; unlock tables; connection default; --reap dec $try; } drop table t1; disconnect con2;