事务与批处理查询，以避免重复的MySQL插入

Question

我有一个PHP脚本(deleteAndReInsert.php)，它删除所有name = 'Bob'行，然后插入1000个带有name = 'Bob'的新行。这工作正常，最初为空的表最终按预期总共有 1000 行。

$query = $pdo->prepare("DELETE FROM table WHERE name=?");
$query->execute(['Bob']);
$query = $pdo->prepare("INSERT INTO table (name, age) VALUES (?,?)");
for ($i = 0; $i < 1000; $i++)
{
$query->execute([ 'name' => 'Bob', 'age' => 34 ]);
}

问题是如果我运行deleteAndReInsert.php两次(几乎在同一时间)，最终表包含 1000 多行。

似乎正在发生的事情是，第一次运行的DELETE查询完成，然后调用许多(但不是全部)1000 INSERTS。

然后，第二个DELETE查询在前 1000 个INSERTS完成之前开始和完成(例如 1000 个INSERTS中的 350 个完成)。现在运行第二个 1000INSERTS，我们最终得到总共 1650 行而不是总共 1000 行，因为在调用第二个DELETE后仍有1000 - 350 = 650 INSERTS。

防止此问题发生的正确方法是什么？我应该将所有内容包装在事务中，还是应该进行 1 次批量插入调用而不是 1000 次单独插入？显然，我可以同时实现这两种解决方案，但我很好奇哪一个可以保证防止此问题。

Answer 1

使用事务 + 批量插入

我认为解决问题的正确方法是使用事务。我们将进行删除+批量插入，这是代码：

$pdo->beginTransaction();
$query = $pdo->prepare("DELETE FROM table WHERE name=?");
$query->execute(['Bob']);
$sql = "INSERT INTO table (name, age) VALUES ".implode(', ',array_fill(0,999, '(:name, :age)'));
$query = $sth->prepare($sql);
$query->execute(array([ ':name' => 'Bob', 'age' => 34 ]));
$pdo->commit();

仅使用批量插入(不起作用)

为什么只做批量插入不能解决问题？想象一下以下场景：

1. 第一个脚本删除并删除前 1000 行。 ==> 将 1000 行转换为 0。
2. 第二个脚本尝试删除，但没有行。 ==> 将 0 行转换为 0。
3. 第一个(或第二个)脚本进行 1000 次批处理插入。 ==> 将 1000 行转换为 1000 行。
第二个
4. (或第一个)脚本进行第二个 1000 批处理插入。==> 从 1000 行到 2000 行。

这就是该过程是异步的，因此第二个脚本可能会在第一个脚本完成插入之前读取表。

使用辅助表模拟锁(不推荐)

如果我们没有交易，我们将如何解决这个问题？我认为这是一个交叉的做法。

这是一个典型的并发问题，其中有两个或多个进程修改相同的数据。为了解决这个问题，我建议你使用第二个辅助表来模拟锁并控制对主表的并发访问。

CREATE TABLE `access_table` (
`access` TINYINT(1) NOT NULL DEFAULT 1
)

而在剧本中

// Here we control the concurrency
do{
$query = $st->prepare('UPDATE access_table SET access = 0 WHERE access = 1');
$query ->execute();
$count = $query ->rowCount();
// You should put here a random sleep
}while($count === 0);

//Here we know that only us we are modifying the table
$query = $pdo->prepare("DELETE FROM table WHERE name=?");
$query->execute(['Bob']);
$query = $pdo->prepare("INSERT INTO table (name, age) VALUES (?,?)");
for ($i = 0; $i < 1000; $i++)
{
$query->execute([ 'name' => 'Bob', 'age' => 34 ]);
}

//And finally we open the table for other process
$query = $st->prepare('UPDATE access_table SET access = 1 WHERE access = 0');
$query ->execute();

您可以根据问题调整表，例如，如果 INSERTS/DELETE 是按名称排列的，则可以使用varchar(XX)for name。

CREATE TABLE `access_table` (
`name` VARCHAR(50) NOT NULL,
`access` TINYINT(1) NOT NULL DEFAULT 1
)

对于此方案

1. 第一个脚本将访问值更改为 0。
2. 第二个脚本无法更改值，因此它保留在循环中
3. 第一个脚本使删除/插入
4. 第一个脚本将状态更改为 1
5. 第二个脚本将访问值更改为 0 并破坏外观。
6. 第二个脚本使删除/插入
7. 第二个脚本将状态更改为 1

这是因为更新是原子的，这意味着两个进程不能同时更新同一日期，所以当第一个脚本更新值时，第二个脚本不能修改，那个动作是原子的。

