mysql - 如何锁定 InnoDB 表以防止在复制该表时进行更新？

Question

我想暂时锁定一个表以防止其他并发进程对其进行更改。这样做的原因是这个表将被复制到一个临时表，改变，然后复制回来(实际上原始表被删除并且新表被重命名)。然后在完成所有这些之后，我想解锁表并希望在锁定恢复期间尝试任何操作。

我还需要能够从已锁定的表中读取以构建新表。

这是我尝试过的方法，但它似乎不起作用。

$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); 

为了测试，我使用 PHPmyadmin 发出了“SET AUTOCOMMIT=0; LOCK TABLE my_table WRITE;”查询，然后尝试从 my_table 中删除某些内容，我能够这样做。我想阻止这个。此外，在发出锁定语句后，过程的其余部分失败并且没有任何改变。

Answer 1

很抱歉回答很长，但这需要分多个部分回答。

<强>1。在一般情况下使用 LOCK TABLES 锁定 InnoDB 表

在 InnoDB 中使用 LOCK TABLES 实际上是可行的，并且可以通过连接到同一服务器的两个 MySQL CLI 实例(由 mysql-1 和mysql-2) 在下面的例子中。由于对客户的影响，通常应避免在任何类型的生产环境中使用它，但有时它可能是唯一的选择。

创建一个表并用一些数据填充它:

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

锁定表格:

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

尝试从 mysql-2 插入，它将挂起等待锁:

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

现在从 mysql-1 解锁表:

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

最后 mysql-2 解锁并返回:

Query OK, 1 row affected (6.30 sec)

<强>2。使用 phpMyAdmin 进行测试

您使用 phpMyAdmin 的测试方法无效，因为 phpMyAdmin 不会在来自其 Web 界面的查询之间保持与服务器的持久连接。为了使用任何类型的锁定 LOCK TABLES、START TRANSACTION 等，您需要在锁定时保持连接。

<强>3。锁定工作期间需要的所有表

MySQL 锁定表的方式，一旦您使用LOCK TABLES 显式锁定任何内容，您将无法访问在LOCK< 期间未显式锁定的任何其他表 ... UNLOCK session 。在上面的示例中，您需要使用:

LOCK TABLES my_table WRITE, new_table WRITE, table2 READ;

(我假设子选择中使用的 table2 不是拼写错误。)

<强>4。使用 RENAME TABLE

进行原子表交换

此外，我应该注意，使用 DROP TABLE 替换现有表，然后使用 RENAME TABLE 将导致表不存在的短暂时刻，这可能会造成混淆期望它存在的客户。这样做通常要好得多:

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

这将执行两个表的原子交换。