MySQL Bugs: #22476: UNLOCK TABLES fails to free locks in mysqld server after InnoDB Hot Backup run

Bug #22476	UNLOCK TABLES fails to free locks in mysqld server after InnoDB Hot Backup run
Submitted:	19 Sep 2006 13:55	Modified:	23 Aug 2008 14:57
Reporter:	Pekka Lampio	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.21	OS:	Linux (Linux 2.4.21-4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A MySQL database with InnoDB and MyISAM tables.
A MySQL client issues the following SQL statements:

  FLUSH TABLES WITH READ LOCK;
  COMMIT;

After some time the client unlocks everything with:

  UNLOCK TABLES;

The mysqld server, however, does not free all table locks.
(This is visible through InnoDB Status monitor output which shows
transactions waiting for readlocks.)
MySQL server has accepted all three SQL statements and
the MySQL client process issuing the statements has terminated
without errors.

How to repeat:
See description,

I have not tried to reproduce this bug myself. I have just
deduced it from the output of ibbackup and innobackup backup tools
in support issue 10103.

I have seen this issue myself.. 

Mysql version: 5.0.21-standard-log
Linux Kernel: 2.6.16.13-4-smp

Let me know ify ou need any further information / traces etc.  

This is a serious issue imho, can we bump up the priority?

thanks!
--Raj.

We are also seeing this issue running 5.0.22. If somebody isn't going to look at this can we get some sort of confirmation to the fact so we know that we need to take some other action to get around it?

I personally think it has to do with the load on the server when the back is happening or the amount of connections to the server.

My backup failed as it was going and this is what we saw...

"ibbackup: You had specified the option --suspend-at-end
070417 5:51:02 ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /var/lib/mysql.backup.spool/2007-04-17_05-38-36/ibbackup_suspended

innobackup: Continuing after ibbackup has suspended
innobackup: Starting mysql with options: --unbuffered --password=passwd --user=root
innobackup: Connected to database with mysql child process (pid=12420)
Use of uninitialized value in pattern match (m//) at /usr/bin/innobackup line 964.
Use of uninitialized value in pattern match (m//) at /usr/bin/innobackup line 964.

innobackup: Error: Connection to mysql child process (pid=12420) timedout. (Time limit of 900 seconds exceeded. You may adjust time limit by editing the value of parameter "$mysql_response_timeout" in this script.) while waiting for reply to MySQL request: 'FLUSH TABLES WITH READ LOCK;' at /usr/bin/innobackup line 322.
"

It died and then we couldn't unlock the tables. We ended up having to kill off mysql and restart it to get our database back up.

I wouldn't mind upgrading to the latest release if I knew that would solve the problem...

Thanks
-Kelly

We are using 5.0.22 from the Ubuntu Edgy release, and we appear to be seeing this issue.

The exact process we're using is:

FLUSH TABLES WITH READ LOCK;
(wait) "tar cf" of the database.
SHOW MASTER STATUS;
UNLOCK TABLES;

There are many connections backing up waiting for read lock; eventually the machine starts refusing connections. Potentially waiting a very long time solves this issue (multiples of hours in our case), but as it's a production server we need to stop the server and restart.

Most of the time, this command completes perfectly, but when the load / number of queries waiting is high, it takes orders of magnitudes longer.

Please check to see if you have a long-running InnoDB transaction that also involves MyISAM. INSERT ... SELECT into a temporary table from an InnoDB table is one possibility. The read lock has to wait for the INSERT ... SELECT to finish, so InnoDB Hot Backup might time out if it takes a long time.

All,

Please check the last comment done by James and comment what you find. Thanks in advance.

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

This has probably been fixed by several fixes involving FLUSH TABLES WITH READ LOCK since the latest version the problem has been reported with. If you're still seeing the problem in this bug report please report your version and server options and operating system. If you can, try upgrading if it's a fairly old version. Then let us know if that helps so we can try to narrow down what could or couldn't be the cause.

Bug #17741 reported a lock that persisted when making a backup with 5.0.18 "mysql --master-data". That was also reported in 5.1 in bug 29088 provide both --master-data and --log options are in use, and wasn't reproducible in 5.0.20 or 5.0.54a so was presumed also fixed in 5.0.

Bug #32528 "For FLUSH TABLES WITH READ LOCK, the server failed to properly detect write-locked tables when running with low-priority updates, resulting
in a crash or deadlock" was fixed in 5.0.54 and could be a cause.

Bug #20048 "FLUSH TABLES WITH READ LOCK causes a deadlock" if NPTL threading is in use was fixed in 5.0.23 and 5.1.10.

Bug #32395 "Alter table under a impending global read lock causes a server crash", also affects other DDL. Only fixed in 5.1.

Bug #23713 "LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock" also happened with DROP TABLE and also has been described as "my backup system gets into a state where "flush tables with read lock" just doesn't complete, but the other threads are not doing anything except insert/update/delete (plus maybe a temporary table thrown in)" which may be more related to the related bug #32395 above.

Crash bugs involving FLUSH TABLES WIH READ LOCK that have been fixed include bug #38691, bug #38816

This is believed to have been fixed by the updates I mentioned earlier so I plan to close this bug. Last call for any objections from anyone who can still reproduce it with a recent version of the server.