rlliso2multi.out

derby database source code.good for you.

APP     |UserTran|TABLE   |4   |IX  |TEST_10     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |X   |TEST_10     |Tablelock |GRANT|ACTIVE  
ij(LOCKTABLE)> commit;
ij(LOCKTABLE)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection ddl;
ij(DDL)> drop table test_10;
0 rows inserted/updated/deleted
ij(DDL)> commit;
ij(DDL)> disconnect;
ij> set connection locktable;
ij(LOCKTABLE)> commit;
ij(LOCKTABLE)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 11: test update locks
--------------------------------------------------------------------------------
connect 'wombat' as t11scanner;
ij(T11SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(T11SCANNER)> connect 'wombat' as t11updater;
ij(T11UPDATER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(T11UPDATER)> connect 'wombat' as t11writer;
ij(T11WRITER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(T11WRITER)> -- set up
set connection t11updater;
ij(T11UPDATER)> autocommit off;
ij(T11UPDATER)> create table test_11 (a int, b int);
0 rows inserted/updated/deleted
ij(T11UPDATER)> insert into test_11 values (1,1);
1 row inserted/updated/deleted
ij(T11UPDATER)> insert into test_11 values (2,2);
1 row inserted/updated/deleted
ij(T11UPDATER)> insert into test_11 values (8,8);
1 row inserted/updated/deleted
ij(T11UPDATER)> create index test11_idx on test_11 (a);
0 rows inserted/updated/deleted
ij(T11UPDATER)> commit;
ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
ij(T11UPDATER)> --
--==================================================
-- 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';
ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
ij(T11UPDATER)> next update_cursor;
B          
-----------
1          
ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11UPDATER)> set connection t11scanner;
ij(T11SCANNER)> autocommit off;
ij(T11SCANNER)> --
--------------------------------------------------
-- try to scan the table, readers are compatible with update lock.
--------------------------------------------------
select * from test_11;
A          |B          
-----------------------
1          |1          
2          |2          
8          |8          
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11SCANNER)> --
--------------------------------------------------
-- try to update the table, should timeout
--------------------------------------------------
update test_11 set b=99 where a = 1;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(T11SCANNER)> --
--------------------------------------------------
-- try to update the table, should go through
--------------------------------------------------
update test_11 set b=99 where a = 8;
1 row inserted/updated/deleted
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,9)     |GRANT|ACTIVE  
ij(T11SCANNER)> commit;
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11SCANNER)> --
--------------------------------------------------
-- try to get an update lock
--------------------------------------------------
get cursor update_cursor2 as
    'select b from test_11 where a=1 for update of b';
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11SCANNER)> --
--------------------------------------------------
-- should timeout (other transaction has a shared lock on this row)
--------------------------------------------------
next update_cursor2;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11SCANNER)> --
--------------------------------------------------
-- 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';
ij(T11SCANNER)> select type, cnt, mode, tabname, lockname, state from lock_table2 order by tabname, type desc, mode, cnt, lockname;
TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
------------------------------------------------
TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT
TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT
ROW     |1   |S   |TEST_11     |(1,1)     |GRANT
ROW     |1   |U   |TEST_11     |(1,7)     |GRANT
ij(T11SCANNER)> next update_cursor3;
B          
-----------
99         
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,9)     |GRANT|ACTIVE  
ij(T11SCANNER)> commit;
ij(T11SCANNER)> close update_cursor2;
ij(T11SCANNER)> close update_cursor3;
ij(T11SCANNER)> set connection t11updater;
ij(T11UPDATER)> commit;
ij(T11UPDATER)> close update_cursor;
ij(T11UPDATER)> set connection t11scanner;
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
ij(T11SCANNER)> --
--==================================================
-- t11scanner gets a read lock
--==================================================
select b from test_11 where a=1;
B          
-----------
1          
ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
ij(T11SCANNER)> --
--------------------------------------------------
-- should succeed (can get an update lock if there is already a shared lock)
--------------------------------------------------
set connection t11updater;
ij(T11UPDATER)> get cursor update_cursor as
    'select b from test_11 where a=1 for update of b';
ij(T11UPDATER)> next update_cursor;
B          
-----------
1          
ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
ij(T11UPDATER)> commit;
ij(T11UPDATER)> close update_cursor;
ij(T11UPDATER)> set connection t11scanner;
ij(T11SCANNER)> commit;
ij(T11SCANNER)> --
--==================================================
-- t11writer gets a write lock
--==================================================
set connection t11writer;
ij(T11WRITER)> autocommit off;
ij(T11WRITER)> update test_11 set b=77 where a=2;
1 row inserted/updated/deleted
ij(T11WRITER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,8)     |GRANT|ACTIVE  
ij(T11WRITER)> set connection t11updater;
ij(T11UPDATER)> get cursor update_cursor as
    'select b from test_11 where a=2 for update of b';
ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,8)     |GRANT|ACTIVE  
ij(T11UPDATER)> --
----------------------------------------------------
-- should timeout
----------------------------------------------------
next update_cursor;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(T11UPDATER)> --------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection t11updater;
ij(T11UPDATER)> close update_cursor;
ij(T11UPDATER)> drop table test_11;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(T11UPDATER)> commit;
ij(T11UPDATER)> disconnect;
ij> set connection t11scan