| 1 |
create table t1( |
| 2 |
id int auto_increment primary key, |
| 3 |
sys_trx_start bigint unsigned as row start invisible, |
| 4 |
sys_trx_end bigint unsigned as row end invisible, |
| 5 |
period for system_time (sys_trx_start, sys_trx_end) |
| 6 |
) |
| 7 |
with system versioning |
| 8 |
engine innodb; |
| 9 |
insert into t1 values (); |
| 10 |
set @ts0= now(6); |
| 11 |
insert into t1 values (); |
| 12 |
select sys_trx_start from t1 where id = last_insert_id() into @tx0; |
| 13 |
select transaction_id = @tx0 from mysql.transaction_registry |
| 14 |
order by transaction_id desc limit 1; |
| 15 |
transaction_id = @tx0 |
| 16 |
1 |
| 17 |
set @ts1= now(6); |
| 18 |
insert into t1 values (); |
| 19 |
select sys_trx_start from t1 where id = last_insert_id() into @tx1; |
| 20 |
select transaction_id = @tx1 from mysql.transaction_registry |
| 21 |
order by transaction_id desc limit 1; |
| 22 |
transaction_id = @tx1 |
| 23 |
1 |
| 24 |
set @ts2= now(6); |
| 25 |
insert into t1 values (); |
| 26 |
select sys_trx_start from t1 where id = last_insert_id() into @tx2; |
| 27 |
select transaction_id = @tx2 from mysql.transaction_registry |
| 28 |
order by transaction_id desc limit 1; |
| 29 |
transaction_id = @tx2 |
| 30 |
1 |
| 31 |
set @ts3= now(6); |
| 32 |
select |
| 33 |
trt_trx_id(@ts0) < @tx0 as A, |
| 34 |
trt_trx_id(@ts0, true) = @tx0 as B, |
| 35 |
trt_trx_id(@ts1) = @tx0 as C, |
| 36 |
trt_trx_id(@ts1, true) = @tx1 as D, |
| 37 |
trt_trx_id(@ts2) = @tx1 as E, |
| 38 |
trt_trx_id(@ts2, true) = @tx2 as F, |
| 39 |
trt_trx_id(@ts3) = @tx2 as G, |
| 40 |
trt_trx_id(@ts3, true) is null as H; |
| 41 |
A B C D E F G H |
| 42 |
1 1 1 1 1 1 1 1 |
| 43 |
select |
| 44 |
trt_commit_id(@ts0) < @tx0 as A, |
| 45 |
trt_commit_id(@ts0, true) = trt_commit_id(null, @tx0) as B, |
| 46 |
trt_commit_id(@ts1) = trt_commit_id(null, @tx0) as C, |
| 47 |
trt_commit_id(@ts1, true) = trt_commit_id(null, @tx1) as D, |
| 48 |
trt_commit_id(@ts2) = trt_commit_id(null, @tx1) as E, |
| 49 |
trt_commit_id(@ts2, true) = trt_commit_id(null, @tx2) as F, |
| 50 |
trt_commit_id(@ts3) = trt_commit_id(null, @tx2) as G, |
| 51 |
trt_commit_id(@ts3, true) is null as H; |
| 52 |
A B C D E F G H |
| 53 |
1 1 1 1 1 1 1 1 |
| 54 |
select |
| 55 |
trt_trx_sees(@tx1, @tx0) as A, |
| 56 |
not trt_trx_sees(@tx0, @tx1) as B, |
| 57 |
trt_trx_sees_eq(@tx1, @tx1) as C, |
| 58 |
not trt_trx_sees(@tx1, @tx1) as D, |
| 59 |
trt_trx_sees(@tx2, 0) as E, |
| 60 |
trt_trx_sees(-1, @tx2) as F; |
| 61 |
A B C D E F |
| 62 |
1 1 1 1 1 1 |
| 63 |
select trt_trx_sees(0, @tx2); |
| 64 |
trt_trx_sees(0, @tx2) |
| 65 |
0 |
| 66 |
set transaction isolation level read uncommitted; |
| 67 |
insert into t1 values (); |
| 68 |
select sys_trx_start from t1 where id = last_insert_id() into @tx3; |
| 69 |
select isolation_level = 'READ-UNCOMMITTED' from mysql.transaction_registry where transaction_id = @tx3; |
| 70 |
isolation_level = 'READ-UNCOMMITTED' |
| 71 |
1 |
| 72 |
set transaction isolation level read committed; |
| 73 |
insert into t1 values (); |
| 74 |
select sys_trx_start from t1 where id = last_insert_id() into @tx4; |
| 75 |
select isolation_level = 'READ-COMMITTED' from mysql.transaction_registry where transaction_id = @tx4; |
| 76 |
isolation_level = 'READ-COMMITTED' |
| 77 |
1 |
| 78 |
set transaction isolation level serializable; |
| 79 |
insert into t1 values (); |
| 80 |
select sys_trx_start from t1 where id = last_insert_id() into @tx5; |
| 81 |
select isolation_level = 'SERIALIZABLE' from mysql.transaction_registry where transaction_id = @tx5; |
| 82 |
isolation_level = 'SERIALIZABLE' |
| 83 |
1 |
| 84 |
set transaction isolation level repeatable read; |
| 85 |
insert into t1 values (); |
| 86 |
select sys_trx_start from t1 where id = last_insert_id() into @tx6; |
| 87 |
select isolation_level = 'REPEATABLE-READ' from mysql.transaction_registry where transaction_id = @tx6; |
| 88 |
isolation_level = 'REPEATABLE-READ' |
| 89 |
1 |
| 90 |
drop table t1; |
| 91 |
call verify_trt; |
| 92 |
No A B C D |
| 93 |
1 1 1 1 1 |
| 94 |
2 1 1 1 1 |
| 95 |
3 1 1 1 1 |
| 96 |
4 1 1 1 1 |
| 97 |
5 1 1 1 1 |
| 98 |
6 1 1 1 1 |
| 99 |
7 1 1 1 1 |
| 100 |
8 1 1 1 1 |