📄 rowlockiso.out
字号:
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16');0 rows inserted/updated/deletedij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------1 |-1 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------2 |-2 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------4 |-4 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------5 |-5 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;No current rowij> 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 |A |Tablelock |GRANT|ACTIVE ij> close scan_cursor;ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> commit;ij> ---------------------------------------------------------------------------------- Test full cursor scan over all the rows in the heap, with default group fetch--------------------------------------------------------------------------------get cursor scan_cursor as 'select a, b from a';ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------1 |-1 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------2 |-2 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------4 |-4 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------5 |-5 ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;No current rowij> 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 |A |Tablelock |GRANT|ACTIVE ij> close scan_cursor;ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> commit;ij> ---------------------------------------------------------------------------------- Test full cursor for update scan over all the rows in the heap, -- with default group fetch. Group fetch should be disabled.--------------------------------------------------------------------------------get cursor scan_cursor as 'select a, b from a for update';ij> 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 |A |Tablelock |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------1 |-1 ij> 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 |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |U |A |(1,7) |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------2 |-2 ij> 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 |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |U |A |(1,8) |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------4 |-4 ij> 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 |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |U |A |(2,7) |GRANT|ACTIVE ij> next scan_cursor;A |B -----------------------5 |-5 ij> 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 |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |U |A |(3,6) |GRANT|ACTIVE ij> next scan_cursor;No current rowij> 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 |A |Tablelock |GRANT|ACTIVE ij> close scan_cursor;ij> 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 |A |Tablelock |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Test full read cursor scan on a join over all the rows in the btree, -- 2 row group fetch.--------------------------------------------------------------------------------drop table a;0 rows inserted/updated/deletedij> create table a (a int, b int, c varchar(1900), d int, e varchar(2000)) ;0 rows inserted/updated/deletedij> create index a_idx on a (a, b, c) ;0 rows inserted/updated/deletedij> commit;ij> create table b (a int, b int, c varchar(1900)) ;0 rows inserted/updated/deletedij> insert into b values (1, -1, PADSTRING('one',1900));1 row inserted/updated/deletedij> insert into b values (2, -2, PADSTRING('two',1900));1 row inserted/updated/deletedij> insert into b values (3, -3, PADSTRING('three',1900));1 row inserted/updated/deletedij> insert into b values (4, -4, PADSTRING('four',1900));1 row inserted/updated/deletedij> insert into b values (5, -5, PADSTRING('five',1900));1 row inserted/updated/deletedij> commit;ij> ---------------------------------------------------------------------------------- Test select from empty index--------------------------------------------------------------------------------select a, b from a;A |B -----------------------ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> commit;ij> ---------------------------------------------------------------------------------- Test select from one row index'd table--------------------------------------------------------------------------------insert into a values (5, -5, PADSTRING('five',1900), 5, PADSTRING('negative five',2000));1 row inserted/updated/deletedij> 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 |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |X |A |(1,7) |GRANT|ACTIVE ij> commit;ij> select a, b from a;A |B -----------------------5 |-5 ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> commit;ij> ---------------------------------------------------------------------------------- Test select from two row indexed heap table - this will release one row-- lock as it moves to the next one.--------------------------------------------------------------------------------insert into a values (4, -4, PADSTRING('four',1900), 4, PADSTRING('negative four',2000));1 row inserted/updated/deletedij> commit;ij> select a,b from a;A |B -----------------------4 |-4 5 |-5 ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> commit;ij> ---------------------------------------------------------------------------------- Test select from three row indexed heap table (multiple pages) - this will-- release one row lock as it moves to the next one.--------------------------------------------------------------------------------insert into a values (3, -3, PADSTRING('three',1900), 3, PADSTRING('negative three',2000));1 row inserted/updated/deletedij> insert into a values (2, -2, PADSTRING('two',1900), 2, PADSTRING('negative two',2000));1 row inserted/updated/deletedij> insert into a values (1, -1, PADSTRING('one',1900), 1, PADSTRING('negtive one',2000));1 row inserted/updated/deletedij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------APP |UserTran|TABLE |6 |IX |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |1 |X |A |(3,6) |GRANT|ACTIVE APP |UserTran|ROW |1 |X |A |(4,6) |GRANT|ACTIVE APP |UserTran|ROW |1 |X |A |(5,6) |GRANT|ACTIVE ij> commit;ij> delete from a where a.a = 3;1 row inserted/updated/deletedij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -