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
|
ij> --
-- 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.
--
xa_datasource 'wombat' create;
ij> -- xa_datasource 'wombat';
---------------------------------------------
-- a single connection, prepare transaction and crash.
---------------------------------------------
xa_connect ;
ij> xa_start xa_noflags 0;
ij> xa_getconnection;
ij(XA)> drop table foo;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist.
ij(XA)> create table foo (a int);
0 rows inserted/updated/deleted
ij(XA)> insert into foo values (0);
1 row inserted/updated/deleted
ij(XA)> select * from foo;
A
-----------
0
ij(XA)> run resource 'global_xactTable.view';
ij(XA)> --
-- 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.
--
create view global_xactTable as
select
cast(global_xid as char(2)) as gxid,
status,
case when first_instant is NULL then 'NULL' else 'false' end as readOnly,
cast (username as char(10)) as username,
type
from syscs_diag.transaction_table;
0 rows inserted/updated/deleted
ij(XA)> run resource 'LockTableQuery.subsql';
ij(XA)> --
-- 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.
--
create view lock_table as
select
cast(username as char(8)) as username,
cast(t.type as char(15)) as trantype,
cast(l.type as char(8)) as type,
cast(lockcount as char(3)) as cnt,
mode,
cast(tablename as char(12)) as tabname,
cast(lockname as char(10)) as lockname,
state,
status
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';
0 rows inserted/updated/deleted
ij(XA)> --on l.xid = t.xid where l.tableType <> 'S' or l.tableType is null
-- order by
-- tabname, type desc, mode, cnt, lockname
-- lock table with system catalog locks included.
create view full_lock_table as
select
cast(username as char(8)) as username,
cast(t.type as char(8)) as trantype,
cast(l.type as char(8)) as type,
cast(lockcount as char(3)) as cnt,
mode,
cast(tablename as char(12)) as tabname,
cast(lockname as char(10)) as lockname,
state,
status
from
syscs_diag.lock_table l right outer join syscs_diag.transaction_table t
on l.xid = t.xid where l.tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(XA)> -- lock table with no join.
-- DERBY-6183, removing CAST's to avoid background locks causing
-- data trunction warnings.
create view lock_table2 as
select
xid,
type,
lockcount as cnt,
mode,
tablename as tabname,
lockname,
state
from
syscs_diag.lock_table
where tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(XA)> -- transaction table with no join.
create view tran_table as
select
*
from
syscs_diag.transaction_table;
0 rows inserted/updated/deleted
ij(XA)> xa_end xa_success 0;
ij(XA)> xa_commit xa_1phase 0;
ij(XA)> ---------------------------------------------
-- a single connection, prepare transaction and crash.
---------------------------------------------
xa_start xa_noflags 1;
ij(XA)> insert into foo values (0);
1 row inserted/updated/deleted
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(1 |ACTIVE |false|APP |UserTransaction
WARNING 01004: Data truncation
ij(XA)> select global_xid, username, type, status, cast(sql_text as varchar(512)) sql_text from tran_table where global_xid is not null order by global_xid, username, type, status, sql_text;
GLOBAL_XID |USERNAME |TYPE |STATUS |SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(1,776f6d626174,776f6d626174) |APP |UserTransaction |ACTIVE |select global_xid, username, type, status, cast(sql_text as varchar(512)) sql_text from tran_table where global_xid is not null&
ij(XA)> xa_end xa_success 1;
ij(XA)> xa_prepare 1;
ij(XA)> xa_start xa_noflags 2;
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(1 |PREPARED|false|APP |UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
(2 |IDLE |NULL |APP |UserTransaction
ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS
----------------------------------------------------------------------------------
APP |UserTransaction|TABLE |1 |IX |FOO |Tablelock |GRANT|PREPARED
APP |UserTransaction|ROW |1 |X |FOO |(1,8) |GRANT|PREPARED
ij(XA)> xa_end xa_success 2;
ij(XA)> xa_commit xa_1phase 2;
ij(XA)> xa_recover xa_startrscan;
[, Recovered 1 in-doubt transactions, , Transaction 1 : (1,776f6d626174,776f6d626174)]
ij(XA)> --- xa_datasource 'wombat' shutdown;
---------------------------------------------
-- shutdown the database.
---------------------------------------------
connect 'jdbc:derby:;shutdown=true';
ERROR XJ015: Derby system shutdown.
ij(XA)> ---------------------------------------------
-- restart the system, should find the prepared transaction
---------------------------------------------
xa_datasource 'wombat';
ij(XA)> ---------------------------------------------
-- a single connection, verify the prepared xact has come back.
---------------------------------------------
xa_connect ;
ij(XA)> xa_start xa_noflags 1;
IJ ERROR: XAER_DUPID
ij(XA)> xa_start xa_noflags 4;
ij(XA)> xa_getconnection;
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(1 |PREPARED|false|NULL |UserTransaction
WARNING 01004: Data truncation
WARNING 01004: Data truncation
(4 |IDLE |NULL |APP |UserTransaction
ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS
----------------------------------------------------------------------------------
NULL |UserTransaction|TABLE |1 |IX |FOO |Tablelock |GRANT|PREPARED
NULL |UserTransaction|ROW |1 |X |FOO |(1,8) |GRANT|PREPARED
ij(XA)> xa_recover xa_startrscan;
[, Recovered 1 in-doubt transactions, , Transaction 1 : (1,776f6d626174,776f6d626174)]
ij(XA)> xa_end xa_success 4;
ij(XA)> xa_commit xa_1phase 4;
ij(XA)> ---------------------------------------------
-- a single connection, now make sure after the commit that it is gone.
---------------------------------------------
xa_start xa_noflags 4;
ij(XA)> xa_commit xa_2phase 1;
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID|STATUS |READ&|USERNAME |TYPE
-------------------------------------------------------------
(4 |IDLE |NULL |APP |UserTransaction
WARNING 01004: Data truncation
ij(XA)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS
----------------------------------------------------------------------------------
ij(XA)> xa_recover xa_startrscan;
[, Recovered 0 in-doubt transactions, ]
ij(XA)> xa_end xa_success 4;
ij(XA)> xa_commit xa_1phase 4;
ij(XA)> ---------------------------------------------
-- a single connection, verify the prepared xact has come back - should
-- get an error trying to start another with same global xact.
---------------------------------------------
exit;
|
