search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.21k stars 5.84k forks source link

when run sysbench prepare，report “Write conflict” error #27197

Open Lily2025 opened 3 years ago

Lily2025 commented 3 years ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1、run sysbench prepare threads=1024 tableNum = 512 tableSize = 10000000000

2. What did you expect to see? (Required)

no “Write conflict” error

3. What did you see instead (Required)

sysbench report “Write conflict” error

writeconflict

4. What is your TiDB version? (Required)

------------+ | Release Version: v5.2.0 Edition: Community Git Commit Hash: 30cf15a59db11c34ffe05fc926152a43327eaa61 Git Branch: heads/refs/tags/v5.2.0 UTC Build Time: 2021-08-09 12:01:42 GoVersion: go1.16.4 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false |

jingshanglu commented 3 years ago

@Lily2025 Can you add information about executing sql?

tiancaiamao commented 3 years ago

The conflicts on the meta key NextGlobalID

The create table statement looks like this:

CREATE TABLE sbtest181(
  id BIGINT NOT NULL AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
) /*! ENGINE = innodb */

One create table statement will use a transaction to increase the NextGlobalID meta key twice, here is the calling stack:

github.com/pingcap/tidb/structure.(*TxStructure).Inc(0xc011c7adc0, 0x5268a90, 0xc, 0xc, 0x1, 0xa, 0x5ecfba739940001, 0xc011c7adc0)
    /home/genius/project/src/github.com/pingcap/tidb/structure/string.go:64 +0x99
