oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yuchen.wyc
5
# owner group: sql4
6
# description: ����select for update�������������
7
--disable_query_log
8
--source mysql_test/include/index_quick_major.inc
9
sleep 2;
10
--enable_query_log
11
--disable_warnings
12
drop table if exists tbl1,tbl2;
13
--enable_warnings
14
create table tbl1 (pk int,i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6),i5 decimal(5,3),i6 bool, primary key(pk));
15
####һ�뾲̬���ݣ�һ�붯̬����
16
insert into tbl1 values(1,1,'abc12','1agc',1.25,'2014-01-01 12:00:00',23.23,1),
17
                       (2,10,'23abc12','4agc1',-0.25,'2015-11-01 12:00:00',-23.230,0),
18
                       (3,201,'xx23abc12','cagc1',-2.25,'2015-11-21 11:20:10',3.231,1),
19
                       (4,80,'zx23abc12','zcagc1',2.25,'2013-01-21 11:20:10',-3.231,1),
20
                       (5,53,'cx23abc12','rcagc1',109.25,'2016-01-22 11:20:10',-13.131,0);
21
#create unique index  idx on tbl1(i1);
22
#create unique index  idx on tbl1(i4);
23
#create unique index  idx on tbl1(i1,v2);
24
--source mysql_test/include/majorfreeze.inc
25
--source mysql_test/include/wait_daily_merge.inc
26

27
insert into tbl1 values (6,78,'tx23abc12','icagc1',-109.25,'2014-09-16 16:24:00',-13.531,1),(8,50,'vx23abc12','wcagc1',-85.85,'2014-09-10 16:20:21',93.131,1),(9,610,'ukx23abc12','pecagc1',-1.85,'2014-09-19 16:20:21',99.131,0),(10,42,'kx23abc12','ecagc1',-85.86,'2014-09-18 16:20:21',-99.131,1),(11,null,null,null,null,null,null,null);
28

29

30
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
31
connect (conn2,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
32
--disable_query_log
33
connection conn1;
34
set session ob_query_timeout = 2000000;
35
set session ob_trx_timeout = 10000000;
36
set session ob_trx_idle_timeout = 5000000;
37
connection conn2;
38
set session ob_query_timeout = 2000000;
39
set session ob_trx_timeout = 10000000;
40
set session ob_trx_idle_timeout = 5000000;
41
--enable_query_log
42
connection conn1;
43
set autocommit=0;
44

45
connection conn2;
46
set autocommit=0;
47

48
####����1 sfuһ������,���ӱ���,����2������У�����1�ٸ��¸��У�
49
connection conn1;
50
#select * from tbl1 a where a.pk=100 for update;
51
#�޸ĸô������ñ���
52
select * from tbl1  where pk=100 for update;
53

54

55
connection conn2;
56
insert into  tbl1 values(100,102,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
57
update tbl1 set i1=i1-300 where pk in (100,1,123,8,9);
58

59
connection conn1;
60
update tbl1 set pk=200 where pk=100;
61
select * from tbl1 where pk in (100,200);
62
commit;
63
connection conn2;
64
select * from tbl1 where pk in (100,200);
65
commit;
66

67
select * from tbl1 where pk in (100,200);
68
delete  from tbl1 where pk in (100,200);
69
commit;
70

71
####����1 sfuһ������,���ӱ���,����2������У�����1�ٲ�����У�
72
connection conn1;
73
#select * from tbl1 abc where abc.pk=100 for update;
74
select * from tbl1  where pk=100 for update;
75

76

77
connection conn2;
78
insert into  tbl1 values(100,102,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
79
update tbl1 set i1=i1-300 where pk in (100,1,123,8,9);
80

81
connection conn1;
82
--error 1205,4012
83
insert into  tbl1 values(100,102,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
84
select * from tbl1 where pk in (100,200);
85
commit;
86
connection conn2;
87
select * from tbl1 where pk in (100,200);
88
commit;
89

90
select * from tbl1 where pk in (100,200);
91
delete  from tbl1 where pk in (100,200);
92
commit;
93

94
####����1 sfuһ������,�ӱ�����,����2 sfu ͬһ��
95
connection conn1;
96
#select * from tbl1 abc where abc.pk=100 for update;
97
select * from tbl1  where pk=100 for update;
98

99

100
connection conn2;
101
insert into  tbl1 values(100,102,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
102
update tbl1 set i1=i1-300 where pk in (100,1,123,8,9);
103
select * from tbl1  where pk=100 for update;
104

105
connection conn1;
106
--error 1205,4012
107
replace  into  tbl1 values(100,105,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
108
select * from tbl1 where pk in (100,200);
109
commit;
110
connection conn2;
111
select * from tbl1 where pk in (100,200);
112
commit;
113

114
select * from tbl1 where pk in (100,200);
115
delete  from tbl1 where pk in (100,200);
116
commit;
117
####����1 sfu����������+�ǿ��У�,�ӱ�������,����2������У�����1��replace ���У�sfu ͬһ��
118
connection conn1;
119
#select * from tbl1 t1 where t1.pk in(100,10,1,200) for update;
120
select * from tbl1  where pk in(100,10,1,200) for update;
121

122

123
connection conn2;
124
insert into  tbl1 values(100,102,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
125
--error 1205,4012
126
update tbl1 set i1=i1-300 where pk in (100,1,123,8,9);
127
update tbl1 set i1=i1-300 where pk in (100,123);
128
--error 1205,4012
129
update tbl1 set pk=1 where pk=1;
130
#--error 1205,4012
131
select * from tbl1 t1 where t1.pk in(100,200) for update;
132

133
connection conn1;
134
--error 1205,4012
135
replace  into  tbl1 values(100,105,'abdggc12','1agc1d',1.215,'2014-01-01 12:00:10',83.23,1);
136
select * from tbl1 where pk in (100,200);
137
update tbl1 set pk=1 where pk=1;
138
commit;
139
connection conn2;
140
select * from tbl1 where pk in (100,200);
141
commit;
142

143
select * from tbl1 where pk in (100,200);
144
delete  from tbl1 where pk in (100,200);
145

146