删除然后插入有时会失败，并带有重复的密钥 [英] Delete then insert occasionally fails with duplicate key

问题描述

我们有一个看起来像这样的表:

We have a table that looks like this:

 appointment_id | team_id
----------------|---------
 1001           | 1
 1005           | 4
 1009           | 7

在此表中， appointment_id 是主要索引，而 team_id 只是常规索引.

In this table appointment_id is the primary index and team_id is just a regular index.

用于创建表的代码:

CREATE TABLE `appointment_primary_teams` (
  `appointment_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  PRIMARY KEY (`appointment_id`),
  KEY `team_id` (`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

但是，偶尔以下代码失败:

// Even though it looks like we are making 2 different PDO connections here
// the return is the same instance of PDO shared by 2 instances of a class for
// running queries. (It is how our system allows 2 different prepared queries
// at the same time)
$remove_query = database::connect('master_db');
$insert_query = database::connect('master_db');

$remove_query->prepare("
    DELETE FROM `appointment_primary_teams` WHERE appointment_id = :appointment_id
");
$insert_query->prepare("
    INSERT INTO `appointment_primary_teams` (
        `appointment_id`,
        `team_id`
    ) VALUES (
        :appointment_id,
        :team_id
    )
");

// Looping through a list of appointment data
foreach($appointments as $appointment) {
    // Runs fine
    $remove_query->bind(':appointment_id', $appointment['id'], CAST_INT);
    $remove_query->run();

    // Occasionlly errors saying $appointment['id'] already exists
    $insert_query->bind(':appointment_id', $appointment['id'], CAST_INT);
    $insert_query->bind(':team_id', $appointment['team_id'], CAST_INT);
    $insert_query->run();
}

确切的错误是:

Database Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1001' for key 'PRIMARY'

起初，我认为这是API中的一种竞争条件，用户双击了提交按钮，但是我们的系统记录了所有请求，我可以确认用户仅发送了1个请求.

At first, I thought this was a race condition within our API where the user was double clicking a submit button, but our system logs all requests and I can confirm that the user is only sending 1 request.

我假设这是由于MySQL中的某种竞争条件而失败，但是我不确定如何防止这种情况.如果是这样，我可以告诉脚本休眠几毫秒，但这不是理想的解决方案，因为如果数据库完全挂起，问题可能会再次出现.

I am assuming that this is failing due to some type of race condition within MySQL, however I am unsure of how to prevent it. If that's true, I could just tell the script to sleep for a few milliseconds, but that's not an ideal solution, because if the DB hangs at all the issue can come back.

我的问题:是什么导致此问题，以及如何防止该错误?

My Question: What is causing this issue, and how do I prevent this error?

这对于Amazon RDS服务器(MySQL 5.6.27);PHP是在Amazon Linux AMI版本2017.09的Ngnix 1.13.9上运行的7.0.27版本.

This for an Amazon RDS server (MySQL 5.6.27); PHP is version 7.0.27 running on Ngnix 1.13.9 on Amazon Linux AMI release 2017.09.

注意::某些代码已更改，以删除专有信息并简化问题，但是我保留了代码的所有功能.

NOTE: Some of the code has been changed to remove proprietary information and simplify the issue, however I have preserved all the functionality of the code.

请注意，尽管显示了代码，但只有1个PDO连接实例在使用中.在运行此代码后，连接ID会恢复原样，这意味着它与MySQL的连接相同.

To be clear, despite the code shown there is only 1 instance of a PDO connection in use. After the running this code the connection IDs came back as the same, meaning that it's the same connection to MySQL.

这最终成为MySQL自身内部的一种竞争条件.我最好的猜测是竞争状况是在查询队列中(在查询完全运行之前，MySQL正在返回PHP)或在MySQL的内存中索引(其中，在运行下一个查询时MySQL不会更新索引).

This ended up being somehow a race-condition within MySQL itself; My best guess is the race condition is either in query queuing (where MySQL is returning to PHP before the query has run completely) or MySQL's in-memory indexes (where MySQL isn't updating the index by the time the next query is run)

我已经完成了多个版本的测试，以尝试确保发生了这种情况，所有测试均指向该版本.如果我不得不猜测这可能是由AWS的一个配置文件解决的，但是在这一点上，我别无选择，只能采用tadman建议的 REPLACE INTO 语法.

I have done several versions of tests to try and make sure that's what's going on, and all the tests point to that. If I had to guess this could probably be fixed by one of AWS's configuration files, but at this point I have no choice but resort to the REPLACE INTO syntax as tadman has suggested.

推荐答案

解决竞争条件的最可靠方法是首先避免出现排序问题.用一个查询替换一对查询:

The most reliable way to fix a race condition is to avoid having a sequencing problem in the first place. Replace the pair of queries with one query:

INSERT INTO `appointment_primary_teams` (
    `appointment_id`,
    `team_id`
) VALUES (
    :appointment_id,
    :team_id
) ON DUPLICATE KEY UPDATE team_id=VALUES(team_id)

这是一个原子操作，它将插入记录或更新现有记录，而无需 DELETE .这是维护此类关系记录的一种很好的通用方法.

This is an atomic operation and it will either insert a record or update an existing record, no DELETE required. This is a good general-purpose approach to maintaining these sorts of relationship records.

另一种方法是使用更笨拙的 REPLACE INTO 方法:

The alternative is the more heavy-handed REPLACE INTO approach:

REPLACE INTO `appointment_primary_teams` (
    `appointment_id`,
    `team_id`
) VALUES (
    :appointment_id,
    :team_id
)

这将占用任何现有记录.不利的一面是它就像原子 DELETE / INSERT 对一样，如果新的 PRIMARY KEY 值是 AUTO_INCREMENT，它们就会分配一个新的对.就您而言，这不是问题，所以这不是问题.

This stomps any existing records. The down-side to this is it acts like an atomic DELETE/INSERT pair which allocates new PRIMARY KEY values if those are AUTO_INCREMENT. In your case it isn't, so this is not an issue.

获得类似竞争条件的方法是， INSERT 查询必须与 DELETE 查询同时运行.只有在有两个连接的情况下才有可能，这可能是因为同时接收到两个都试图更改记录的请求，或者是因为单个实例以某种方式并行地运行了两个查询.

The way you get race conditions like that is that the INSERT query must be running at the same time as the DELETE query. That's only possible if there's two connections, and that could be because there's two requests being received simultaneously that are both attempting to alter the record, or because the single instance is somehow running both queries in parallel.

这篇关于删除然后插入有时会失败，并带有重复的密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！