Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 同一个事务中，不能百分百获得上一个sql提交的数据更新
[TiDB Usage Environment] Production
[TiDB Version] 5.2.1
[Encountered Problem] There is a transaction in the code that updates data in one of the tables. Later in the transaction, it references this table to perform a sum and updates the result into another table. Previously, there were no issues on MySQL. After fully migrating to TiDB, some data discrepancies were found. The transaction cannot always sum the latest data. Currently, TiDB is using pessimistic transactions with the isolation level set to repeatable read. It is unclear where the problem lies.
What isolation level are you using in MySQL?
Try switching the isolation level to commit read and see if it works.
In a production environment, we don’t dare to switch easily.
There are still some differences in behavior between TiDB and MySQL under the RR isolation level. TiDB approximates SI as RR. In MySQL, a snapshot is not created immediately after START TRANSACTION; it is created when the first SQL statement is executed. In TiDB, a consistent snapshot is created immediately upon START TRANSACTION, equivalent to START TRANSACTION WITH CONSISTENT SNAPSHOT in MySQL. Therefore, in your scenario, you need to check when the sum operation starts the transaction. It might be that TiDB creates the snapshot earlier, causing the sum to not see the updated data.
You can switch at the session level by yourself.
After switching, it will be effective for your session. Once the session is over, it will default to the global parameters and will not change the original settings. However, it can help you verify the issue~
This topic was automatically closed 1 minute after the last reply. No new replies are allowed.
|
username: leo_zxl
|
username: 
|
username: 
|
username: 
|
username: