MySQL Bugs: #61391: Metadata lock held on MyISAM table when AUTOCOMMIT turned off

Bug #61391	Metadata lock held on MyISAM table when AUTOCOMMIT turned off
Submitted:	2 Jun 2011 20:48	Modified:	3 Jun 2011 18:50
Reporter:	Arash Alavi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Linux
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	autocommit, myisam, table metadata lock

Description:
With autocommit turned off (set autocommit=0), a SELECT statement against a MyISAM table will hold a lock on the table until a COMMIT is issued.

This doesn't seem like normal behavior, but it is specially a problem, for example, with Python's MySQLdb:

"Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249)." http://bit.ly/jq9Z33

How to repeat:
You can reproduce the problem using two MySQL client sessions, as follows:

Session 1:
----------

use test;
show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

create table foo (num int) ENGINE=MyISAM;
insert into foo values (1),(2),(3);
select * from foo;

Session 2:
----------

use test;
truncate table foo;
Query OK, 0 rows affected (0.01 sec)

[no problem]

Session 1:
----------

set autocommit=0;
show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

select * from foo;

Session 2:
----------

truncate table foo;
[hangs]

Session 1:
---------

select user, host, db, command, state, info from information_schema.processlist where user='root';
+------+-----------+------+---------+---------------------------------+---------------------------------------------------------------------------------------------------+
| user | host      | db   | command | state                           | info                                                                                              |
+------+-----------+------+---------+---------------------------------+---------------------------------------------------------------------------------------------------+
| root | localhost | test | Query   | executing                       | select user, host, db, command, state, info from information_schema.processlist where user='root' |
| root | localhost | test | Query   | Waiting for table metadata lock | truncate table foo                                                                                |
+------+-----------+------+---------+---------------------------------+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

commit;

[as soon as commit is issued in session 1, "truncate table foo;" in
session 2 returns]

select user, host, db, command, state, info from information_schema.processlist where user='root';
+------+-----------+------+---------+-----------+---------------------------------------------------------------------------------------------------+
| user | host      | db   | command | state     | info                                                                                              |
+------+-----------+------+---------+-----------+---------------------------------------------------------------------------------------------------+
| root | localhost | test | Query   | executing | select user, host, db, command, state, info from information_schema.processlist where user='root' |
| root | localhost | test | Sleep   |           | NULL                                                                                              |
+------+-----------+------+---------+-----------+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Metadata locks are kept for the duration of the transaction for tables of any storage engine. This is required to achieve proper isolation and serialization (e.g. replication).

Further details, see http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

Is it not true that MyISAM tables should "effectively always operate in 
autocommit = 1 mode"?  If so, does this not mean that turning autocommit
off should not have any effect on a select query on MyISAM tables?

This page from the the 5.5 Manual, under the heading "Transaction and 
Atomic Operation Differences", discusses the differences between the 
"transactional paradigm" (used by InnoDB, for example) and the "atomic
operations paradigm", used by non-transactional engines such as MyISAM:

"The other nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called “atomic operations.” In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode." http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html

Does this not mean that MyISAM tables should be ignoring the "autocommit = 1" 
mode, and since they are not ignoring it (as illustrated by the steps I have
provided in the bug report), then this is indeed a bug?

Sorry, I meant to say:

Does this not mean that MyISAM tables should be ignoring the 
-- "autocommit = 0" -- mode, and since they are not ignoring 
it (as illustrated by the steps I have provided in the bug 
report), then this is indeed a bug?

That remark is about data manipulation (integrity, atomicity), in the sense that, for example, you cannot rollback data modifications made to a MyISAM. Metadata locking protects the _structure_ of tables regardless of what storage engine a table belongs to.

ok, but does it really make sense that metadata locking is triggered on a 
MyISAM table by setting autocommit=0?

Yes, see Bug#989.

I see bug 989 is about _transactions_.  So, you are saying that if autocommit
is set to 0 (turned off), then a select on a MyISAM table is considered
a transaction, even though MyISAM is non-transactional.

In case it is not clear, I'd like to point out that applications that run 
successfully on MySQL 5.0 and MySQL 5.1 stop working on MySQL 5.5 because of
this issue.  Perhaps this issue should be pointed out in the documentation
where autocommit is discussed in the context of MyISAM.

Just because operations on MyISAM tables are non-transactional does not mean that such tables can't participate in a transaction. It just means that the operations won't be rolled back. For example, mixing transactional and non-transactional tables in a transaction is a scenario where the order of the operations, including those to non-transactional tables, is important for serializability.

Thanks for the explanation.  I thought transactions are started by
START TRANSACTION or BEGIN, but instead it seems that if autocommit 
is turned off at the beginning of a session, then every statement 
in that session is treated as being part of a transaction.

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