Oracle数据字典之——v$lock 和v$locked_object
v$lock视图
v$lock视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下：
11g如下：
| 字段名称 | 类型 | 说明 |
|---|---|---|
| ADDR | RAW(8) | 锁定状态对象的地址 |
| KADDR | RAW(8) | 锁的地址 |
| SID | NUMBER | 会话（SESSION）标识； |
| TYPE | VARCHAR（2） | 区分该锁保护对象的类型； |
| ID1 | NUMBER | 锁标识1； |
| ID2 | NUMBER | 锁标识2； |
| LMODE | NUMBER | 锁模式：0（None），1（null）,2（row share）, 3（row exclusive）,4 （share）,5（share row exclusive）,6（exclusive） |
| REQUEST | NUMBER | 申请的锁模式：具体值同上面的LMODE |
| CTIME | NUMBER | 已持有或等待锁的时间； |
| BLOCK | NUMBER | 是否阻塞其它锁申请； |
12c如下：
| 字段名称 | 类型 | 说明 |
|---|---|---|
| ADDR | RAW(8) | 锁定状态对象的地址 |
| KADDR | RAW(8) | 锁的地址 |
| SID | NUMBER | 会话（SESSION）标识 |
| TYPE | VARCHAR（2） | 区分该锁保护对象的类型 |
| ID1 | NUMBER | 锁标识1 |
| ID2 | NUMBER | 锁标识2 |
| LMODE | NUMBER | 锁模式：0（None），1（null）,2（row share）, 3（row exclusive）,4 （share）,5（share row exclusive）,6（exclusive） |
| REQUEST | NUMBER | 申请的锁模式：具体值同上面的LMODE |
| CTIME | NUMBER | 已持有或等待锁的时间 |
| BLOCK | NUMBER | 是否阻塞其它锁申请 |
| CON_ID | NUMBER | 所属容器标识 |
英文：
| 字段名称 | 类型 | 说明 |
|---|---|---|
| ADDR | RAW(8) | Address of lock state object |
| KADDR | RAW(8) | Address of lock |
| SID | NUMBER | Identifier for session holding or acquiring the lock |
| TYPE | VARCHAR（2） | Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: TM - DML enqueue TX - Transaction enqueue UL - User supplied |
| ID1 | NUMBER | Lock identifier #1 (depends on type) |
| ID2 | NUMBER | Lock identifier #2 (depends on type) |
| LMODE | NUMBER | 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 | NUMBER | 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) |
| CTIME | NUMBER | Time since current mode was granted |
| BLOCK | NUMBER | Indicates whether the lock in question is blocking other processes. The possible values are: 0 - The lock is not blocking any other processes 1 - The lock is blocking other processes 2 - The lock is not blocking any blocked processes on the local node, but it may or may not be blocking processes on remote nodes. This value is used only in Oracle Real Application Clusters (Oracle RAC) configurations (not in single instance configurations). |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include: 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. 1: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data |
其中在TYPE字段的取值中，本文只关心TM、TX两种DML锁类型；
关于ID1、ID2，TYPE取值不同其含义也有所不同：
| TYPE | ID1 | ID2 |
|---|---|---|
| TM | 被修改表的标识（object_id） | 0 |
| TX | 以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表（Transaction table）中所占用的槽号（slot number，可理解为记录号）。其组成形式为： 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。 | 以十进制数值表示环绕（wrap）次数，即该槽（slot）被重用的次数； |
v$locked_object视图
v$locked_object视图列出当前系统中哪些对象正被锁定，其主要字段说明如下：
11g如下：
| 字段名称 | 类型 | 说明 |
|---|---|---|
| XIDUSN | NUMBER | 回滚段号 |
| XIDSLOT | NUMBER | 槽号 |
| XIDSQN | NUMBER | 序列号 |
| OBJECT_ID | NUMBER | 被锁对象标识 |
| SESSION_ID | NUMBER | 持有锁的会话（SESSION）标识 |
| ORACLE_USERNAME | VARCHAR2（30） | 持有该锁的Oracle用户名 |
| OS_USER_NAME | VARCHAR2（30） | 持有该锁的操作系统用户名 |
| PROCESS | VARCHAR2（24） | 操作系统的进程号 |
| LOCKED_MODE | NUMBER | 锁模式 |
12c如下：
| 字段名称 | 类型 | 说明 |
|---|---|---|
| XIDUSN | NUMBER | 回滚段号 |
| XIDSLOT | NUMBER | 槽号 |
| XIDSQN | NUMBER | 序列号 |
| OBJECT_ID | NUMBER | 被锁对象标识 |
| SESSION_ID | NUMBER | 持有锁的会话（SESSION）标识 |
| ORACLE_USERNAME | VARCHAR2（128） | 持有该锁的Oracle用户名 |
| OS_USER_NAME | VARCHAR2（128） | 持有该锁的操作系统用户名 |
| PROCESS | VARCHAR2（24） | 操作系统的进程号 |
| LOCKED_MODE | NUMBER | 锁模式 |
| CON_ID | NUMBER | 所属容器标识 |
英文：
| Column | Datatype | Description |
|---|---|---|
| XIDUSN | NUMBER | Undo segment number |
| XIDSLOT | NUMBER | Slot number |
| XIDSQN | NUMBER | Sequence number |
| OBJECT_ID | NUMBER | Object ID being locked |
| SESSION_ID | NUMBER | Session ID |
| ORACLE_USERNAME | VARCHAR2（128） | Oracle user name |
| OS_USER_NAME | VARCHAR2（128） | Operating system user name |
| PROCESS | VARCHAR2（24） | Operating system process ID |
| LOCKED_MODE | NUMBER | Lock mode. The numeric values for this column map to these text values for the lock modes for table locks: 0 - NONE: lock requested but not yet obtained 1 - NULL 2 - ROWS_S (SS): Row Share Lock 3 - ROW_X (SX): Row Exclusive Table Lock 4 - SHARE (S): Share Table Lock 5 - S/ROW-X (SSX): Share Row Exclusive Table Lock 6 - Exclusive (X): Exclusive Table Lock See Also: Oracle Database Concepts for more information about lock modes for table locks |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include: 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. 1: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data |
参考链接：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781