MySQL Bugs: #28639: Read lock not released

Bug #28639	Read lock not released
Submitted:	24 May 2007 0:50	Modified:	8 Jan 2008 19:23
Reporter:	Matthias Urlichs	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.22-Debian_0ubuntu6.06.3-log	OS:	Linux (Ubuntu Dapper)
Assigned to:		CPU Architecture:	Any

Description:
Sometimes a read lock is not released when the process which did the locking dies.

| 156323 | root | localhost | mawu_ubuntu_fr | Query   | 279  | Waiting for rele
ase of readlock | CREATE TABLE `forum_reports` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `post_id` int(10)  |
|

How to repeat:
I had three jobs running at the same time: two piped the result of a remote "mysqldump -q --single-transaction --master-data=1 --opt" into the local database, the other was running a "mysqldump --single-transaction --master-data=2 -c" on it. Since I knew that the dump was superfluous, I killed it.

A long time later, the above state is unchanged.

The other mysqldump|mysql job progresses normally.

=====================================
070524  2:52:21 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 51515, signal count 51295
Mutex spin waits 72717, rounds 1246561, OS waits 3199
RW-shared spins 245365, OS waits 27674; RW-excl spins 32655, OS waits 20642
------------
TRANSACTIONS
------------
Trx id counter 0 19659951
Purge done for trx's n:o < 0 19659925 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 25480, OS thread id 3019013040
MySQL thread id 156855, query id 12242290 localhost root
show innodb status
---TRANSACTION 0 19659689, not started, process no 25480, OS thread id 2966150064
MySQL thread id 156323, query id 12240709 localhost root Waiting for release of readlock
CREATE TABLE `forum_reports` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `post_id` int(10) unsigned NOT NULL default '0',
  `topic_id` int(10) unsigned NOT NULL default '0',
  `forum_id` int(10) unsigned NOT NULL default '0',
  `reported_by` int(10) unsigned NOT NULL default '0',
  `created` int(10) unsigned NOT NULL default '0',
  `message` text NOT NULL,
  `zapped` int(10) unsigned default NULL,
  `zapped_by` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `forum_reports_zapped_idx` (`zapped`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
---TRANSACTION 0 19659943, ACTIVE 139 sec, process no 25480, OS thread id 2967747504 inserting, thread declared inside InnoDB 349
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 45743
MySQL thread id 156308, query id 12242116 localhost root update
INSERT INTO `ubuntu_search_wordmatch` VALUES (81136,232954,0),(81136,14486,0),(81136,10994,0),(81136,88832,0),(81136,10878,0),(81136,1784,0),(81136,5018,0),(81136,70155,0),(81136,30960,0),(81136,200328,0),(81136,31980,0),(81136,51889,0),(81136,8708,0),(81136,9764,0),(81136,58726,0),(81136,5274,0),(81136,12820,0),(81136,1681,0),(81136,5931,0),(81136,6175,0),(81136,1986,0),(81136,151223,0),(81136,97089,0),(81136,9549,0),(81136,7658,0),(81136,200330,0),(81136,5742,0),(81136,197424,0),(81136,2379,0),(81136,12479,0),(81136,200331,0),(81136,12827,0),(81136,24540,0),(81136,200332,0),(81136,14720,0),(

Thank you for a problem report. Please, upload complete SHOW INNODB STATUS results as a separate file.

mysql -uroot -e"show innodb status" >/tmp/status

Attachment: status (application/octet-stream, text), 3.54 KiB.

Please, try to repeat with a newer version, 5.0.51 or 5.0.52, and inform about the results.

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".