What are the scenarios for the pessimistic lock `lock_only_if_exists` parameter?

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

Original topic: 悲观锁 lock_only_if_exists 参数的场景有哪些呢?

| username: ylldty

In the code of TiKV, the PessimisticLock interface:

From the comments, it can be seen that there are generally two scenarios:

  1. Locking is required only when there is a write record.
  2. Locking is required regardless of whether there is a write record.

Which SQL transaction situations correspond to these two scenarios?
I currently guess:
insert/select for update/update/delete statements seem to require locking regardless of whether there is a record.

I’m not quite sure about the scenario where locking is not required if there is no record.

| username: dba远航 | Original post link

Pessimistic locking is suitable for high concurrency environments.

| username: wangccsy | Original post link

Optimistic Lock.

| username: ylldty | Original post link

What I want to ask is in which scenarios the lock_only_if_exists parameter is used during the pessimistic lock request process, not in which scenarios the pessimistic lock is used.

| username: 哈喽沃德 | Original post link

  1. Data Update Operation: When you need to update a data record, you can first check if the record exists. If it does, use the lock_only_if_exists parameter to lock the record, ensuring that it won’t be modified by other transactions during the update process.
  2. Resource Reservation/Allocation: When you need to reserve or allocate resources, you can first check the availability of the resources. If the resources exist and are available, use the lock_only_if_exists parameter to lock the resources, preventing other requests from operating on the same resources simultaneously.
  3. Order Processing: When processing orders or transactions, you can first check the order status or inventory levels. If the order exists or the inventory is sufficient, use the lock_only_if_exists parameter to lock the relevant data, ensuring data integrity during the order processing.
  4. Concurrency Control: In scenarios where data concurrency control is needed, you can use the lock_only_if_exists parameter to precisely lock specific data, avoiding locking the entire data table or dataset, thereby improving system concurrency performance.
| username: forever | Original post link

select * from t for update; Locks if there are records, does not lock if there are no records.

| username: cassblanca | Original post link

The lock_only_if_exists parameter is an option in TiDB’s pessimistic lock implementation, indicating that TiDB will only lock the target row if it exists when applying a pessimistic lock. The usage scenarios roughly include:

1. Foreign Key Constraint Check: When handling foreign key constraints, TiDB may need to lock the referenced row to ensure the correctness of the foreign key constraint. In this case, if the referenced row does not exist, there is no need to lock it. Of course, TiDB does not enforce foreign key constraint checks, so this point can be ignored.

2. SELECT FOR UPDATE Statement: When executing a SELECT FOR UPDATE statement, users may only want to lock the rows that actually exist. By specifying lock_only_if_exists, TiDB can apply a pessimistic lock only to the existing rows found by the query, rather than locking all possible rows (e.g., gaps in range queries).

3. Performance Optimization: Under the Read Committed isolation level, some queries may only need to check if a lock exists without actually acquiring the lock. Using lock_only_if_exists can avoid unnecessary lock waits, thereby improving query performance.

4. Reducing Lock Scope: In some scenarios, to reduce the scope of locks and the duration of lock holding, locking only existing rows can minimize the impact of locks and improve concurrency performance.

5. Avoiding Deadlocks: In some cases, if multiple transactions are waiting for each other to release locks, a deadlock may occur. Using lock_only_if_exists can allow transactions to immediately return when detecting that the lock does not exist, thereby avoiding deadlocks.

| username: ylldty | Original post link

If there is no record with id = 1 in the table for the statement select * from t where id=1 for update;, should we worry about concurrent transactions inserting a record with id = 1 if no lock is applied?

| username: forever | Original post link

This depends on the transaction isolation level, and whether it is a pessimistic transaction or an optimistic transaction. You can conduct some experiments to test it.

| username: system | Original post link

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