Oracle blockingblocked)

Blocking is a common situation for DBAs. especially poor application design blocking can cause serious performance degradation until the database crashes. For DBA, it is necessary to know how to locate

Blocking is a common situation for DBAs. especially poor application design blocking can cause serious performance degradation until the database crashes. For DBA, it is necessary to know how to locate

Blocking is a common situation for DBAs. especially poor application design blocking can cause serious performance degradation until the database crashes. For DBA, it is necessary to know how to locate the current system blocking, who is the blocking person, and who is the blocking person. This article provides a description and a demonstration.

1. Blocking and its type

A. What is blocking?

One session holds a lock on a resource, and the other session requests this resource, blocking will occur ). That is to say, the new session will be suspended until the locked session gives up the locked resource. In most cases, when an interactive application is severely blocked, it indicates that the application logic is faulty. This is the root cause of blocking.

B. Blocking type

Five common DML statements in the database may be blocked: INSERT, UPDATE, DELETE, MERGE, and select for update.

2. Solutions to different types of Blocking

A. INSERT blocking is mainly caused by a table with a primary key or a unique constraint on the table. Blocking is triggered when two sessions attempt to INSERT a row with the same value. When multiple tables are linked by reference integrity constraints, insertion of child tables may be blocked when the parent table on which they depend is being created or deleted. We recommend that you use a sequence to generate the primary key/unique column value in this case.

B. FOR UPDATE, DELETE, MERGE, and SELECT FOR UPDATE blocking, As long as any session uses these operations to lock the row, the rest must be in the waiting state. The lock (exclusive lock) on the currently locked row is released. In this case, we recommend that you submit transactions as quickly as possible or submit transactions in batches using SQL.

C. FOR a blocked select for update, the solution is simple: simply add the NOWAIT clause and it will not be blocked.

3. Demonstration Blocking

-- Update the table. Note: The prompt scott @ CNMMBO indicates that the user is scott's session. The user name is different and the 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)

-- Start two other sessions to update the same row. Both sessions will be waiting until the first session is submitted or rolled back.
Leshami @ CNMMBO> update scott. emp set sal = sal + 100 where empno = 7788;

Goex_admin @ CNMMBO & gt; update scott. emp set sal = sal-50 where empno = 7788;

-- Query the blocking condition in the first session.
Scott @ CNMMBO> @ blocker

BLOCK_MSG BLOCK
------------------------------------------------------------
Pts/5 ('1970 2 ') is blocking 1073,464, limit 38 1
Pts/5 ('2014 2 ') is blocking 1065,4464 1
-- The above result indicates that session blocks the next two
-- That is, session is the blocking operator, and the last two sessions are the blocking operator.

-- Author: Leshami
-- Blog:

-- The following queries the id of the blocked session, the SQL statement, and the blocked time.
Scott @ CNMMBO> @ blocking_session_detail. SQL

'Sid = '|. SID | 'waitclass = '|. WAIT_CLASS | 'time = '|. SECONDS_IN_WAIT | CHR (10) | 'query = '| B. SQL _TEXT
Bytes -------------------------------------------------------------------------------------------------------
Sid = 1067 Wait Class = Application Time = 5995
Query = update scott. emp set sal = sal + 100 where empno = 7788

Sid = 1065 Wait Class = Application Time = 225
Query = update scott. emp set sal = sal-50 where empno = 7788

-- Lock hold in the following query for blocking
Scott @ CNMMBO> @ request_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
-- We can see that two users, LESHAMI and GOEX_ADMIN, are applying the Exclusive lock on request 524319/27412, and SCOTT has applied the Exclusive lock.

-- Query the lock hold details during Blocking
Scott @ CNMMBO> @ request_lock_detail

Sid username osuser 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

Download the related script:

Free on

Both the user name and password are

The specific download directory is in/July 6,/July 18,/Oracle blocking (blocking blocked)

For the download method, see

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------