MySQL Bugs: #61935: Hang Waiting for table metadata lock

Bug #61935	Hang Waiting for table metadata lock
Submitted:	21 Jul 2011 5:24	Modified:	10 Aug 2011 19:51
Reporter:	Heine Escobedo Jimenez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.11	OS:	Linux (openSUSE 11.2 "Emerald")
Assigned to:		CPU Architecture:	Any

Description:
I'm trying truncate a table but this cause a hangs Waiting for table metadata lock. Not exist other transactions working on this table. 

Here the steps executed:

mysql> SELECT * FROM performance_schema.MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM performance_schema.RWLOCK_INSTANCES WHERE WRITE_LOCKED_BY_THREAD_ID IS NOT NULL OR READ_LOCKED_BY_COUNT IS NOT NULL;
Empty set (0.00 sec)

mysql> use bse_eis;
Database changed

mysql> truncate table zipcodes;

the transaction is on this state by more than 3 hours...and have the same state until the moment of this report

How to repeat:
I don't have any idea why this happen nor how repeat an minimum scenary to make it happen when not exist more than only one transaction running on the database.

I don't have this problem with my Production environment with MySQL 5.1.37,  this bug appears during the migration tests to the new versions of MySQL 5.5.x. that I come ran.

Steps executed after and before locking table

Attachment: steps_executed_before_after_lock_07212011.txt (text/plain), 26.61 KiB.

What exact version, 5.5.x, do you use? Please, send the output of:

show full processlist;

if you still have this TRUNCATE hanging. Send the results of 

SHOW CREATE TABLE zipcodes\G 

also.

Steps executed after and before locking table, including the truncate table operation

Attachment: steps_executed_before_after_lock_07212011.txt (text/plain), 26.91 KiB.

I still miss server version and CREATE TABLE. I see that there are other connections, and some of them have transactions active for a long time:

---TRANSACTION ACC79F, ACTIVE 1549 sec, process no 22803, OS thread id 139689575450896
MySQL thread id 1669, query id 3485553 lim-int1-acce-01.bluestarenergy.com 10.3.251.48 ACCE_I_ACCE_03
---TRANSACTION ACB954, ACTIVE 3947 sec, process no 22803, OS thread id 139689571723536
MySQL thread id 1382, query id 3403939 lim-int1-husp-01.bluestarenergy.com 10.3.251.35 HUSP_I_HUSP_02
---TRANSACTION ACB14E, ACTIVE 5149 sec, process no 22803, OS thread id 139689575983376
MySQL thread id 1217, query id 3446611 lim-int1-acce-01.bluestarenergy.com 10.3.251.48 ACCE_I_ACCE_03
---TRANSACTION AC878D, ACTIVE 7318 sec, process no 22803, OS thread id 139689576249616
MySQL thread id 959, query id 3482317 lim-wrk-win-011.bse.local 10.3.50.111 USR_GLB_ACC
---TRANSACTION 95BBAD, ACTIVE 17560 sec, process no 22803, OS thread id 139691439585552
MySQL thread id 123, query id 3297743 lim-int1-sfap-01.bluestarenergy.com 10.3.251.40 MSCP_I_SFAP_02

Are you sure none of them worked with that table you try to truncate? See bug #60563 also.

Thanks for your attention Valeriy, here the data requested:

Attachment: data requested 07212011_1250.txt (text/plain), 19.53 KiB.

Valeriy
as you can see on the last file sent, no other transaction is working with this table. Only the connection that is trying to truncate the table.

Thank you for the feedback.

Which value of autocommit do you use?

Dear Sveta Smirnova

I'm attaching two files, one with an type of tracking about my recently tests with the last available release of MySQL (5.5.15) and also I send you the MySQL configuration file that I used.

I have two instances of MySQL and starts the service with mysqld_multi. 

I'm trying to be it most descriptive possible if you need something more or any other data to help you to identify the issue, please let me know. 

Thank you for your time and attention.

Heine Escobedo Jimenez.

track of last tests

