Mysql hangs once in 2 days with mysterious deadlock?
Hi!
I am running a mysql for almost a year, and a month ago it started to freeze with bunch of semaphores waiting for the locks. I think it is a bug in mysql because I do not use transactions - only single line queries (select or update or insert).
Furthermore, there's no foreign keys in the database, all tables are independent.
Here's SHOW ENGINE INNODB STATUS
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
140914 23:28:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 21893011 1_second, 21892880 sleeps, 2189285 10_second, 15091 background, 15091 flush
srv_master_thread log flush and writes: 22098576
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2904643908, signal count 1609487365
--Thread 139583144167168 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583333746432 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583292086016 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583524013824 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583399978752 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139595676866304 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583503140608 has waited at que0que.c line 1344 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583274624768 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583418042112 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583505549056 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583298909952 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583497721600 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139595652982528 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583427475200 has waited at trx0trx.c line 845 for 1.00 seconds the semaphore:
Mutex at 0x7f07d06ad8e0 created file trx0rseg.c line 210, lock var 1
waiters flag 1
--Thread 139583242712832 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583373084416 has waited at lock0lock.c line 4005 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583439918848 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583604496128 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583527626496 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583453566720 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139595363706624 has waited at trx0trx.c line 931 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583309260544 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583331137280 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583494108928 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583300916992 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583492904704 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583349802752 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583590647552 has waited at trx0undo.c line 1758 for 4.00 seconds the semaphore:
Mutex at 0x7f07d0674120 created file trx0rseg.c line 210, lock var 1
waiters flag 1
--Thread 139596725970688 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583302924032 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583230871296 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583430084352 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583593858816 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583236089600 has waited at trx0trx.c line 931 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583289476864 has waited at trx0trx.c line 861 for 1.00 seconds the semaphore:
Mutex at 0x7f07d0680f60 created file trx0rseg.c line 210, lock var 1
waiters flag 1
--Thread 139583458182912 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583361042176 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583299311360 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139583287871232 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583278438144 has waited at btr0cur.c line 535 for 1.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583214413568 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583606703872 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583255557888 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583233480448 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583429281536 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583520401152 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583201167104 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583547094784 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583370073856 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583470425856 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583531239168 has waited at trx0purge.c line 396 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139596723762944 has waited at trx0undo.c line 1758 for 0.00 seconds the semaphore:
Mutex at 0x7f07d06793b0 created file trx0rseg.c line 210, lock var 1
waiters flag 1
wait has ended
--Thread 139583503943424 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583538263808 has waited at trx0trx.c line 845 for 2.00 seconds the semaphore:
Mutex at 0x7f07d0676cb0 created file trx0rseg.c line 210, lock var 1
waiters flag 1
--Thread 139583490696960 has waited at trx0undo.c line 1758 for 2.00 seconds the semaphore:
Mutex at 0x7f07d06992a0 created file trx0rseg.c line 210, lock var 1
waiters flag 1
--Thread 139583148984064 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583607908096 has waited at lock0lock.c line 5647 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
wait has ended
--Thread 139595689912064 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139595683690240 has waited at row0sel.c line 3878 for 4.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef6cfb0f340 created in file buf0buf.c line 938
a writer (thread id 139595692521216) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.c line 3878
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/row/row0upd.c line 2169
--Thread 139595660609280 has waited at trx0trx.c line 1684 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139596709713664 has waited at trx0trx.c line 1112 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0
--Thread 139583523211008 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583600682752 has waited at btr0cur.c line 535 for 2.00 seconds the semaphore:
S-lock on RW-latch at 0x7ef4c4230338 created in file dict0dict.c line 1838
a writer (thread id 139583383521024) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.c line 535
Last time write locked in file /build/buildd/mysql-5.5-5.5.34/storage/innobase/btr/btr0cur.c line 528
--Thread 139583257966336 has waited at trx0trx.c line 754 for 0.00 seconds the semaphore:
Mutex at 0x7f076f3ab278 created file srv0srv.c line 1024, lock var 1
waiters flag 0