SELECT FOR UPDATE 语句报错 Write conflict

🪐 TiDB 技术问题 性能调优
1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

[TiDB 版本]
5.7.25-TiDB-v4.0.7

schema

CREATE TABLE `tasks_queue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bucket` varchar(32) DEFAULT NULL,
  `key` varchar(512) DEFAULT NULL,
  `identity` varchar(255) DEFAULT NULL,
  `length` bigint(20) DEFAULT NULL,
  `bytesTransferred` bigint(20) DEFAULT NULL,
  `state` enum('WAITING','RUNNING','SUCCESS','FAILED') DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `errorMessage` longtext DEFAULT NULL,
  `multiPartUploadParallelism` int(11) DEFAULT NULL,
  `runCount` int(11) DEFAULT NULL,
  `submitTime` timestamp NULL DEFAULT NULL,
  `startTime` timestamp NULL DEFAULT NULL,
  `finishTime` timestamp NULL DEFAULT NULL,
  `heartbeatTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY  (`bucket`,`key`)
) ;

SQL

# 50 个节点每隔 1s 执行以下 sql
START TRANSACTION;

SELECT *
FROM `tasks_queue`
WHERE `state` = 'WAITING'
OR (`state` = 'RUNNING' AND
TIME_TO_SEC(CURRENT_TIMESTAMP) - TIME_TO_SEC(`heartbeatTime`) > 60)
ORDER BY `submitTime`
LIMIT 1 FOR UPDATE;

UPDATE `tasks_queue` AS table1,
  (SELECT count(*) AS c
   FROM `tasks_queue`
   WHERE `state` = 'RUNNING'
     AND TIME_TO_SEC(`heartbeatTime`) - TIME_TO_SEC(`startTime`) <= 60
  ) AS table2
SET `state`         = 'RUNNING',
    `host`          = #{task.host},
    `heartbeatTime` = CURRENT_TIMESTAMP()
WHERE `updateTime` = #{task.updateTime}
  AND `bucket` = #{task.bucket}
  AND `key` = #{task.key}
  AND table2.c < #{maxCount};
  
COMMIT;

Exception

org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction.  Cause: java.sql.SQLException: Write conflict, txnStartTS=422372140194730836, conflictStartTS=422372140194729828, conflictCommitTS=422372140247157492, key={tableID=871, handle=5174890} primary={tableID=871, handle=5174890} [try again later]
### Cause: java.sql.SQLException: Write conflict, txnStartTS=422372140194730836, conflictStartTS=422372140194729828, conflictCommitTS=422372140247157492, key={tableID=871, handle=5174890} primary={tableID=871, handle=5174890} [try again later]
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:226)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:217)
	at com.zhihu.unionstore.task.mysql.ReadTasksThread.run(ReadTasksThread.java:50)
	at java.util.TimerThread.mainLoop(Timer.java:555)
	at java.util.TimerThread.run(Timer.java:505)
Caused by: java.sql.SQLException: Write conflict, txnStartTS=422372140194730836, conflictStartTS=422372140194729828, conflictCommitTS=422372140247157492, key={tableID=871, handle=5174890} primary={tableID=871, handle=5174890} [try again later]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1555)
	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:387)
	at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
	at org.apache.ibatis.transaction.jdbc.JdbcTransaction.commit(JdbcTransaction.java:72)
	at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:244)
	at org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:119)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:223)
	... 4 more
2

请确认一下目前这套集群采用的是不是 默认模式？还是乐观模式。

3

是默认模式

4

理论上来说，当一个节点提for update语句的时候，其他节点提同样的语句会被锁住，直到第一个抢到锁的节点commit，其他节点才会释放，开始抢锁

5

这两个事务应该都是悲观事务，通过 显示事务 会有等锁和抢锁。可以确认一下两个事务是不是都是显示事务。

6

tidb_txn_mode

  • 作用域：SESSION | GLOBAL
  • 默认值：“pessimistic”
  • 这个变量用于设置事务模式。TiDB v3.0 支持了悲观事务，自 v3.0.8 开始，默认使用悲观事务模式。
  • 但如果从 3.0.7 及之前的版本升级到 >= 3.0.8 的版本，不会改变默认事务模型，即 只有新创建的集群才会默认使用悲观事务模型 。
  • 将该变量设置为 “optimistic” 或 “” 时，将会使用乐观事务模式。