📄 rowlockiso.out
字号:
USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------APP |UserTran|TABLE |2 |IX |A |Tablelock |GRANT|ACTIVE APP |UserTran|ROW |2 |X |A |(3,6) |GRANT|ACTIVE ij> commit;ij> select a,b from a;A |B -----------------------1 |-1 2 |-2 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 full read cursor scan over all the rows in the indexed heap, -- no group fetch. This should be a covered index scan (make sure rows come-- back in order sorted by index).--------------------------------------------------------------------------------CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');0 rows inserted/updated/deletedij> -- RESOLVE: missing row locks-- WORKAROUND: creating an index and dropping it -- to force the query 'select a, b from a' to be recompiledcreate index ix1 on a(a);0 rows inserted/updated/deletedij> drop index ix1;0 rows inserted/updated/deletedij> commit;ij> get cursor scan_cursor as 'select a, b from a';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 APP |UserTran|ROW |1 |S |A |(2,1) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(5,6) |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 APP |UserTran|ROW |1 |S |A |(4,1) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(4,6) |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 APP |UserTran|ROW |1 |S |A |(2,6) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(3,1) |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 APP |UserTran|ROW |1 |S |A |(1,7) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(3,1) |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> select * from lock_table order by tabname, type desc, mode, cnt, lockname;USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS ---------------------------------------------------------------------------ij> ---------------------------------------------------------------------------------- Test full cursor scan over all the rows in the index , 2 row group fetch.--------------------------------------------------------------------------------CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');0 rows inserted/updated/deletedij> -- RESOLVE: missing row locks-- WORKAROUND: creating an index and dropping it -- to force the query 'select a, b from a' to be recompiledcreate index ix1 on a(a);0 rows inserted/updated/deletedij> drop index ix1;0 rows inserted/updated/deletedij> commit;ij> get cursor scan_cursor as 'select a, b from a';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 APP |UserTran|ROW |1 |S |A |(4,1) |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 APP |UserTran|ROW |1 |S |A |(4,1) |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 APP |UserTran|ROW |1 |S |A |(3,1) |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 APP |UserTran|ROW |1 |S |A |(3,1) |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 index, with default group-- fetch---------------------------------------------------------------------------------- RESOLVE: missing row locks-- WORKAROUND: creating an index and dropping it -- to force the query 'select a, b from a' to be recompiledcreate index ix1 on a(a);0 rows inserted/updated/deletedij> drop index ix1;0 rows inserted/updated/deletedij> commit;ij> 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 getting index lock on a drop index - track 1634--------------------------------------------------------------------------------drop table a;0 rows inserted/updated/deletedij> commit;ij> create table a (a int);0 rows inserted/updated/deletedij> create index a2 on a (a);0 rows inserted/updated/deletedij> insert into a values (1);1 row inserted/updated/deletedij> commit;ij> drop index a2;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 |X |*** TRANSIEN|Tablelock |GRANT|ACTIVE APP |UserTran|TABLE |2 |X |A |Tablelock |GRANT|ACTIVE ij> commit;ij> drop table a;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -