renaming a locked table

user_33165 0
Viewed 4535

When migrating a table to a new schema I want to make sure to have an atomic switch to the new table using the copy and rename procedure. Hence I am trying to rename a locked table like this:

CREATE TABLE foo_new (...)

-- copy data to new table, might take very long
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id <= 3;

LOCK TABLES foo WRITE, foo_new WRITE;

-- quickly copy the tiny rest over
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id > 3;

-- now switch to the new table
RENAME TABLE foo TO foo_old, foo_new TO foo;

UNLOCK TABLES;

Unfortunately that results in ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction.

How should this be done differently?

This is with mariadb:10.1.

mariadb mysql
4 Answers

"No data should be written or read between the LOCK and UNLOCK STATEMENT."

I met the same matter, and found the reason in MySQL Docs:

MySQL8.0

As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES statement, provided that they are locked with a WRITE lock or are the product of renaming WRITE-locked tables from earlier steps in a multiple-table rename operation.

MySQL5.7

To execute RENAME TABLE, there must be no active transactions or tables locked with LOCK TABLES.

By the way, in MySQL 5.7, when table is locked by "LOCK tables tbl WRITE" statement, the lock will be released because of executing "ALTER TABLE tbl_0 RENAME TO tbl_1" and strange behavior will be happening in the same session and new session. 

# MySQL 5.7

# session 0
mysql> lock tables tbl_0 WRITE;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE tbl_0 RENAME TO tbl_1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tbl_1;
ERROR 1100 (HY000): Table 'tbl_1' was not locked with LOCK TABLES

# then start new session 
# session 1
mysql> select * from tbl_1;
...
1 row in set (0.01 sec)

# session 0
mysql> unlock tables;

Hope it could help.

user_67815660
Related