Home Database Mysql Tutorial RAC环境下的阻塞(blockingblocked)

RAC环境下的阻塞(blockingblocked)

Jun 07, 2016 pm 03:58 PM
rac environment block

RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环

RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考:Oracle 阻塞(blocking blocked)

1、演示环境

scott@DEVDB> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--在scott session中发布SQL语句,并未提交
scott@DEVDB> begin
  2  update emp set sal=sal+100 where empno=7788;
  3  update dept set dname=&#39;DBA&#39; where deptno=10;
  4  end;
  5  /

PL/SQL procedure successfully completed.

--在leshami session中更新emp对象
leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788;

--在usr1 session中更新emp对象
usr1@DEVDB> update scott.dept set dname=&#39;DEV&#39; where deptno=10;
Copy after login

2、寻找阻塞

scott@DEVDB> @block_session_rac

USER_STATUS     SID_SERIAL      CONN_INSTANCE     SID PROGRAM                        OSUSER  MACHINE         LOCK_TYPE       LOCK_MODE        CTIME OBJECT_NAME
--------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------
Blocking ->     &#39;20,1545&#39;       devdb1             20 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     Exclusive          666 DEPT
Blocking ->     &#39;20,1545&#39;       devdb1             20 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     Exclusive          666 EMP
Waiting         &#39;49,1007&#39;       devdb1             49 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     None               618 EMP
Waiting         &#39;933,11691&#39;     devdb2            933 sqlplus@Linux-02 (TNS V1-V3)   oracle  Linux-02        Transaction     None               558 DEPT

--通过上述脚本我们可以看到session &#39;20,1545&#39; 锁住了对象DEPT以及EMP,而此时session &#39;49,1007&#39;与&#39;933,11691&#39;处于等待状态。

--下面是另外的一种方式来获取阻塞的情形
scott@DEVDB> @block_session_rac2

BLOCKING_STATUS
----------------------------------------------------------------------------------------------------------------------------
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 )
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 )

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami
Copy after login

3、演示中用到的脚本

