oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yuchen.wyc
5
# owner group: SQL1
6
# description: OUR GOAL: Make all this simple and effective!
7
--disable_warnings
8
drop table if exists t1;
9
--enable_warnings
10

11

12
--echo set timeout to 10s
13
connect (conn3,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
14

15
set global  ob_trx_timeout = 10000000;
16
create table t1(c1 int primary key, c2 int);
17
insert into t1 values(1,1);
18
insert into t1 values(2,2);
19
insert into t1 values(3,3);
20
disconnect conn3;
21

22
--echo create 2 sessions
23
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
24
connect (conn2,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
25

26

27
connection conn1;
28
--echo trx timeout
29
begin;
30
insert into t1 values(4,4);
31
--real_sleep 3
32
insert into t1 values(5,5);
33
--real_sleep 3
34
insert into t1 values(6,6);
35
--real_sleep 3
36
commit;
37

38
select * from t1;
39

40
begin;
41
insert into t1 values(7,7);
42
--real_sleep 3
43
insert into t1 values(8,8);
44
--real_sleep 3
45
insert into t1 values(9,9);
46
--real_sleep 3
47
insert into t1 values(10,10);
48
--real_sleep 3
49
--error 6002
50
commit;
51

52
select * from t1;
53

54
--echo trx idle timeout
55

56
begin;
57
insert into t1 values(11,11);
58
--real_sleep 12
59
--error 4012
60
insert into t1 values(12,12);
61
--error 6002
62
commit;
63

64
select * from t1;
65

66
--echo test two users
67

68
#下面的case，在切主场景下，上面的事务被kill，行锁会释放，下面的事务不会有锁冲突，从而造成case失败
69
#connection conn1;
70
#begin;
71
#select * from t1 where c1=1 for update;
72
#sleep 11;
73
#connection conn2;
74
#begin;
75
#--error 1205,4012
76
#select * from t1 where c1=1 for update;
77
#--error 4012
78
#update t1 set c2=1 where c1=1;
79
#--error 4012
80
#commit;
81
#select * from t1;
82
#connection conn1;
83
#--error 4012
84
#commit;
85

86
#begin;
87
#update t1 set c2=1 where c1=2;
88
#--real_sleep 3
89
#update t1 set c2=1 where c1=3;
90
#--real_sleep 3
91
#update t1 set c2=1 where c1=4;
92
#--real_sleep 3
93
#update t1 set c2=1 where c1=5;
94
#--real_sleep 3
95

96
#connection conn2;
97
#begin;
98
#--error 1205,4012
99
#update t1 set c2=c2+1 where c1=2;
100
#--error 4012
101
#update t1 set c2=c2+1 where c1=3;
102
#--error 4012
103
#update t1 set c2=c2+1 where c1=4;
104
#--error 4012
105
#update t1 set c2=c2+1 where c1=5;
106
#--error 4012
107
#commit;
108
#select * from t1;
109
#connection conn1;
110
#--error 4012
111
#commit;
112
set global  ob_trx_timeout = 100000000; 
113