Bug #12739 Deadlock in multithread env during creating/droping trigger on innodb table
Submitted: 23 Aug 2005 0:15 Modified: 20 Oct 2005 18:09
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:the latest 5.0.12pre OS:Linux (Linux)
Assigned to: Dmitry Lenev

[23 Aug 2005 0:15] Alexey Stroganov
Description:
Running following test case in multithread environment(stress test) for some
time (less than 1-2 min) for at least 2 threads will lead to deadlock.

This deadlock happens only if table was created with ENGINE=InnoDB. With MyISAM test case passed ok.

#Output of mysqladmin proc:
+-----+------+-----------+------+---------+------+-------+-----------------------------------------------------------------+
| 683 | root | localhost |      | Query   | 0    |       | show processlist                                                |
| 930 | root | localhost | test | Query   | 10   |       | create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2 |
| 931 | root | localhost | test | Query   | 10   |       | create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2 |
+-----+------+-----------+------+---------+------+-------+-----------------------------------------------------------------+

#Table definition 
create table tb3 (f1 int) engine = innodb;

#Test case
use test;

create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2;

--disable_warnings
drop trigger tr1;
--enable_warnings

#backtraces for two active threads that I got after killing of mysqld with signal 11

(gdb) thread 941
[Switching to thread 941 (Thread 1116134320 (LWP 29940))]#0  0xffffe410 in ?? ()
(gdb) bt
#0  0xffffe410 in ?? ()
#1  0x4286c700 in ?? ()
#2  0x00000045 in ?? ()
#3  0x00000000 in ?? ()
#4  0x4004cccc in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib/tls/libpthread.so.0
#5  0x0840ce7f in safe_cond_timedwait (cond=0x8609660, mp=0x86091c0, abstime=0x4286c700,
    file=0x84a7b40 "sql_base.cc", line=4950) at thr_mutex.c:240
#6  0x081dd753 in remove_table_from_cache (thd=0x8c6be08, db=0x8c95c40 "test",
    table_name=0x8c95a58 "tb3", flags=2) at sql_base.cc:4950
#7  0x08287763 in wait_while_table_is_used (thd=0x8c6be08, table=0x8c953d0,
    function=HA_EXTRA_PREPARE_FOR_DELETE) at sql_table.cc:1867
#8  0x082877df in close_cached_table (thd=0x8c6be08, table=0x8c953d0) at sql_table.cc:1893
#9  0x082eee79 in mysql_create_or_drop_trigger (thd=0x8c6be08, tables=0x8ca2530, create=true)
    at sql_trigger.cc:188
#10 0x081b0ddd in mysql_execute_command (thd=0x8c6be08) at sql_parse.cc:4513
#11 0x081b3309 in mysql_parse (thd=0x8c6be08,
    inBuf=0x8ca2450 "create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2",
    length=63) at sql_parse.cc:5444
#12 0x081a88f9 in dispatch_command (command=COM_QUERY, thd=0x8c6be08,
    packet=0x8c90379 "create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2",
    packet_length=64) at sql_parse.cc:1664
#13 0x081a80c6 in do_command (thd=0x8c6be08) at sql_parse.cc:1463
#14 0x081a71a6 in handle_one_connection (arg=0x8c6be08) at sql_parse.cc:1116
#15 0x4004aaa7 in start_thread () from /lib/tls/libpthread.so.0
#16 0x4017bc2e in clone () from /lib/tls/libc.so.6

(gdb) thread 940
[Switching to thread 940 (Thread 1115732912 (LWP 29939))]#0  0xffffe410 in ?? ()
(gdb) bt
#0  0xffffe410 in ?? ()
#1  0x4280a700 in ?? ()
#2  0x00000046 in ?? ()
#3  0x00000000 in ?? ()
#4  0x4004cccc in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib/tls/libpthread.so.0
#5  0x0840ce7f in safe_cond_timedwait (cond=0x8609660, mp=0x86091c0, abstime=0x4280a700,
    file=0x84a7b40 "sql_base.cc", line=4950) at thr_mutex.c:240
#6  0x081dd753 in remove_table_from_cache (thd=0x8c68a00, db=0x8c4de50 "test",
    table_name=0x8c4dc68 "tb3", flags=2) at sql_base.cc:4950
#7  0x08287763 in wait_while_table_is_used (thd=0x8c68a00, table=0x8c4f938,
    function=HA_EXTRA_PREPARE_FOR_DELETE) at sql_table.cc:1867
#8  0x082877df in close_cached_table (thd=0x8c68a00, table=0x8c4f938) at sql_table.cc:1893
#9  0x082eee79 in mysql_create_or_drop_trigger (thd=0x8c68a00, tables=0x8c984b8, create=true)
    at sql_trigger.cc:188
#10 0x081b0ddd in mysql_execute_command (thd=0x8c68a00) at sql_parse.cc:4513
#11 0x081b3309 in mysql_parse (thd=0x8c68a00,
    inBuf=0x8c983d8 "create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2",
    length=63) at sql_parse.cc:5444
#12 0x081a88f9 in dispatch_command (command=COM_QUERY, thd=0x8c68a00,
    packet=0x8c8a359 "create trigger tr1 BEFORE INSERT on tb3 for each row set @a:= 2",
    packet_length=64) at sql_parse.cc:1664
#13 0x081a80c6 in do_command (thd=0x8c68a00) at sql_parse.cc:1463
#14 0x081a71a6 in handle_one_connection (arg=0x8c68a00) at sql_parse.cc:1116
#15 0x4004aaa7 in start_thread () from /lib/tls/libpthread.so.0
#16 0x4017bc2e in clone () from /lib/tls/libc.so.6

How to repeat:
Run stress test with test case above.

Until stress test will be integrated to regular mysql-test suite please contact me and I will provide all necessary information.
[23 Aug 2005 20:56] Heikki Tuuri
Hi!

The deadlock does not happen inside InnoDB code, and I do not see how InnoDB could cause this deadlock.

Have you tried running the test with a ./BUILD/compile-pentium-debug build? Maybe a thread forgets to unlock some mutex.

Unassigning myself from this bug.

Regards,

Heikki
[12 Oct 2005 8:49] 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/internals/30949
[17 Oct 2005 18:39] 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/internals/31187
[17 Oct 2005 21:11] Dmitry Lenev
Fixed in 5.0.16
[20 Oct 2005 18:09] Paul Dubois
Noted in 5.0.16 changelog.