1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
--
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
------------------------------------------------------------------------------
-- 2 CONCURRENT USER TESTS of READ UNCOMMITTED TESTS
--
-- each test assumes calling script has set up a writer connection, it is
-- expected the calling script(s) will vary the isolation of the writer
-- connection to test the interaction of read uncommitted with various isolation
-- levels.
--
-- overview:
-- TEST 0: test RU(read uncommitted) heap read interaction insert/delete/update
-- TEST 1: test RU(read uncommitted) heap read interaction insert/delete/update
-- TEST 2: test RU(read uncommitted) ddl interaction
--
------------------------------------------------------------------------------
-- SCRIPT SETUP
connect 'wombat' as iso_read_uncommitted;
autocommit off;
commit;
------------------------------------------------------------------------------
-- TEST 0: test RU(read uncommitted) heap read interaction insert/delete/update
------------------------------------------------------------------------------
-- set up
set connection iso_read_uncommitted;
autocommit off;
set isolation read uncommitted;
create table test_0 (a int);
insert into test_0 values (1);
commit;
-- READ UNCOMMITTED CONNECTION:
-- read uncommitted should maintain no lock on the row.
set connection iso_read_uncommitted;
select * from test_0;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
'select a from test_0';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;
-- WRITER CONNECTION:
set connection writer;
-- writer should not be blocked.
insert into test_0 values (2);
-- serializable: update will get table level lock so will be blocked.
-- other iso: writer should in no way be blocked by reader.
update test_0 set a = 3 where a = 1;
-- READ UNCOMMITTED CONNECTION:
-- next in cursor should see the new uncommitted row just inserted (2)
set connection iso_read_uncommitted;
next scan_cursor;
-- full select should see the update and the new row.
select * from test_0;
-- WRITER CONNECTION:
-- delete the row that the read uncommitted connection is positioned on.
set connection writer;
-- serializable: update will get table level lock so will be blocked.
-- other iso: writer should in no way be blocked by reader.
delete from test_0 where a = 2;
-- READ UNCOMMITTED CONNECTION:
-- next in cursor should get to end of scan.
set connection iso_read_uncommitted;
next scan_cursor;
close scan_cursor;
-- full select should see just one row.
select * from test_0;
-- WRITER CONNECTION:
set connection writer;
rollback;
-- READ UNCOMMITTED CONNECTION:
-- should now see original rows - note all of this in same xact.
set connection iso_read_uncommitted;
select * from test_0;
-- cleanup
set connection iso_read_uncommitted;
commit;
set connection writer;
commit;
drop table test_1;
commit;
------------------------------------------------------------------------------
-- TEST 1: test RU(read uncommitted) heap read interaction insert/delete/update
------------------------------------------------------------------------------
-- set up
set connection iso_read_uncommitted;
autocommit off;
set isolation read uncommitted;
create table test_1 (a int, b int);
insert into test_1 values (1, 1);
create index test_1_idx on test_1 (a);
commit;
-- READ UNCOMMITTED CONNECTION:
-- read uncommitted should maintain no lock on the row.
set connection iso_read_uncommitted;
select * from test_1;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
'select a from test_1';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;
-- WRITER CONNECTION:
set connection writer;
-- writer should not be blocked.
insert into test_1 values (2, 2);
-- writer should in no way be blocked by reader.
update test_1 set b = 10 where a = 1;
-- READ UNCOMMITTED CONNECTION:
-- next in cursor should see the new uncommitted row just inserted (2)
set connection iso_read_uncommitted;
next scan_cursor;
-- full select should see the update and the new row.
select * from test_1;
-- WRITER CONNECTION:
-- delete the row that the read uncommitted connection is positioned on.
set connection writer;
-- writer should in no way be blocked by reader.
delete from test_1 where a = 2;
-- READ UNCOMMITTED CONNECTION:
-- next in cursor should get to end of scan.
set connection iso_read_uncommitted;
next scan_cursor;
close scan_cursor;
-- full select should see just one row.
select * from test_1;
-- WRITER CONNECTION:
set connection writer;
rollback;
-- READ UNCOMMITTED CONNECTION:
-- should now see original rows - note all of this in same xact.
set connection iso_read_uncommitted;
select * from test_1;
-- cleanup
set connection iso_read_uncommitted;
commit;
set connection writer;
commit;
drop table test_1;
commit;
------------------------------------------------------------------------------
-- TEST 2: test RU(read uncommitted) ddl interaction
------------------------------------------------------------------------------
-- set up
set connection iso_read_uncommitted;
autocommit off;
set isolation read uncommitted;
create table test_2 (a int);
insert into test_2 values (1);
commit;
-- READ UNCOMMITTED CONNECTION:
-- read uncommitted should maintain no lock on the row.
set connection iso_read_uncommitted;
select * from test_2;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
'select a from test_2';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;
-- WRITER CONNECTION:
set connection writer;
-- writer can't do ddl while reader has open cursor (reader blocks writer).
drop table test_2;
-- get lock on test_2, held to end of xact.
insert into test_2 values (2);
-- READ UNCOMMITTED CONNECTION:
-- uncommitted reader is blocked from doing ddl while other xact has locks.
-- (writer blocks reader).
set connection iso_read_uncommitted;
close scan_cursor;
commit;
-- should block on lock (writer blocks reader).
drop table test_2;
-- cleanup
set connection iso_read_uncommitted;
commit;
set connection writer;
commit;
drop table test_2;
commit;
--------------------------------------------------------------------------------
-- FINAL CLEANUP (only disconnect at end of script)
set connection writer;
commit;
disconnect;
set connection iso_read_uncommitted;
commit;
disconnect;
|
