Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: mvcc和读写冲突
[TiDB Usage Environment] Online, Testing, Research
[TiDB Version]
[Encountered Problem]
Since TiDB implements MVCC, why do read-write conflicts still exist? Isn’t MVCC supposed to solve such scenarios?
[Reproduction Path] What operations were performed to encounter 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.
In pessimistic mode, does this mean that read-write conflicts no longer exist?
The pessimistic mode has the same problem.
Referring to the scenario of a flash sale, where 100 people all want the same item at the same time (accurate to the second),
In reality, the database’s handling of such operations is accurate to microseconds, and the contention scenario will still occur, which is unavoidable (hence the distinction between pessimistic locking and optimistic locking).
In pessimistic mode, when autocommit is enabled, optimistic locking is used first, and it switches to pessimistic locking only when there is a conflict retry.
The purpose of read-write conflicts is to ensure that linear consistency is not compromised, and it is unrelated to pessimistic or optimistic modes. According to the sequentiality in linear consistency, the logical order of events cannot violate the physical order. For example, a read operation initiated at time T must be able to read all changes made before time T. Therefore, the essence of a read-write conflict is that a read operation must wait for any write operations that occurred before it to complete before reading, thereby satisfying sequentiality. On the other hand, the commit of a transaction is a process. For example, if Txn0 starts the commit phase of 2PC at time T1 and determines the commit_ts as T2, and the commit completes at time T3 (T1 < T2 < T3, not considering Async Commit), then if the start_ts of a read transaction Txn1 falls between T2 and T3, it must be able to read the data written by Txn0 (because T2 is less than its start_ts). Therefore, Txn1 can only wait or backoff at this time.
May I ask again, under what circumstances would the MVCC mechanism be used?
The functionality of database concurrency access consistency is not a human usage scenario.
MVCC is multi-version. When the commit_ts of a write transaction is greater than the start_ts of a read transaction, meaning the modifications of the write transaction are not visible to the read transaction, the read transaction will use this multi-version mechanism to read the old version of the data.
To put it plainly:
Transaction A wants to read data and gets a start_ts.
At this point, the entire database can’t be locked for writing.
MVCC allows other transactions to write normally, and when Transaction A reads, it only reads the version before start_ts.
Will read-write conflicts occur in this situation?
MVCC is designed to solve the problem of read-write conflicts in concurrent transaction processing.
My initial question was this doubt: why are there still read-write conflicts in TiDB after having MVCC? But looking at the discussion above, it seems that’s not the case. 
With MVCC and the need to ensure consistency, read-write conflicts can still occur under extreme conditions.
Many databases implement Multi-Version Concurrency Control (MVCC), and TiKV is no exception. Imagine a scenario where two clients simultaneously modify the value of a key. Without multi-version control of the data, it would be necessary to lock the data, which could lead to performance issues and deadlocks in a distributed environment. TiKV’s MVCC implementation is achieved by appending version numbers to the keys.
My personal understanding is as follows, not sure if it’s correct. If there’s anything wrong, please point it out, teachers.
-
Suppose transaction B wants to read the row with id=1, and the start.tso of transaction B is 11:00:00. According to TiDB’s isolation design principles, transaction B can only read the latest version of the row with id=1 before 11:00:00 (for example, if the row with id=1 was updated by a transaction at 10:58, and then updated again by another transaction at 10:59, it can only read the state of id=1 at 10:59).
-
A situation where there is a read-write conflict:
Suppose the row with id=1 was modified and committed by a transaction at 10:58:00 (the commit.tso of the row with id=1 is 10:58:00). If transaction B tries to read the row with id=1 and finds a record in the lock cf (assumed to be generated by transaction A, which has not yet completed the entire commit process), and the start.tso of transaction A is 10:59:10 (the lock cf also contains the start.tso record), this indicates that transaction A has completed the prewrite phase of the two-phase commit and is in the commit phase, having already obtained a commit.tso of 10:59:50. In this case, transaction B should wait for transaction A to complete its commit before reading the latest data of the row with id=1 (the state at commit.tso 10:59:50, rather than directly reading the state at commit.tso 10:58:00).
I personally think that the read-write conflict in MVCC is to ensure the correctness of the isolation level, but I’m not sure if there would be any adverse consequences if the data of id=1 at commit.tso 10:58:00 is read?
The teacher explained it very clearly, but I have a question. What would happen if we don’t read the data written by tx0? Can you give an example?
The consequence is that it contradicts the statement you initially wrote:
Transaction B can only read the latest version of the data for id=1 before 11:00:00 (for example, if a transaction updated id=1 at 10:58, and then another transaction updated it at 10:59, then it can only read the state of id=1 at 10:59).
Alright, but this kind of MVCC design actually feels more costly than MySQL’s MVCC.
|
username: TiDBer_h9um7nOg
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: