Bug #41506 InnoDB hang on purge_sys->latch in row0vers.c:107 and trx0purge.c:1059
Submitted: 16 Dec 2008 15:00 Modified: 20 Aug 19:14
Reporter: Axel Schwenke
Status: Open
Category:Server: InnoDB Severity:S1 (Critical)
Version:5.0.22 OS:Linux (Ubuntu 6.06/x86_64)
Assigned to: Target Version:
Tags: innodb, Semaphore, wait, purge

[16 Dec 2008 15: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 15: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