MySQL Bugs: #12423: Deadlock in multithread environment (stress test)

Bug #12423	Deadlock in multithread environment (stress test)
Submitted:	7 Aug 2005 22:04	Modified:	4 Sep 2005 2:55
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) with at least 3 threads for some time (less than 1 min) will cause deadlock:

--disable_query_log
--disable_abort_on_error

delimiter //;
use test//

create user 'test'@'localhost'//-disable_query_log
grant execute on test.* to 'test'@'localhost'//
flush privileges//

delimiter ;//

It is possible sometime to get information about  state of active threads in server:

| 60 | root | localhost | test1 | Query   | 2    | Waiting on cond | flush privileges               
| 61 | root | localhost | test1 | Query   | 2    |                 | create user 'test'@'localhost' 
| 62 | root | localhost | test1 | Query   | 2    | Locked       | create user 'test'@'localhost' 

I ran server under gdb and when it hangs I killed mysqld with -11 and there are backtraces for active threads(Due to size limitation for description field I attached file with threads backtraces).

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.

Threads backtraces for deadlock issue

Attachment: deadlock.backtraces (application/octet-stream, text), 6.15 KiB.

made a small error in  test case above

please replace 
create user 'test'@'localhost'//-disable_query_log
with 
create user 'test'@'localhost'//

I found similar issue during running stress test for another tests. This time running only 2 concurrent threads lead to deadlock.

#test case
--disable_abort_on_error

create User test_general@localhost;
set password for test_general@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;

#output of mysqladmin proc
----------------------------+
| 1  | root | localhost |      | Query   | 0    |        | show processlist                                                |
| 24 | root | localhost | test | Query   | 24   |        | revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost |
| 25 | root | localhost | test | Query   | 24   | Locked | set password for test_general@localhost = password('PWD')       |
+----+------+-----------+------+---------+------+--------+-------------------------------------
#backtraces
(gdb) thread 33
[Switching to thread 33 (Thread 1113476016 (LWP 11746))]#0  0xffffe410 in ?? ()
(gdb) bt
#0  0xffffe410 in ?? ()
#1  0x425e32b8 in ?? ()
#2  0x00000002 in ?? ()
#3  0x00000000 in ?? ()
#4  0x4004f05e in __lll_mutex_lock_wait () from /lib/tls/libpthread.so.0
#5  0x4004be45 in _L_mutex_lock_205 () from /lib/tls/libpthread.so.0
#6  0x0000000e in ?? ()
#7  0x0000000f in ?? ()
#8  0x08c50920 in ?? ()
#9  0x00000000 in ?? ()
#10 0x425e4bb0 in ?? ()
#11 0x425e32e8 in ?? ()
#12 0x0840c9b3 in safe_mutex_lock (mp=0x8c4e8ac, file=0x4259dbb0 "╟шYB\f=е\b╟шYB\001", line=0)
    at thr_mutex.c:116
#13 0x0840c9b3 in safe_mutex_lock (mp=0x8c4e894, file=0x84b21c0 "sql_acl.cc", line=5070)
    at thr_mutex.c:116
#14 0x08246194 in mysql_revoke_all (thd=0x8c50920, list=@0x8c50d5c) at sql_acl.cc:5070
#15 0x081ae3be in mysql_execute_command (thd=0x8c50920) at sql_parse.cc:3728
#16 0x081b3309 in mysql_parse (thd=0x8c50920,
    inBuf=0x8c96fe0 "revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost",
    length=63) at sql_parse.cc:5444
#17 0x081a88f9 in dispatch_command (command=COM_QUERY, thd=0x8c50920,
    packet=0x8c686b9 "revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost",
    packet_length=64) at sql_parse.cc:1664
#18 0x081a80c6 in do_command (thd=0x8c50920) at sql_parse.cc:1463
#19 0x081a71a6 in handle_one_connection (arg=0x8c50920) at sql_parse.cc:1116
#20 0x4004aaa7 in start_thread () from /lib/tls/libpthread.so.0
#21 0x4017bc2e in clone () from /lib/tls/libc.so.6

(gdb) thread 34
[Switching to thread 34 (Thread 1113185200 (LWP 11747))]#0  0xffffe410 in ?? ()
(gdb) bt
#0  0xffffe410 in ?? ()
#1  0x4259bfc8 in ?? ()
#2  0x00000001 in ?? ()
#3  0x00000000 in ?? ()
#4  0x4004ca56 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/tls/libpthread.so.0
#5  0x0840ccfc in safe_cond_wait (cond=0x8cc2584, mp=0x8c934e4, file=0x84f0673 "thr_lock.c",
    line=413) at thr_mutex.c:202
#6  0x0840a8cb in wait_for_lock (wait=0x8c93534, data=0x8c9b1ec, in_wait_list=0 '\0')
    at thr_lock.c:411
#7  0x0840b154 in thr_lock (data=0x8c9b1ec, owner=0x8c90e48, lock_type=TL_WRITE)
    at thr_lock.c:677
#8  0x0840b93c in thr_multi_lock (data=0x8c91458, count=1, owner=0x8c90e48) at thr_lock.c:919
#9  0x0818be11 in mysql_lock_tables (thd=0x8c8ffd0, tables=0x4259c5ec, count=1, flags=0)
    at lock.cc:147
#10 0x081d7a49 in open_ltable (thd=0x8c8ffd0, table_list=0x4259c5a0, lock_type=TL_WRITE)
    at sql_base.cc:2114
#11 0x0823aad8 in update_user_table (thd=0x8c8ffd0, host=0x8c53f50 "localhost",
    user=0x8c53f60 "test_general",
    new_password=0x8cd05f0 "*C49735D016A099C0CF104EF9183F374A54CA2576", new_password_len=41)
    at sql_acl.cc:1522
#12 0x0823a28b in change_password (thd=0x8c8ffd0, host=0x8cd05c0 "localhost",
    user=0x8cd05b0 "test_general",
    new_password=0x8cd05f0 "*C49735D016A099C0CF104EF9183F374A54CA2576") at sql_acl.cc:1352
#13 0x0819ddb0 in set_var_password::update (this=0x8cd0620, thd=0x8c8ffd0) at set_var.cc:3120
#14 0x0819d73e in sql_set_variables (thd=0x8c8ffd0, var_list=0x8c90470) at set_var.cc:2927
#15 0x081ad700 in mysql_execute_command (thd=0x8c8ffd0) at sql_parse.cc:3478
#16 0x081b3309 in mysql_parse (thd=0x8c8ffd0,
    inBuf=0x8cd0540 "set password for test_general@localhost = password('PWD')", length=57)
    at sql_parse.cc:5444
#17 0x081a88f9 in dispatch_command (command=COM_QUERY, thd=0x8c8ffd0,
    packet=0x8cae2f1 "set password for test_general@localhost = password('PWD')",
    packet_length=58) at sql_parse.cc:1664
#18 0x081a80c6 in do_command (thd=0x8c8ffd0) at sql_parse.cc:1463
#19 0x081a71a6 in handle_one_connection (arg=0x8c8ffd0) at sql_parse.cc:1116
#20 0x4004aaa7 in start_thread () from /lib/tls/libpthread.so.0
#21 0x4017bc2e in clone () from /lib/tls/libc.so.6

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/28888

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/29171

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/29205

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/29206

Fixed in 4.1.15 and 5.0.15

Deadlock occured when several account management commands were run (particularly between FLUSH PRIVILEGES/SET PASSWORD and GRANT/REVOKE commands).

Noted in 4.1.5, 5.0.13 changelogs.
(I assume 5.0.15 is a typo in previous
comment.)