DBA Hub

📋Steps in this guide1/5

How to find Blocking Sessions

How to find Blocking Sessions Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second session until the first session has done its work. 1. Simulation 2. Finding Out Who’s Holding a Blocking Lock … Continue reading Blocking Sessions →

oracle clusteringintermediate
by OracleDba
14 views
1

Overview

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second session until the first session has done its work. 1. Simulation
2

Section 2

Session 1: 46 Session 2:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[oracle@rac2 ~]$
sqlplus sh/sh;
SQL>
create table t (a varchar2(1));
Table created.

SQL>
insert into t values ('z');
1 row created.

SQL>
commit;
Commit complete.

SQL>
select * from t where a='z' for update;
A
-
z

SQL>
SQL>
select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46
SQL>
3

Section 3

In second session try to update the rows which you have selected above. -- hanging here -- 2. Finding Out Who’s Holding a Blocking Lock

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
In second session try to update the rows which you have selected above.
[oracle@rac2 ~]$
sqlplus sh/sh;
SQL>
select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
39
SQL>
SQL>
update t set a='x' where a='z';
-- hanging here --
..
..

sqlplus / as sysdba
SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' 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 l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;
select sid, serial#, username,status from v$session where sid in ('holder','waiter');
-- OR --
select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;
-- OR --
SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;
-- OR --
SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
-- OR --

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';
SELECT object_name FROM dba_objects WHERE object_id='&object_id_from_above_output';
select sid,type,lmode,request,ctime,block from v$lock;
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;
4

Section 4

Output: 3. Solution Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
SQL> SELECT s1.username || '@' || s1.machine
  2      || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
  3    4    5      WHERE s1.sid=l1.sid AND s2.sid=l2.sid
  6      AND l1.BLOCK=1 AND l2.request > 0
  7      AND l1.id1 = l2.id1
  8      AND l1.id2 = l2.id2;

BLOCKING_STATUS
----------------------------------------------------------------------------------
[email protected] (
SID=46
)
is blocking
[email protected] (
SID=39
)

SQL>
select sid, serial#, username,status from v$session where sid in (46,39);
SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        39         77 SH                   ACTIVE
<-- waiter
46         13 SH                   INACTIVE
<-- Holder
SQL>


SQL>

-- OR --

SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;  2    3    4    5    6

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 46                                           589826       1571          6          0 TX
Waiter: 39                                           589826       1571          0          6 TX

SQL>

-- OR --

SQL> SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;  2    3    4    5    6    7    8    9

BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------
              46         39         77            1626

SQL>

-- OR --

SQL> SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;  2    3    4    5    6    7    8    9

BLOCKING_SESSIONS
----------------------------------------------------------
46 is blocking 39

SQL>

Inform to the holder to commit/rollback.
--- OR ----
kill the holder session, if it is ok
Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)
SQL> alter system kill session '46,13';
System altered.

SQL>
-- After killing holder session, waiter session got completed
SQL> update t set a='x' where a='z';

1 row updated.

SQL>
5

Section 5

Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!