我希望对你有所帮助。

Answer 2

其他解决方案的替代方法是在脚本启动时创建一个实际的锁定文件，并在运行之前检查它是否存在。

while( file_exists("isrunning.lock") ){
sleep(1);
}
//create file isrunning.lock
$myfile = fopen("isrunning.lock", "w");
//deleteAndinsert code
//delete lock file when finished
fclose($myfile);
unlink("isrunning.lock");

Answer 3

您必须锁定操作，并且在插入结束之前不要释放它。

你可以在文件系统上使用一个文件，但正如@chris Hass建议的那样，你可以像这样使用symfony的软件包：

安装symfony Lock：

composer require symfony/lock

你灵魂包括作曲家的自动加载

require __DIR__.'/vendor/autoload.php';

然后在您的删除和重新插入中.php ：

use SymfonyComponentLockLockFactory;
use SymfonyComponentLockStoreSemaphoreStore;
//if you are on windows or for any reason this store(FlockStore) didnt work
// you can use another stores available here: https://symfony.com/doc/current/components/lock.html#available-stores 
$store = new FlockStore();
$factory = new LockFactory($store);
$lock = $factory->createLock('bob-recreation');
$lock->acquire(true)
$query = $pdo->prepare("DELETE FROM table WHERE name=?");
$query->execute(['Bob']);
$query = $pdo->prepare("INSERT INTO table (name, age) VALUES (?,?)");
for ($i = 0; $i < 1000; $i++)
{
$query->execute([ 'name' => 'Bob', 'age' => 34 ]);
}
$lock->release();

发生了什么事

正如您提到的，发生的事情是竞争条件：

如果两个并发进程正在访问共享资源， 这类似于可能需要用锁保护的关键部分

Answer 4

计数是近似值

