txn-start是否有事务启动时间戳-NOW()，而不是语句启动

Question

我正在调查时态查询，我发现了MariaDB的一个问题(我遵循了这里的步骤(，尽管它们对时态查询的实现似乎是更好的。MariaDB代码就在这个fiddle上(PostgreSQL比较fiddle(。

然而，在我的研究中，我发现MariaDB缺乏transaction_timestamp()函数，就像他们在PostgreSQL中一样。

PostgreSQL还提供了返回当前语句，以及当前时刻的实际时间函数被调用。非SQL标准时间的完整列表功能是：

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

我做了以下操作(此处为步骤(：

create table t 
(
x int, test timestamp(6),
start_tid bigint unsigned generated always as row start invisible,
end_tid   bigint unsigned generated always as row end   invisible,
period for system_time(start_tid, end_tid)
) with system versioning;

然后运行：

start transaction;
insert into t (x, test) values (1, now()), (2, now()), (3, now());
select sleep (5);  -- inagine this is some (very) long-running report query
insert into t (x, test) values (11, now()), (12, now()), (13, now());
commit work;

然后：

select  
x, test, start_tid, end_tid
from t;

获得

x   test                start_tid   end_tid
1   2021-11-07 11:43:25 60612       18446744073709551615
2   2021-11-07 11:43:25 60612       18446744073709551615
3   2021-11-07 11:43:25 60612       18446744073709551615
-- notice 5 second gap here!    
11  2021-11-07 11:43:30 60612       18446744073709551615
12  2021-11-07 11:43:30 60612       18446744073709551615
13  2021-11-07 11:43:30 60612       18446744073709551615

因此，我有两组不同插入发生的时间戳，但我不能得到的是MariaDB中整个事务的单个事务开始时间。

在PostgreSQL中做几乎相同的事情(fiddle(给了我的结果

x       tx_time                         clock_time
1   2021-11-07 12:01:05.574651  2021-11-07 12:01:05.575062
2   2021-11-07 12:01:05.574651  2021-11-07 12:01:05.575145
3   2021-11-07 12:01:05.574651  2021-11-07 12:01:05.57515
-- here, the tx_time remains unchanged whereas the clock_time changes by 5 seconds as wanted
11  2021-11-07 12:01:05.574651  2021-11-07 12:01:10.577289
12  2021-11-07 12:01:05.574651  2021-11-07 12:01:10.577307
13  2021-11-07 12:01:05.574651  2021-11-07 12:01:10.577324 

也许我可以重新加入mysql.transaction_registry表，但更希望能够在SQL中执行此操作？

Answer 1

MariaDB [halley]> TRUNCATE TABLE t;
Query OK, 0 rows affected (0.526 sec)
MariaDB [halley]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [halley]> SELECT @T:=now();
+---------------------+
| @T:=now()           |
+---------------------+
| 2021-11-07 16:21:15 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [halley]> insert into t (x, test) values (1, @T), (2, @T), (3, @T);
Query OK, 3 rows affected (0.000 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [halley]> select sleep (5);  -- inagine this is some (very) long-running report query
+-----------+
| sleep (5) |
+-----------+
|         0 |
+-----------+
1 row in set (5.000 sec)
MariaDB [halley]> insert into t (x, test) values (11, @T), (12, @T), (13, @T);
Query OK, 3 rows affected (0.000 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [halley]> commit work;
Query OK, 0 rows affected (0.037 sec)
MariaDB [halley]> select  
->   x, test, start_tid, end_tid
-> from t;
+------+----------------------------+-----------+----------------------+
| x    | test                       | start_tid | end_tid              |
+------+----------------------------+-----------+----------------------+
|    1 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
|    2 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
|    3 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
|   11 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
|   12 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
|   13 | 2021-11-07 16:21:15.000000 |       755 | 18446744073709551615 |
+------+----------------------------+-----------+----------------------+
6 rows in set (0.000 sec)