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	CPU Architecture:	Any

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.

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

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

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

Fixed in 5.0.16

Noted in 5.0.16 changelog.

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.