Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
Lily2025 opened this issue Aug 13, 2021 · 7 comments · May be fixed by #27209
Open

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

Lily2025 opened this issue Aug 13, 2021 · 7 comments · May be fixed by #27209
Assignees
@tiancaiamao
Labels
sig/sql-infra SIG: SQL Infra type/enhancement The issue or PR belongs to an enhancement.

Comments

@Lily2025
Copy link

Lily2025 commented Aug 13, 2021 •
edited
Loading

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: 30cf15a
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 |
@Lily2025 Lily2025 added the type/bug The issue is confirmed as a bug. label Aug 13, 2021
@jingshanglu
Copy link
Contributor

@Lily2025 Can you add information about executing sql?

@tiancaiamao
Copy link
Contributor

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
Copy link
Contributor

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
Copy link
Contributor

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

@tiancaiamao tiancaiamao linked a pull request Aug 13, 2021 that will close this issue
Draft
12 tasks
@jingshanglu jingshanglu added the sig/transaction SIG:Transaction label Aug 13, 2021
@tiancaiamao tiancaiamao self-assigned this Aug 13, 2021
@ichn-hu ichn-hu mentioned this issue Aug 13, 2021
Closed
@cfzjywxk cfzjywxk added sig/sql-infra SIG: SQL Infra and removed sig/transaction SIG:Transaction labels Aug 23, 2021
@bb7133
Copy link
Member

bb7133 commented Aug 23, 2021

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

@bb7133 bb7133 added type/enhancement The issue or PR belongs to an enhancement. and removed type/bug The issue is confirmed as a bug. severity/major labels Aug 23, 2021
@tiancaiamao
Copy link
Contributor

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

@GMHDBJD GMHDBJD mentioned this issue Apr 24, 2024
Merged
13 tasks
@D3Hunter
Copy link
Contributor

D3Hunter commented Jul 9, 2024 •
edited
Loading

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tiancaiamao tiancaiamao

Labels
sig/sql-infra SIG: SQL Infra type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[DNM] ddl: use pessimistic transaction for allocating global ID tiancaiamao/tidb
7 participants
@bb7133 @tiancaiamao @D3Hunter @cfzjywxk @jebter @jingshanglu @Lily2025