sysbench花式採坑之二：自增值導致的主鍵衝突

上期 《sysbench花式採坑之一：自增值導致的TPS不可靠》 介紹到，在sysbench壓測過程中，如果自增值不為1會導致效能測試值偏高的現象，其實在發現這個現象之前，在單例項效能測試時我還遇到了一個主鍵衝突的問題。

| MySQL單例項sysbench壓測時出現主鍵衝突

《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。

由上述過程看來，正常情況下是不會出現主鍵衝突的，因為若想插入一行，首先要刪掉這一行，且刪除操作受其他會話行鎖的影響。

邏輯就是：

• 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的這一行資料是不存在的。

上面復現了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的這一行資料是不存在的。

| 總結

• 當自增值為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調優、資料庫故障診斷、資料遷移、備份恢復