rlliso2multi.sql

derby database source code.good for you.

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;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection client_1;
drop table test_8;
commit;
disconnect;

set connection client_2;
commit;
disconnect;

--------------------------------------------------------------------------------
-- 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;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
autocommit off;
commit;

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

set connection scanner;
create table test_9 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
insert into test_9 values (5, 50, PADSTRING('e',1000), 'test 9.1');
insert into test_9 values (4, 40, PADSTRING('d',1000), 'test 9.1');
insert into test_9 values (3, 30, PADSTRING('c',1000), 'test 9.1');
insert into test_9 values (2, 20, PADSTRING('b',1000), 'test 9.1');
insert into test_9 values (1, 10, PADSTRING('a',1000), 'test 9.1');
create unique index test_9_idx on test_9 (b) ;
commit;

--------------------------------------------------------------------------------
-- 9.1 test - open a cursor for update on table, and make sure splitter waits
--            on the scan position.
--------------------------------------------------------------------------------

set connection scanner;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
    'select b from test_9 where b >= ''a'' ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;
next scan_cursor;

-- the following will get a couple of rows and then block on the split.
set connection splitter;
insert into test_9 values (0, 10, PADSTRING('aa',1000), 'test 9.1');
commit;
insert into test_9 values (0, 10, PADSTRING('ab',1000), 'test 9.1');
commit;

-- 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');
commit;

set connection scanner;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
commit;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection scanner;
drop table test_9;
commit;
disconnect;

set connection splitter;
commit;
disconnect;

--------------------------------------------------------------------------------
-- Test 10: Make sure a ddl does not block the lock table vti.
--------------------------------------------------------------------------------

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

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

set connection ddl;
run resource 'LockTableQuery.subsql';
commit;



--------------------------------------------------------------------------------
-- 10 test - do ddl in one connection and look at lock table in another 
--           connection.
--------------------------------------------------------------------------------

set connection ddl;

create table test_10 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
insert into test_10 values (4, 40, PADSTRING('d',1000), 'test 9.1');
insert into test_10 values (3, 30, PADSTRING('c',1000), 'test 9.1');
insert into test_10 values (2, 20, PADSTRING('b',1000), 'test 9.1');
insert into test_10 values (1, 10, PADSTRING('a',1000), 'test 9.1');

set connection locktable;
-- this should not block on the other thread.
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection ddl;
drop table test_10;
commit;
disconnect;

set connection locktable;
commit;
disconnect;



--------------------------------------------------------------------------------
-- Test 11: test update locks
--------------------------------------------------------------------------------

connect 'wombat' as t11scanner;
-- 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 t11updater;
-- 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 t11writer;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

-- set up
set connection t11updater;
autocommit off;
create table test_11 (a int, b int);
insert into test_11 values (1,1);
insert into test_11 values (2,2);
insert into test_11 values (8,8);
create index test11_idx on test_11 (a);
commit;

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--==================================================
-- t11updater gets an update lock on row where a=1
--==================================================

get cursor update_cursor as
    'select b from test_11 where a=1 for update of b';

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next update_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

set connection t11scanner;
autocommit off;

--
--------------------------------------------------
-- try to scan the table, readers are compatible with update lock.
--------------------------------------------------
select * from test_11;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- try to update the table, should timeout
--------------------------------------------------
update test_11 set b=99 where a = 1;

--
--------------------------------------------------
-- try to update the table, should go through
--------------------------------------------------
update test_11 set b=99 where a = 8;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- try to get an update lock
--------------------------------------------------
get cursor update_cursor2 as
    'select b from test_11 where a=1 for update of b';
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- should timeout (other transaction has a shared lock on this row)
--------------------------------------------------
next update_cursor2;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- should succeed (no other transaction has a shared lock on this row)
--------------------------------------------------
get cursor update_cursor3 as
    'select b from test_11 where a=8 for update of b';
select type, cnt, mode, tabname, lockname, state from lock_table2 order by tabname, type desc, mode, cnt, lockname;

next update_cursor3;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
close update_cursor2;
close update_cursor3;

set connection t11updater;
commit;
close update_cursor;

set connection t11scanner;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--==================================================
-- t11scanner gets a read lock
--==================================================

select b from test_11 where a=1;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;


--
--------------------------------------------------
-- should succeed (can get an update lock if there is already a shared lock)
--------------------------------------------------
set connection t11updater;
get cursor update_cursor as
    'select b from test_11 where a=1 for update of b';
next update_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
close update_cursor;

set connection t11scanner;
commit;

--
--==================================================
-- t11writer gets a write lock
--==================================================

set connection t11writer;
autocommit off;
update test_11 set b=77 where a=2;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

set connection t11updater;
get cursor update_cursor as
    'select b from test_11 where a=2 for update of b';
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
--
----------------------------------------------------
-- should timeout
----------------------------------------------------
next update_cursor;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection t11updater;
close update_cursor;
drop table test_11;
commit;
disconnect;
set connection t11scanner;
disconnect;
set connection t11writer;
disconnect;


exit;