Ideas on the Data Reading Process of TiDB MVCC

Translated
1

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

Original topic: tidb mvcc 读取数据流程的想法

| username: Raymond

Recently, I have been studying the process of reading data with TiDB MVCC. I have been pondering a question: why is there a read-write conflict during MVCC reads? In this post mvcc和读写冲突 - TiDB 的问答社区, I provided an example.

In the example I gave, “Transaction B should wait for Transaction A to complete before reading the latest data of the row with id=1 (the state at commit.tso 10:59:50, not the state at commit.tso 10:58:00)” because:

Hypothesis 1: I believe it is to prevent phantom reads. Assuming the current isolation level is snapshot isolation (repeatable read), and Transaction B executes two select queries (according to the principles of snapshot isolation, it should only get the same start.tso). If MVCC did not have read-write conflicts, the following situation might occur: Transaction B executes the first query and gets the data at commit.tso=10:58:00, then executes the second query and gets the data at commit.tso=10:59:50 (assuming Transaction A was fully committed at 10:59:00). This could result in the second query’s value being different from the first, causing a phantom read (or non-repeatable read), violating the principles of snapshot isolation (repeatable read). I personally think this is one of the reasons for read-write conflicts under MVCC.

Hypothesis 2: Based on this hypothesis, I have another guess. If the isolation level is read committed, which inherently has phantom reads, then MVCC reads might not have read-write conflicts. Does this mean that in some cases, such as when Transaction A generates a large update transaction and Transaction B wants to read the rows involved in Transaction A, if it’s under snapshot isolation, Transaction B needs to wait (for Transaction A to complete), but under read committed isolation, Transaction B does not need to wait? In such cases, does this imply that the performance of read committed isolation is better than snapshot isolation?

I am not sure if my two hypotheses are correct. Could the experts please provide some guidance?

2
| username: ddhe9527 | Original post link

To give a somewhat inappropriate example: Shelves A and B each have 50 items, and now we want to move 10 items from shelf A to shelf B.

  • At T1: Perform an initial check to confirm that there are 100 items in total on shelves A and B.
  • At T2: Remove 10 items from shelf A and confirm that they are taken by the logistics robot. At this point, UPDATE the database to subtract 10 items from shelf A.
  • At T3: The logistics robot arrives at shelf B and places the 10 items on it. At this point, UPDATE the database to add 10 items to shelf B.
  • At T4: Perform a final check to confirm that there are 100 items in total on shelves A and B, and the entire process is complete.

The above business logic has strict chronological requirements; otherwise, the final check will fail. Therefore, at T4, it must be able to see the changes at T2 and T3 to ensure the system logic is correct. In simple terms, this is consistency.

3
| username: Raymond | Original post link

Hello, teacher.
I feel that this example highlights the “D” in ACID transactions. We want to discuss the differences in reading data under different isolation levels.

4
| username: ddhe9527 | Original post link

It has nothing to do with persistence. The biggest difference between RR and RC in MVCC is that in RR, the snapshot is created when the transaction is created (TiDB) or when the first SQL of the transaction is executed (MySQL), and all SQLs in the entire transaction use this one snapshot. In RC, each SQL statement creates a separate snapshot. Whether it is RC or RR, once a snapshot is created, to ensure ACID based on this snapshot, there will be read-write conflicts, which is unrelated to the isolation level. The example I wrote is to say that the snapshot created at time T4 (whether RR or RC) must be able to see the changes of T2 and T3 (there may be read-write conflicts between transactions T3 and T4), otherwise, it would violate linear consistency. Of course, if the business does not have such high consistency requirements, such as only needing to ensure causal consistency or eventual consistency, then that is another matter.

5
| username: system | Original post link

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