[bbk3205] 第68集 -Chapter 17-Monitoring and Detecting Lock Contention(01)

DML Locks

A DML transaction gets at least two blocks:

• A shared table lock
• An exclusive row lock

A shared table lock主要是阻止DDL语句对当前表进行修改.

An exclusive row lock主要是阻止别的transaction修改同一条记录.

如何查看锁的信息?

有三个重要的动态性能视图:

• v$transaction:记录了当前活动的transaction.

  • SQL> desc v$transaction;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ADDR                                               RAW(8)
     XIDUSN                                             NUMBER
     XIDSLOT                                            NUMBER
     XIDSQN                                             NUMBER
     UBAFIL                                             NUMBER
     UBABLK                                             NUMBER
     UBASQN                                             NUMBER
     UBAREC                                             NUMBER
     STATUS                                             VARCHAR2(16)
     START_TIME                                         VARCHAR2(20)
     START_SCNB                                         NUMBER
     START_SCNW                                         NUMBER
     START_UEXT                                         NUMBER
     START_UBAFIL                                       NUMBER
     START_UBABLK                                       NUMBER
     START_UBASQN                                       NUMBER
     START_UBAREC                                       NUMBER
     SES_ADDR                                           RAW(8)
     FLAG                                               NUMBER
     SPACE                                              VARCHAR2(3)
     RECURSIVE                                          VARCHAR2(3)
     NOUNDO                                             VARCHAR2(3)
     PTX                                                VARCHAR2(3)
     NAME                                               VARCHAR2(256)
     PRV_XIDUSN                                         NUMBER
     PRV_XIDSLT                                         NUMBER
     PRV_XIDSQN                                         NUMBER
     PTX_XIDUSN                                         NUMBER
     PTX_XIDSLT                                         NUMBER
     PTX_XIDSQN                                         NUMBER
     DSCN-B                                             NUMBER
     DSCN-W                                             NUMBER
     USED_UBLK                                          NUMBER
     USED_UREC                                          NUMBER
     LOG_IO                                             NUMBER
     PHY_IO                                             NUMBER
     CR_GET                                             NUMBER
     CR_CHANGE                                          NUMBER
     START_DATE                                         DATE
     DSCN_BASE                                          NUMBER
     DSCN_WRAP                                          NUMBER
     START_SCN                                          NUMBER
     DEPENDENT_SCN                                      NUMBER
     XID                                                RAW(8)
     PRV_XID                                            RAW(8)
     PTX_XID                                            RAW(8)

• v$lock:

  • SQL> desc v$lock;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ADDR                                               RAW(8)
     KADDR                                              RAW(8)
     SID                                                NUMBER
     TYPE                                               VARCHAR2(2)
     ID1                                                NUMBER
     ID2                                                NUMBER
     LMODE                                              NUMBER
     REQUEST                                            NUMBER
     CTIME                                              NUMBER
     BLOCK                                              NUMBER