Attachment: track_mysql_lock_issue_08042011.txt (text/plain), 201.70 KiB.

mysql configuration file

Attachment: mysql_conf_lim-prd1-vm-04.txt (text/plain), 5.33 KiB.

Thank you for the feedback.

This is actually not a bug. You set AUTOCOMMIT=0 in your configuration file. This means every activity which you do is part of transaction until you explicitly call COMMIT or ROLLBACK. Once you accessed table energyhourlyusagedetail using SELECT its metadata is blocked until you call COMMIT or ROLLBACK in same section. See http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html for details.

Dear Sveta

I've read the information suggested previously but sincerely I had not thought that be needed an COMMIT or ROLLBACK command for each SELECT command that any user or application can send to the database, and even it isn't mentioned explicitly.

As you know according with the ACID concept for management of transactions, is unfeasible setting AUTOCOMMIT = 1 because it left open the posibility of save only part of transactions block if the application doesn't have a correct manage of his operations and this could be the most regular situation on a DEV or QA environment. 

Thats means that if we want enjoy the improvements of the last release 5.5.x, at least on my case,we need to change the current applications or revise at minimum detail that all queries (select comands) must have it necesarily a COMMIT or ROLLBACK associated and customize the accesses by the common or single users, right ?

Please, can you say me if is possible manage another configuration or solution to avoid this kind of behavior with the last releases of MYSQL (> 5.5.3)?

Is possible configure, reduce or control on some way the time of waiting for the metadata locking ?

Thanks for your attention.

The notion that a thread's use of SELECT (without FOR UPDATE) should open a transaction that is not automatically closed with a DDL statement is unique to MySQL and it is abhorrent behavior with respect to applications.
This is most certainly a bug, since it is completely unexpected behavior based on how DDL is implemented on other databases.
First of all, no DBMS engine should hang forever due to a non-closed transaction on the same connection being used for DDL. That is a bug in and of itself.
Second, the "open transaction" referenced here is not a commit-able transaction because it did not lock anything explicitly, e.g., via FOR UPDATE or by using an UPDATE or DELETE operation.
Third, DDL should not be transactional and it should not depend on any transactional state of the current connection.  It may cause a transaction to close, but DDL trumps DML transactions in the same connection in all cases. For transactions on other connections, the DDL may produce an error due to simultaneously open transactions, but it should never ever hang waiting for them. Such policies can produce deadlocks.

Application programmers make these assumptions all the time, and if MySQL violates them, then MySQL has a bug.

> The notion that a thread's use of SELECT (without FOR UPDATE) should open a transaction
> that is not automatically closed with a DDL statement is unique to MySQL and it is
> abhorrent behavior with respect to applications.

Almost all DDL statements in MySQL will implicitly commit a open transaction. This is about concurrent sessions holding a metadata lock on a table and this behavior is shared among all major RDBMS.

> This is most certainly a bug, since it is completely unexpected behavior based on how DDL
> is implemented on other databases.

I think you are confused on what is the behavior here.

> First of all, no DBMS engine should hang forever due to a non-closed transaction on the
> same connection being used for DDL. That is a bug in and of itself.

It does not hang forever, there is a timeout.

> Second, the "open transaction" referenced here is not a commit-able transaction because
> it did not lock anything explicitly, e.g., via FOR UPDATE or by using an UPDATE or DELETE
> operation.

FOR UPDATE is a data (i.e. row) lock, not a metadata lock. SELECT, UPDATE or DELETE will take metadata and data locks. A metadata lock guarantees that a transaction will always view the same definition of a table.

> Third, DDL should not be transactional and it should not depend on any transactional

Yes, DDL should be transactional. It eventually will be transaction in MySQL.

> state of the current connection.  It may cause a transaction to close, but DDL trumps DML
> transactions in the same connection in all cases. For transactions on other connections,

A pending DDL trumps waiting DMLs (expect for high priority DML).

> the DDL may produce an error due to simultaneously open transactions, but it should never
> ever hang waiting for them. Such policies can produce deadlocks.

