Oracle blocking)

Source: Internet
Author: User

Oracle 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_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:

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

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

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

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.