Oracle blocking (blocked), blocking

Source: Internet
Author: User

Oracle blocking (blocked), blocking

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 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_envSPID sid serial # username program ------------ ----------------- listen 11205 1073 4642 robin oracle @ SZDB (TNS V1-V3) -- start two other sessions to update the same line, both sessions are waiting until the first session is submitted or rolled back to 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; -- The following describes how to query blocking in the first session. scott @ CNMMBO> @ blocker BLOCK_MSG BLOCK blocking ---------- pts/5 ('2017 2 ') is blocking 1073,464, listen 38 1pts/5 ('1970 2') is blocking, 4464 1 -- the above results show that session listen 3, 4642 blocks the next two -- that is, session listen 3, 4642 is the blocking person, the following two sessions are blocked: Author: Leshami -- Blog: Consumer id, SQL statement, and blocked time scott @ CNMMBO >@blocking_session_detail. SQL 'sid = '|. SID | 'waitclass = '|. WAIT_CLASS | 'time = '|. SECONDS_IN_WAIT | CHR (10) | 'query = '| B. SQL _TEXT partition sid = 1067 Wait Class = Application Time = 5995 Query = update scott. emp set sal = sal + 100 where empno = 7788sid = 1065 Wait Class = Application Time = 225 Query = update scott. emp set sal = sal-50 where empno = 7788 -- lock hold in the following query: scott @ CNMMBO> @ request_lock_typeUSERNAME sid lmode request ID1 ID2 certificate ------------------------------------------------------------------------------------ SCOTT 1073 TX Exclusive None 524319 27412 LESHAMI 1067 TX None Exclusive 524319 27412GOEX_ADMIN 1065 TX None Exclusive 524319 27412 -- we can see that LESHAMI, two GOEX_ADMIN users are requesting the Exclusive lock on 524319/27412, at this time, SCOTT has applied the Exclusive Lock. -- the Lock hold details when the query is blocked. scott @ CNMMBO> @ request_lock_detail sid username osuser terminal OBJECT_NAME TY Lock Mode Req_Mode ---------- detail --------------- begin certificate ------------------------------------------------------------------------------------------------------- -------------------- 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 in this article: http://download.csdn.net/detail/robinson_0612/7607033

More references

DML Error Logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % FOUND)

Batch SQL FORALL statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution PLAN


How can I check which sessions in the oracle database are abnormally blocking the system? It usually has three features: (1) the session can block multiple sessions; (2) the session is blocked by multiple sessions; (3) the session is blocked by multiple sessions) session blocking will form a loop (the loop is that the deadlock oracle can be automatically removed) therefore, session-blocking relational tree and all sessionBLOCK blocking relationships in the DB system are composed of several session-blocking relational trees. In this case, abnormal sessions are rooted when a fault occurs) therefore, the process of searching for the session of the exception lock table has two features: (1) the number of sessions blocked by the root layer when the size of the block tree is too large; (2) the average Blocking wait time is too long to find the abnormal session. Method 2: select r. root_sid, s. serial #, r. blocked_num, r. avg_wait_seconds, s. username, s. status, s. event, s. MACHINE, s. PROGRAM, s. SQL _id, s. prev_ SQL _idfrom (select root_sid, avg (seconds_in_wait) as avg_wait_seconds, count (*)-1 as blocked_numfrom (select CONNECT_BY_ROOT sid as root_sid, seconds_in_waitfrom v $ sessionstart with blocking_session is nullconnect by prior sid = blocking_session) group by root_sidhaving count (*)> 1) r, v $ session swhere r. root_sid = s. sidorder by r. blocked_num desc, r. avg_wait_seconds desc; this SQL statement is used to calculate the number of blocked sessions and the average blocking time based on the blocking_session field of the v $ session field. sessions are usually abnormal before sorting and ranking.
How can I check which sessions in the oracle database are abnormally blocking the system? The Oracle Database O & M process may sometimes encounter exceptions due to incorrect operations or code bugs, causing the session to hold the lock abnormally and release the lock, and a large number of blocking systems. In this case, you need to find out and clear the sessions that cause exceptions.
Oracle session usually has three features:
(1) sessions can block multiple sessions;
(2) sessions are mostly blocked by sessions;
(3) session blocking will form a loop (the loop means that the deadlock oracle can be automatically removed)
Therefore, session-blocking relational tree and all sessionBLOCK blocking relationships in the DB system are composed of several session-blocking relational trees. In this case, abnormal sessions are rooted when a fault occurs) therefore, find the abnormal root in the session process of the exception lock table.
Generally, the root account has two features: (1) the number of sessions blocked by the root account is too large; (2) the average blocking wait time is too long.
Method for searching abnormal sessions:
OEM-> performance-> Blocking Sessions
Method 2:
Select r. root_sid, s. serial #,
R. blocked_num, r. avg_wait_seconds,
S. username, s. status, s. event, s. MACHINE,
S. PROGRAM, s. SQL _id, s. prev_ SQL _id
From (select root_sid, avg (seconds_in_wait) as avg_wait_seconds,
Count (*)-1 as blocked_num
From (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
From v $ session
Start with blocking_session is null
Connect by prior sid = blocking_session)
Group by root_sid
Having count (*)> 1) r,
V $ session s
Where r. root_sid = s. sid
Order by r. blocked_num desc, r. avg_wait_seconds desc;
This SQL statement is used to calculate the blocking session count and average blocking time based on the blocking_session field of the v $ session field. Before sorting and ranking, sessions are usually abnormal.
In addition, you need to pay attention to the long lock hold time, or long wait time sessions are all set to cause blocking of the root session

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.