A DDL waits for a metadata lock on a table up to a certain timeout.

> Application programmers make these assumptions all the time, and if MySQL violates them,
> then MySQL has a bug.

Application programmers should try to familiarize themselves with the changes in the product.

Davi apparently has not been reading this thread. It's not about locking in one session affecting truncate in another session. The poster explicitly said that NO other transactions were open on the table.  
Also, Davi did not correct Sveta's comment, which stated that the SELECT with AUTOCOMMIT=0 would open a transaction on the session, which then would block the truncate on the same session.  Is that true or not? If I and Heine are confused, it is because you are not answering the questions clearly.
Also, you said that "Almost all DDL" will implicitly commit an open transaction. Does the documentation define which ones do and which ones don't? In particular, I am assuming "truncate" does, in direct contradiction to Sveta's comment.
Finally, you are free to implement whatever you like in MySQL, but if you break application interface conventions that are widely held, you will keep MySQL in the backwaters of second or third-tier SQL databases and provide valid motivation for developers to reconsider key-value "NoSQL databases".  The reason for not violating widely held de facto standards is to allow application-database independence, which would make MySQl more useful.

> Davi apparently has not been reading this thread. It's not about locking
> in one session affecting truncate in another session. The poster
> explicitly said that NO other transactions were open on the table.

The data he provided indicates otherwise.

> Also, Davi did not correct Sveta's comment, which stated that the
> SELECT with AUTOCOMMIT=0 would open a transaction on the session,
> which then would block the truncate on the same session.  Is that true or not?

Not true. But don't take my word, test it.

> If I and Heine are confused, it is because you are not answering the
> questions clearly.

I'm under no obligation to even answer at all.

> Also, you said that "Almost all DDL" will implicitly commit an open
> transaction. Does the documentation define which ones do and which ones
> don't? In particular, I am assuming "truncate" does, in direct
> contradiction to Sveta's comment.

http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

It does not contradict what Sveta said.

> Finally, you are free to implement whatever you like in MySQL, but if
> you break application interface conventions that are widely held, you
> will keep MySQL in the backwaters of second or third-tier SQL databases
> and provide valid motivation for developers to reconsider key-value
> "NoSQL databases".  The reason for not violating widely held de facto
> standards is to allow application-database independence, which would
> make MySQl more useful.

This change made MySQL more standard compliant.

mysql session 1> SELECT VERSION();
+-------------------------------+
| VERSION()                     |
+-------------------------------+
| 5.5.17-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)

mysql session 1> use test;
Database changed

mysql session 1> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 1> CREATE TABLE t1 (a iNT);
Query OK, 0 rows affected (0.01 sec)

mysql session 1> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql session 1> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql session 1> SELECT a FROM t1;
Empty set (0.00 sec)

[ switch to session 2 ]

mysql session 2> use test;
Database changed

mysql session 2> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 2> SET lock_wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)

mysql session 2> TRUNCATE TABLE t1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[ switch to session 1 ]

mysql session 1> COMMIT;
Query OK, 0 rows affected (0.00 sec)

[ switch to session 2 ]

mysql session 2> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)

Thanks for the clarification.
I still see a problem with MySQL's handling of "metadata locks".
Using the same sequence of two threads in Oracle, the truncate table works fine, because the "select" statement did not induce any locks on the table. When MySQL puts a metadata lock on the table, it is inventing its own odd behavior which does not match first-tier databases, even those from the company that owns MySQL! This creates a nightmare for app developers who want database independence.

Second, the Oracle NOWAIT option provides for immediate return from the "truncate table" if there IS a lock on the table, e.g., via "select...for update" on a separate thread.  This is much friendlier than hanging the thread for some lock timeout period (which seems like forever unless you mess with the database metadata settings).  MySQL needs this option to make things simpler. 

Responsive applications often cannot wait for long timeouts so MySQL should allow apps to overcome such limitations.

FLUSH TABLES is a nice work-around to eliminate the metadata lock while you troubleshoot where it is originating from.