使用INSERT ... ON DUPLICATE KEY UPDATE在并发场景下可能导致数据覆盖

【标题描述】：
【测试类型：SQL功能】【测试版本：5.0.0】 使用INSERT ... ON DUPLICATE KEY UPDATE在并发场景下可能导致数据覆盖

【操作系统和硬件信息】：
 CentOS Linux release 7.9.2009 (Core)
 Linux host01 3.10.01160.el7.x86_64 1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

【测试环境】：
1主2备

【被测功能】：
DML语句  INSERT ... ON DUPLICATE KEY UPDATE

【测试类型】：
并发功能

【数据库版本】：
gaussdb (openGauss) 5.0.0 build a08d17c3

【预置条件】：
1. 存在一张有唯一索引的表 test_tbl。
    sql
    CREATE TABLE test_tbl (
        id INT PRIMARY KEY,
        value INT,
        version INT DEFAULT 0
    );
    CREATE UNIQUE INDEX idx_id ON test_tbl(id);
    INSERT INTO test_tbl (id, value) VALUES (1, 100);

【操作步骤】：
1. 开启两个数据库会话（Session A 和 Session B）。
2. 在 Session A 中执行：
    sql
    BEGIN;
    INSERT INTO test_tbl (id, value) VALUES (1, 200)
    ON DUPLICATE KEY UPDATE value = value + 1, version = version + 1;
     先不提交
    
3. 在 Session B 中执行：
    sql
    BEGIN;
    INSERT INTO test_tbl (id, value) VALUES (1, 300)
    ON DUPLICATE KEY UPDATE value = value + 1, version = version + 1;
     此语句会被阻塞，等待Session A
    
4. 在 Session A 中执行 COMMIT;。
5. 观察 Session B 的执行情况并提交。
6. 查询最终数据：SELECT  FROM test_tbl WHERE id = 1;

【预期输出】：
 Session B 的更新应该基于 Session A 提交后的最新数据。
 最终数据应为：id=1, value=102, version=2。（因为两次更新都是在原值100基础上+1）

【实际输出】：
 Session B 的更新直接覆盖了 Session A 的更新。
 最终数据为：id=1, value=101, version=1。value的值不是102，而是101，且version只增加了1次。

【原因分析】：
1.  这个问题的根因：（由开发分析）ON DUPLICATE KEY UPDATE 在实现时，在并发场景下，后提交的事务可能直接使用了它自己读取到的旧值进行更新，而没有考虑到其执行期间该行已被其他事务更新，导致了“更新丢失”。
2.  问题推断过程：两个事务都读取了初始值（value=100），然后分别计算并更新，后提交的覆盖了先提交的。
3.  还有哪些原因可能造成类似现象：缺乏行锁或锁机制失效、乐观锁实现有误。
4.  该问题是否有临时规避措施：有。在应用层使用悲观锁，例如在事务开始时先执行 SELECT FOR UPDATE 锁定该行。
5.  问题解决方案：需要修改 ON DUPLICATE KEY UPDATE 的执行逻辑，确保在更新时能检测到并发冲突，或者使用更安全的锁机制。
6.  预计修复问题时间：（由开发/项目经理填写）

【日志信息】：
 附件：gs_log_20240521.tar.gz 包含了问题发生时间段的数据库日志。
 截图：![image](https://placehold.co/600x400?text=此处为两个会话的操作和结果截图)

【测试代码】：
sql
 预置条件
DROP TABLE IF EXISTS test_tbl;
CREATE TABLE test_tbl (
    id INT PRIMARY KEY,
    value INT,
    version INT DEFAULT 0
);
INSERT INTO test_tbl (id, value) VALUES (1, 100);

会话A
BEGIN;
INSERT INTO test_tbl (id, value) VALUES (1, 200) ON DUPLICATE KEY UPDATE value = value + 1, version = version + 1;
 暂停，等待会话B执行到阻塞点

会话B (在另一个连接中执行)
BEGIN;
INSERT INTO test_tbl (id, value) VALUES (1, 300) ON DUPLICATE KEY UPDATE value = value + 1, version = version + 1;
 此时会阻塞

回到会话A提交
COMMIT;

回到会话B，此时语句执行成功，提交
COMMIT;

验证结果
SELECT  FROM test_tbl WHERE id = 1;

openGauss/openGauss-connector-jdbc

内容风险标识

评论 (1)

openGauss/openGauss-connector-jdbc .gitee-modal { width: 500px !important; }

内容风险标识