`Write conflict` when concurrent creation of bindings
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
```
package main
import (
"context"
"database/sql"
"sync"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8"
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
defer db.Close()
db.Exec("drop table if exists t;")
db.Exec("create table t(a int, b int, index idx(a));")
peers := []string{
"root:@tcp(127.0.0.1:4000)/test?charset=utf8",
"root:@tcp(127.0.0.1:4001)/test?charset=utf8",
}
dbs := make([]*sql.DB, len(peers))
for i := 0; i < len(dbs); i++ {
db, err := sql.Open("mysql", peers[i])
if err != nil {
panic(err)
}
dbs[i] = db
}
defer func() {
for i := 0; i < len(dbs); i++ {
dbs[i].Close()
}
}()
var wg sync.WaitGroup
startTime := time.Now()
f := func(db *sql.DB) {
for i := 0; i < 10; i++ {
wg.Add(1)
go func() error {
defer wg.Done()
c, err := db.Conn(context.Background())
if err != nil {
panic(err)
}
defer c.Close()
ss := []string{
"create global binding for update t set a = a + 1 using update t set a = a + 1;",
"update t use index(idx) set a = a + 1;",
"update t ignore index(idx) set a = a + 1",
"drop global binding for update t set a = a + 1",
}
for {
if time.Now().Sub(startTime) > 10*time.Minute {
return nil
}
for _, s := range ss {
if _, err := c.ExecContext(context.Background(), s); err != nil {
println(s, time.Now().Sub(startTime))
panic(err)
}
}
}
return nil
}()
}
}
for i := 0; i < len(dbs); i++ {
f(dbs[i])
}
wg.Wait()
if _, err := db.Exec("drop global binding for select * from t"); err != nil {
panic(err)
}
if _, err := db.Exec("drop global binding for update t set a = a + 1"); err != nil {
panic(err)
}
if _, err := db.Exec("drop global binding for delete from t where a < 10"); err != nil {
panic(err)
}
}
```
### 2. What did you expect to see? (Required)
No error
### 3. What did you see instead (Required)
```
panic: Error 9007: Write conflict, txnStartTS=434600045202112518, conflictStartTS=434600045162790918, conflictCommitTS=434600045228326914, key={tableID=37, indexID=2, indexValues={1850238838459440656, 2047133, }} primary={tableID=37, handle=1} [try again later]
```
In TiDB log
```
[2022/07/15 14:46:44.351 +08:00] [INFO] [conn.go:1149] ["command dispatched failed"] [conn=5010874710007742871] [connInfo="id:5010874710007742871, addr:127.0.0.1:52204 status:10, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="create global binding for update t set a = a + 1 using update t set a = a + 1;"] [txn_mode=PESSIMISTIC] [timestamp=0] [err="[kv:9007]Write conflict, txnStartTS=434600045202112518, conflictStartTS=434600045162790918, conflictCommitTS=434600045228326914, key={tableID=37, indexID=2, indexValues={1850238838459440656, 2047133, }} primary={tableID=37, handle=1} [try again later]\nprevious statement: INSERT INTO mysql.bind_info VALUES ('update `test` . `t` set `a` = `a` + ?','UPDATE `test`.`t` SET `a`=`a` + 1', 'test', 'enabled', '2022-07-15 14:46:44.217', '2022-07-15 14:46:44.217', 'utf8', 'utf8_bin', 'manual')"]
[2022/07/15 14:46:44.386 +08:00] [INFO] [lock_resolver.go:1014] ["resolveLock rollback"] [lock="key: 7480000000000000255f698000000000000001017570646174652060ff7465737460202e20ff6074602073657420ff606160203d206061ff60202b203f000000fc017465737400000000fb0380000000001e8732, primary: 7480000000000000255f728000000000000001, txnStartTS: 434600045202112518, lockForUpdateTS:434600045228326928, minCommitTs:434600045267648515, ttl: 3716, type: Put, UseAsyncCommit: false, txnSize: 1"]
[2022/07/15 14:46:44.391 +08:00] [INFO] [lock_resolver.go:1014] ["resolveLock rollback"] [lock="key: 7480000000000000255f7280000000001f3d27, primary: 7480000000000000255f728000000000000001, txnStartTS: 434600045202112518, lockForUpdateTS:434600045228326928, minCommitTs:434600045267648515, ttl: 20212, type: Put, UseAsyncCommit: false, txnSize: 1137"]
[2022/07/15 14:46:44.391 +08:00] [INFO] [lock_resolver.go:1014] ["resolveLock rollback"] [lock="key: 7480000000000000255f7280000000001e85c1, primary: 7480000000000000255f728000000000000001, txnStartTS: 434600045202112518, lockForUpdateTS:434600045228326928, minCommitTs:434600045267648515, ttl: 20212, type: Put, UseAsyncCommit: false, txnSize: 1137"]
```
### 4. What is your TiDB version? (Required)
https://github.com/pingcap/tidb/tree/release-6.1-20220622
Actually, when we test in 1a89decdb192cbdce6a7b0020d71128bc964d30f commit, the error appear occasionally.
But in b21d36dc7a0efb60f9472c9c3c36dc78507471f1 commit(next commit after the previous one, but this commit is related to br, it's a little strange), the error appear every time.
Contributor guide
Assessment
This issue has not been assessed yet.