StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I lock an InnoDB table to prevent updates while that table is being copied?
text
Body
copied!<p>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.</p> <p>I also need to be able to read from the table that has been locked to build the new table.</p> <p>Here is what I tried, but it doesn't appear to work.</p> <pre><code>$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); </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload