How to Understand TiDB Pessimistic Lock Mode and Optimistic Lock Mode? What Are the Impacts?

Translated
1

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

Original topic: TiDB悲观锁模式与乐观锁模式如何理解？有哪些影响？

| username: alfred

【TiDB Usage Environment】Production, Testing, Research
【TiDB Version】
【Encountered Problem】
【Reproduction Path】What operations were performed that led to the problem
【Problem Phenomenon and Impact】

【Attachments】

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: Raymond | Original post link

In the pessimistic locking mode, the lock must be acquired before the transaction modifies the data and commits, similar to MySQL’s InnoDB row lock. The lock is obtained before the transaction is committed, ensuring that the transaction will definitely succeed. On the other hand, optimistic locking modifies the data first and then acquires the lock when the transaction is committed. If the lock can be acquired, the transaction is successfully committed; if not, the transaction is rolled back. This means that in optimistic mode, there is a certain probability of transaction failure. Both optimistic and pessimistic locks have their applicable scenarios. If the business is not the type that frequently encounters conflicts, optimistic locking is suitable. Essentially, it bets on whether there will be a conflict when the transaction is committed. If there is no conflict, using optimistic locking is advantageous.

3
| username: Kamner | Original post link

Refer to the official documentation:

4
| username: HACK | Original post link

Generally speaking, the locking stages are different:

Optimistic: The client commits, and the locking operation is performed at the prewrite node of 2PC. At this stage, lock conflicts may occur.

Pessimistic: Locking is performed during the DML stage before 2PC, and there are basically no lock conflicts after entering 2PC.

5
| username: alfred | Original post link

Got it, thank you.

6
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. No new replies are allowed.