Bug #41506 InnoDB hang on purge_sys->latch in row0vers.c:107 and trx0purge.c:1059
Submitted: 16 Dec 2008 14:00 Modified: 25 Jan 2010 6:36
Reporter: Axel Schwenke Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.22 OS:Linux (Ubuntu 6.06/x86_64)
Assigned to: CPU Architecture:Any
Tags: innodb, purge, Semaphore, wait

[16 Dec 2008 14:00] Axel Schwenke
Description:
InnoDB deliberately crashes the MySQL server after a long semaphore wait. The root cause seems to be purge_sys->latch, held by the purge (main) thread #1140881760

Some lines from the error log:

Dec 10 13:49:35 db02 mysqld[5722]: ----------
Dec 10 13:49:35 db02 mysqld[5722]: SEMAPHORES
Dec 10 13:49:35 db02 mysqld[5722]: ----------
Dec 10 13:49:35 db02 mysqld[5722]: OS WAIT ARRAY INFO: reservation count 173060701, signal count 161294457
Dec 10 13:49:35 db02 mysqld[5722]: --Thread 1164536160 has waited at row0vers.c line 107 for 813.00 seconds the semaphore:
Dec 10 13:49:35 db02 mysqld[5722]: S-lock on RW-latch at 0x2aaabdefecd8 created in file trx0purge.c line 214
Dec 10 13:49:35 db02 mysqld[5722]: a writer (thread id 1140881760) has reserved it in mode  wait exclusive
Dec 10 13:49:35 db02 mysqld[5722]: number of readers 1, waiters flag 1
Dec 10 13:49:35 db02 mysqld[5722]: Last time read locked in file row0vers.c line 107
Dec 10 13:49:35 db02 mysqld[5722]: Last time write locked in file trx0purge.c line 1059
Dec 10 13:49:35 db02 mysqld[5722]: --Thread 1140881760 has waited at trx0purge.c line 1059 for 813.00 seconds the semaphore:
Dec 10 13:49:35 db02 mysqld[5722]: X-lock on RW-latch at 0x2aaabdefecd8 created in file trx0purge.c line 214
Dec 10 13:49:35 db02 mysqld[5722]: a writer (thread id 1140881760) has reserved it in mode  wait exclusive
Dec 10 13:49:35 db02 mysqld[5722]: number of readers 1, waiters flag 1
Dec 10 13:49:35 db02 mysqld[5722]: Last time read locked in file row0vers.c line 107
Dec 10 13:49:35 db02 mysqld[5722]: Last time write locked in file trx0purge.c line 1059

note that the purge thread waits for a latch that is has reserved itself?

[many more threads waiting in row0vers.c:107]

Dec 10 13:49:35 db02 mysqld[5722]: Main thread process no. 5721, id 1140881760, state: purging

complete error log is available

How to repeat:
now idea how to repeat this
[16 Dec 2008 14:05] Axel Schwenke
I checked the given line numbers and they are consistent with current 5.0 sources. So this problem seems to be separate from other semaphore race conditions like bug #20358. And I also do not expect any fixes in MySQL versions newer than 5.0.22
[25 Dec 2009 6:36] Sveta Smirnova
Thank you for the report.

Could you please try to find which kind of queries were issued when mysqld crashes? Did user use multi-statement transactions?
[26 Jan 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".