Oracle blocking (blocking blocked) Introduction and example Demo _oracle

Blocking is a common scenario for DBAs, especially where poor application design blocking can cause severe performance degradation until the database crashes. For DBAs, it is necessary to know how to navigate to what is blocking the current system, who is the blocking, and who is the blocked. This paper gives a description of this and the related demo.

1. Blocking and its type

A, what is blocking
A session holds a lock on a resource, and another session requests the resource, and a blocking (blocking) occurs. That is, the new session is suspended until the locked session is discarded. In most cases, being heavily blocked in an interactive application can indicate a problem with the application logic, which is the root of the blockage.
B. Type of obstruction
There are 5 common DML statements in the database that can be blocked: INSERT, UPDATE, DELETE, MERGE, and select for UPDATE.

2, several different types of blocking treatment methods

A, insert blocking is primarily due to a table with a primary key, or a unique constraint on the table, which causes blocking when two sessions attempt to insert a row with the same value. When multiple tables are linked by referential integrity constraints, the insertion of the child table may block during the parent table on which it is dependent being created or deleted. It is recommended that you use sequences to generate primary key/Unique column values for this type of scenario.
b, for update, DELETE, MERGE, and select for update blocking, the rest must be in the waiting state as long as there are any sessions that use these operations to lock the row. The lock (exclusive lock) is released until the current lock line. For this type of scenario, it is recommended that you commit the transaction as quickly as possible or in bulk SQL.
C, for a blocked select for UPDATE, the solution is simple: simply add the NOWAIT clause and it will not block.

3. Demo Blocking

--Update the table, note, prompt Scott@cnmmbo indicates that the user is Scott's session, user name is different, session is different.

scott@cnmmbo> Update emp set sal=sal*1.1 where empno=7788;

1 row updated. scott@cnmmbo> @my_env SPID SID serial# USERNAME program------------------------------------------------------ -----------------------------------------11205 1073 4642 Robin Oracle@szdb (TNS v1-v3)--Another two sessions update the same line, the two Sess

Ion will be waiting until the first session commits or rolls back the leshami@cnmmbo> update scott.emp set sal=sal+100 where empno=7788;

goex_admin@cnmmbo> Update scott.emp set sal=sal-50 where empno=7788; --below the first session query blocking situation scott@cnmmbo> @blocker block_msg block-------------------------------------------- ----------------PTS/5 (' 1073,4642 ') is blocking 1067,10438 1 pts/5 (' 1073,4642 ') is blocking 1065,4464 1--Results above Indicates that session 1073,4642 blocks the following 2-that is, the session 1073,4642 is a block, and the following 2 sessions are blocked--author:leshami--blog:http://blog.csdn.net/ Leshami-The following query is blocking the session Id,sql statement and the blocked time 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=1067 wait class=application time=5995 query=update scott.emp set sal=sal+100 where empno=7788 sid=1065 wait Clas S=application time=225 query=update scott.emp set sal=sal-50 where empno=7788--The following query holds the lock when it is blocked scott@cnmmbo> @reques T_lock_type USERNAME SID TY lmode REQUEST ID1 ID2--------------------------------------------------------  ----------------------------SCOTT 1073 TX Exclusive None 524319 27412 leshami 1067 TX None Exclusive 524319 27412 goex_admin 1065 TX None Exclusive 524319 27412--you can see Leshami,goex_admin 2 users are on request 524319/27412      Usive lock, which has been added by Scott exclusive lock--details of lock hold for query blocking scott@cnmmbo> @request_lock_detail SID USERNAME osuser TERMINAL object_name TY Lock Mode req_mode---------------------------------------------------------------------------------------------------------------------------10 Goex_admin Robin PTS/1 EMP TM Row excl 1065 goex_admin Robin PTS/1 Trans-524319 TX--waiting- -Exclusive 1067 Leshami Robin pts/0 EMP TM Row excl 1067 leshami Robin pts/0 Trans-524319 T X--waiting--Exclusive 1073 Scott Robin PTS/5 EMP TM Row excl 1073 SCOTT Robin PTS/5 Trans-52 
 4319 TX Exclusive

The relevant scripts involved in the article are as follows:

robin@szdb:~/dba_scripts/custom/sql> more My_env.sql SELECT spid, s.sid, s.serial#, P.username, P.program from V$proc

ESS p, v$session s WHERE p.addr = s.paddr and S.sid = (SELECT sid from V$mystat WHERE rownum = 1); 
robin@szdb:~/dba_scripts/custom/sql> more blocker.sql Col block_msg format A50; Select C.terminal| | ' ('''|| a.sid| | ', ' | | c.serial#| | "") Is Blocking ' | | b.sid| | ', ' | | d.serial# block_msg, a.block from V$lock a,v$lock b,v$session c,v$session D where A.id1=b.id1 and A.id2=b.id2 and a 

. block>0 and A.sid <>b.sid and A.sid=c.sid and B.sid=d.sid; robin@szdb:~/dba_scripts/custom/sql> more Blocking_session_detail.sql--to find the query for blocking session--ACCE SS Privileges:select on V$session, V$sqlarea SELECT ' sid= ' | | A.sid | | ' Wait class= ' | | A.wait_class | | ' Time= ' | | a.seconds_in_wait | | CHR (10) | | ' Query= ' | | B.sql_text from V$session A, V$sqlarea b WHERE a.blocking_session are not NULL and A.sql_Address = b.address ORDER by a.blocking_session/robin@szdb:~/dba_scripts/custom/sql> more Request_lock_type.sql--
This script generates a of the users waiting for locks.  --access privileges:select on V$session, V$lock SELECT sn.username, M.sid, M.type, DECODE (m.lmode, 0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row excl. ', 4, ' Share ', 5, ' s/row excl. ', 6, ' Exclusive ', l  Mode, LTrim (To_char (Lmode, ' 990 ')) Lmode, DECODE (m.request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3,
    ' Row excl. ', 4, ' Share ', 5, ' s/row excl. ', 6, ' Exclusive ', request, LTrim (To_char, ' 990 ')) request, M.ID1, M.id2 from V$session sn, v$lock m WHERE (sn.sid = m.sid and m.request!= 0) OR (sn.sid = M.  Sid and M.request = 0 and Lmode!= 4 and (ID1, Id2) in (SELECT s.id1, s.id2 from V$lock s WHERE request!= 
0 and S.id1 = m.id1 and S.id2 = M.id2) Order by ID1, Id2, m.request; Robin@szdb:~/dba_scripts/custom/sql> more Request_lock_detail.sql set linesize 190 col osuser format A15 col username fo Rmat A20 wrap Col object_name format A20 wrap Col terminal format A25 wrap Col req_mode format A20 select B.sid, C.usernam E, 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, ' Row Share ', 3, ' row
      Excl ', 4, ' Share ', 5, ' Sha Row exc ', 6, ' Exclusive ', ' other ' ', ' Lock Mode ', DECODE (b.request,0, ' ", 1, ' Null ', 2, ' Row Share ', 3, ' Row excl ', 4, ' Share ', 5, ' Sha Row exc ', 6, ' Exclusive ', ' Other '] "Req_mode" from Dba_objects A, V$lock B, V$session C where a.object_id (+) = b.id1 and B.sid = C.sid and C.usern
 AME is isn't null order by B.SID, B.id2;