SHOW TABLE STATUS(以及此类的许多变体)仅提供行数的估计值。 (请说出您是如何获得"1650"的。

精确的计数方法是

SELECT COUNT(*) FROM table;

---

进一步讨论

有两种主要方法可以进行"交易锁定"。 两者都可以防止其他连接干扰。

• 自动提交：

  SET autocommit = ON;   -- probably this is the default
  -- Now each SQL statement is a separate "transaction"
  

• 开始。。。犯

  BEGIN;  -- (this is performed in a variety of ways by the db layer)
  delete...
  insert...
  COMMIT;  --everything above either entire happens or is entirely ROLLBACK'd
  

性能：

• DELETE-->TRUNCATE
• 批量插入(单INSERT1000 行)
• BEGIN...COMMIT
• LOAD DATA而不是INSERT

但是，没有任何性能技术可以改变您遇到的问题 - 除了"巧合"。

<小时 />

为什么是1650？

(或其他一些数字)InnoDB的事务性质要求它挂在以前被删除或插入的行的副本上，直到COMMIT(无论是显式还是自动提交)。 这会用可能会消失的"行"使数据库混乱。 因此，任何试图死心塌地计算确切的行数都是不切实际的。

这导致使用不同的技术来估计行计数。 它是这样的：该表占用这么多磁盘，我们估计平均行是这么多字节。 将这些除以得到行计数。

这导致您关于删除未完成的理论。 就任何SQL而言，删除已完成。 但是，临时保存的 1000 行副本尚未从表中彻底清除。 因此，行数的计算不精确。

<小时 />

锁定？

没有任何锁定技术可以"修复"1650。 如果您不希望其他线程在运行 Delete+Insert 实验时插入/删除行，则需要锁定。 为此，应使用锁定。

同时，如果要精确计数，则必须使用COUNT(*)。

Answer 5

防止此问题发生的正确方法是什么？

这不是问题，并且是访问数据库上同一表的两个页面的预期行为。

我应该将所有

内容包装在事务中，还是应该进行 1 次批量插入调用而不是 1000 次单独插入？显然，我可以同时实现这两种解决方案，但我很好奇哪一个可以保证防止此问题。

除了根据您运行的页数限制插入量为 n000 之外，不会产生盲目的差异。

---

方案 1 - 不执行任何操作

有两个页面一个接一个地运行或在相似的时间运行。这就是为什么由于 execute 方法中的隐式事务，您会看到 1650 条记录，从而允许其他进程(在您的案例中为页面)访问表中的数据。

插入一行插入一行一

Action	Page a	Page b	表行计数
1	删除所有鲍勃		0
。	插入第 1 行
351	插入一行	删除所有鲍勃	0
352	插入一行		2
。	插入一行		4
1001	插入	行 插入一行	1298
1002		插入一行	1299
。		插入一行	...
1352		插入一行	1650

如果存在另一个实例，则可以检查服务器上的进程列表并阻止脚本执行。

你按了两次deleteAndReInsert.php，每个脚本都有 1001 个命令，首先是删除所有name = Bob，其余的再次插入 Bob 1000 次。 所以你总共有 2002 个命令，并且你没有声明一些让 Mysql 理解你想要同步执行它的东西，并且你的 2002 命令将并发运行，并会导致意外的结果。(超过 1000 个插入name= Bob)。该过程可以这样描述：

->delete `name= bob` (clear count = 0)
->insert `name = bob`
->insert `name = bob`
->insert `name = bob`
->insert `name = bob`
....
->insert `name = bob`
->delete `name= bob` (the second time deleteAndReInsert.php 
hit deleted at 300 times insert `name = bob` of first
time deleteAndReInsert.php -> clear count rows = 0)
->insert `name = bob`
->insert `name = bob`
->insert `name = bob`
....
-> insert `name = bob` (now it could be more than 1000 rows)

因此，如果您想要结果是 1000 行。 你必须让 MySQL 明白： 我想要 删除和重新插入.php 同步运行，逐步。 并存档，您可以执行以下解决方案之一：

1. 使用LOCK TABLE语句锁定表并在完成后UNLOCK，除非第一个脚本完成，否则第二个脚本不能对表执行任何操作。
2. 将所有内容包装在事务BEGIN COMMIT中，然后MySQL将作为原子操作运行。 (好)
3. 通过 Redis 模拟LOCK(Redlock)，文件 .. 使您的操作同步运行(良好)

希望这可以帮助您解决问题。

您要做的是发布LOCK TABLE ... WRITE作为工作的第一个语句，RELEASE TABLES作为最后一个语句。

然后，将删除一千行，然后插入，然后删除，然后再次插入。

但整个过程对我来说闻起来像一个XY问题。你真正需要做的是什么？

因为我经常需要做你描述的这样的事情(例如"刷新"一些摘要)，而最好的方法是，在这种情况下，在我看来，既不锁定也不删除/插入，而是

INSERT INTO table
ON DUPLICATE KEY UPDATE ...

就我而言，如果我只需要添加或刷新记录，那就足够了。

否则，我通常会添加一个"time"字段，允许我识别刷新周期中"遗漏"的所有记录;这些记录 - 并且只有那些 - 在完成后被删除。

例如，假设我需要用复杂的 PHP 计算来计算许多客户的最大财务风险，然后插入到表中以方便使用。每天晚上刷新每个客户的值，然后在第二天使用"缓存"表。截断表并重新插入所有内容是一种痛苦。

相反，我计算所有值并构建一个非常大的多重 INSERT 查询(如果需要，我可以将其拆分为 X 个较小的多个查询)：

SELECT barrier:=NOW();
INSERT INTO `financial_exposures` ( ..., amount, customer_id, last_update )
VALUES 
( ..., 172035.12, 12345, NOW()),
( ..., 123456.78, 12346, NOW()),
...
( ..., 450111.00, 99999, NOW())
ON DUPLICATE KEY UPDATE 
amount=VALUES(amount),
last_update=VALUES(last_update);
DELETE FROM financial_exposures WHERE last_update < @barrier;

新客户被插入，旧客户被更新，除非他们的值不变(在这种情况下，MySQL跳过更新，节省时间)，并且在每个时刻，始终存在一条记录 - 更新前的记录，或更新后的记录。删除的客户将在最后一步删除。

当您有一个需要经常使用和更新的表时，这效果更好。您可以添加一个没有锁的事务(SET autocommit = 0在INSERT之前，COMMIT WORK在DELETE之后)，以确保所有客户端都看到整个更新，就好像它立即发生一样。

@Pericodes的答案是正确的，但代码片段中存在错误。

您可以通过将代码包装在事务中来避免重复(停止重复不需要批量插入)。

最好使用 1 个批量插入而不是 1000 个单独的插入，即使这不是必需的。

您可以通过运行此代码两次(几乎同时)进行测试，该表最终正好包含 1000 条记录。

<?
$pdo->beginTransaction();
$query = $pdo->prepare("DELETE FROM t1 WHERE name=?");
$query->execute(['Bob']);
$query = $pdo->prepare("INSERT INTO t1 (name, age) VALUES (:name,:age)");
for ($i = 0; $i < 100; $i++)
{
$query->execute([ 'name' => 'Bob', 'age' => 34 ]);
}
$pdo->commit();

有几个答案提到了锁(数据库级和代码级)，但这些对于这个问题不是必需的，并且是矫枉过正的imo。