rlliso3multi.out

derby database source code.good for you.

ij> --------------------------------------------------------------------------------
-- Test multi user lock interaction under isolation level 3.  default isolation
-- level has been set as a property to serializable.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Test 0: verify isolation level by seeing if a read lock is released or not.
--------------------------------------------------------------------------------
connect 'wombat' as scanner;
ij(SCANNER)> set isolation to rr;
0 rows inserted/updated/deleted
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)> connect 'wombat' as writer;
ij(WRITER)> set isolation to rr;
0 rows inserted/updated/deleted
ij(WRITER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(WRITER)> run resource 'createTestProcedures.subsql';
ij(WRITER)> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij(WRITER)> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij(WRITER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
0 rows inserted/updated/deleted
ij(WRITER)> -- set up
set connection scanner;
ij(SCANNER)> autocommit off;
ij(SCANNER)> create table test_0 (a int);
0 rows inserted/updated/deleted
ij(SCANNER)> insert into test_0 values (1);
1 row inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> set connection writer;
ij(WRITER)> autocommit off;
ij(WRITER)> -- scanner should hold read lock on table until end of transaction.
set connection scanner;
ij(SCANNER)> select * from test_0;
A          
-----------
1          
ij(SCANNER)> -- writer should get a lock timeout.
set connection writer;
ij(WRITER)> insert into test_0 values (2);
ERROR 40XL1: A lock could not be obtained within the time requested
ij(WRITER)> -- scanner should only see the original row.
set connection scanner;
ij(SCANNER)> select * from test_0;
A          
-----------
1          
ij(SCANNER)> commit;
ij(SCANNER)> select * from test_0;
A          
-----------
1          
ij(SCANNER)> -- cleanup
set connection scanner;
ij(SCANNER)> drop table test_0;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> set connection writer;
ij(WRITER)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 1: make sure a leaf root growing get's the right lock.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Test setup - create a 1 page btre, with the page ready to split.
--------------------------------------------------------------------------------
connect 'wombat' as scanner;
ij(SCANNER)> set isolation to rr;
0 rows inserted/updated/deleted
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)> connect 'wombat' as rootgrower;
ij(ROOTGROWER)> set isolation to rr;
0 rows inserted/updated/deleted
ij(ROOTGROWER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(ROOTGROWER)> set connection scanner;
ij(SCANNER)> autocommit off;
ij(SCANNER)> create table a (a varchar(1000), b varchar(1000)) ;
0 rows inserted/updated/deleted
ij(SCANNER)> insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));
1 row inserted/updated/deleted
ij(SCANNER)> create index a_idx on a (a) ;
0 rows inserted/updated/deleted
ij(SCANNER)> ;
ij(SCANNER)> commit;
ij(SCANNER)> set connection rootgrower;
ij(ROOTGROWER)> autocommit off;
ij(ROOTGROWER)> commit;
ij(ROOTGROWER)> --------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked covered scan on the index.
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> autocommit off;
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 a from a where a >= PADSTRING(''a'',1000) and a < PADSTRING(''c'',1000) ';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor;
A                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
a                                                                                                                              &
ij(SCANNER)> --------------------------------------------------------------------------------
-- This should block on the scan lock held by the scanner on the first page.
--------------------------------------------------------------------------------
set connection rootgrower;
ij(ROOTGROWER)> autocommit off;
ij(ROOTGROWER)> insert into a values (PADSTRING('d',1000), PADSTRING('d',1000));
ERROR 40XL1: A lock could not be obtained within the time requested
ij(ROOTGROWER)> --------------------------------------------------------------------------------
-- The scan should continue unaffected.
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> next scan_cursor;
A                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
b                                                                                                                              &
ij(SCANNER)> next scan_cursor;
No current row
ij(SCANNER)> --------------------------------------------------------------------------------
-- This insert will block on the previous key lock of the scanner.
--------------------------------------------------------------------------------
set connection rootgrower;
ij(ROOTGROWER)> insert into a values (PADSTRING('ab',1000), PADSTRING('ab',1000));
ERROR 40XL1: A lock could not be obtained within the time requested
ij(ROOTGROWER)> --------------------------------------------------------------------------------
-- 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;
ij(ROOTGROWER)> insert into a values (PADSTRING('d',1000), PADSTRING('d',1000));
1 row inserted/updated/deleted
ij(ROOTGROWER)> select a from a;
A                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
a                                                                                                                              &
b                                                                                                                              &
c                                                                                                                              &
d                                                                                                                              &
ij(ROOTGROWER)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection rootgrower;
ij(ROOTGROWER)> commit;
ij(ROOTGROWER)> disconnect;
ij> set connection scanner;
ij(SCANNER)> commit;
ij(SCANNER)> drop table a;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 2: make sure previous key locks are gotten correctly.
--------------------------------------------------------------------------------
connect 'wombat' as client_1;
ij(CLIENT_1)> set isolation to rr;
0 rows inserted/updated/deleted
ij(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;
ij(CLIENT_1)> connect 'wombat' as client_2;
ij(CLIENT_2)> set isolation to rr;
0 rows inserted/updated/deleted
ij(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;
ij(CLIENT_2)> set connection client_1;
ij(CLIENT_1)> autocommit off;
ij(CLIENT_1)> create table a (a varchar(1000), b varchar(1000)) ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create index a_idx on a (a) ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
1 row inserted/updated/deleted
ij(CLIENT_1)> set connection client_2;
ij(CLIENT_2)> autocommit off;
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- the following will not cause a time out, as the previous
-- key insert lock will not conflict with other insert locks, only other
-- select locks, or non insert update locks.
--------------------------------------------------------------------------------
insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));
1 row inserted/updated/deleted
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- the following should NOT cause a time out
--------------------------------------------------------------------------------
insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));
1 row inserted/updated/deleted
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> commit;
ij(CLIENT_1)> set connection client_2;
ij(CLIENT_2)> commit;
ij(CLIENT_2)> drop table a;
0 rows inserted/updated/deleted
ij(CLIENT_2)> commit;
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- Test 3: make sure an exact key insert into unique key index blocks.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> autocommit off;
ij(CLIENT_1)> create table a (a varchar(1000), b varchar(1000));
0 rows inserted/updated/deleted
ij(CLIENT_1)> create unique index a_idx on a (a) ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
1 row inserted/updated/deleted
ij(CLIENT_1)> set connection client_2;
ij(CLIENT_2)> autocommit off;
ij(CLIENT_2)> --------------------------------------------------------------------------------