Oracle阻塞(blockingblocked)实例详解
一、概述：
阻塞是DBA经常碰到的情形，尤其是不良的应用程序设计所造成的阻塞将导致数据库性能的严重下降，直至数据库崩溃。对DBA而言，有必要知道如何定位到当前系统有哪些阻塞，到底谁是阻塞者，谁是被阻塞者。本文对此给出了描述并做了相关演示。
二、演示阻塞：
--更新表，注，提示符scott@CNMMBO表明用户为scott的session，用户名不同，session不同。
scott@CNMMBO>updateempsetsal=sal*1.1whereempno=7788;
1rowupdated.
scott@CNMMBO>@my_env
SPIDSIDSERIAL#USERNAMEPROGRAM
-----------------------------------------------------------------------------------------------
1120510734642robinoracle@SZDB(TNSV1-V3)
--另起两个session更新同样的行，这两个session都会处于等待，直到第一个session提交或回滚
leshami@CNMMBO>updatescott.empsetsal=sal+100whereempno=7788;
goex_admin@CNMMBO>updatescott.empsetsal=sal-50whereempno=7788;
--下面在第一个session查询阻塞情况
scott@CNMMBO>@blocker
BLOCK_MSGBLOCK
------------------------------------------------------------
pts/5('1073,4642')isblocking1067,104381
pts/5('1073,4642')isblocking1065,44641
--上面的结果表明session1073,4642阻塞了后面的2个
--即session1073,4642是阻塞者，后面2个session是被阻塞者
--Author:Leshami
--Blog:http://blog.csdn.net/leshami
--下面查询正在阻塞的sessionid，SQL语句以及被阻塞的时间
scott@CNMMBO>@blocking_session_detail.sql
'SID='||A.SID||'WAITCLASS='||A.WAIT_CLASS||'TIME='||A.SECONDS_IN_WAIT||CHR(10)||'QUERY='||B.SQL_TEXT
------------------------------------------------------------------------
sid=1067WaitClass=ApplicationTime=5995
Query=updatescott.empsetsal=sal+100whereempno=7788
sid=1065WaitClass=ApplicationTime=225
Query=updatescott.empsetsal=sal-50whereempno=7788
--下面的查询阻塞时锁的持有情况
scott@CNMMBO>@request_lock_type
USERNAMESIDTYLMODEREQUESTID1ID2
------------------------------------------------------------------------------------
SCOTT1073TXExclusiveNone52431927412
LESHAMI1067TXNoneExclusive52431927412
GOEX_ADMIN1065TXNoneExclusive52431927412
--可以看到LESHAMI，GOEX_ADMIN2个用户都在请求524319/27412上的Exclusive锁，而此时已经被SCOTT加了Exclusive锁
--查询阻塞时锁的持有详细信息
scott@CNMMBO>@request_lock_detail
SIDUSERNAMEOSUSERTERMINALOBJECT_NAMETYLockModeReq_Mode
---------------------------------------------------------------------------------------------------------------------------
1065GOEX_ADMINrobinpts/1EMPTMRowExcl
1065GOEX_ADMINrobinpts/1Trans-524319TX--Waiting--Exclusive
1067LESHAMIrobinpts/0EMPTMRowExcl
1067LESHAMIrobinpts/0Trans-524319TX--Waiting--Exclusive
1073SCOTTrobinpts/5EMPTMRowExcl
1073SCOTTrobinpts/5Trans-524319TXExclusive
三、文中涉及到的相关SQL脚本完整代码如下：
robin@SZDB:~/dba_scripts/custom/sql>moremy_env.sql
SELECTspid,s.sid,s.serial#,p.username,p.program
FROMv$processp,v$sessions
WHEREp.addr=s.paddr
ANDs.sid=(SELECTsid
FROMv$mystat
WHERErownum=1);
robin@SZDB:~/dba_scripts/custom/sql>moreblocker.sql
colblock_msgformata50;
selectc.terminal||'('''||a.sid||','||c.serial#||''')isblocking'||b.sid||','||d.serial#block_msg,a.block
fromv$locka,v$lockb,v$sessionc,v$sessiond
wherea.id1=b.id1
anda.id2=b.id2
anda.block>0
anda.sid<>b.sid
anda.sid=c.sid
andb.sid=d.SID;
robin@SZDB:~/dba_scripts/custom/sql>moreblocking_session_detail.sql
--Tofindthequeryforblockingsession
--AccessPrivileges:SELECTonv$session,v$sqlarea
SELECT'sid='
||a.SID
||'WaitClass='
||a.wait_class
||'Time='
||a.seconds_in_wait
||CHR(10)
||'Query='
||b.sql_text
FROMv$sessiona,v$sqlareab
WHEREa.blocking_sessionISNOTNULLANDa.sql_address=b.address
ORDERBYa.blocking_session
/
robin@SZDB:~/dba_scripts/custom/sql>morerequest_lock_type.sql
--Thisscriptgeneratesareportofuserswaitingforlocks.
--AccessPrivileges:SELECTonv$session,v$lock
SELECTsn.username,m.sid,m.type,
DECODE(m.lmode,0,'None',
1,'Null',
2,'RowShare',
3,'RowExcl.',
4,'Share',
5,'S/RowExcl.',
6,'Exclusive',
lmode,ltrim(to_char(lmode,'990')))lmode,
DECODE(m.request,0,'None',
1,'Null',
2,'RowShare',
3,'RowExcl.',
4,'Share',
5,'S/RowExcl.',
6,'Exclusive',
request,ltrim(to_char(m.request,
'990')))request,m.id1,m.id2
FROMv$sessionsn,v$lockm
WHERE(sn.sid=m.sidANDm.request!=0)
OR(sn.sid=m.sid
ANDm.request=0ANDlmode!=4
AND(id1,id2)IN(SELECTs.id1,s.id2
FROMv$locks
WHERErequest!=0
ANDs.id1=m.id1
ANDs.id2=m.id2)
)
ORDERBYid1,id2,m.request;
robin@SZDB:~/dba_scripts/custom/sql>morerequest_lock_detail.sql
setlinesize190
colosuserformata15
colusernameformata20wrap
colobject_nameformata20wrap
colterminalformata25wrap
colReq_Modeformata20
selectB.SID,C.USERNAME,C.OSUSER,C.TERMINAL,
DECODE(B.ID2,0,A.OBJECT_NAME,
'Trans-'||to_char(B.ID1))OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'RowShare',
3,'RowExcl',
4,'Share',
5,'ShaRowExc',
6,'Exclusive',
'Other')"LockMode",
DECODE(B.REQUEST,0,'',
1,'Null',
2,'RowShare',
3,'RowExcl',
4,'Share',
5,'ShaRowExc',
6,'Exclusive',
'Other')"Req_Mode"
fromDBA_OBJECTSA,V$LOCKB,V$SESSIONC
whereA.OBJECT_ID(+)=B.ID1
andB.SID=C.SID
andC.USERNAMEisnotnull
orderbyB.SID,B.ID2;