如何在复制InnoDB表时锁定它以防止更新？

Question

如何在复制InnoDB表时锁定它以防止更新？

3

我想临时锁定一张表，以防止其他并发进程对其进行更改。原因是这个表将被复制到一个临时表中，进行修改，然后再复制回来（实际上，原来的表会被删除，新表会被重命名）。完成所有操作后，我想解锁表格，并希望在锁定期间进行的任何尝试都能够继续。

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

以下是我尝试过的内容，但似乎并不起作用。

$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中删除某些内容，结果删除成功。我想阻止这种情况发生。此外，在发出锁定语句后，其余的过程会失败且不会进行任何修改。

- compcentral

似乎无法工作。你进行了哪些调试？ - Lightness Races in Orbit

我编辑了问题，让您知道哪里出了问题。我只想知道锁定INNODB表以防止更新的正确程序。 - compcentral

这样就好了。你检查过权限了吗？你确定删除操作是立即执行的，而不是“排队”等待执行的吗？你调查过关闭自动提交是否会导致问题吗？我从来没有发现自己需要这样做。 - Lightness Races in Orbit

权限没问题。移除自动提交也没有任何影响。 - compcentral

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jeremycole · Accepted Answer

很抱歉回答比较长，需要分几个部分来解释。

1. 关于使用LOCK TABLES锁定InnoDB表的一般情况

使用LOCK TABLES锁定InnoDB表确实有效，并且可以通过下面的示例在连接到同一服务器的两个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会话期间将无法访问未显式锁定的任何其他表。在上面的示例中，您需要使用：

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;

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