Performance Differences Between TiDB Optimistic Lock and Pessimistic Lock

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

Original topic: tidb 乐观锁和悲观锁性能差距

| username: zhanggame1

【TiDB Usage Environment】Testing
【TiDB Version】7.1
【Reproduction Path】What operations were performed that led to the issue
【Encountered Issue: Issue Phenomenon and Impact】
According to the documentation, TiDB has optimistic locks and pessimistic locks. The documentation states that optimistic locks have lower overhead when conflicts are few, but it does not specify where the overhead is lower, whether it is CPU or IO usage. If lock conflicts are not considered, in terms of update performance, how much higher can the performance of optimistic locks be approximately?

| username: h5n1 | Original post link

TiDB’s locks are implemented by writing Lock data to KV. In pessimistic mode, a pessimistic lock is written to TiKV during the DML phase. During the commit phase, Prewrite+commit is the same as in optimistic mode, writing data and optimistic locks to TiKV.

| username: zhanggame1 | Original post link

If there is no lock conflict, is there a performance difference?

| username: h5n1 | Original post link

I didn’t feel much of a difference; it’s best to test it yourself.

| username: 有猫万事足 | Original post link

Simply put, the optimistic transaction model commits directly and rolls back when conflicts occur, while the pessimistic transaction model attempts to lock the resources that need to be modified before actually committing the transaction. It only starts committing after ensuring that the transaction will definitely succeed.

For the optimistic transaction model, it is more suitable for scenarios with low conflict rates because direct commits are likely to succeed, and conflicts are rare events. However, once a transaction conflict occurs, the cost of rolling back is relatively high.

The advantage of pessimistic transactions is that for scenarios with high conflict rates, the cost of locking in advance is lower than the cost of rolling back afterward. It can also resolve scenarios where multiple concurrent transactions conflict with each other, causing none to succeed, at a relatively low cost. However, in scenarios with low conflict rates, pessimistic transactions are not as efficient as optimistic transactions.

In terms of implementation complexity from the application side, pessimistic transactions are more intuitive and easier to implement. Optimistic transactions require a complex application-side retry mechanism to ensure success.

Believe me, very few developers like optimistic transactions. Using optimistic transactions means their workload will increase. If TiDB didn’t have pessimistic transactions before, I wouldn’t have recommended it to my boss. :joy:

| username: zhanggame1 | Original post link

If it can be confirmed that this operation will not have conflicts, then it’s fine to use it. Some operations indeed will not have conflicts.

| username: redgame | Original post link

We tested it once, about 2 times, in the OA scenario.

| username: system | Original post link

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