[oracle@Linux-01 ~]$ more block_session_rac.sql 
set linesize 180
col user_status format a15
col sid_serial format  a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, &#39;Waiting&#39;, &#39;Blocking ->&#39;) user_status,
         CHR (39) || s.sid || &#39;,&#39; || s.serial# || CHR (39) sid_serial,
         (SELECT instance_name
            FROM gv$instance
           WHERE inst_id = l.inst_id)
            conn_instance,
         s.sid,
         s.program,
         s.osuser,
         s.machine,
         DECODE (l.TYPE,
                 &#39;RT&#39;, &#39;Redo Log Buffer&#39;,
                 &#39;TD&#39;, &#39;Dictionary&#39;,
                 &#39;TM&#39;, &#39;DML&#39;,
                 &#39;TS&#39;, &#39;Temp Segments&#39;,
                 &#39;TX&#39;, &#39;Transaction&#39;,
                 &#39;UL&#39;, &#39;User&#39;,
                 &#39;RW&#39;, &#39;Row Wait&#39;,
                 l.TYPE)
            lock_type--,id1
                     --,id2
         ,
         DECODE (l.lmode,
                 0, &#39;None&#39;,
                 1, &#39;Null&#39;,
                 2, &#39;Row Share&#39;,
                 3, &#39;Row Excl.&#39;,
                 4, &#39;Share&#39;,
                 5, &#39;S/Row Excl.&#39;,
                 6, &#39;Exclusive&#39;,
                 LTRIM (TO_CHAR (lmode, &#39;990&#39;)))
            lock_mode,
         ctime--,DECODE(l.BLOCK, 0, &#39;Not Blocking&#39;, 1, &#39;Blocking&#39;, 2, &#39;Global&#39;) lock_status
         ,
         object_name
    FROM gv$lock l
         JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
         JOIN gv$locked_object o
            ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
         JOIN dba_objects d ON (d.object_id = o.object_id)
   WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                                      FROM gv$lock
                                     WHERE request > 0)
ORDER BY id1, id2, ctime DESC;

[oracle@Linux-01 ~]$ more block_session_rac2.sql 
SELECT DISTINCT
          s1.username
       || &#39;@&#39;
       || s1.machine
       || &#39; ( INST=&#39;
       || s1.inst_id
       || &#39; SID=&#39;
       || s1.sid
       || &#39; Serail#=&#39;
       || s1.serial#
       || &#39; ) IS BLOCKING &#39;
       || s2.username
       || &#39;@&#39;
       || s2.machine
       || &#39; ( INST=&#39;
       || s2.inst_id
       || &#39; SID=&#39;
       || s2.sid
       || &#39; Serial#=&#39;
       || s2.serial#
       || &#39; ) &#39;
          AS blocking_status
  FROM gv$lock l1,
       gv$session s1,
       gv$lock l2,
       gv$session s2
 WHERE     s1.sid = l1.sid
       AND s2.sid = l2.sid
       AND s1.inst_id = l1.inst_id
       AND s2.inst_id = l2.inst_id
       AND l1.block > 0
       AND l2.request > 0
       AND l1.id1 = l2.id1
       AND l1.id2 = l2.id2; 
Copy after login

更多参考

DML Error Logging 特性

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Unable to boot into Windows recovery environment Unable to boot into Windows recovery environment Feb 19, 2024 pm 11:12 PM

Windows Recovery Environment (WinRE) is an environment used to repair Windows operating system errors. After entering WinRE, you can perform system restore, factory reset, uninstall updates, etc. If you are unable to boot into WinRE, this article will guide you through fixes to resolve the issue. Unable to boot into the Windows Recovery Environment If you cannot boot into the Windows Recovery Environment, use the fixes provided below: Check the status of the Windows Recovery Environment Use other methods to enter the Windows Recovery Environment Did you accidentally delete the Windows Recovery Partition? Perform an in-place upgrade or clean installation of Windows below, we have explained all these fixes in detail. 1] Check Wi

What are the differences between Python and Anaconda? What are the differences between Python and Anaconda? Sep 06, 2023 pm 08:37 PM

In this article, we will learn about the differences between Python and Anaconda. What is Python? Python is an open source language that places great emphasis on making the code easy to read and understand by indenting lines and providing whitespace. Python's flexibility and ease of use make it ideal for a variety of applications, including but not limited to scientific computing, artificial intelligence, and data science, as well as creating and developing online applications. When Python is tested, it is immediately translated into machine language because it is an interpreted language. Some languages, such as C++, require compilation to be understood. Proficiency in Python is an important advantage because it is very easy to understand, develop, execute and read. This makes Python

Introduction and core concepts of Oracle RAC Introduction and core concepts of Oracle RAC Mar 07, 2024 am 11:39 AM

Introduction and core concepts of OracleRAC (RealApplicationClusters) As the amount of enterprise data continues to grow and the demand for high availability and high performance becomes increasingly prominent, database cluster technology becomes more and more important. OracleRAC (RealApplicationClusters) is designed to solve this problem. OracleRAC is a high-availability, high-performance cluster database solution launched by Oracle.

What are the PHP integrated environment packages? What are the PHP integrated environment packages? Jul 24, 2023 am 09:36 AM

PHP integrated environment packages include: 1. PhpStorm, a powerful PHP integrated environment; 2. Eclipse, an open source integrated development environment; 3. Visual Studio Code, a lightweight open source code editor; 4. Sublime Text, a A popular text editor, widely used in various programming languages; 5. NetBeans, an integrated development environment developed by the Apache Software Foundation; 6. Zend Studio, an integrated development environment designed for PHP developers.

11 Ways to Set Environment Variables on Windows 3 11 Ways to Set Environment Variables on Windows 3 Sep 15, 2023 pm 12:21 PM

Setting environment variables on Windows 11 can help you customize your system, run scripts, and configure applications. In this guide, we'll discuss three methods along with step-by-step instructions so you can configure your system to your liking. There are three types of environment variables System environment variables – Global variables are the lowest priority and are accessible to all users and applications on Windows and are typically used to define system-wide settings. User Environment Variables – Higher priority, these variables only apply to the current user and process running under that account, and are set by the user or application running under that account. Process environment variables – have the highest priority, they are temporary and apply to the current process and its sub-processes, providing the program

Common problems and solutions for Laravel environment configuration file .env Common problems and solutions for Laravel environment configuration file .env Mar 10, 2024 pm 12:51 PM

Common problems and solutions for Laravel environment configuration file .env When using the Laravel framework to develop projects, the environment configuration file .env is very important. It contains key configuration information of the project, such as database connection information, application keys, etc. However, sometimes there are some common problems when configuring the .env file. This article will introduce these problems and provide solutions, and attach specific code examples for reference. Problem 1: Unable to read the .env file when we have configured the .env file

Understand the implementation methods and advantages of blocking in Go language Understand the implementation methods and advantages of blocking in Go language Mar 24, 2024 am 08:36 AM

Go language is a programming language with very powerful concurrency features. It uses the concept of goroutine to achieve concurrency, and also provides a wealth of tools and methods to deal with blocking. In the Go language, the implementation methods and advantages of blocking are important things we need to understand. This article will introduce the implementation method of blocking in Go language and its advantages, and provide specific code examples to help readers better understand. Implementation methods of blocking In the Go language, blocking can be implemented in a variety of ways, including channels, mutual

A deep dive into the architecture and features of Oracle RAC A deep dive into the architecture and features of Oracle RAC Mar 07, 2024 pm 03:18 PM

OracleRAC (RealApplicationClusters) is a scalability solution provided by Oracle Corporation that allows Oracle database instances to be run on multiple servers and multiple servers are combined together to provide high availability and scalability. The architecture and features of OracleRAC are very unique. This article will deeply explore the architecture, features and specific code examples of OracleRAC. 1. OracleRAC architecture Oracle

See all articles