ADDR Address of lock state object KADDR Address of lock Lock Type System Type BL Buffer hash table instance CF Control file schema global enqueue CI Cross-instance function invocation instance CU Cursor bind DF Data file instance DL Direct loader parallel index create DM Mount/startup db primary/secondary instance DR Distributed recovery process DX Distributed transaction entry FS File set HW Space management operations on a specific segment IN Instance number IR Instance recovery serialization global enqueue IS Instance state IV Library cache invalidation instance JQ Job queue KK Thread kick LA .. LP Library cache lock instance lock (A..P = namespace) MM Mount definition global enqueue MR Media recovery NA..NZ Library cache pin instance (A..Z = namespace) PF Password File PI, PS Parallel operation PR Process startup QA..QZ Row cache instance (A..Z = cache) RT Redo thread global enqueue SC System change number instance SM SMON SN Sequence number instance SQ Sequence number enqueue SS Sort segment ST Space transaction enqueue SV Sequence number value TA Generic enqueue TS Temporary segment enqueue (ID2=0) TS New block allocation enqueue (ID2=1) TT Temporary table enqueue UN User name US Undo segment DDL WL Being-written redo log instance User Type TX --- Transaction TM --- Table Manipulate UL --- PL/SQL USER LOCK lmode(Lock mode) Lock mode in which the session holds the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) request Lock mode in which the process requests the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) Block 0 --- Not blocking any other processes 1 --- This lock blocks other processes 2 --- Global
1、NULL,可以某些情况下,如分布式数据库的查询会产生此锁。
2、SS,表结构共享锁
3、SX,表结构共享锁+被操作的记录的排它锁
4、S, 表结构共享锁+所有记录共享锁
5、SRX 表结构共享锁+所有记录排它锁
6、X 表结构排它锁+所有记录排它锁
以下为补充上面 摘自:http://www.itpub.net/thread-940126-1-1.html
2 SS, 表结构共享+加上部分数据共享.虽然有人说,ORACLE里,数据没有S状态,但我还是愿意这样理解:
第1个S代表表结构共享.
第2个代表表里的数据共享.
你可以想象一下,当往子表里增加纪录时,主表的相关主键是不是得处于共享模式.
3 SX, 用于DML操作,第1个S代表表结构共享,第2个代表表里被操作的数据独占.
4 S, 代表表结构+表里的数据都是处于共享模式.当对表创建索引时,在创建期间,表处于这种模式.
5 SRX=S(4)+SX(3),
6 X, 删除表是会用上. REQUEST NUMBER 同上LMODE 大于0时,表示当前会话被阻塞,其它会话占有改锁的模式 CTIME NUMBER 已持有或者等待锁的时间 BLOCK NUMBER 是否阻塞其他会话锁申请 2:Global 1:阻塞 0:不阻塞
相关视图
模式 | 锁定的SQL | 排斥的模式 | 允许的DML |
2 | lock table t in row share mode; | 6 | select,insert,update,delete,for update |
3 | lock table t in row exclusive mode; | 4,5,6 | select,insert,update, delete,for update |
4 | lock table t in share mode; | 3,5,6 | select |
5 | lock table t in share row exclusive mode; | 3,4,5,6 | select |
6 | lock table t in exclusive mode; | 2,3,4,5,6 | select |