github.com/pingcap/tidb/meta.(*Meta).GenGlobalIDs(0xc012001a70, 0x1, 0x0, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/meta/meta.go:130 +0xcc
github.com/pingcap/tidb/ddl.(*ddl).addBatchDDLJobs.func1(0x3e0e340, 0xc000122000, 0x3e4d420, 0xc011ee71f0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_worker.go:285 +0x86
github.com/pingcap/tidb/kv.RunInNewTxn(0x3e0e340, 0xc000122000, 0x3e43568, 0xc000f894c0, 0x7f2255658b01, 0xc00fc81d50, 0xc00058e3c0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/kv/txn.go:46 +0x103
github.com/pingcap/tidb/ddl.(*ddl).addBatchDDLJobs(0xc001044850, 0xc00fc81f38, 0x1, 0xa)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_worker.go:283 +0xf5
github.com/pingcap/tidb/ddl.(*ddl).limitDDLJobs(0xc001044850)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_worker.go:273 +0x225
created by github.com/pingcap/tidb/ddl.(*ddl).Start
github.com/pingcap/tidb/structure.(*TxStructure).Inc(0xc011f74d00, 0x5268a90, 0xc, 0xc, 0x1, 0xa, 0x5ecfba739900001, 0xc011f74d00)
    /home/genius/project/src/github.com/pingcap/tidb/structure/string.go:64 +0x99
github.com/pingcap/tidb/meta.(*Meta).GenGlobalIDs(0xc0006a7cb0, 0x1, 0x0, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/meta/meta.go:130 +0xcc
github.com/pingcap/tidb/ddl.(*ddl).genGlobalIDs.func1(0x3e0e340, 0xc000122000, 0x3e4d420, 0xc011fb03b0, 0x0, 0xb)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl.go:441 +0x74
github.com/pingcap/tidb/kv.RunInNewTxn(0x3e0e340, 0xc000122000, 0x3e43568, 0xc000f894c0, 0x1, 0xc011e8e270, 0xc011e8e2d0, 0x23de1cf)
    /home/genius/project/src/github.com/pingcap/tidb/kv/txn.go:46 +0x103
github.com/pingcap/tidb/ddl.(*ddl).genGlobalIDs(0xc001044850, 0x1, 0x9, 0xc0000539d8, 0x4, 0xc0000539dc, 0x4)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl.go:432 +0xac
github.com/pingcap/tidb/ddl.(*ddl).assignTableID(0xc001044850, 0xc011e6bc80, 0x4, 0xc0000539dc)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_api.go:1761 +0x38
github.com/pingcap/tidb/ddl.(*ddl).CreateTableWithInfo(0xc001044850, 0x3e51518, 0xc00fb71200, 0xc0000539d8, 0x4, 0xc0000539dc, 0x4, 0xc011e6bc80, 0x0, 0x0, ...)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_api.go:1880 +0x173
github.com/pingcap/tidb/ddl.(*ddl).CreateTable(0xc001044850, 0x3e51518, 0xc00fb71200, 0xc0006c8420, 0xc0000cb500, 0xc0000c9e00)
    /home/genius/project/src/github.com/pingcap/tidb/ddl/ddl_api.go:1821 +0x2f0
github.com/pingcap/tidb/executor.(*DDLExec).executeCreateTable(0xc011f535f0, 0xc0006c8420, 0xc0006a79b0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/executor/ddl.go:332 +0x9a
github.com/pingcap/tidb/executor.(*DDLExec).Next(0xc011f535f0, 0x3e0e3b0, 0xc0006a79b0, 0xc011ed3680, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/executor/ddl.go:182 +0x9b7
github.com/pingcap/tidb/executor.Next(0x3e0e3b0, 0xc0006a79b0, 0x3e12978, 0xc011f535f0, 0xc011ed3680, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/executor/executor.go:285 +0x2de
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor(0xc011e5cd00, 0x3e0e3b0, 0xc0006a79b0, 0x3e12978, 0xc011f535f0, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/executor/adapter.go:590 +0x33c
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay(0xc011e5cd00, 0x3e0e3b0, 0xc0006a79b0, 0x3e12978, 0xc011f535f0, 0x5889000, 0x3e0e301, 0x0, 0x0, 0x0, ...)
    /home/genius/project/src/github.com/pingcap/tidb/executor/adapter.go:471 +0x1e5
github.com/pingcap/tidb/executor.(*ExecStmt).Exec(0xc011e5cd00, 0x3e0e3b0, 0xc0006a79b0, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/executor/adapter.go:420 +0x707
github.com/pingcap/tidb/session.runStmt(0x3e0e3b0, 0xc0006a77d0, 0xc00fb71200, 0x3e250f8, 0xc011e5cd00, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/session/session.go:1786 +0x37f
github.com/pingcap/tidb/session.(*session).ExecuteStmt(0xc00fb71200, 0x3e0e3b0, 0xc0006a77d0, 0x3e29018, 0xc0006c8420, 0x0, 0x0, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/session/session.go:1680 +0xab1
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt(0xc010dd57d0, 0x3e0e3b0, 0xc0006a77d0, 0x3e29018, 0xc0006c8420, 0xc0006dcea0, 0x3e0e3b0, 0xc0006a77d0, 0x1162b12)
    /home/genius/project/src/github.com/pingcap/tidb/server/driver_tidb.go:218 +0x6b
github.com/pingcap/tidb/server.(*clientConn).handleStmt(0xc0110db200, 0x3e0e308, 0xc0006a77d0, 0x3e29018, 0xc0006c8420, 0xc0006c37c0, 0x0, 0x0, 0x1, 0x0, ...)
    /home/genius/project/src/github.com/pingcap/tidb/server/conn.go:1818 +0x1bb
github.com/pingcap/tidb/server.(*clientConn).handleQuery(0xc0110db200, 0x3e0e308, 0xc011de3ec0, 0xc011ca2701, 0xd1, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/server/conn.go:1690 +0x492
github.com/pingcap/tidb/server.(*clientConn).dispatch(0xc0110db200, 0x3e0e308, 0xc011de3ec0, 0xc011ca2700, 0xd2, 0xd1, 0x0, 0x0)
    /home/genius/project/src/github.com/pingcap/tidb/server/conn.go:1215 +0xafd
github.com/pingcap/tidb/server.(*clientConn).Run(0xc0110db200, 0x3e0e3b0, 0xc010dd4c30)
    /home/genius/project/src/github.com/pingcap/tidb/server/conn.go:978 +0x296
github.com/pingcap/tidb/server.(*Server).onConn(0xc000da7790, 0xc0110db200)
    /home/genius/project/src/github.com/pingcap/tidb/server/server.go:501 +0xa53
created by github.com/pingcap/tidb/server.(*Server).startNetworkListener
    /home/genius/project/src/github.com/pingcap/tidb/server/server.go:404 +0x8fc
tiancaiamao commented 3 years ago

From the TiDB log:

[2021/08/13 13:54:00.464 +08:00] [INFO] [session.go:2948] ["CRUCIAL OPERATION"] [conn=989] [schemaVersion=27] [cur_db=mytest4] [sql="CREATE TABLE sbtest491(\n  id BIGINT NOT NULL AUTO_INCREMENT,\n  k INTEGER DEF
AULT '0' NOT NULL,\n  c CHAR(120) DEFAULT '' NOT NULL,\n  pad CHAR(60) DEFAULT '' NOT NULL,\n  PRIMARY KEY (id)\n) /*! ENGINE = innodb */ "] [user=root@172.16.6.193]
[2021/08/13 13:54:00.465 +08:00] [INFO] [prewrite.go:326] ["prewrite encounters lock"] [session=0] [lock="key: 6d4e657874476c6f62ff616c494400000000fb0000000000000073, primary: 6d4e657874476c6f62ff616c49440000000
0fb0000000000000073, txnStartTS: 426989070585954352, lockForUpdateTS:0, minCommitTs:426989070651490343, ttl: 3096, type: Put, UseAsyncCommit: false"]
....
[2021/08/13 13:54:00.485 +08:00] [INFO] [session.go:2948] ["CRUCIAL OPERATION"] [conn=1023] [schemaVersion=27] [cur_db=mytest4] [sql="CREATE TABLE sbtest512(\n  id BIGINT NOT NULL AUTO_INCREMENT,\n  k INTEGER DEFAULT '0' NOT NULL,\n  c CHAR(120) DEFAULT '' NOT NULL,\n  pad CHAR(60) DEFAULT '' NOT NULL,\n  PRIMARY KEY (id)\n) /*! ENGINE = innodb */ "] [user=root@172.16.6.193]
[2021/08/13 13:54:00.494 +08:00] [INFO] [session.go:2948] ["CRUCIAL OPERATION"] [conn=1027] [schemaVersion=27] [cur_db=mytest4] [sql="CREATE TABLE sbtest511(\n  id BIGINT NOT NULL AUTO_INCREMENT,\n  k INTEGER DEFAULT '0' NOT NULL,\n  c CHAR(120) DEFAULT '' NOT NULL,\n  pad CHAR(60) DEFAULT '' NOT NULL,\n  PRIMARY KEY (id)\n) /*! ENGINE = innodb */ "] [user=root@172.16.6.193]
[2021/08/13 13:54:00.494 +08:00] [INFO] [session.go:2948] ["CRUCIAL OPERATION"] [conn=1029] [schemaVersion=27] [cur_db=mytest4] [sql="CREATE TABLE sbtest509(\n  id BIGINT NOT NULL AUTO_INCREMENT,\n  k INTEGER DEFAULT '0' NOT NULL,\n  c CHAR(120) DEFAULT '' NOT NULL,\n  pad CHAR(60) DEFAULT '' NOT NULL,\n  PRIMARY KEY (id)\n) /*! ENGINE = innodb */ "] [user=root@172.16.6.193]
[2021/08/13 13:54:00.495 +08:00] [INFO] [session.go:2948] ["CRUCIAL OPERATION"] [conn=1047] [schemaVersion=27] [cur_db=mytest4] [sql="CREATE TABLE sbtest510(\n  id BIGINT NOT NULL AUTO_INCREMENT,\n  k INTEGER DEFAULT '0' NOT NULL,\n  c CHAR(120) DEFAULT '' NOT NULL,\n  pad CHAR(60) DEFAULT '' NOT NULL,\n  PRIMARY KEY (id)\n) /*! ENGINE = innodb */ "] [user=root@172.16.6.193]
[2021/08/13 13:54:00.503 +08:00] [WARN] [txn.go:65] [RunInNewTxn] ["retry txn"=426989070585954405] ["original txn"=426989070585954405] [error="[kv:9007]Write conflict, txnStartTS=426989070585954405, conflictStartTS=426989070585954352, conflictCommitTS=426989070651490364, key=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} primary=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} [try again later]"]
[2021/08/13 13:54:00.503 +08:00] [WARN] [txn.go:65] [RunInNewTxn] ["retry txn"=426989070585954396] ["original txn"=426989070585954396] [error="[kv:9007]Write conflict, txnStartTS=426989070585954396, conflictStartTS=426989070585954352, conflictCommitTS=426989070651490364, key=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} primary=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} [try again later]"]
[2021/08/13 13:54:00.503 +08:00] [WARN] [txn.go:65] [RunInNewTxn] ["retry txn"=426989070585954412] ["original txn"=426989070585954412] [error="[kv:9007]Write conflict, txnStartTS=426989070585954412, conflictStartTS=426989070585954352, conflictCommitTS=426989070651490364, key=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} primary=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} [try again later]"]
[2021/08/13 13:54:00.503 +08:00] [WARN] [txn.go:65] [RunInNewTxn] ["retry txn"=426989070560264257] ["original txn"=426989070560264257] [error="[kv:9007]Write conflict, txnStartTS=426989070560264257, conflictStartTS=426989070585954352, conflictCommitTS=426989070651490364, key=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} primary=[]byte{0x6d, 0x4e, 0x65, 0x78, 0x74, 0x47, 0x6c, 0x6f, 0x62, 0xff, 0x61, 0x6c, 0x49, 0x44, 0x0, 0x0, 0x0, 0x0, 0xfb, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x73} [try again later]"]
tiancaiamao commented 3 years ago

So, the problem is, creating new tables need to modify the NextGlobalID meta key. When the concurrency is too high ... the write conflict happens. @Lily2025

bb7133 commented 3 years ago

The current behavior is by-design and I'll change it as the 'enhancement'.

tiancaiamao commented 3 years ago

I have a PR change the optimistic to pessimistic, it may alleviate the problem, waiting for feedback from the QA.

D3Hunter commented 3 months ago

RunInNewTxn retries max 100 times, but here we have 1024 threads, threads except the first 100 will exhaust retry count. write conflict shouldn't be counted for retry count