Error(Txn(Error(Mvcc(Error(PessimisticLockNotFound

Translated
1

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

Original topic: Error(Txn(Error(Mvcc(Error(PessimisticLockNotFound

| username: navyaijm2017

To improve efficiency, please provide the following information. A clear problem description can help resolve the issue faster:
【TiDB Usage Environment】Research
【TiDB Version】V5.4.2
【Encountered Problem】
2022-08-30T06:08:27.122217166Z [v1] [2022-08-30T14:08:27.120+0800] [error] [main] index [TID:N/A] uncaughtException|err: QueryFailedError: tikv aborts txn: Error(Txn(Error(Mvcc(Error(PessimisticLockNotFound { start_ts: TimeStamp(435641308093362171), key: [116, 128, 0, 0, 0, 0, 0, 1, 74, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 1, 70, 89, 81, 48, 48, 48, 48, 48, 255, 48, 48, 48, 49, 51, 51, 52, 50, 255, 55, 0, 0, 0, 0, 0, 0, 0, 248, 1, 112, 101, 114, 102, 84, 101, 115, 116, 255, 0, 0, 0, 0, 0, 0, 0, 0, 247, 1, 107, 90, 121, 86, 72, 97, 98, 49, 255, 117, 99, 0, 0, 0, 0, 0, 0, 249, 1, 99, 114, 101, 97, 116, 101, 0, 0, 253, 3, 64, 0, 0, 0, 186, 103, 6, 162] })))))
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
Currently, we are conducting performance testing for the business. It was running fine for several consecutive days, but this issue suddenly appeared yesterday. This morning, I checked and found that the issue disappeared again after 2 AM. The current observation is that only delete statements encounter this problem, and the transaction model used is the default pessimistic transaction.
| Variable_name | Value |
±--------------±------------+
| tidb_txn_mode | pessimistic |
±--------------±------------+

[FAQ] tikv aborts txn: Txn(Mvcc(PessimisticLockNotFound - TiDB 的问答社区 This post also does not mention the cause and solution to the problem.

2
| username: xfworld | Original post link

Pessimistic lock transactions may encounter this type of error. When the transaction starts executing, it locks, and after reaching the TTL of the [pessimistic-txn] part, the lock is released. TTL is the lock timeout period for pessimistic transactions, with a default value of 30 seconds, configurable between 15 seconds and 120 seconds. Exceeding this range will result in an error. If the execution time of the transaction exceeds the TTL, the transaction will fail. Setting it too high can cause issues where residual pessimistic locks block writes for a long time if the TiDB server crashes. Setting it too low can cause the transaction to be rolled back by other transactions before it has time to complete.

The FAQ describes this very clearly. Basically, it times out… If it doesn’t complete, the pessimistic lock will be considered an invalid transaction and will be cleaned up. However, if the running SQL hasn’t finished executing, it will look for this pessimistic lock to confirm the execution status, which will result in this error.

3
| username: navyaijm2017 | Original post link

From the slow log, there are no SQL statements exceeding 5 seconds, so there shouldn’t be any exceeding 30 seconds. This issue disappears during the early morning hours when the cluster load remains unchanged. :grin:

4
| username: xfworld | Original post link

You can enable resource tracking to log execution time and memory usage that exceed the set values, which helps with diagnostics.

5
| username: navyaijm2017 | Original post link

Is there any documentation on enabling resource location? Please send it over.

6
| username: xfworld | Original post link

Enable this feature and you will get the information you want.

7
| username: system | Original post link

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