MySQL Bugs: #9998: MySQL client hangs on USE "database"

Bug #9998	MySQL client hangs on USE "database"
Submitted:	19 Apr 2005 13:04	Modified:	24 Jun 2005 2:58
Reporter:	Stefan Hinz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	N/A
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
I wanted to test what happens when you try to get a lock on a table
that is locked by another thread, and I found this behaviour in 5.0.4:

1. Client A WRITE locks a MyISAM table in '<database>'.
2. Client B issues 'USE <database>' and hangs until the lock is released by client A. 'SHOW PROCESSLIST' issued in client A shows client B is in status 'LOCKED'.

I've asked on dev-public@ whether this is intended/expected behaviour, and Guilhem believes this is a bug:

What may have happened is that you used a 'mysql' command-line client, without the -A option. Then when you type "USE database;" your client wants to list all tables in this database, and all columns of these tables (this is for auto-completion to work). And probably it hangs in mysql_list_fields(), because that's reading the table's definition:

MASTER> show processlist;
+----+------+-----------+------+------------+------+--------+------------------+
| Id | User | Host      | db   | Command    | Time | State  | Info             |
+----+------+-----------+------+------------+------+--------+------------------+
|  2 | root | localhost | NULL | Query      |    0 | NULL   | show processlist |
|  3 | root | localhost | test | Field List |    3 | Locked |                  |
+----+------+-----------+------+------------+------+--------+------------------+

Now I'm far from sure that reading the table definition is supposed to block on a write lock. Normally a LOCK TABLES WRITE is just that you're updating _data_ (not metadata) and you don't want others to read data at this time.
And one more suspicious thing is... in 5.0 I can reproduce the hang but not in 4.1 :)

So I would file this as a bug!!

How to repeat:
See description.

Suggested fix:
Make it behave as in 4.1, i.e. not hang for client B. ;-)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25573

Pushed to 5.0.7

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.7 changelog; closed.