rlliso2multi.out

derby database source code.good for you.

ij(SCANNER)> select a,b from test_6;
A          |B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
ij(DELETER)> commit;
ij(DELETER)> disconnect;
ij> set connection scanner;
ij(SCANNER)> commit;
ij(SCANNER)> drop table test_6;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> --------------------------------------------------------------------------------
-- 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;
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 deleter;
ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(DELETER)> --------------------------------------------------------------------------------
-- HEAP SCAN
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> autocommit off;
ij(SCANNER)> create table test_7 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
0 rows inserted/updated/deleted
ij(SCANNER)> insert into test_7 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_7 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_7 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_7 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into test_7 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
1 row inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter;
ij(DELETER)> autocommit off;
ij(DELETER)> commit;
ij(DELETER)> -- Set up scanner to be doing a row locked heap scan, going one row at a time. 
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_1 as 
    'select a, a2 from test_7';
ij(SCANNER)> get cursor scan_cursor_2 as 
    'select a, a2 from test_7';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor_1;
A          |A2         
-----------------------
1          |10         
ij(SCANNER)> next scan_cursor_1;
A          |A2         
-----------------------
2          |20         
ij(SCANNER)> next scan_cursor_1;
A          |A2         
-----------------------
3          |30         
ij(SCANNER)> next scan_cursor_1;
A          |A2         
-----------------------
4          |40         
ij(SCANNER)> next scan_cursor_1;
A          |A2         
-----------------------
5          |50         
ij(SCANNER)> next scan_cursor_2;
A          |A2         
-----------------------
1          |10         
ij(SCANNER)> close scan_cursor_2;
ij(SCANNER)> -- Get exclusive table lock on test_7.  Should fail with table cannot be locked.
set connection deleter;
ij(DELETER)> lock table test_7 in exclusive mode;
ERROR X0X02: Table 'TEST_7' cannot be locked in 'EXCLUSIVE' mode.
ERROR 40XL1: A lock could not be obtained within the time requested
ij(DELETER)> -- release all read locks, by moving the cursor past all the rows.
set connection scanner;
ij(SCANNER)> next scan_cursor_1;
No current row
ij(SCANNER)> close scan_cursor_1;
ij(SCANNER)> -- Get exclusive table lock on test_7.  Now that both scan closed this should
-- work.
set connection deleter;
ij(DELETER)> delete from test_7;
5 rows inserted/updated/deleted
ij(DELETER)> commit;
ij(DELETER)> -- scanner should see no rows.
set connection scanner;
ij(SCANNER)> select a,b from test_7;
A          |B                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
ij(DELETER)> commit;
ij(DELETER)> disconnect;
ij> set connection scanner;
ij(SCANNER)> commit;
ij(SCANNER)> drop table test_7;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> --------------------------------------------------------------------------------
-- 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;
ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(2000), c char(10)) 
    ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create index test_8_idx on test_8 (a);
0 rows inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',2000), 'test 8.1');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',2000), 'test 8.1');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',2000), 'test 8.1');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',2000), 'test 8.1');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',2000), 'test 8.1');
1 row inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> --------------------------------------------------------------------------------
-- 8.1 test - simply delete rows from table, heap post commit will run and 
--            reclaim all pages.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> delete from test_8;
5 rows inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> select a from test_8;
A          
-----------
ij(CLIENT_1)> commit;
ij(CLIENT_1)> --------------------------------------------------------------------------------
-- 8.2 setup 
--------------------------------------------------------------------------------
drop table test_8;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(1000), c char(10))
    ;
0 rows inserted/updated/deleted
ij(CLIENT_1)> create index test_8_idx on test_8 (a);
0 rows inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',1000), 'test 8.2');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',1000), 'test 8.2');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',1000), 'test 8.2');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',1000), 'test 8.2');
1 row inserted/updated/deleted
ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',1000), 'test 8.2');
1 row inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> --------------------------------------------------------------------------------
-- 8.2 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',1000), 'test 8.2');
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)> --------------------------------------------------------------------------------
-- 8.3 setup 
--------------------------------------------------------------------------------
drop table test_8;
0 rows inserted/updated/deleted
ij(CLIENT_2)> create table test_8 (a int, a2 int, b varchar(1500), c char(10));
0 rows inserted/updated/deleted
ij(CLIENT_2)> create index test_8_idx on test_8 (a, b)
    ;
0 rows inserted/updated/deleted
ij(CLIENT_2)> insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.3');
1 row inserted/updated/deleted
ij(CLIENT_2)> insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.3');
1 row inserted/updated/deleted
ij(CLIENT_2)> insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.3');
1 row inserted/updated/deleted
ij(CLIENT_2)> insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.3');
1 row inserted/updated/deleted
ij(CLIENT_2)> insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.3');
1 row inserted/updated/deleted
ij(CLIENT_2)> commit;
ij(CLIENT_2)> --------------------------------------------------------------------------------
-- 8.3 test - simply delete rows from index table, btree post commit will run
--            and reclaim all pages.
--------------------------------------------------------------------------------
set connection client_1;
ij(CLIENT_1)> delete from test_8;
5 rows inserted/updated/deleted
ij(CLIENT_1)> commit;
ij(CLIENT_1)> select a from test_8;
A          
-----------
ij(CLIENT_1)> commit;
ij(CLIENT_1)> --------------------------------------------------------------------------------