rlliso2multi.sql

derby database source code.good for you.

--------------------------------------------------------------------------------
-- Test multi user lock interaction under isolation level 2.  default isolation
-- level has been set as a property to serializable.
--------------------------------------------------------------------------------
run resource 'createTestProcedures.subsql';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');

autocommit off;

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

--------------------------------------------------------------------------------
-- Test 0: verify isolation level by seeing if a read lock is released or not.
--------------------------------------------------------------------------------
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 writer;
-- 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 scanner;
autocommit off;
create table test_0 (a int);
insert into test_0 values (1);
commit;

set connection writer;
autocommit off;

-- isolation 2 scanner should release read lock on table after statement.
set connection scanner;
select * from test_0;

-- writer should be able to insert into table - scanner released read lock.
set connection writer;
insert into test_0 values (2);

-- scanner will now block on uncommitted insert, and get lock timeout
set connection scanner;
select * from test_0;
commit;

-- commit writer - releasing all locks.
set connection writer;
commit;

-- scanner will now see 2 rows
set connection scanner;
select * from test_0;
commit;

-- cleanup
set connection scanner;
drop table test_0;
commit;
disconnect;
set connection writer;
disconnect;


--------------------------------------------------------------------------------
-- Test 1: make sure a leaf root growing get's the right lock.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Test setup - create a 1 page btree, with the page ready to split.
--------------------------------------------------------------------------------
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 rootgrower;
-- 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 a (a varchar(1200), b varchar(1000)) ;
insert into a values (PADSTRING('a',1200), PADSTRING('a',1000));
insert into a values (PADSTRING('b',1200), PADSTRING('b',1000));
insert into a values (PADSTRING('c',1200), PADSTRING('c',1000));
create index a_idx on a (a) ;
commit;

set connection rootgrower;
autocommit off;
commit;


--------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked covered scan on the index.
--------------------------------------------------------------------------------
set connection scanner;
autocommit off;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as 
    'select a from a where a >= PADSTRING(''a'',1200) and a < PADSTRING(''c'',1200) ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;

--------------------------------------------------------------------------------
-- This should block and timeout on the scan lock held by the scanner on the first page.
--------------------------------------------------------------------------------
set connection rootgrower;
autocommit off;
insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));

--------------------------------------------------------------------------------
-- The scan should continue unaffected.
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
next scan_cursor;

--------------------------------------------------------------------------------
-- This insert will block on the previous key lock of the scanner.
--------------------------------------------------------------------------------
set connection rootgrower;
insert into a values (PADSTRING('ab',1200), PADSTRING('ab',1000));

--------------------------------------------------------------------------------
-- Now the grow root should be allowed (note that cursor scan has locks
-- on the leaf page being grown - just not the scan lock).
--------------------------------------------------------------------------------
set connection rootgrower;
insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));

select a from a;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection rootgrower;
commit;
disconnect;
set connection scanner;
commit;
drop table a;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 2: make sure previous key locks are gotten correctly.
--------------------------------------------------------------------------------
connect 'wombat' as client_1;
-- 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 client_2;
-- 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 client_1;
autocommit off;
create table a (a varchar(1000), b varchar(1000)) ;
create unique index a_idx on a (a) ;
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));
insert into a values (PADSTRING('e',1000), PADSTRING('e',1000));
insert into a values (PADSTRING('f',1000), PADSTRING('f',1000));
insert into a values (PADSTRING('g',1000), PADSTRING('g',1000));
commit;

set connection client_2;
autocommit off;

--------------------------------------------------------------------------------
-- client 1 will get exclusive locks on 'c'.
--------------------------------------------------------------------------------
set connection client_1;
update a set b = 'new value' where a > 'b' and a <= 'd';

-- run resource 'LockTableQuery.subsql';

set connection client_2;

--------------------------------------------------------------------------------

-- the following will not time out, the insert
-- will get a previous key insert lock which will not conflict with the
-- non-insert read-committed exclusive lock on 'c'.

--------------------------------------------------------------------------------
insert into a values (PADSTRING('d',1000), PADSTRING('d',1000));

--------------------------------------------------------------------------------
-- the following should NOT cause a time out
--------------------------------------------------------------------------------
insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));

--------------------------------------------------------------------------------
-- the following will block because it is a unique index, and the insert is of
-- the same row being locked by client_1
--------------------------------------------------------------------------------
insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));

-- run resource 'LockTableQuery.subsql';

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection client_1;
select * from a;
commit;
set connection client_2;
commit;
select * from a;
drop table a;
commit;

--------------------------------------------------------------------------------
-- Test 3: make sure an exact key insert into unique key index blocks.
--------------------------------------------------------------------------------
set connection client_1;
autocommit off;
create table a (a varchar(1000), b varchar(1000)) ;
create unique index a_idx on a (a) ;
commit;
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));

set connection client_2;
autocommit off;
--------------------------------------------------------------------------------
-- the following should cause a time out, as the previous
-- key lock will conflict with client_1's lock on 'b'
--------------------------------------------------------------------------------
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));

--------------------------------------------------------------------------------
-- Test 4: make sure that row lock wait in a heap scan works
--------------------------------------------------------------------------------
set connection client_1;
autocommit off;
create table test_4 (a int, b varchar(1000), c varchar(1000)) ;
commit;
set connection client_2;
autocommit off;
commit;

-- client_1 will get a single row lock in the heap.
set connection client_1;
insert into test_4 values (1, PADSTRING('a',1000), PADSTRING('b',1000)); 

-- client_2 scans table, blocking on a row lock on the client_1 insert row, 
-- will get timeout message.
set connection client_2;
select * from test_4;

-- release the insert lock.
set connection client_1;
commit;

-- reader should be able to see row now.
set connection client_2;
select * from test_4;
commit;

-- cleanup
set connection client_1;
drop table test_4;
commit;

--------------------------------------------------------------------------------
-- Test 5: make sure a that a group fetch through a secondary index correctly
--         handles a row that is deleted after it has read a row from the index
--         but before it has read the row from the base table.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Test setup - create a 1 page btre, with the page ready to split.
--------------------------------------------------------------------------------
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_5 (a int, a2 int, b varchar(1000), c varchar(1000)) ;
insert into test_5 values (1, 10, PADSTRING('a',1000), PADSTRING('a',1000));
insert into test_5 values (2, 20, PADSTRING('b',1000), PADSTRING('b',1000));
insert into test_5 values (3, 30, PADSTRING('c',1000), PADSTRING('c',1000));
insert into test_5 values (4, 40, PADSTRING('d',1000), PADSTRING('d',1000));
insert into test_5 values (5, 50, PADSTRING('e',1000), PADSTRING('e',1000));
insert into test_5 values (6, 60, PADSTRING('f',1000), PADSTRING('f',1000));
create index test_5_idx on test_5 (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;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','10');
get cursor scan_cursor as 
    'select a, a2 from test_5 where a > 1 ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

-- because of group locking will get locks on 1, 2, 3, 4, and 5 and then will
-- release the locks on 1, 2, 3, and 4.  The last one is released on close or
-- on next call emptying the cursor.
next scan_cursor;

--------------------------------------------------------------------------------
-- Delete a row that the scanner has looked at but not reported back to the
-- caller.
--------------------------------------------------------------------------------
set connection deleter;
delete from test_5 where a = 4;