📄 rowlockbasic.out
字号:
ij> select a from a;A -----------1 2 3 4 ij> delete from a where a = 3;1 row inserted/updated/deletedij> select a from a;A -----------1 2 4 ij> commit;ij> insert into a values (3, PADSTRING('c',1000));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,9) |GRANT|ACTIVE APP |UserTran|ROW |1 |X |A |(2,7) |GRANT|ACTIVE ij> drop table a;0 rows inserted/updated/deletedij> create table a (a int, b char(200));0 rows inserted/updated/deletedij> create unique index a_idx on a (a);0 rows inserted/updated/deletedij> insert into a values (1, 'a');1 row inserted/updated/deletedij> insert into a values (2, 'b');1 row inserted/updated/deletedij> insert into a values (3, 'c');1 row inserted/updated/deletedij> insert into a values (4, 'd');1 row inserted/updated/deletedij> commit;ij> ---------------------------------------------------------------------------------- Do full covered index scan.--------------------------------------------------------------------------------select a from a;A -----------1 2 3 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 |S |A |Tablelock |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Do single probe into covered index (first key in table).--------------------------------------------------------------------------------select a from a where a = 1;A -----------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 |(1,7) |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Do single probe into covered index (last key in table).--------------------------------------------------------------------------------select a from a where a = 4;A -----------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 |(1,10) |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Do set of range scans that all return 1 row from covered index.--------------------------------------------------------------------------------select a from a where a <= 1;A -----------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 |(1,3) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(1,7) |GRANT|ACTIVE ij> commit;ij> select a from a where a >= 2 and a < 3;A -----------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 |(1,8) |GRANT|ACTIVE ij> commit;ij> select a from a where a > 3;A -----------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 |(1,10) |GRANT|ACTIVE APP |UserTran|ROW |1 |S |A |(1,9) |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Do range scans that all return 0 row from covered index.--------------------------------------------------------------------------------select a from a where a < 1;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 APP |UserTran|ROW |1 |S |A |(1,3) |GRANT|ACTIVE ij> commit;ij> select a from a where a > 4;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 APP |UserTran|ROW |1 |S |A |(1,10) |GRANT|ACTIVE ij> commit;ij> select a from a where a > 2 and a < 3;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 APP |UserTran|ROW |1 |S |A |(1,8) |GRANT|ACTIVE ij> commit;ij> ---------------------------------------------------------------------------------- Verify that create index does table level locking--------------------------------------------------------------------------------drop table a;0 rows inserted/updated/deletedij> create table a (a int, b char(200));0 rows inserted/updated/deletedij> create table b (a int, b char(200));0 rows inserted/updated/deletedij> insert into a values (1, 'a');1 row inserted/updated/deletedij> insert into a values (2, 'b');1 row inserted/updated/deletedij> insert into a values (3, 'c');1 row inserted/updated/deletedij> insert into a values (4, 'd');1 row inserted/updated/deletedij> commit;ij> create unique index a_idx on a (a);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 |4 |S |A |Tablelock |GRANT|ACTIVE APP |UserTran|TABLE |1 |X |A |Tablelock |GRANT|ACTIVE APP |UserTran|TABLE |1 |X |A |Tablelock |GRANT|ACTIVE ij> commit;ij> select a from a;A -----------1 2 3 4 ij> select a from b;A -----------ij> commit;ij> -- clean upautocommit on;ij> drop index a_idx;0 rows inserted/updated/deletedij> drop table a;0 rows inserted/updated/deletedij> drop table b;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -