Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb 的悲观事务&&可重复读 RR 级别中，快照读 select 不需要检测 lock 吗？
TiDB’s optimistic transactions are by default at the SI level. For Select statements, if the rowid being read has a lock record, it needs to wait for the lock, clear the lock, and retry.
In TiDB’s pessimistic transactions with RC (Read Committed) isolation level, Select statements are snapshot reads. Even if the rowid has a lock record, it does not need to wait for the lock and can directly read the latest committed value from the write record.
However, for the default RR (Repeatable Read) isolation level in pessimistic transactions, do Select statements in snapshot reads need to check lock records like SI, thus requiring TiKV to check lock records in advance and wait for the lock? Or is it like snapshot reads where it does not need to check lock records?
Snapshot reads do not need to check lock records.
As long as the snapshot read is confirmed to be committed, there is no need to consider the locking issue.
TiDB implements Snapshot Isolation (SI) level consistency. To maintain consistency with MySQL, it is also referred to as “REPEATABLE READ”.
Differences from MySQL’s REPEATABLE READ isolation level:
MySQL’s REPEATABLE READ isolation level does not check if the current version is visible when updating, meaning that even if the row has been updated after the transaction started, it can still be updated. This situation can cause transaction rollbacks when using optimistic transactions in TiDB, leading to eventual transaction failure. However, TiDB’s default pessimistic transactions and MySQL can successfully update.
The locks here are different. The read-write conflicts in optimistic and pessimistic locks refer to the fact that during the two-phase commit, the lock added during Prewrite will block the read of transactions that can see the lock. This is because the transaction that wrote the lock may have already obtained the commit ts, and the commit ts is less than the start ts of the transaction that sees the lock. At this point, the select operation has to wait for the lock.
The pessimistic lock you described does not need to wait for the lock. This means that before the two-phase commit of the pessimistic lock, a lock is added to ensure that no write conflict errors occur during the two-phase commit. The transaction here has definitely not yet obtained the commit ts, so the pessimistic lock does not block reads (similar to the optimistic lock, the pessimistic transaction still needs to go through the complete Percolator commit process, and only when all pessimistic locks are Prewritten into Percolator locks does it reach the Commit phase).
By DEBUGging the TiKV code:
There are two scenarios that can be summarized:
-
If there are only pessimistic transactions, then RC/RR level select snapshot reads will ignore the locks added by other pessimistic transactions. For example, there are two pessimistic transactions, one transaction t1 executes select for update without committing, and another transaction t2 executes select snapshot read. Although the request sent by t2 snapshot read to TiKV is still at the SI level and detects the pessimistic lock added by transaction t1, it will actively ignore this lock and directly return the previous commit record without waiting for the lock or retrying.
-
If pessimistic transactions and optimistic transactions coexist, RC/RR level select snapshot reads may encounter lock waiting and retries. For example, there are two transactions, one transaction is an optimistic transaction t1, currently undergoing two-phase commit, just completed prewrite, and has not yet committed. Transaction t2 is a pessimistic transaction, executing an RC level snapshot read, and at this time the snapshot read gets the timestamp read_ts2. At this time, t2’s snapshot read will detect t1’s lock optimistic lock. If it cannot determine whether the final commit time commit_ts1 of this optimistic lock will be greater than read_ts2 or less than read_ts2, it needs to wait for the lock and retry. This prevents transaction t2 from successfully reading the snapshot and then suddenly having an optimistic transaction commit before read_ts2, thereby improving correctness.
The second scenario also applies when a pessimistic transaction undergoes two-phase commit, and after prewrite is completed, the pessimistic lock of the pessimistic transaction is modified to an optimistic lock.
Thank you all for your replies.
The purpose of snapshots is to eliminate some locks, allowing read and write operations to occur simultaneously.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: ylldty
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: