How to lock an InnoDB table to prevent updates while copying this table?

Question

I would like to temporarily lock the table so that other parallel processes do not make changes to it. The reason for this is that this table will be copied to the temporary table, modified, and then copied back (well, actually, the original is actually discarded, and the new table is renamed). Then, after all this is completed, I want to unlock the table and hopefully have something that was taken while resuming the lock.

I also need to be able to read from a table that was locked to create a new table.

Here is what I tried, but it does not work.

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT); $mysqli->autocommit(false) // create a table to hold parts to keep $q1 = "CREATE TABLE new_table LIKE my_table;"; $res1 = $mysqli ->query($q1); // Lock table to avoid concurrent update issues $q2 = "LOCK TABLE my_table WRITE;"; $res2 = $mysqli ->query($q2); // Insert data to keep into new table $q3 = "INSERT INTO new_table SELECT * FROM my_table WHERE some_id IN (SELECT ID FROM table2)"; $res3 = $mysqli ->query($q3); // drop original table $q4 = "DROP TABLE my_table;"; $res4 = $mysqli ->query($q4); // rename new table $q5 = "RENAME TABLE new_table TO my_table;"; $res5 = $mysqli ->query($q5); $mysqli ->commit(); // commit changes and re-enable autocommit $q = "UNLOCK TABLES;"; $res = $mysqli ->query($q); 

To test using PHPmyadmin, I issued "SET AUTOCOMMIT = 0; LOCK TABLE my_table WRITE;" and then tried to delete something from my_table, and I was able to do it. I want to block it. In addition, after issuing a lock statement, the rest of the procedure fails and nothing changes.

Answer 1

Sorry for the long answer, but it will need to be answered in several parts.

1. When locking InnoDB tables with LOCK TABLES in general

Using LOCK TABLES with InnoDB actually works and can be demonstrated using two MySQL CLI instances connected to the same server (indicated by mysql-1 and mysql-2 ) in the example below. Generally, this should be avoided in any kind of production context due to exposure to customers, but sometimes this may be the only option.

Create a table and fill it with some data:

 mysql-1> create table a (id int not null primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql-1> insert into a (id) values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 

Lock table:

 mysql-1> lock tables a write; Query OK, 0 rows affected (0.00 sec) 

Try pasting from mysql-2 , which will wait for a lock wait:

 mysql-2> insert into a (id) values (4); 

Now open the table from mysql-1 :

 mysql-1> unlock tables; Query OK, 0 rows affected (0.00 sec) 

And finally, mysql-2 unlocks and returns:

 Query OK, 1 row affected (6.30 sec) 

2. Using phpMyAdmin for testing

Your test method using phpMyAdmin is not valid because phpMyAdmin does not support a constant connection to the server between requests from its web interface. To use any LOCK TABLES , START TRANSACTION , etc. locks, you need to maintain a connection while the locks are held.

3. Lock all tables needed during operation

How MySQL locks tables, once you used LOCK TABLES to explicitly lock something, you won’t be able to access any other tables that were not explicitly locked during the LOCK ... UNLOCK session. In the above example, you need to use:

 LOCK TABLES my_table WRITE, new_table WRITE, table2 READ; 

(I assume that table2 used in the subquery was not a typo.)

4. Atomic table exchange using RENAME TABLE

In addition, it should be noted that replacing an existing table with DROP TABLE , followed by RENAME TABLE , will lead to a brief moment when the table does not exist, and this can confuse clients who expect it to exist. As a rule, it is much better to do:

 CREATE TABLE t_new (...); <Populate t_new using some method> RENAME TABLE t TO t_old, t_new TO t; DROP TABLE t_old; 

This will result in an atomic replacement of the two tables.