sysbench花式采坑之二：自增值导致的主键冲突

系统管理员 2022-04-14 01:55 531阅读 0赞

上期[《sysbench花式采坑之一：自增值导致的TPS不可靠》][sysbench_TPS]介绍到，在sysbench压测过程中，如果自增值不为1会导致性能测试值偏高的现象，其实在发现这个现象之前，在单实例性能测试时我还遇到了一个主键冲突的问题。

### **| MySQL单实例sysbench压测时出现主键冲突** ###

[《sysbench花式采坑之一：自增值导致的TPS不可靠》][sysbench_TPS]这篇文章里解释了sysbench关于插入的操作是先随机生一个id，然后通过这个id先删掉一行，再通过这个id插入一行。

# 在1和压测语句中指定的 oltp_table_size 中生成一个随机数i
    i = sb_rand(1, oltp_table_size)
    # 删除 id 为 i 的列
    rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
    c_val = sb_rand_str([[
    ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
    pad_val = sb_rand_str([[
    ###########-###########-###########-###########-###########]])
    # 插入 id 为 i 的列
    rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))

看一下sysbench oltp压测中SQL具体的内容。

# 打开general_log
    mysql> set global general_log=1;
    # 压测语句，指定num-threads为1
    shell> sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=localhost --mysql-socket=/tmp/mysql-3306.sock --mysql-db=sbtest --mysql-user='root' --mysql-password='xxxxxx' --test=/usr/local/sysbench/share/sysbench/oltp.lua --oltp-table-size=100000 --oltp-tables-count=8 --num-threads=1 --report-interval=1 --max-requests=0 run
    # 截取日志
    shell> cat dk-1.log
    2018-10-07T11:37:02.380453Z 1711 Query BEGIN
    2018-10-07T11:37:02.380582Z 1711 Query SELECT c FROM sbtest1 WHERE id=49619
    2018-10-07T11:37:02.380840Z 1711 Query SELECT c FROM sbtest1 WHERE id=50396
    2018-10-07T11:37:02.380998Z 1711 Query SELECT c FROM sbtest1 WHERE id=49687
    2018-10-07T11:37:02.381356Z 1711 Query SELECT c FROM sbtest1 WHERE id=49867
    2018-10-07T11:37:02.381540Z 1711 Query SELECT c FROM sbtest1 WHERE id=50497
    2018-10-07T11:37:02.381654Z 1711 Query SELECT c FROM sbtest1 WHERE id=50190
    2018-10-07T11:37:02.381764Z 1711 Query SELECT c FROM sbtest1 WHERE id=49522
    2018-10-07T11:37:02.381873Z 1711 Query SELECT c FROM sbtest1 WHERE id=50290
    2018-10-07T11:37:02.381982Z 1711 Query SELECT c FROM sbtest1 WHERE id=49847
    2018-10-07T11:37:02.382327Z 1711 Query SELECT c FROM sbtest1 WHERE id=50374
    2018-10-07T11:37:02.382706Z 1711 Query SELECT c FROM sbtest1 WHERE id BETWEEN 50199 AND 50199+99
    2018-10-07T11:37:02.383052Z 1711 Query SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN 49799 AND 49799+99
    2018-10-07T11:37:02.383401Z 1711 Query SELECT c FROM sbtest1 WHERE id BETWEEN 50407 AND 50407+99 ORDER BY c
    2018-10-07T11:37:02.383731Z 1711 Query SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 39813 AND 39813+99 ORDER BY c
    2018-10-07T11:37:02.384016Z 1711 Query UPDATE sbtest1 SET k=k+1 WHERE id=44427
    2018-10-07T11:37:02.384196Z 1711 Query UPDATE sbtest1 SET c='39796464622-61637750575-93947547009-47134109736-07171516110-63345053556-59776318828-88369235114-00300639058-75856680209' WHERE id=50088
    2018-10-07T11:37:02.384338Z 1711 Query DELETE FROM sbtest1 WHERE id=50818
    2018-10-07T11:37:02.384481Z 1711 Query INSERT INTO sbtest1 (id, k, c, pad) VALUES (50818, 40688, '34959694295-76205879259-69202024081-99676022818-19182933803-60381845875-01731928300-26416934042-12567052432-22892123768', '05095121478-64779362588-34409017007-03135411714-21152054317')
    2018-10-07T11:37:02.384579Z 1711 Query COMMIT

可以看到oltp一个事务中包含18条SQL，其中有14条select、2条update、1条delete和1条insert，select和update的id都是随机生成的，delete和insert的id是一样的。

以id为3为例，正常情况下，是不会出现主键冲突问题的，比如此时有两个事务同时随机到了3这个id。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70][]

由上述过程看来，正常情况下是不会出现主键冲突的，因为若想插入一行，首先要删掉这一行，且删除操作受其他会话行锁的影响。

逻辑就是：

*  delete等待行锁-->insert不会执行-->不会出现主键冲突，
 *  delete成功-->我持有了这一行的行锁，在我插入成功之前其他事务都不能对这一行进行操作-->不会出现主键冲突

这么看来横竖都不会出现主键冲突，那我怎么老是报个主键冲突的错误呢？

FATAL: mysql_drv_query() returned error 1062 (Duplicate entry '25142' for key 'PRIMARY') for query 'INSERT INTO sbtest6 (id, k, c, pad) VALUES (25142, 24229, '47810027939-05079056430-17746446726-65189861997-62729316405-52380254349-47572050878-63091919627-24127885987-09251673046', '28933824552-12773012256-13972489057-17055446224-90582567020')'

没错，还是自增值不为1造成的。

当时我们的隔离级别是READ-COMMITTED，自增值设置为2，模拟一下sysbench自增值为2生成表的内容。

mysql> show variables like 'auto_in%';
    +--------------------------+-------+
    | Variable_name | Value |
    +--------------------------+-------+
    | auto_increment_increment | 2 |
    | auto_increment_offset | 1 |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    mysql> show variables like 'tx_isolation';
    +---------------+----------------+
    | Variable_name | Value |
    +---------------+----------------+
    | tx_isolation | READ-COMMITTED |
    +---------------+----------------+
    1 row in set (0.01 sec)
    mysql> select * from t3;
    +----+------+------+
    | id | age | name |
    +----+------+------+
    | 1 | 1 | aa |
    | 3 | 3 | bb |
    | 5 | 5 | cc |
    | 7 | 7 | dd |
    | 9 | 9 | ee |
    | 11 | 11 | ff |
    +----+------+------+
    6 rows in set (0.01 sec)

当sysbench同时有两个thread对id不存在的列进行删除插入操作会怎么样呢？下面以id为6为例，由上表可以看到id为6的这一行数据是不存在的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1][]

上面复现了READ-COMMITTED隔离级别下自增值不为1导致sysbench压测出现主键冲突的错误，那隔离级别为REPEATABLE-READ时会是什么样呢？同样以上表为例。

mysql> show variables like 'auto_in%';
    +--------------------------+-------+
    | Variable_name | Value |
    +--------------------------+-------+
    | auto_increment_increment | 2 |
    | auto_increment_offset | 1 |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    mysql> show variables like 'tx_isolation';
    +---------------+----------------+
    | Variable_name | Value |
    +---------------+----------------+
    | tx_isolation | REPEATABLE-READ |
    +---------------+----------------+
    1 row in set (0.01 sec)
    mysql> select * from t3;
    +----+------+------+
    | id | age | name |
    +----+------+------+
    | 1 | 1 | aa |
    | 3 | 3 | bb |
    | 5 | 5 | cc |
    | 7 | 7 | dd |
    | 9 | 9 | ee |
    | 11 | 11 | ff |
    +----+------+------+
    6 rows in set (0.01 sec)

以id为8为例，可以看到id为8的这一行数据是不存在的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2][]

### **| 总结** ###

*  当自增值为1时，sysbench压测不会出现主键冲突的错误
 *  当自增值不为1且在RC隔离级别时，sysbench压测会出现主键冲突的错误
 *  当自增值不为1且在RR隔离级别时，sysbench压测会加大出现死锁的概率
 *  RC级别的主键冲突和RR级别的死锁根本原因都是主键值之间存在空隙，而自增值不为1时用sysbench制作的数据刚好吻合主键值之间存在间隙的情况
 *  再鉴于之前自增值不为1会导致性能测试结果不准确的情况，再次强调sysbench压测最好把自增值设置为1

### **| 避免主键冲突的错误姿势** ###

当时出现主键冲突时没有分析出现象的原因，是采用了修改lua脚本的方式来避免主键冲突。

rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
    # 修改为
    rs = db_query("INSERT INTO " .. table_name .. " (k, c, pad) VALUES " .. string.format("(%d, '%s', '%s')",sb_rand(1, oltp_table_size) , c_val, pad_val))

上述方式是让插入使用利用自增主键来自动生成主键值避免主键冲突。

后来发现这个方式存在很大的隐患，sysbench压测id生成的值是根据指定的oltp-table-size的值来限制范围的，也就是说select、update、delete语句只会操作id值小于oltp-table-size的数据，但是因为我们修改插入采用自增的方式，插入的值都是id大于oltp-table-size的数据。

由于delete的原因，id值小于oltp-table-size的数据会越来越少，这样只删不增，会有一种sysbench结果越来越高的现象，甚至会出现一种极端的情况，我们指定的压测的范围内根本不存在数据了，sysbench发来的所有select、update、delete都在做空操作。

### **| 遗留问题** ###

两个事务同时以主键为where条件操作不存在的列时，为什么RC级别下事务2的delete不会阻塞事务1的insert，在RR级别下事务2的delete就会阻塞事务1的insert？

### **| 作者简介** ###

**李文航·沃趣科技数据库技术专家**

熟悉MySQL体系结构和工作原理、SQL调优、数据库故障诊断、数据迁移、备份恢复

[sysbench_TPS]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247484354&idx=1&sn=db450ba0f97a15186d5d84816bf26198&chksm=fb242acfcc53a3d9d0276ee5b87185c3d1d14d9ab4a51617768a46d7d917cae78c48252f4738&scene=21#wechat_redirect
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70]: /images/20220414/21886adb717e41cda74f75c0dc42aa18.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1]: /images/20220414/663c7c3ac090490fac49e86ede1d9228.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2]: /images/20220414/02dfbaca64e244be8768316f501f912b.png