MySQL Forums
Forum List  »  InnoDB

Mysql hangs once in 2 days with mysterious deadlock?
Posted by: Viacheslav Romanov
Date: September 15, 2014 06:30AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

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.