Yesterday I was going through the locks related code; and found that in 5.0; when you have a global read lock; then the SHOW TABLE STATUS actually gets blocked when it had its own read lock on the new session; and this is not the case with MySQL 5.1
For example; lets execute the following set of statements in two sessions with MySQL 5.0 (latest bazaar version with InnoDB enabled):
Session 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected (0.07 sec) mysql> create table test.t1(c1 int)Engine=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) |
Session 2:
|
1 2 3 4 5 6 7 8 |
mysql> use test Database changed mysql> lock table t1 read; Query OK, 0 rows affected (0.01 sec) mysql> show table status like 't1'\G |
And you will notice that the last SHOW TABLE STATUS command actually gets blocked until you release the lock in session 1 or rollback the transaction or until lock wait timeout.
And when you execute the same set of statements with 5.1; then it won™t get blocked; and that is supposed to be what one expects as read-lock(global or session) shouldn™t block read-only query from any session.
Here is the code snippet in MySQL 5.0; which is causing the problem as it internally calls ha_commit_trans on the statement, which internally gets blocked on global read lock when closing the tables (sql_base.cc:close_thread_tables) where as in 5.1; this is entirely taken care; and ha_commit_stmt is not actually called from this function at all..
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
if (thd->locked_tables || prelocked_mode) { /* Let us commit transaction for statement. Since in 5.0 we only have one statement transaction and don't allow several nested statement transactions this call will do nothing if we are inside of stored function or trigger (i.e. statement transaction is already active and does not belong to statement for which we do close_thread_tables()). TODO: This should be fixed in later releases. */ ha_commit_stmt(thd); |
I am not sure if this is a bug or not; but does not harm anybody; other than if one has mysqldump running in “single-transaction mode; then if your application is using individual READ LOCKs and processing any SHOW commands; then it may be a problem. And in case if you already have READ LOCKS; then FLUSH TABLES WITH READ LOCK will anyway block till you release it explicitly
InnoDB show table status gets blocked: Yesterday I was going through the locks related code; and foun.. http://tinyurl.com/6kk6c3