|
|
oracle enq: TM & enq: TX 分析
作者简介：
----------------------------------------------------
@ 孙显鹏，海天起点oracle技术专家，十年从业经验
@ 精通oracle内部原理，擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题，提高生产效率。
@ 爱好：书法，周易，中医。微信：sunyunyi_sun
@ 易曰：精义入神，以致用也！
@ 调优乃燮理阴阳何其难也！
-----------------------------------------------------
现象：
TOP 等待：
1： 第一位：enq: TM - contention
2： 第三位：enq: TX - row lock contention
查询ASH：
set lines 1200 pages 1200
col evnet for a30
col p1 for a23
col block_sess for a9
select h.session_id,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,h.p2,
blocking_inst_id||blocking_session block_sess,time_waited/100000 wait
from dba_hist_active_sess_history h
where h.sample_time>=to_date('2018-07-04:16:00','yyyy-mm-dd-hh24:mi:ss')
and h.sample_time<=to_date('2018-07-04:16:30','yyyy-mm-dd-hh24:mi:ss')
and h.event like 'enq%'
6524 dw8sgchczzj16 enq: TX - row lock contention name|mode= 458779 16656 0
54580006
---大量的 dw8sgchczzj16 enq: TX - row lock contention --这里不显示了，TX 如何处理后面附件有说明
下面是TM 锁信息：
note： BLOCK_SES 列 16364 前面的1代表实例号，应该写成1-6364，着急忘记标记！
SESSION_ID SQL_ID EVENT P1 P2 BLOCK_SES WAIT
---------- ------------- ---------------------------------------------------------------- ----------------------- ---------- --------- ----------
5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
3641 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
5244 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
5932 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
3641 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0
544D0003
908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0
544D0004
3169 atqmkk25d9kpg enq: TX - contention name|mode= 3670034 13337 0
54580004
5976 7w586m0dhr2cd enq: TX - contention name|mode= 5308448 15896 .10316
54580004
1460 2nububwzrnc9k enq: IV - contention type|mode= 1280262987 .09024
49560005
3906 34n2zhhqmsunb enq: IV - contention type|mode= 1280262987 .01777
49560005
2608 enq: IV - contention type|mode= 1398361667 .01438
49560003
3906 34n2zhhqmsunb enq: IV - contention type|mode= 1280262987 .0097
49560005
2608 enq: IV - contention type|mode= 1398361667 .00714
49560003
425 rows selected.
TM 分析：
相关SQL：
TM-03 ----sx--ROW EXCLUSIVE WAIT
Schema Name: ACCT
SQL ID : 4j9ya06f6f55d
SQL Text : INSERT INTO ACCT_BALANCE ( ACCT_BALANCE_ID, BALANCE_TYPE_ID,
EFF_DATE, EXP_DATE, BALANCE, CYCLE_UPPER, CYCLE_LOWER,
CYCLE_UPPER_TYPE, CYCLE_LOWER_TYPE, BANK_ACCT, STATE,
STATE_DATE, ACCT_ID, SERV_ID, ITEM_GROUP_ID, OBJECT_TYPE_ID
) VALUES ( :lAcctBalanceID, :iBalanceTypeID,
TRUNC(TO_DATE((DECODE(:sEffDate, :"SYS_B_00", NULL,
:"SYS_B_01",NULL, :sEffDate)), :"SYS_B_02")),
TRUNC(TO_DATE((DECODE(:sExpDate, :"SYS_B_03", NULL,
:"SYS_B_04",NULL, :sExpDate)), :"SYS_B_05")), :lBalance,
DECODE(:lCycleUpper, -:"SYS_B_06", NULL, :lCycleUpper),
DECODE(:lCycleLower, -:"SYS_B_07", NULL, :lCycleLower),
DECODE(:sCycleUpperType, :"SYS_B_08", NULL, :sCycleUpperType),
DECODE(:sCycleLowerType, :"SYS_B_09", NULL, :sCycleLowerType),
DECODE(:sBankAcct, :"SYS_B_10", NULL, :sBankAcct), :sState,
TO_DATE(:sStateDate, :"SYS_B_11"), :lAcctID, DECODE(:lServID,
-:"SYS_B_12", NULL, :lServID), DECODE(:iAcctItemGroupID,
-:"SYS_B_13", NULL, :lAcctItemGroupID), :lObjectTypeID )
c6qn2qxs46qfz tm-04-S -请求S mode，等待，阻塞者 6364
-------------------------------------------------------------------------------
Schema Name: ACCT
SQL ID : c6qn2qxs46qfz
SQL Text : DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID
=:ACCT_BALANCE_ID
Bind Variables :
1 - (NUMBER):46471271
对象分析：
desc ACCT.ACCT_BALANCE
CREATE TABLE "ACCT"."ACCT_BALANCE"
( "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,
"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
"EFF_DATE" DATE,
"EXP_DATE" DATE,
"BALANCE" NUMBER(16,5) NOT NULL ENABLE,
"CYCLE_UPPER" NUMBER(16,5),
"CYCLE_LOWER" NUMBER(16,5),
"CYCLE_UPPER_TYPE" VARCHAR2(3),
"CYCLE_LOWER_TYPE" VARCHAR2(3),
"BANK_ACCT" VARCHAR2(30),
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ITEM_GROUP_ID" NUMBER(9,0),
"OBJECT_TYPE_ID" NUMBER(5,0),
"UPDATE_DATE" DATE,
CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")
USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ACCT_INDEX01" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ACCT_DATA01"
PARTITION BY HASH ("ACCT_ID")
(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE
TABLESPACE "ACCT_DATA01"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE
TABLESPACE "ACCT_DATA02"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE
TABLESPACE "ACCT_DATA03"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE
TABLESPACE "ACCT_DATA04"
NOCOMPRESS )
疑惑不解：
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID =:ACCT_BALANCE_ID
这个DELETE 操作怎么请求TM-4-Smode 锁呢？
一般DML操作请求TM-SX锁，SX和SX是兼容的，SX和S是不兼容的，这种情况是外键没有索引的情况下，DELETE主表会给子表添加TM-S锁，
由INSRRT 语句P2和delete语句P2相同，那么DELETE操作就直接请求该表的TM-S锁，这个P2=364840是什么对象？存在外键无索引？
今天让客户查询了对象364840：
sys@ACCTDB>select object_name,subobject_name from dba_objects where object_id=364840;
OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------
BALANCE_SHARE_RULE
经查BALANCE_SHARE_RULE 是ACCT_BALANCE 表的子表。--好吧还是外键无索引导致！！
在自己本机测试：
CREATE TABLE "WOLF"."ACCT_BALANCE"
( "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,
"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
"EFF_DATE" DATE,
"EXP_DATE" DATE,
"BALANCE" NUMBER(16,5) NOT NULL ENABLE,
"CYCLE_UPPER" NUMBER(16,5),
"CYCLE_LOWER" NUMBER(16,5),
"CYCLE_UPPER_TYPE" VARCHAR2(3),
"CYCLE_LOWER_TYPE" VARCHAR2(3),
"BANK_ACCT" VARCHAR2(30),
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ITEM_GROUP_ID" NUMBER(9,0),
"OBJECT_TYPE_ID" NUMBER(5,0),
"UPDATE_DATE" DATE,
CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")
USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST"
PARTITION BY HASH ("ACCT_ID")
(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE
TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE
TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE
TABLESPACE "TEST"
NOCOMPRESS ,
PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE
TABLESPACE "TEST"
NOCOMPRESS )
INSERT INTO ACCT_BALANCE values (46471271,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
commit;
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271
/
SQL> select sid,type,id1,id2,lmode,request from v$lock where type='TM';
SID TYPE ID1 ID2 LMODE REQUEST
------- -------- ---------- ---------- ---------- ----------
25 TM 86358 0 3 0
25 TM 86360 0 3 0 --TM-SX 3 级锁
添加一个外键表，且无索引：
CREATE TABLE "WOLF"."ACCT_BALANCE_REF"
( "ACCT_BALANCE_ID" NUMBER(9,0) NULL references ACCT_BALANCE(ACCT_BALANCE_ID),
"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
"EFF_DATE" DATE,
"EXP_DATE" DATE,
"BALANCE" NUMBER(16,5) NOT NULL ENABLE,
"CYCLE_UPPER" NUMBER(16,5),
"CYCLE_LOWER" NUMBER(16,5),
"CYCLE_UPPER_TYPE" VARCHAR2(3),
"CYCLE_LOWER_TYPE" VARCHAR2(3),
"BANK_ACCT" VARCHAR2(30),
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ITEM_GROUP_ID" NUMBER(9,0),
"OBJECT_TYPE_ID" NUMBER(5,0),
"UPDATE_DATE" DATE )
TABLESPACE "TEST"
/
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271
ERROR at line 1:
ORA-02292: integrity constraint (WOLF.SYS_C0013150) violated - child record found
这种情况不不允许的，因为外键存在！
先删除外键，再删除主键：
session 1:
DELETE FROM ACCT_BALANCE_REF WHERE ACCT_BALANCE_ID=46471271
/
session 2:
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271
/
SID TYPE ID1 ID2 LMODE REQUEST
------- -------- ---------- ---------- ---------- ----------
1 TM 86358 0 2 0
25 TM 86358 0 3 0
1 TM 86365 0 3 0
25 TM 86365 0 0 4 ---session 2 请求TM-S 4级锁
col object_name for a20
col subobject_name for a20
select l.sid,l.type,o.object_name,l.id1,l.id2,lmode,l.request from v$lock l,dba_objects o where l.id1=o.object_id and l.type='TM';
SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
1 TM ACCT_BALANCE 86358 0 2 0
25 TM ACCT_BALANCE 86358 0 3 0
1 TM ACCT_BALANCE_REF 86365 0 3 0
25 TM ACCT_BALANCE_REF 86365 0 0 4
删除主表的时候需要给子表添加4级锁，此时子表由于delete已经持有3级锁，4级和3级锁不兼容
USERNAME SID SQL_ID SQL_TEXT EVENT MODULE STATE
------------ ------ --------------- ---------------------------- ---------------------------- ---------- ------------
SYS 67 517qpp3b76vrc select /*+ all_rows*/ SQL*Net message to client sqlplus@se WAITED SHORT
WOLF 25 5q8rsb0an7j4t DELETE FROM ACCT_BALANCE WHE enq: TM - contention SQL*Plus WAITING
等待事件：
enq: TM - contention
set lines 1200 pages 1200
col evnet for a30
col p1 for a23
col block_sess for a9
select h.sid,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,
BLOCKING_INSTANCE||blocking_session block_sess,SECONDS_IN_WAIT
from v$session h
where h.event like 'enq%'
SID SQL_ID EVENT P1 BLOCK_SES SECONDS_IN_WAIT
---------- --------------------------------------- -------------------- ------------------------------ --------- ---------------
25 5q8rsb0an7j4t enq: TM - contention name|mode= 11 837
544D0004
删除没有记录呢？
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=0
SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
25 TM ACCT_BALANCE 86358 0 3 0
删除正在插入的数据呢？
sess1:
INSERT INTO ACCT_BALANCE values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);
INSERT INTO ACCT_BALANCE_REF values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);
sess2:
DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471273
SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
1 TM ACCT_BALANCE 86360 0 3 0
1 TM ACCT_BALANCE 86358 0 3 0
25 TM ACCT_BALANCE 86358 0 3 0
1 TM ACCT_BALANCE_REF 86365 0 3 0
25 TM ACCT_BALANCE_REF 86365 0 0 4 -- 一样的
关于TM-S四级锁就明白了。
还有一个问题，为什么insert 主表给子表却添加了TM-SX 3级锁，应该TM 2才对呀？ 如上面ASH信息，
那么只有一种可能就是现在主表插入，继而在子表插入锁从TM2级升级为TM3级：
测试如下：
session 1：
SQL> INSERT INTO ACCT_BALANCE values (46471274,46471272,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
2 /
1 row created.
SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
24 TM ACCT_BALANCE 86360 0 3 0
24 TM ACCT_BALANCE 86358 0 3 0
24 TM ACCT_BALANCE_REF 86365 0 2 0 --子表持有TM 2级锁
session 2：
SQL> INSERT INTO ACCT_BALANCE_REF values (46471274,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)
2
SQL> /
1 row created.
SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST
------- -------- -------------------- ---------- ---------- ---------- ----------
24 TM ACCT_BALANCE 86360 0 3 0
24 TM ACCT_BALANCE 86358 0 3 0
24 TM ACCT_BALANCE_REF 86365 0 3 0 ---子表持有TM 3级锁
到此问题全部搞明白！
处理方案：
1：dump 进程6364，分析6364为什么阻塞了后面的进程，由于事故发生后处理没有得到dump信息
2：kill 6364 进程，释放资源
3：给存在外键的子表BALANCE_SHARE_RULE 外键添加索引，添加索引后delete主表会请求子表的TM SX 3级锁，SX和SX是兼容的就不会出现TM 锁等待。
为了防止系统存在外键无索引问题，可执行下面脚本进行检查: （建议执行第一个脚本）
a:执行oracle 提供脚本FKCHLK.SQL可从MOS下载---脚本太长，这里不贴了
b:执行下面脚本：
SET ECHO off
REM NAME: CHKFK.SQL
REM USAGE:"@path/chkfk"
REM --------------------------------------------------------------------------
REM This file checks the ALL users Foreign Keys no index col
REM -------------------------------------------------------------------------
REM Main text of script follows:
set lines 1200 pages 1200
col owner for a30
col table_name for a30
col CONSTRAINT_NAME for a30
col COLUMNS for a30
SELECT OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
CNAME1 ||
NVL2(CNAME2, ',' || CNAME2, NULL) ||
NVL2(CNAME3, ',' || CNAME3, NULL) ||
NVL2(CNAME4, ',' || CNAME4, NULL) ||
NVL2(CNAME5, ',' || CNAME5, NULL) ||
NVL2(CNAME6, ',' || CNAME6, NULL) ||
NVL2(CNAME7, ',' || CNAME7, NULL) ||
NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT B.OWNER,
B.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
COUNT(*) COL_CNT
FROM (SELECT OWNER,
SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
POSITION
FROM DBA_CONS_COLUMNS) A,
DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER=B.OWNER
AND A.OWNER NOT IN ('SYS','SYSTEM','SYSMAN','OLAPSYS','MDSYS','ORDDATA','EXFSYS','SCOTT','DBSNMP')
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
WHERE COL_CNT > ALL
(SELECT COUNT(*)
FROM DBA_IND_COLUMNS I
WHERE I.TABLE_NAME = CONS.TABLE_NAME and I.INDEX_OWNER =CONS.OWNER
AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
CNAME6, CNAME7, CNAME8)
AND I.COLUMN_POSITION <= CONS.COL_CNT
GROUP BY I.INDEX_NAME)
order by 1,2
/
附：
锁的介绍：
1：enq: TM - contention
关于 enq: TM - contention 锁结合下面信息：
This wait indicates time spent waiting for a TM lock. TM locks are used to coordinate activities for many base table / partition operations.
Individual Waits:
Parameters:
P1 = name|mode
P2 = object #
P3 = table/partition
name|mode
The lock name and requested mode are encoded into P1 as "name<<16 | mode". This is best seen in P1RAW (or you can convert P1 to hexadecimal).
For this wait:
name is always the ASCII for "TM" = 0x544d. --结合上面查询ASH信息对照
mode is the mode that the TM lock is being requested in.
TM locks may be requested in several different modes:
"2" = RS = Row Share wait - also known as sub-share table lock (SS)
"3" = RX = Row Exclusive wait - also known as sub-exclusive table lock (SX)
"4" = S = Share mode wait.
"5" = SRX = Share Row Exclusive wait - also known as share-subexclusive table lock (SSX)
"6" = X = eXclusive mode wait
eg: P1RAW of 544D0006 = eXclusive mode TM wait, P1RAW of 544D0004 = Shared mode TM wait.
object # (dictionary object id)
This is the dictionary object id of the object that the TM lock protects.
ie: The dictionary object with OBJECT_ID = P2
P2RAW corresponds to the ID1 part of the TM lock itself.
table/partition
Indicates ID2 of the TM lock being waited on:
"0" = wait for the normal table / partition lock
"1" = wait for the partition-wait lock
This wait thus indicates a wait for the lock "TM-<P2RAW>-<P3RAW>"
2：enq: TX - row lock contention
关于 enq: TX - row lock contention 是由于应用设计问题导致，但是我们需要检查：
1：redo 日志写和切换正常，不要由于redo 写问题导致前台事务时间经历较长时间，加重TX锁
2：enq: TX - row lock contention 等待时间不能太长，太长说明SQL可能需要优化提高SQL执行效率
3：enq: TX - row lock contention 等待时间非常短，但是存在大量enq: TX - row lock contention说明系统事务频繁操作，需要应用控制事务！
|
|
IP卡
狗仔卡
发表于 2018-7-10 16:43
收藏
提升卡
置顶卡
变色卡
显身卡