lighting import data encounter check tasks exclusively failed error

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

[2023/12/27 02:10:20.471 +00:00] [INFO] [import.go:1238] ["everything imported, stopping periodic actions"]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:521] ["run failed"] [step=4] [error="[Lightning:Restore:ErrRestoreTable]restore table `db_286`.`xxx` failed: check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446572304660693005, conflictStartTS=446572304660693006, conflictCommitTS=0, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary=[]byte(nil), originalPrimaryKey=, reason=Optimistic [try again later]"]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:531] ["the whole procedure failed"] [takeTime=34h48m22.770884941s] [error="[Lightning:Restore:ErrRestoreTable]restore table `db_286`.`xxx` failed: check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446572304660693005, conflictStartTS=446572304660693006, conflictCommitTS=0, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary=[]byte(nil), originalPrimaryKey=, reason=Optimistic [try again later]"]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:168] ["tables failed to be imported"] [count=3]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:170] [-] [table=`db_286`.`xxx`] [status=index_imported] [error="check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446572304660693005, conflictStartTS=446572304660693006, conflictCommitTS=0, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary=[]byte(nil), originalPrimaryKey=, reason=Optimistic [try again later]"]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:170] [-] [table=`db_357`.`xxx`] [status=index_imported] [error="check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446579091857211408, conflictStartTS=446579091857211399, conflictCommitTS=446579091857211415, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalPrimaryKey=7480000000000278e15f7297a34c7ef532e3fc, reason=Optimistic [try again later]"]
[2023/12/27 02:10:20.473 +00:00] [ERROR] [import.go:170] [-] [table=`db_95`.`xxx`] [status=index_imported] [error="check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446593955068116994, conflictStartTS=446593954989211714, conflictCommitTS=446593955081224211, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalPrimaryKey=7480000000000278e15f7297a34c7ef532e3fc, reason=Optimistic [try again later]"]
[2023/12/27 02:10:20.473 +00:00] [DEBUG] [engine.go:157] ["closing local engine"] [engine=6c467f34-a7d2-52ed-b3c1-922a23238987] [stack="github.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Engine).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/engine.go:157\ngithub.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Backend).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/local.go:741\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:482\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:596\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:388\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]
[2023/12/27 02:10:20.474 +00:00] [DEBUG] [engine.go:157] ["closing local engine"] [engine=ad10273e-4a93-59cf-b6d4-c170a11b4155] [stack="github.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Engine).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/engine.go:157\ngithub.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Backend).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/local.go:741\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:482\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:596\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:388\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]
[2023/12/27 02:10:20.475 +00:00] [DEBUG] [engine.go:157] ["closing local engine"] [engine=38259e64-0da2-544e-b7f0-2d712fe6333f] [stack="github.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Engine).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/engine.go:157\ngithub.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Backend).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/local.go:741\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:482\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:596\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:388\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]
[2023/12/27 02:10:20.476 +00:00] [DEBUG] [engine.go:157] ["closing local engine"] [engine=b2aa20d0-f189-573d-8103-6240af961593] [stack="github.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Engine).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/engine.go:157\ngithub.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Backend).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/local.go:741\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:482\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:596\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:388\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]
[2023/12/27 02:10:20.476 +00:00] [DEBUG] [engine.go:157] ["closing local engine"] [engine=e89473c6-499b-5f73-89e2-8bb37c57eb54] [stack="github.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Engine).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/engine.go:157\ngithub.com/pingcap/tidb/br/pkg/lightning/backend/local.(*Backend).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/backend/local/local.go:741\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).Close\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:482\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:596\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:388\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]
[2023/12/27 02:10:20.523 +00:00] [INFO] [checksum.go:499] ["service safe point keeper exited"]
[2023/12/27 02:10:20.523 +00:00] [ERROR] [main.go:103] ["tidb lightning encountered error stack info"] [error="[Lightning:Restore:ErrRestoreTable]restore table `db_286`.`xxx` failed: check tasks exclusively failed: commit transaction failed: Error 9007 (HY000): Write conflict, txnStartTS=446572304660693005, conflictStartTS=446572304660693006, conflictCommitTS=0, key={tableID=162017, tableName=lightning_metadata.task_meta_v2, handle=1703289192239522812}, originalKey=7480000000000278e15f7297a34c7ef532e3fc, primary=[]byte(nil), originalPrimaryKey=, reason=Optimistic [try again later]"]

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

v7.1.2

[lance6716]

the problem is m.session is a sql.DB, not sql.Conn, it can't ensure multiple calls are using the same connection. So SET SESSION tidb_txn_mode = 'pessimistic'; does not take effect

tidb/lightning/pkg/importer/meta_manager.go

Lines 666 to 670 in 72e5460

	exec := &common.SQLWithRetry{
	DB: m.session,
	Logger: log.FromContext(ctx),
	}
	err = exec.Exec(ctx, "enable pessimistic transaction", "SET SESSION tidb_txn_mode = 'pessimistic';")