MySQL Bugs: #22246: Allow LOCK TABLE WRITE lock a non-existent table.

Bug #22246	Allow LOCK TABLE WRITE lock a non-existent table.
Submitted:	11 Sep 2006 21:37	Modified:	12 Dec 2009 16:03
Reporter:	Shawn Green	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	4.1.22, 5.0.45, 5.1.43, 6.0.14	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, lock, rename

Description:
If you explicitly lock a table, you cannot rename it. However you can do an ALTER TABLE RENAME ... on the table.

Sample script:
DROP TABLE IF EXISTS tableA;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableAA;
DROP TABLE IF EXISTS tableBB;
CREATE TABLE tableA (a int);
CREATE TABLE tableB (b int);
LOCK TABLE tableA WRITE, tableB WRITE;
ALTER TABLE tableB RENAME tableBB;
RENAME TABLE tableA to tableAA;
UNLOCK TABLES;

Excerpt from the output:
mysql>ALTER TABLE tableB RENAME tableBB;
Query OK, 0 rows affected (0.00 sec)

mysql>RENAME TABLE tableA to tableAA;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

Query OK, 0 rows affected (0.00 sec)

localhost.test>show variables like 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 5.0.24-community-nt |
+---------------+---------------------+
1 row in set (0.00 sec)

localhost.test>

How to repeat:
Perform the test as described above.

Suggested fix:
Allow RENAME TABLE to use the LOCK granted to the existing user session instead of making it get its own LOCK.

Thank you for a problem report. Verified just as described with 5.0.26-BK on Linux. This is inconsistent behaviour and, thus, a bug.

Same problem in there :
query: 
---------
use testdb;
rename table1 to testdb.table1;
----------
response :
ERROR 1050 (42S01): Table 'table1' already exists

My environment is the following :
----------------------------------
Platform : Microsoft windows Xp Media Center Edition, v2002 sp2
mysql server version : mysql  Ver 14.12 Distrib 5.0.26, for Win32 (ia32)

Adding 4.1, as the bug is there too.

This is documented behaviour:
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
"When you execute RENAME, you cannot have any locked tables or active transactions."

After a discussion with Monty it has become clear that there is no way to fix this bug in the current locking framework without a change in the behaviour.
Proposed and rejected changes were:
- prohibit DDL under LOCK TABLES
- let DDL cancel LOCK TABLES locks
- allow LOCK TABLES to lock a non-existent table and under LOCK TABLES allow only DDL that uses the locked tables.

In future, when we have a better table-level deadlock resolution in place, it'll be possible to fix the bug without a change in behaviour.

If the bug is still considered severe enough to be fixed before that, please provide us with an opinion which of the three incompatible changes to choose and we will consider fixing it in an alpha/beta tree.

Thank you.

My proposed fix was to teach the RENAME TABLE how to recognize and inherit a lock already owned by the session for the table it is trying to change rather than requiring it to create its own, personal lock on the table.

I, as a user, should be able to lock a table or group of tables and be able to perform a sequence of actions on those tables, including RENAME TABLE, then release the lock. This bug report was about the fact that our implementation of RENAME TABLE does not recognize that the session already owns a lock it can "borrow" for it's own purposes.

None of the other three suggestions seem to provide for the ability for a user to place a manual lock on one or more tables then do a sequence of actions
>>Proposed and rejected changes were:
>>- prohibit DDL under LOCK TABLES
>>- let DDL cancel LOCK TABLES locks
>>- allow LOCK TABLES to lock a non-existent table and under LOCK TABLES
>>allow only DDL that uses the locked tables.

a) prohibit DDL: that can't work as you cannot always take a server down to lockout all other users.

b) cancel LOCK TABLES: that won't work as I may still need my user lock after I complete a DDL to perform another DDL on the same talbe or tables.

c) lock a phantom table: Do what? why?  A lock on the table already exists, thanks to a previous LOCK TABLES statement, why can't the DDL recognize this and "borrow" or "inherit" this from the user session?

Please reconsider your previous approaches to solving this bug.

Shawn,
a thing that you are perhaps missing is that LOCK TABLES locks are data locks, not metadata locks. Historically WRITE lock for MyISAM was exclusive, and thus MySQL allows ALTER TABLE under LOCK TABLES. But essentially this is a different type of lock, and internally we have to acquire a stronger lock when executing ALTER TABLE under LOCK TABLES (the lock on the table name).
Again, historically, RENAME works slightly differently, as it is possibly to do a multi-table rename in atomic fashion:

RENAME t1 to t2, t2 to t1; /* works atomically in one statement */
There is nothing internally RENAME could borrow - we can make sure RENAME works under LOCK TABLES, but that would be a substantial change internally, and we will have to think about possible deadlocks very carefully when doing that, as right now the differenes in what locks are acquired for what statement make the locking scheme quite convoluted.

Shawn, 
reading more your comments, I think you're missing the nature of LOCK TABLES statement with regard to DDL. 
Consider the following:
 1) ALTER TABLE ... RENAME doesn't keep the destination table locked when the statement is finished. That is, ALTER TABLE implicitly cancels LOCK TABLE lock on a table.
 2) DROP TABLE t1 under LOCK TABLES also implicitly cancels the lock on the table.
I know of no way to drop and recreate a table in MySQL in atomically. See also Bug#24144.

While I appreciate the difficulty in actually meeting the desired goal I fail to see how that affects the intended behavior of a LOCK TABLE command. 

As a user, I may want or need to gain temporary exclusive control over a table. The mechanism we have provided to the user is the LOCK TABLE command. 

I understand that the ALTER TABLE currently breaks the lock. Does it need to? I mean to the end user, this is not an expected behavior. This is a result of our implementation of ALTER TABLE and not because the user requested the lock to be released. Why can the results of the ALTER TABLE not be locked if the original table was also locked?

The arguments against why RENAME TABLE cannot be locked are also purely based on our implementation. The expectation to the user is that they grabbed a lock on the table. If I want to change that name to something else before I unlock that data, then I should be able to do so. The user cares not one whit about if it's a metadata lock or if it's a block on some kind of internal structure, they just know that they wanted to lock that table for exclusive use (or shared use in the case of a read lock).

A viable solution is to extend the LOCK TABLE statement to allow to lock a non-existent table. This is possible in Celosia and up  (new MDL) with medium risk/effort.
For consistency, we may want to alter the current behavior under LOCK TABLES when DROP and ALTER TABLE RENAME also drop LOCK TABLES lock. This will increase risk, since it is an incompatible change.

This is a feature request, WL#5403.
The effort is moderate (one week of development, one week for reviews, will need additional QA testing) for all versions starting from 5.5 (requires the new metadata locks).
The change involves a change in behavior, since along with it we no longer should silently drop LOCK TABLE locks for renamed or dropped tables.

Hi All,

As requested in yesterday's MySQL Road Show Oracle London City Office, I need this feature please.  I must ensure a couple of queries have exclusive use of tables before renaming, for example:

LOCK TABLE t WRITE, TABLE t2 WRITE ...
UPDATE t SET ...
RENAME TABLE t TO t_old, TABLE t2 TO t ...
UNLOCK TABLES;

Thanks