rlliso2multi.out

derby database source code.good for you.

-- 8.4 setup 
--------------------------------------------------------------------------------
drop table test_8;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(1500), c char(10)) ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create index test_8_idx1 on test_8 (a);
0 rows inserted/updated/deleted
ij(CLIENT_1)> create index test_8_idx2 on test_8 (a, b)
    ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> --------------------------------------------------------------------------------
-- 8.4 test - client 1 holds row lock which will prevent client 2 post commit.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> insert into test_8 values (6, 60, PADSTRING('a',1500), 'test 8.4');
1 row inserted/updated/deleted
ij(CLIENT_1)> set connection client_2;
ij(CLIENT_2)> delete from test_8 where a < 5;
4 rows inserted/updated/deleted
ij(CLIENT_2)> commit;
ij(CLIENT_2)> set connection client_1;
ij(CLIENT_1)> select a from test_8;
A          
-----------
5          
6          
ij(CLIENT_1)> commit;
ij(CLIENT_1)> set connection client_2;
ij(CLIENT_2)> select a from test_8;
A          
-----------
5          
6          
ij(CLIENT_2)> commit;
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> drop table test_8;
0 rows inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> disconnect;
ij> set connection client_2;
ij(CLIENT_2)> commit;
ij(CLIENT_2)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 9: Make sure scan positioning in the beginning of a unique scan
--         properly gets the scan lock to block with splits.
--
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- 9.1 setup 
--------------------------------------------------------------------------------
connect 'wombat' as scanner;
ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(SCANNER)> autocommit off;
ij(SCANNER)> commit;
ij(SCANNER)> connect 'wombat' as splitter;
ij(SPLITTER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(SPLITTER)> autocommit off;
ij(SPLITTER)> commit;
ij(SPLITTER)> set connection scanner;
ij(SCANNER)> create table test_9 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
0 rows inserted/updated/deleted
ij(SCANNER)> insert into test_9 values (5, 50, PADSTRING('e',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_9 values (4, 40, PADSTRING('d',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_9 values (3, 30, PADSTRING('c',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_9 values (2, 20, PADSTRING('b',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_9 values (1, 10, PADSTRING('a',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SCANNER)> create unique index test_9_idx on test_9 (b) ;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- 9.1 test - open a cursor for update on table, and make sure splitter waits
--            on the scan position.
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
0 rows inserted/updated/deleted
ij(SCANNER)> get cursor scan_cursor as
    'select b from test_9 where b >= ''a'' ';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor;
B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
b                                                                                                                              &
ij(SCANNER)> next scan_cursor;
B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
c                                                                                                                              &
ij(SCANNER)> -- the following will get a couple of rows and then block on the split.
set connection splitter;
ij(SPLITTER)> insert into test_9 values (0, 10, PADSTRING('aa',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SPLITTER)> commit;
ij(SPLITTER)> insert into test_9 values (0, 10, PADSTRING('ab',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(SPLITTER)> commit;
ij(SPLITTER)> -- insert ahead in the cursor to make sure we pick it up later.
insert into test_9 values (0, 10, PADSTRING('dd',1000), 'test 9.1');
ERROR 40XL1: A lock could not be obtained within the time requested
ij(SPLITTER)> commit;
ij(SPLITTER)> set connection scanner;
ij(SCANNER)> next scan_cursor;
B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
d                                                                                                                              &
ij(SCANNER)> next scan_cursor;
B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
e                                                                                                                              &
ij(SCANNER)> next scan_cursor;
No current row
ij(SCANNER)> next scan_cursor;
No current row
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> drop table test_9;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> set connection splitter;
ij(SPLITTER)> commit;
ij(SPLITTER)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 10: Make sure a ddl does not block the lock table vti.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- 10 setup 
--------------------------------------------------------------------------------
connect 'wombat' as ddl;
ij(DDL)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(DDL)> autocommit off;
ij(DDL)> commit;
ij(DDL)> connect 'wombat' as locktable;
ij(LOCKTABLE)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(LOCKTABLE)> autocommit off;
ij(LOCKTABLE)> commit;
ij(LOCKTABLE)> set connection ddl;
ij(DDL)> run resource 'LockTableQuery.subsql';
ij(DDL)> create view 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 
    new org.apache.derby.diag.LockTable() l  right outer join new org.apache.derby.diag.TransactionTable() t
on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction';
0 rows inserted/updated/deleted
ij(DDL)> --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 
    new org.apache.derby.diag.LockTable() l right outer join new org.apache.derby.diag.TransactionTable() t
on l.xid = t.xid where l.tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(DDL)> -- lock table with no join.
create view lock_table2 as
select 
    cast(l.xid as char(8)) as xid,
    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
from 
    new org.apache.derby.diag.LockTable() l  
where l.tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(DDL)> -- transaction table with no join.
create view tran_table as
select 
    *
from 
    new org.apache.derby.diag.TransactionTable() t ;
0 rows inserted/updated/deleted
ij(DDL)> commit;
ij(DDL)> --------------------------------------------------------------------------------
-- 10 test - do ddl in one connection and look at lock table in another 
--           connection.
--------------------------------------------------------------------------------
set connection ddl;
ij(DDL)> create table test_10 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
0 rows inserted/updated/deleted
ij(DDL)> insert into test_10 values (4, 40, PADSTRING('d',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(DDL)> insert into test_10 values (3, 30, PADSTRING('c',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(DDL)> insert into test_10 values (2, 20, PADSTRING('b',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(DDL)> insert into test_10 values (1, 10, PADSTRING('a',1000), 'test 9.1');
1 row inserted/updated/deleted
ij(DDL)> set connection locktable;
ij(LOCKTABLE)> -- this should not block on the other thread.
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------