How can I lock an InnoDB table to prevent updates while that table is being copied?

Question

I would like to temporarily lock a table to prevent other concurrent processes from making changes to it. The reason for this is that this table is going to be copied to a temp table, altered, and then copied back (well original is actually dropped and new table is renamed). Then after all this is complete, I want to unlock the table and hopefully have anything that was attempted during the lock resume.

I also need to be able to read from the table that has been locked to build the new table.

Here is what I tried, but it doesn't appear to 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've issued the "SET AUTOCOMMIT=0; LOCK TABLE my_table WRITE;" query and then tried to delete something from my_table and I was able to do so. I want to block this. Also, after issuing the lock statement, the rest of the procedure fails and nothing is altered.

Solution

Sorry for the long answer, but this will need to be answered in multiple parts.

1. On locking InnoDB tables with LOCK TABLES in general

Using LOCK TABLES with InnoDB does in fact work, and can be demonstrated with two instances of the MySQL CLI connected to the same server (denoted by mysql-1 and mysql-2) in the below example. It should generally be avoided in any sort of production context because of the impact to clients, but sometimes it can be the only option.

Create a table and populate 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 the table:

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

Try to insert from mysql-2, which will hang waiting on the lock:

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

Now unlock the table from mysql-1:

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

And finally mysql-2 unblocks and returns:

Query OK, 1 row affected (6.30 sec)

2. Using phpMyAdmin for testing

Your testing method using phpMyAdmin is invalid because phpMyAdmin does not maintain a persistent connection to the server between queries from its web interface. In order to use any sort of locking LOCK TABLES, START TRANSACTION, etc., you need to maintain a connection while the locks are held.

3. Locking all tables needed during work

The way that MySQL locks tables, once you have used LOCK TABLES to explicitly lock anything, you will not be able to access any other tables that were not locked explicitly during the LOCK ... UNLOCK session. In your above example, you need to use:

LOCK TABLES my_table WRITE, new_table WRITE, table2 READ;

(I am assuming table2 used in the subselect was not a typo.)

4. Atomic table swap using RENAME TABLE

Additionally, I should note that replacing the existing table using DROP TABLE followed by RENAME TABLE will cause a brief moment where the table does not exist, and this may confuse clients that expect it to exist. It is generally 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 perform an atomic swap of the two tables.

Comments

[Lightness Races in Orbit]

"doesn't appear to work" What debugging have you performed?

[compcentral]

I edited the question to let you know what is failing. I just want to know the proper procedure for locking an INNODB table to prevent updates.

[Lightness Races in Orbit]

That's better. Have you checked permissions? Are you sure the deletion was immediately applied and not "queued"? Have you investigated whether turning auto-commit off is causing a problem? I've never found myself having to do that.

[compcentral]

permissions are fine. removing autocommit made no difference

[Samuel Åslund]

My MySQL complains that I may not have locked tables when doing a RENAME. "Can't execute the given command because you have active locked tables or an active transaction" Any idea what I'm doing wrong?

[John]

Often you see simple answers for beginner questions upvoted 500+ times. Then you see a great answer for a relevant good question upvoted only 9 times in 4 years! Thanks, especially #4 was new to me and helped me solve a potential race condition.