derby94.out

derby database source code.good for you.

ij> create view lock_table as select 
cast(l.type as char(8)) as type,cast(lockcount as char(3)) as
cnt,mode,cast(tablename as char(12)) as tabname,cast(lockname as char(10))
as lockname,state from new org.apache.derby.diag.LockTable() l ;
0 rows inserted/updated/deleted
ij> autocommit off;
ij> create table derby94_t1(c1 int, c2 int not null primary key);
0 rows inserted/updated/deleted
ij> create table derby94_t2(c1 int);
0 rows inserted/updated/deleted
ij> insert into derby94_t1 values (0, 200), (1, 201), (2, 202), (3, 203), (4, 204), (5, 205), (6, 206), (7, 207), (8, 208), (9, 209);
10 rows inserted/updated/deleted
ij> insert into derby94_t1 select c1+10 , c2 +10 from derby94_t1;
10 rows inserted/updated/deleted
ij> insert into derby94_t1 select c1+20 , c2 +20 from derby94_t1;
20 rows inserted/updated/deleted
ij> insert into derby94_t1 select c1+40 , c2 +40 from derby94_t1;
40 rows inserted/updated/deleted
ij> insert into derby94_t1 select c1+80 , c2 +80 from derby94_t1;
80 rows inserted/updated/deleted
ij> insert into derby94_t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
10 rows inserted/updated/deleted
ij> commit;
ij> get cursor c1 as 'select * from derby94_t1 FOR UPDATE of c1';
ij> next c1;
C1         |C2         
-----------------------
0          |200        
ij> update derby94_t1 set c1=c1+999 WHERE CURRENT OF c1;
1 row inserted/updated/deleted
ij> next c1;
C1         |C2         
-----------------------
1          |201        
ij> get cursor c2 as 'select *  from derby94_t2 FOR UPDATE of c1';
ij> next c2 ;
C1         
-----------
0          
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
------------------------------------------------
TABLE   |2   |IX  |DERBY94_T1  |Tablelock |GRANT
ROW     |1   |U   |DERBY94_T1  |(1,8)     |GRANT
ROW     |1   |X   |DERBY94_T1  |(1,7)     |GRANT
TABLE   |1   |IX  |DERBY94_T2  |Tablelock |GRANT
ROW     |1   |U   |DERBY94_T2  |(1,7)     |GRANT
ij> --following insert should get X lock on derby94_t2 because of escalation , but should leave U lock on derby94_t1 as it is
insert into derby94_t2 select c1 from derby94_t1 ;
160 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
------------------------------------------------
TABLE   |3   |IX  |DERBY94_T1  |Tablelock |GRANT
ROW     |1   |U   |DERBY94_T1  |(1,8)     |GRANT
ROW     |1   |X   |DERBY94_T1  |(1,7)     |GRANT
TABLE   |4   |IX  |DERBY94_T2  |Tablelock |GRANT
TABLE   |1   |X   |DERBY94_T2  |Tablelock |GRANT
ij> --following update statement should escalate the locks on derby94_t1 to table level X lock
update derby94_t1 set c1=c1+999 ;
160 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
------------------------------------------------
TABLE   |8   |IX  |DERBY94_T1  |Tablelock |GRANT
TABLE   |1   |X   |DERBY94_T1  |Tablelock |GRANT
TABLE   |4   |IX  |DERBY94_T2  |Tablelock |GRANT
TABLE   |1   |X   |DERBY94_T2  |Tablelock |GRANT
ij> close c1 ;
ij> close c2 ;
ij> commit ;
ij> --following lock table dump should not show any  locks, above commit should have release them
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
------------------------------------------------
ij> drop table derby94_t1;
0 rows inserted/updated/deleted
ij> drop table derby94_t2;
0 rows inserted/updated/deleted
ij> commit;
ij> --end derby-94 case
;
ij>