• v$session:

  • SQL> desc v$session;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SADDR                                              RAW(8)
     SID                                                NUMBER
     SERIAL#                                            NUMBER
     AUDSID                                             NUMBER
     PADDR                                              RAW(8)
     USER#                                              NUMBER
     USERNAME                                           VARCHAR2(30)
     COMMAND                                            NUMBER
     OWNERID                                            NUMBER
     TADDR                                              VARCHAR2(16)
     LOCKWAIT                                           VARCHAR2(16)
     STATUS                                             VARCHAR2(8)
     SERVER                                             VARCHAR2(9)
     SCHEMA#                                            NUMBER
     SCHEMANAME                                         VARCHAR2(30)
     OSUSER                                             VARCHAR2(30)
     PROCESS                                            VARCHAR2(24)
     MACHINE                                            VARCHAR2(64)
     PORT                                               NUMBER
     TERMINAL                                           VARCHAR2(30)
     PROGRAM                                            VARCHAR2(48)
     TYPE                                               VARCHAR2(10)
     SQL_ADDRESS                                        RAW(8)
     SQL_HASH_VALUE                                     NUMBER
     SQL_ID                                             VARCHAR2(13)
     SQL_CHILD_NUMBER                                   NUMBER
     SQL_EXEC_START                                     DATE
     SQL_EXEC_ID                                        NUMBER
     PREV_SQL_ADDR                                      RAW(8)
     PREV_HASH_VALUE                                    NUMBER
     PREV_SQL_ID                                        VARCHAR2(13)
     PREV_CHILD_NUMBER                                  NUMBER
     PREV_EXEC_START                                    DATE
     PREV_EXEC_ID                                       NUMBER
     PLSQL_ENTRY_OBJECT_ID                              NUMBER
     PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
     PLSQL_OBJECT_ID                                    NUMBER
     PLSQL_SUBPROGRAM_ID                                NUMBER
     MODULE                                             VARCHAR2(48)
     MODULE_HASH                                        NUMBER
     ACTION                                             VARCHAR2(32)
     ACTION_HASH                                        NUMBER
     CLIENT_INFO                                        VARCHAR2(64)
     FIXED_TABLE_SEQUENCE                               NUMBER
     ROW_WAIT_OBJ#                                      NUMBER
     ROW_WAIT_FILE#                                     NUMBER
     ROW_WAIT_BLOCK#                                    NUMBER
     ROW_WAIT_ROW#                                      NUMBER
     TOP_LEVEL_CALL#                                    NUMBER
     LOGON_TIME                                         DATE
     LAST_CALL_ET                                       NUMBER
     PDML_ENABLED                                       VARCHAR2(3)
     FAILOVER_TYPE                                      VARCHAR2(13)
     FAILOVER_METHOD                                    VARCHAR2(10)
     FAILED_OVER                                        VARCHAR2(3)
     RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)
     PDML_STATUS                                        VARCHAR2(8)
     PDDL_STATUS                                        VARCHAR2(8)
     PQ_STATUS                                          VARCHAR2(8)
     CURRENT_QUEUE_DURATION                             NUMBER
     CLIENT_IDENTIFIER                                  VARCHAR2(64)
     BLOCKING_SESSION_STATUS                            VARCHAR2(11)
     BLOCKING_INSTANCE                                  NUMBER
     BLOCKING_SESSION                                   NUMBER
     FINAL_BLOCKING_SESSION_STATUS                      VARCHAR2(11)
     FINAL_BLOCKING_INSTANCE                            NUMBER
     FINAL_BLOCKING_SESSION                             NUMBER
     SEQ#                                               NUMBER
     EVENT#                                             NUMBER
     EVENT                                              VARCHAR2(64)
     P1TEXT                                             VARCHAR2(64)
     P1                                                 NUMBER
     P1RAW                                              RAW(8)
     P2TEXT                                             VARCHAR2(64)
     P2                                                 NUMBER
     P2RAW                                              RAW(8)
     P3TEXT                                             VARCHAR2(64)
     P3                                                 NUMBER
     P3RAW                                              RAW(8)
     WAIT_CLASS_ID                                      NUMBER
     WAIT_CLASS#                                        NUMBER
     WAIT_CLASS                                         VARCHAR2(64)
     WAIT_TIME                                          NUMBER
     SECONDS_IN_WAIT                                    NUMBER
     STATE                                              VARCHAR2(19)
     WAIT_TIME_MICRO                                    NUMBER
     TIME_REMAINING_MICRO                               NUMBER
     TIME_SINCE_LAST_WAIT_MICRO                         NUMBER
     SERVICE_NAME                                       VARCHAR2(64)
     SQL_TRACE                                          VARCHAR2(8)
     SQL_TRACE_WAITS                                    VARCHAR2(5)
     SQL_TRACE_BINDS                                    VARCHAR2(5)
     SQL_TRACE_PLAN_STATS                               VARCHAR2(10)
     SESSION_EDITION_ID                                 NUMBER
     CREATOR_ADDR                                       RAW(8)
     CREATOR_SERIAL#                                    NUMBER
     ECID                                               VARCHAR2(64)

     

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        70

Oracle 不会随着锁住的资源越多，占用的资源越多；因为Oracle将锁信息存放在Blokc header里面的.

INITRANS=2,MAXTRANS=255，这两个参数的含义：默认情况下有2个Transaction同时对同一张表进行加锁事务处理，最大255个Transaction对同一张表进行加锁事务处理.自oracle 10g以后，已经废弃MAXTRANS参数，直接默认就是255.

共享锁的目的是阻止别人获得排他性锁，不阻止别人使用共享锁. 

图一（连贯看下面图2）

 

备注:当用户感觉很慢的时候，就可以查看此数据字典中的数据信息，就可以分析出是否是因为锁的原因导致.(REQUEST=6，表示有存在处于等待的事务.)

图二（紧接着看图三）

图三

another demo

首先执行SQL,暂时不要提交.

其次，执行脚本lock_sys.sql

SELECT addr,xidusn FROM V$TRANSACTION;
SELECT sid,type,id1,id2,lmode,request FROM V$LOCK ORDER BY 1,5;

SELECT sid,type,trunc(id1/power(2,16)) rbs,
        bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 req,lmode,request
FROM v$lock
WHERE sid=137;


SELECT xidusn,xidslot,xidsqn FROM V$TRANSACTION;

脚本执行内容:

通过上述demo就可以查看到，v$transaction与v$lock之间的练习.

延伸：

                                 

结论:

综上所述，就可以追根溯源，根据V$TRANSACTION与V$LOCK中的数据，结合到USER_OBJECTS数据字典，就能找到具体的事务发生对象.从而就能判断到是哪个表导致慢或者异常发生等问题.

ID1对于（TX）排他性的行级锁，代表着高16位，低16位，分别对应着V$TRANSACTION中的XIDUSN、XIDSLOT、XIDSQN;

ID1对于（TM）共享的表级锁，代表着锁定了哪一个Object.

DML Locks

A DML transaction gets at least two locks:

• A shared table lock
• An exclusive row lock

注意：如果表中含有materialize view的话，相应的v$lock中也会包含关于materialized log锁信息.

Enqueue Mechanism

The enqueue mechanism keeps track of:

• Users waiting for locks
• The requested lock mode
• The order in which users requested the lock.