TiDB Read-Write Table Conflict Issue

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIdb读写表冲突问题

| username: Inkjade

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.2
[Reproduction Path] Operations performed that led to the issue
Logical code: as follows:
// Multi-threaded batch insert
insert into customer_reach_standard_record values(…);

// Multi-threaded read statistics:
select count(*) from customer_reach_standard_record;

// Or queries such as:
SELECT t1.customer_no FROM customer_reach_standard_record t1 where t1.customer_no = ? AND t1.standard_type = ? limit 1

[Encountered Issue: Lock exception on the business side
Application-side error log:

The error may exist in class path resource [mapper/CustomerReachStandardRecordMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT t1.customer_no FROM customer_reach_standard_record t1 where t1.customer_no = ? AND t1.standard_type = ? limit 1

Cause: java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795

; uncategorized SQLException; SQL state [HY000]; error code [1105]; other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795; nested exception is java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy198.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:90)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy224.getByCustomerAndType(Unknown Source)
at com.yss.reward.service.impl.CustomerReachStandardRecordServiceImpl.lambda$checkCustomerBatch$0(CustomerReachStandardRecordServiceImpl.java:113)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795
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:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute$original$BR1eDmhd(PreparedStatement.java:1242)
at com.mysql.jdbc.PreparedStatement.execute$original$BR1eDmhd$accessor$r4nNE4ok(PreparedStatement.java)
at com.mysql.jdbc.PreparedStatement$auxiliary$kUNBnNaX.call(Unknown Source)

| username: tidb狂热爱好者 | Original post link

If you use queries within a transaction, this will happen.
Do not use select * for update;

| username: tidb菜鸟一只 | Original post link

I don’t understand. These SQL statements can’t all be in one transaction, right?

| username: zhanggame1 | Original post link

Is there any issue with read-only or write?

| username: Raymond | Original post link

Are you using the RR isolation level? Try changing it to RC and see.

| username: redgame | Original post link

Unable to acquire the lock.

| username: Inkjade | Original post link

The isolation level of the transaction is RC level.

It can be understood as multiple different people operating:
Task A: Perform batch operations, insert data, and check for duplicate insertions before inserting.
Task B: Users query the batch-inserted data on the page, mainly for pagination queries.

| username: Inkjade | Original post link

Did not use select for update;

| username: zhanggame1 | Original post link

Reading by default should not lock, unless “for update” is added. Now the lock should have nothing to do with reading, right? Will closing the read transaction still report an error? According to the official documentation, if there is no “for update,” TiDB performs snapshot reads and cannot see the updated data at the time of the transaction.

| username: 春风十里 | Original post link

Please check

TiDB Lock Conflict Handling | PingCAP Documentation Center

First, check whether the transaction mode is pessimistic lock mode or optimistic lock mode. Both modes can trigger read-write conflicts, and version 6.5.2 should default to pessimistic lock mode.
The official documentation states:

Handling Optimistic Lock Conflict Issues

The following introduces common lock conflict handling methods in optimistic transaction mode.

Read-Write Conflict

In TiDB, when reading data, a globally unique and incrementing timestamp containing the current physical time is obtained as the start_ts of the current transaction. When reading, the transaction needs to read the latest data version of the target key whose commit_ts is less than the start_ts of this transaction. When a lock is found on the target key during reading, it is impossible to know whether the transaction that locked it is in the Commit phase or the Prewrite phase, resulting in a read-write conflict, as shown below:

Analysis:

Txn0 completed Prewrite, and during the Commit process, Txn1 initiated a read request for the key. Txn1 needs to read the latest version of the key with start_ts > commit_ts. At this time, the lock information on the key that Txn1 needs to read has not been cleared, so it is impossible to determine whether Txn0 has been successfully committed, resulting in a read-write conflict between Txn1 and Txn0.

You can detect whether there are read-write conflicts in the current environment through the following two methods:

  1. TiDB Monitoring and Logs
  • Through TiDB Grafana monitoring analysis: Observe the not_expired/resolve monitoring items under the Lock Resolve OPS panel and the tikvLockFast monitoring items under the KV Backoff OPS panel. If there is a significant upward trend, it may indicate a large number of read-write conflicts in the current environment. Among them, not_expired means that the corresponding lock has not expired, resolve means the operation of attempting to clear the lock, and tikvLockFast represents a read-write conflict.
  • Through TiDB log analysis: You can see the following information in the TiDB logs:
[INFO] [coprocessor.go:743] ["[TIME_COP_PROCESS] resp_time:406.038899ms txnStartTS:416643508703592451 region_id:8297 store_addr:10.8.1.208:20160 backoff_ms:255 backoff_types:[txnLockFast,txnLockFast] kv_process_ms:333 scan_total_write:0 scan_processed_write:0 scan_total_data:0 scan_processed_data:0 scan_total_lock:0 scan_processed_lock:0"]
* txnStartTS: The start_ts of the transaction that initiated the read request, as in the example above, 416643508703592451
* backoff_types: A read-write conflict occurred, and the read request was retried with backoff, with the retry type being txnLockFast
* backoff_ms: The time taken for the read request backoff retry, in ms, as in the example above, 255
* region_id: The id of the target region accessed by the read request
  1. Through TiKV log analysis: You can see the following information in the TiKV logs:
[ERROR] [endpoint.rs:454] [error-response] [err=""locked primary_lock:7480000000000004D35F6980000000000000010380000000004C788E0380000000004C0748 lock_version: 411402933858205712 key: 7480000000000004D35F7280000000004C0748 lock_ttl: 3008 txn_size: 1""]

This error message indicates a read-write conflict. When reading data, it is found that the key has a lock that hinders reading. This lock includes an uncommitted optimistic lock and an uncommitted pessimistic lock after prewrite.

  • primary_lock: The primary lock of the transaction corresponding to the lock.
  • lock_version: The start_ts of the transaction corresponding to the lock.
  • key: The key that is locked.
  • lock_ttl: The TTL of the lock.
  • txn_size: The number of keys in the transaction in its Region, guiding the way to clear the lock.

Handling suggestions:

  • When encountering a read-write conflict, there is an automatic backoff retry mechanism. As in the example above, Txn1 will perform a backoff retry, with an initial single retry of 10 ms, a maximum single retry of 3000 ms, and a total maximum of 20000 ms.
  • You can use the TiDB Control subcommand decoder to view the table id and rowid of the row corresponding to the specified key:
./tidb-ctl decoder "t\x00\x00\x00\x00\x00\x00\x00\x1c_r\x00\x00\x00\x00\x00\x00\x00\xfa"
format: table_row
table_id: -9223372036854775780
row_id: -9223372036854775558

Although it is understandable, it does seem that the official documentation acknowledges the occurrence of read-write conflicts but does not provide a solution.

| username: 春风十里 | Original post link

You can check whether it is in pessimistic mode or optimistic mode.

| username: Raymond | Original post link

If you change it to RC, there will be no read-write conflicts.

| username: Inkjade | Original post link

The isolation level of the transaction is RC level.

Currently, it is in pessimistic mode, but it seems that no solution has been provided. This problem cannot be resolved and can only be handled on the application side.

| username: 南征北战 | Original post link

The official provides a retry mechanism to handle this.
But the original poster’s issue is with count(*) and order by, both of which involve full table scans, leading to a high probability of read-write lock conflicts.
You can only address this from the application side.

| username: Inkjade | Original post link

Idempotence has already been achieved on the application side by integrating Redis to avoid this issue.

| username: TiDBer_iCdTOZ1r | Original post link

Check which type of lock it is.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.