Oracle blocking (blocking blocked)

Blocking is a common scenario for DBAs, especially poor application design blocking that 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 person and who is being blocked. This paper gives a description of this and makes a related demonstration.

1. Blocking and its type
A, what is blocking
A session holds a lock on a resource, and another session is blocking (blocking) when it requests the resource. This means that the new session will be suspended until the session holding the lock discards the locked resource. In most cases, being severely blocked in an interactive application can indicate a problem with the application logic, which is the source of the blocking.
B. Type of block
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 mainly 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 a child table may be blocked while its dependent parent table is being created or deleted. It is recommended that you use a sequence 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 any session uses these operations to lock the row. Until the lock (exclusive lock) is released on the currently locked line. For such cases, it is recommended to 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. Presentation Blocking

--Update table, note, prompt [email protected] indicates that the user is Scott's session, the user name is different, the session is different. [email protected]> Update emp Set sal=sal*1.1 where Empno=7788;1 row updated. [email protected]> @my_envSPID SID serial# USERNAME program---------------------------- -------------------------------------------------------------------11205 1073 4642 Robin [E Mail protected] (TNS v1-v3)--Another two sessions update the same row, and the two sessions will be waiting until the first session is committed or rolled back [email protected]> Update scott.emp set sal=sal+100 where empno=7788; [email protected]> Update scott.emp set sal=sal-50 where empno=7788;--the first session queries the blocking situation [email  protected]> @blocker block_msg BLOCK------------------------------------- -----------------------PTS/5 (' 1073,4642 ') is blocking 1067,10438 1pts/5 (' 1073,4642 ') is blocking 1065, 4464 the above results indicate that the session 1073,4642 blocks the next 2--that is, the session 1073,4642 is a block, the back2 sessions are blocked--author:leshami--blog:http://blog.csdn.net/leshami--the following query is blocked session Id,sql statement and blocked time [email  protected]> @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=7788sid=1065 wait Class=Applicat Ion time=225 query=update scott.emp set sal=sal-50 where empno=7788--hold the lock when the query is blocked [email protected]> @request _lock_typeusername SID TY lmode REQUEST ID1 ID2----------------------    --------------------------------------------------------------SCOTT 1073 TX Exclusive None 524319 27412LESHAMI 1067 TX None Exclusive 524319 2            7412goex_admin               1065 TX None Exclusive 524319 27412--can see leshami,goex_admin 2 users on request 524319/27412 on Exclusi ve lock, which has been added by Scott exclusive lock--hold details of the lock when the query is blocked [email protected]> @request_lock_detail SID USERNAME O Suser TERMINAL object_name TY Lock Mode req_mode-----------------------------------           ----------------------------------------------------------------------------------------1065 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 TX--waiting--Exclusive 1073 SCOTT Robin PTS/5              Emp    TM Row Excl 1073 SCOTT Robin PTS/5 Trans-524319 TX Exclusive

Related script Download: http://download.csdn.net/detail/robinson_0612/7607033

More references

DML Error Logging Attribute

PL-/-Cursors

PL/SQL-to-implicit cursors (sql%found)

FORALL statements for Bulk SQL

BULK COLLECT clause of bulk SQL

Initialization and assignment of PL/SQL collections

PL/SQL Union arrays and nested tables
PL/SQL variable-length arrays
PL/SQL and PL/SQL Records

SQL Tuning Steps

Efficient SQL statement must kill skill

Parent cursors, child cursors, and shared cursors

Binding variables and their pros and cons

The use of the Display_cursor function of Dbms_xplan

The use of the display function of Dbms_xplan

Description of each of the field modules in the execution plan

Get SQL statement execution plan using EXPLAIN plan