How to Enable Optimistic Locking in Code

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

Original topic: 如何在代码中开启乐观锁

| username: breakyang

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.1.1
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

I would like to ask everyone, in the case where the default global setting is pessimistic locking, how can I implement session-level optimistic locking using Java code? The background is that I have a batch insert operation, and there is almost no write conflict. I just need to ensure atomicity first. How should I do this?

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

If the framework has an additional layer, you need to check the implementation of the framework.
For TiDB, you can specify whether the transaction is optimistic or pessimistic when you begin.

| username: 大飞哥online | Original post link

You can take a look at this: 乐观事务和悲观事务 | PingCAP 文档中心

| username: 大飞哥online | Original post link

Or modify the TiDB parameters to optimistic; the default is pessimistic, so the code can remain unchanged.

pessimistic-auto-commit

  • This parameter controls the transaction mode used for automatically committed transactions when the global pessimistic transaction mode (tidb_txn_mode='pessimistic') is enabled. By default, even if the global pessimistic transaction mode is enabled, automatically committed transactions still use the optimistic transaction mode. When this configuration item is enabled (set to true), automatically committed transactions will also use the pessimistic transaction mode under the global pessimistic transaction mode. The behavior is the same as other explicitly committed pessimistic transactions.
  • In scenarios with conflicts, enabling this switch can include automatically committed transactions in the global lock management, thereby avoiding deadlocks and improving latency spikes caused by deadlock conflicts.
  • In scenarios without conflicts, if there are a large number of automatically committed transactions (for example, if the number of automatically committed transactions accounts for more than half or even more of the business transactions, which needs to be analyzed based on the actual situation) and the data volume of a single transaction operation is large, enabling this configuration item may cause performance degradation. For example, automatically committed INSERT INTO SELECT statements.
  • Default value: false
| username: breakyang | Original post link

Thank you for your response, but I have noticed a phenomenon and would like to ask further:

I wrote a piece of batch insertion code without writing code like this:
connection.createStatement().executeUpdate(txnComment + "begin optimistic");
It’s just a simple batch insert, and I didn’t set auto commit = false.
The connection string is:
characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&prepStmtCacheSqlLimit=10000000000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-2147483648

However, I found that the metrics in Grafana are all optimistic lock data.

| username: 大飞哥online | Original post link

So it’s auto-commit then. By default, auto-commit is optimistic. Only when pessimistic-auto-commit=true (default is false), auto-commit is pessimistic.

| username: breakyang | Original post link

So, let me ask you again, does batch insert with auto commit = false still count as a transaction? Can it ensure atomicity? Or in other words, does batch insert need to explicitly start a transaction?

| username: 大飞哥online | Original post link

Hello, this doesn’t affect it. Whether to use explicit or implicit transactions depends on the business code and the database. Generally, auto-commit is enabled. If you want to use explicit transactions, just add begin and commit in the code. Compared to MySQL, TiDB has an additional optimistic lock, so you need to pay attention to the parameters as well.

| username: 大飞哥online | Original post link

The optimistic transaction model involves direct commits and rolling back when conflicts occur. 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 can be relatively high.

The pessimistic transaction model, on the other hand, attempts to lock the resources that need to be modified before actually committing the transaction. It only starts the commit process after ensuring that the transaction will definitely succeed. The advantage is that for high-conflict scenarios, the cost of pre-locking is lower than the cost of rolling back afterward. It can also resolve scenarios where multiple concurrent transactions conflict with each other, preventing any of them from succeeding, at a relatively low cost.

In scenarios with low conflict rates, pessimistic transactions are not as efficient as optimistic transactions.

| username: system | Original post link

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