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: | |
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 |
[11 Sep 2006 21:37]
Shawn Green
[12 Sep 2006 8:56]
Valeriy Kravchuk
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.
[6 Nov 2006 15:14]
alan Kubrick
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)
[8 Nov 2006 14:10]
Tomash Brechko
Adding 4.1, as the bug is there too.
[16 Nov 2006 14:10]
Sergei Golubchik
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."
[17 Nov 2006 17:58]
Konstantin Osipov
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.
[17 Nov 2006 18:17]
MySQL Verification Team
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.
[21 Nov 2006 16:40]
Konstantin Osipov
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.
[21 Nov 2006 16:50]
Konstantin Osipov
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.
[6 Dec 2008 18:06]
MySQL Verification Team
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).
[15 Dec 2009 17:50]
Konstantin Osipov
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.
[2 Aug 2010 18:40]
Konstantin Osipov
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.
[7 Feb 2018 9:51]
james wang
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