Bug #9510 LOCKING table doesn't prevent reading not locked tables in SQL_CACHE queries

• Submitted: 31 Mar 2005 2:27
• Modified: 31 Mar 2005 10:00
• Reporter: Gleb Paharenko
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.10a
• OS: Linux (Linux)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[31 Mar 2005 2:27] Gleb Paharenko

Description:
Locking the table usually prevent us to read other (not locked tables). That was mentioned
by Bob O'Neill at:
  
  http://lists.mysql.com/mysql/181927

How to repeat:
mysql> lock tables ta read;
Query OK, 0 rows affected (0.00 sec)

mysql> select SQL_CACHE count(*) from tb;
+----------+
| count(*) |
+----------+
|     4096 |
+----------+
1 row in set (0.00 sec)

I assume that the query select SQL_CACHE caount(*) from tb already in cache.

[31 Mar 2005 2:46] Jorge del Conde

I was unable to repeat this with 4.1.11 from bk:

mysql> LOCK TABLES a READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_CACHE COUNT(*) FROM b;
ERROR 1100 (HY000): Table 'b' was not locked with LOCK TABLES
mysql>

[31 Mar 2005 9:43] Sergei Golubchik

Of course, reading from query cache is not affected by LOCK TABLES:

mysql> create table t1 select 1;
mysql> create table t2 select 2;
mysql> select * from t1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> lock table t2 read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Similary, you can lock the table and do SELECT from another connection.

See also http://bugs.mysql.com/bug.php?id=2693

[31 Mar 2005 10:00] Oleksandr Byelkin

Access to other tables during lock in force is prohibited because MySQL can't lock them too (to avoid deadlock all tables should be locked at once).
During QC answer non-locked tables are not touched, so it can answer.