Issue with Data Order in the Same Transaction for TiDB Binlog Parsing

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

Original topic: TiDB Binlog解析同个事务的数据顺序问题

| username: TiDBer_0Rc1Ypm9

For the two updates in the same transaction, I see that the commitTs values in the binlog are the same. So, can we only distinguish the order of the two record changes based on the sequence of mutations? Because I found that the update_sys_tm time of the two pieces of data for the same order might be the same, making it impossible to distinguish the order of updates.

| username: TiDBer_CkS2lbTx | Original post link

If two update statements are in the same transaction and the transaction ends normally, the final result will definitely be the last one, which is 989.

| username: 有猫万事足 | Original post link

Because you have wrapped them in the same transaction, the commitTs will be the same. Try removing

START TRANSACTION;

and see what happens.

| username: 江湖故人 | Original post link

Transactions are sorted by commit timestamp, and within the same transaction, there is only one commit time, so it should be sorted by the text order of the records.

| username: tidb菜鸟一只 | Original post link

Which version are you using? Still using binlog? Switch to CDC.

| username: xfworld | Original post link

This is the basic mechanism of transactions, which must ensure that operations within a transaction either all succeed or all fail…

Are you referring to the serialization level for the order you want? It seems that TiDB’s transaction mechanism hasn’t reached such a high level…

Or are you referring to this:

| username: 连连看db | Original post link

You can use “default current_timestamp on update current_timestamp” in a column. Even if the transaction commit times are the same, there will still be differences in the execution times of the statements within the transaction.

| username: chris-zhang | Original post link

Is the issue resolved?

| username: 小于同学 | Original post link

Is the issue resolved?

| username: TiDBer_rvITcue9 | Original post link

Check in.

| username: kelvin | Original post link

I think the transaction commit time is the same, but the execution time of the statements within the transaction should be different.

| username: TiDBer_0Rc1Ypm9 | Original post link

The final result is definitely 989, but when synchronizing the binlog to the data warehouse, it becomes unclear which operation record, 66 or 989, came first because the update_sys_tm fields are the same. Additionally, the commitTs values in the binlog content are also the same.

| username: TiDBer_0Rc1Ypm9 | Original post link

I understand it too.

| username: TiDBer_0Rc1Ypm9 | Original post link

There is no problem on the TiDB side, it’s just that when I am synchronizing the binlog operation logs, it’s not easy to distinguish the order of these two update operations.

| username: TiDBer_0Rc1Ypm9 | Original post link

@Xiaoyu @chris-zhang Currently, the order of mutations is used to distinguish the sequence of two update operations. So far, no issues have been found. I wonder if anyone else has a better solution.

| username: TiDBer_CkS2lbTx | Original post link

Since the final result is 989, it proves that the answer is already there, and the 989 must have been executed later.

| username: TiDBer_0Rc1Ypm9 | Original post link

It’s not a question of doubting the execution result, but in the binlog, the commitTs of two update statements are the same. Does this mean that the order of the two update statements can only be distinguished by the order of mutations?

| username: system | Original post link

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