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
|
--
-- 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.
--
-- testing Transaction table
maximumdisplaywidth 9000;
connect 'wombat' as c1;
set isolation to rr;
-- Only look at user transactions. Depending on timing of background
-- threads for post commit and checkpoint there may be system and
-- and internal transactions that vary from machine to machine.
create view xactTable as
select username, type, status,
case when first_instant is NULL then 'readonly' else 'not readonly' end as readOnly, cast(sql_text as varchar(512)) sql_text
from syscs_diag.transaction_table
where type = 'UserTransaction';
commit;
select * from xactTable order by username, sql_text, status, type;
create table foo (a int);
create index fooi on foo (a);
select * from xactTable order by username, sql_text, status, type;
autocommit off;
select * from foo;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
commit;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table ;
insert into foo values (1), (3), (5), (7), (9);
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
commit;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table;
insert into foo values (6), (10);
-- make another connection
connect 'wombat' as c2;
set isolation to rr;
autocommit off;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
autocommit off;
select * from foo where a < 2;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
insert into foo values (2), (4);
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
-- this should block and result in a timeout
select * from foo;
select * from xactTable order by username, sql_text, status, type;
-- when last statement finished rolling back, this transaction should be IDLE;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
-- this should also block
drop table foo;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
commit;
disconnect;
set connection c1;
select * from xactTable order by username, sql_text, status, type;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
drop table foo;
commit;
select * from xactTable order by username, sql_text, status, type;
select l.type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table l right outer join syscs_diag.transaction_table t
on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction'
order by lockname, mode, cnt, state;
commit;
-- ensure the system vti can not be modified.
drop table syscs_diag.transaction_table;
alter table syscs_diag.transaction_table add column x int;
update syscs_diag.transaction_table set xid = NULL;
delete from syscs_diag.transaction_table where 1 = 1;
insert into syscs_diag.transaction_table(xid) values('bad');
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1);
call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1, 1, 1);
-- ensure the old syntax still works until it is deprecated
select xid from new org.apache.derby.diag.TransactionTable() AS t where 1 = 0;
update new org.apache.derby.diag.TransactionTable() set xid = NULL;
delete from new org.apache.derby.diag.TransactionTable() where 1 = 0;
disconnect;
|
