escalatelock.out

derby database source code.good for you.

4          
4          
4          
4          
4          
4          
ij> -- check to make sure we have IS table and S row locks.
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   |IS  |FOO         |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,10)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,100)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,101)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,107)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,108)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,109)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,11)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,110)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,111)   |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,17)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,18)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,19)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,20)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,21)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,27)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,28)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,29)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,3)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,30)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,31)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,37)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,38)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,39)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,40)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,41)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,47)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,48)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,49)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,50)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,51)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,57)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,58)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,59)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,60)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,61)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,67)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,68)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,69)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,70)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,71)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,77)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,78)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,79)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,8)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,80)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,81)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,87)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,88)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,89)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,9)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,90)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,91)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,97)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,98)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |S   |FOO         |(1,99)    |GRANT|ACTIVE  
ij> -- now get enough IS row locks to push over the lock escalation limit
select a from foo where a >= 5;
A          
-----------
5          
5          
5          
5          
5          
5          
5          
5          
5          
5          
5          
6          
6          
6          
6          
6          
6          
6          
6          
6          
6          
6          
7          
7          
7          
7          
7          
7          
7          
7          
7          
7          
7          
8          
8          
8          
8          
8          
8          
8          
8          
8          
8          
8          
9          
9          
9          
9          
9          
9          
9          
9          
9          
9          
9          
ij> -- check to make sure we now just have a S table lock.
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |3   |IS  |FOO         |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |S   |FOO         |Tablelock |GRANT|ACTIVE  
ij> -- make sure subsequent IS locks are recognized as covered by the S table lock.
select a from foo where a = 8;
A          
-----------
8          
8          
8          
8          
8          
8          
8          
8          
8          
8          
8          
ij> -- check to make sure we now just have a S table lock.
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |4   |IS  |FOO         |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |S   |FOO         |Tablelock |GRANT|ACTIVE  
ij> commit;
ij> -- TEST 3 - reproduce abort failure similar to bug 4328
create table aborttest (keycol int, data varchar(1000));
0 rows inserted/updated/deleted
ij> -- first insert 110 rows
insert into aborttest values (0, PADSTRING('0',1000)), (0, PADSTRING('1',1000)), (0, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('3',1000)), (0, PADSTRING('4',1000)), (0, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('6',1000)), (0, PADSTRING('7',1000)), (0, PADSTRING('8',1000)), (0, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values  (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> insert into aborttest values (0, PADSTRING('0',1000)), (1, PADSTRING('1',1000)), (2, PADSTRING('2',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values (3, PADSTRING('3',1000)), (4, PADSTRING('4',1000)), (5, PADSTRING('5',1000));
3 rows inserted/updated/deleted
ij> insert into aborttest values  (6, PADSTRING('6',1000)), (7, PADSTRING('7',1000)), (8, PADSTRING('8',1000)), (9, PADSTRING('9',1000));
4 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
0 rows inserted/updated/deleted
ij> create index idx on aborttest (keycol, data);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
0 rows inserted/updated/deleted
ij> commit;
ij> delete from aborttest where keycol < 3;
40 rows inserted/updated/deleted
ij> -- check to make sure we have a X row locks and IX table lock;
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  |ABORTTEST   |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |ABORTTEST   |(1,3)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(1,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(1,8)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(1,9)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(11,6)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(11,7)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(11,8)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(14,7)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(14,8)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(15,6)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(17,8)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(18,6)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(18,7)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(2,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(2,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(2,8)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(21,6)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(21,7)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(21,8)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(24,7)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(24,8)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(25,6)    |GRANT|ACTIVE  
APP     |UserTran|ROW     |2   |X   |ABORTTEST   |(27,8)    |GRANT|ACTIVE