[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.
-
desc v$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:
-
desc 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:
-
desc 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)
-
查看自己的session id;根据sesion id确定是哪一个session
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
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.
