rlliso2multi.sql

derby database source code.good for you.

--------------------------------------------------------------------------------
-- The scan will requalify rows when it goes to the base table, thus it will
-- see 3, but block when it gets to the key of deleted row (4).
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
next scan_cursor;

-- commit the delete
set connection deleter;
commit;

-- scanner should see 1,2,3,4,6
set connection scanner;
close scan_cursor;

select a,b from test_5;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_5;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 6: make sure a that heap scans which cross page boundaries release
--         locks correctly.
--------------------------------------------------------------------------------

-- Test setup - create a heap with one row per page.

connect 'wombat' as scanner;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

connect 'wombat' as deleter;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;


set connection scanner;
autocommit off;
create table test_6 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
insert into test_6 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
insert into test_6 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
insert into test_6 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
insert into test_6 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
insert into test_6 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
create index test_6_idx on test_6 (a);
commit;

set connection deleter;
autocommit off;
commit;


--------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked index to base row scan on the index.
-- By using group fetch it will read and release locks on multiple rows from
-- the index and save away row pointers from the index.
--------------------------------------------------------------------------------
set connection scanner;
get cursor scan_cursor as 
    'select a, a2 from test_6';

next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;

--------------------------------------------------------------------------------
-- Delete all rows that the scanner has looked at, and should have released the
-- lock on.
--------------------------------------------------------------------------------
set connection deleter;
delete from test_6 where a = 1;
delete from test_6 where a = 2;
delete from test_6 where a = 3;
delete from test_6 where a = 4;

--------------------------------------------------------------------------------
-- The scan should either block on the delete or continue and not return the
-- the deleted row.
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
close scan_cursor;

-- commit the delete
set connection deleter;
delete from test_6 where a = 5;
commit;

-- scanner should see no rows.
set connection scanner;
select a,b from test_6;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_6;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 7: make sure that 2 heap cursor scans in same transaction work (at one
--         point there was a problem where releasing locks in one of the cursors
--         released locks in the other cursor).
--------------------------------------------------------------------------------

-- Test setup - create a heap with one row per page.

connect 'wombat' as scanner;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

connect 'wombat' as deleter;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;


--------------------------------------------------------------------------------
-- HEAP SCAN
--------------------------------------------------------------------------------
set connection scanner;
autocommit off;
create table test_7 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
insert into test_7 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
insert into test_7 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
insert into test_7 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
insert into test_7 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
insert into test_7 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
commit;

set connection deleter;
autocommit off;
commit;


-- Set up scanner to be doing a row locked heap scan, going one row at a time. 
set connection scanner;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor_1 as 
    'select a, a2 from test_7';
get cursor scan_cursor_2 as 
    'select a, a2 from test_7';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;

next scan_cursor_2;

close scan_cursor_2;

-- Get exclusive table lock on test_7.  Should fail with table cannot be locked.
set connection deleter;
lock table test_7 in exclusive mode;

-- release all read locks, by moving the cursor past all the rows.
set connection scanner;
next scan_cursor_1;
close scan_cursor_1;

-- Get exclusive table lock on test_7.  Now that both scan closed this should
-- work.
set connection deleter;
delete from test_7;
commit;

-- scanner should see no rows.
set connection scanner;
select a,b from test_7;
commit;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_7;
commit;
disconnect;

--------------------------------------------------------------------------------
-- Test 8: Exercise post commit cases, force the code through the path, no easy
--         way to make sure the post commit work is actually doing something.
--         All these case were run with lock debugging by hand to make sure the
--         right thing was happening:
--         
--         8.1 - heap post commit successfully gets table X lock and cleans up.
--         8.2 - heap post commit can't get table X lock, so gives up and let's
--               client continue on with work.
--         8.3 - btree post commit successfully gets table X lock and cleans up.
--         8.4 - btree post commit can't get table X lock, so gives up and let's
--               client continue on with work.
--               client continue on with work.
--
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- 8.1 setup 
--------------------------------------------------------------------------------
set connection client_1;
create table test_8 (a int, a2 int, b varchar(2000), c char(10)) 
    ;
create index test_8_idx on test_8 (a);
insert into test_8 values (1, 10, PADSTRING('a',2000), 'test 8.1');
insert into test_8 values (2, 20, PADSTRING('b',2000), 'test 8.1');
insert into test_8 values (3, 30, PADSTRING('c',2000), 'test 8.1');
insert into test_8 values (4, 40, PADSTRING('d',2000), 'test 8.1');
insert into test_8 values (5, 50, PADSTRING('e',2000), 'test 8.1');
commit;

--------------------------------------------------------------------------------
-- 8.1 test - simply delete rows from table, heap post commit will run and 
--            reclaim all pages.
--------------------------------------------------------------------------------

set connection client_1;
delete from test_8;
commit;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.2 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1000), c char(10))
    ;
create index test_8_idx on test_8 (a);
insert into test_8 values (1, 10, PADSTRING('a',1000), 'test 8.2');
insert into test_8 values (2, 20, PADSTRING('b',1000), 'test 8.2');
insert into test_8 values (3, 30, PADSTRING('c',1000), 'test 8.2');
insert into test_8 values (4, 40, PADSTRING('d',1000), 'test 8.2');
insert into test_8 values (5, 50, PADSTRING('e',1000), 'test 8.2');
commit;

--------------------------------------------------------------------------------
-- 8.2 test - client 1 holds row lock which will prevent client 2 post commit.
--------------------------------------------------------------------------------

set connection client_1;
insert into test_8 values (6, 60, PADSTRING('a',1000), 'test 8.2');

set connection client_2;
delete from test_8 where a < 5;
commit;

set connection client_1;
select a from test_8;
commit;

set connection client_2;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.3 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1500), c char(10));
create index test_8_idx on test_8 (a, b)
    ;
insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.3');
insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.3');
insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.3');
insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.3');
insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.3');
commit;

--------------------------------------------------------------------------------
-- 8.3 test - simply delete rows from index table, btree post commit will run
--            and reclaim all pages.
--------------------------------------------------------------------------------

set connection client_1;
delete from test_8;
commit;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.4 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1500), c char(10)) ;
create index test_8_idx1 on test_8 (a);
create index test_8_idx2 on test_8 (a, b)
    ;
insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.4');
insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.4');
insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.4');
insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.4');
insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.4');
commit;

--------------------------------------------------------------------------------
-- 8.4 test - client 1 holds row lock which will prevent client 2 post commit.
--------------------------------------------------------------------------------

set connection client_1;
insert into test_8 values (6, 60, PADSTRING('a',1500), 'test 8.4');

set connection client_2;