Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 如何获得 TiDB 中事务的开始时间戳与提交时间戳？
Question:
How to obtain the start timestamp and commit timestamp of transactions in TiDB?
Our Solution:
We added log statements in TiCDC, then used TiDB as the upstream and MySQL as the downstream. The logs in TiCDC saved the start timestamp and commit timestamp of transactions.
Our Doubts:
-
Is the above method of obtaining the start timestamp and commit timestamp of transactions correct?
-
How to obtain the start timestamp and commit timestamp of read-only transactions?
Currently, we are unable to obtain the start timestamp and commit timestamp of read-only transactions. According to the TiCDC documentation, synchronization between upstream and downstream only occurs when data changes.
Could you please describe your problem in Chinese?
His question is: How to obtain the start time and end time of a transaction in TiDB?
His solution is: He modified TiCDC, then used TiDB as the upstream and MySQL as the downstream. This way, the logs in TiCDC would store the start time and end time of the transaction.
His doubts are:
- Is this method correct for obtaining the start time and end time of a transaction?
- How to obtain the start time and end time for read-only transactions?
They are unable to obtain the start time and end time for read-only transactions. According to the documentation, synchronization between upstream and downstream only occurs when there are data changes.
Yes, thank you. Considering future communication with collaborators at work, I used English. I will add a Chinese description.
- The timestamps obtained from TiCDC should be accurate.
- For read-only transactions, there is only a start timestamp and no commit timestamp.
- It seems there is no query interface to directly obtain transaction timestamps, especially for read-only transactions. However, there is a feasible workaround: TiDB’s slow log prints out
Txn_start_ts, which should be the transaction start timestamp you need.
So, find a way to print out the slow log. For example, after enabling the slow log, set the threshold for the slow log to be low enough to output the slow log, or modify the source code to always print it out regardless of the situation. If you can modify TiCDC, slightly modifying TiDB shouldn’t be too difficult.
You can check tidb_last_txn_info 系统变量 | PingCAP 文档中心 and tidb_current_ts 系统变量 | PingCAP 文档中心
Thank you for your reply, we will go and experiment with it 
Hello! Thank you for your reply. How should the value of tidb_current_ts be defined, and will this variable be output in tidb.log?
In TiDB, the transaction start timestamp and commit timestamp are both obtained by the tidb-server from the tipd-server, which acts as the time service system for the entire distributed database. During the data writing process to the storage layer, the 2PC mechanism is used to persist both the data and the timestamp information.
TiCDC is a real-time data synchronization tool used to capture real-time data changes in the storage layer, convert the changes in KV key-value pairs into SQL or other protocol data, and then synchronize them downstream. Due to the MVCC mechanism, each key-value pair carries its own timestamp information, meaning that each data change synchronized by TiCDC comes with a transaction start timestamp and a commit timestamp.
Read-only transactions, although they have a start timestamp (used to determine the specific version of the data to read), do not involve any changes, so their related data will not be included in TiCDC.
I tried it, and by lowering the threshold for full logs, I was able to print them out. The unit for tidb_slow_log_threshold is ms, so if you set it to 1, all SQL statements can be printed.
The tidb_current_ts mentioned by wd0517 is also very useful, as long as you explicitly start a transaction:
begin;
show variables like "tidb_current_ts";
exec other_sql;
In TiDB, snapshot reads within the same transaction all use the same start timestamp.
Sure! Thank you so much!! 
Yes, so read-only transactions can no longer obtain timestamps through TiCDC. We are trying to use other methods to obtain them. The current methods are: 1. Adjust the slow log threshold. In theory, as long as the threshold is small enough, the slow log can print out the timestamp information of all transactions. 2. Use tidb_current_ts to explicitly start transactions.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: hengxin
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: