A BLOCKING SESSION
QUESTIONS
What is a waiting session?
How to find a blocking session?
Why my transaction hangs when I try to update a row?
Who blocked my transaction?
SYMPTOMPS
A user complains about his update is executed forever or his update sometimes takes lots of time to complete.
CAUSE
When a session does something with a row (update, delete, insert) a row is locked by the session, therefore no one can do anything with this row until a lock has been removed (commit or rollback).
QUERY
------------------------------------------------------------
-- Victor Khalturin
-- Version 12:37 16.10.2020 (001)
--
-- Display both a blocking and a blocked sessions with rowid of a blocked row.
--
------------------------------------------------------------
SET LINES 300
SET PAGES 999
COL BLOCKING FOR A20
COL WAITING FOR A20
COL ON_OBJECT FOR A15
COL WAITING_SQLID FOR A15
SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || SES1.USERNAME || ')' "BLOCKING",
SES2.SID || ',' || SES2.SERIAL# || '(' || SES2.USERNAME || ')' "WAITING",
SES2.SECONDS_IN_WAIT,
DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#,
SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
SES2.SQL_ID "WAITING_SQLID"
FROM V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO
WHERE SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND
SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;
TESTCASE
A user and a table for this testcase
CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT SELECT ON V_$MYSTAT TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON DBA_OBJECTS TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> SHO USER
USER is "TESTCASE"
CREATE TABLE B_ROW(DIGIT NUMBER, STRING VARCHAR(10));
INSERT INTO B_ROW VALUES (1, 'ONE');
COMMIT;
SQL> SELECT * FROM B_ROW;
DIGIT STRING
---------- ----------
1 ONE
Open 2 more sessions under TESTCASE user
-- Session 1
set sqlprompt "_user'@'SES_1 > "
SELECT DISTINCT SID FROM V$MYSTAT;
-- Session 2
set sqlprompt "_user'@'SES_2 > "
SELECT DISTINCT SID FROM V$MYSTAT;
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_1 > "
TESTCASE@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
10
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_2 > "
TESTCASE@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
625
In the first session start a transaction without committing it
TESTCASE@SES_1 > SELECT * FROM B_ROW;
DIGIT STRING
---------- ----------
1 ONE
TESTCASE@SES_1 > UPDATE B_ROW SET STRING='one' WHERE DIGIT=1;
1 row updated.
TESTCASE@SES_1 > SELECT * FROM B_ROW;
DIGIT STRING
---------- ----------
1 one
Now try to update the same row in the second session. The session hangs until the first session release its lock by issuing a commit or rollback
TESTCASE@SES_2 > SELECT * FROM B_ROW;
DIGIT STRING
---------- ----------
1 ONE
TESTCASE@SES_2 > SET TIMING ON
TESTCASE@SES_2 > UPDATE B_ROW SET STRING='001' WHERE DIGIT=1;
By querying the V$SESSION view it’s easy to identify a blocking session. If a session waits for another session, then it has TX – row lock contention wait event and BLOCKING_SESSION column has SID of a blocking session.
SET LINES 300
SET PAGES 999
COL SID FOR 99999
COL SERIAL# FOR 99999
COL EVENT FOR A30
SELECT SID, SERIAL#, SQL_ID, EVENT, SECONDS_IN_WAIT, BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
SID SERIAL# SQL_ID EVENT SECONDS_IN_WAIT BLOCKING_SESSION
------ ------- ------------- ------------------------------ --------------- ----------------
625 10264 5zprqh5ydng78 enq: TX - row lock contention 43 10
Or you can use V$SESSION_BLOCKERS to find the same data (11g and above)
COL WAIT_EVENT_TEXT FOR A30
SELECT BLOCKER_SID,BLOCKER_SESS_SERIAL#,SID,SESS_SERIAL#,WAIT_EVENT_TEXT FROM V$SESSION_BLOCKERS;
BLOCKER_SID BLOCKER_SESS_SERIAL# SID SESS_SERIAL# WAIT_EVENT_TEXT
----------- -------------------- ------ ------------ ------------------------------
10 12498 625 10264 enq: TX - row lock contention
In this testcase the session with SID 10 is blocking the session with SID 625 for 43 seconds.
To find out what statement is waiting to be executed
SELECT SID, SERIAL#, PREV_SQL_ID, SQL_ID, SQL_CHILD_NUMBER, STATUS FROM V$SESSION WHERE SID IN (10, 625);
SID SERIAL# PREV_SQL_ID SQL_ID SQL_CHILD_NUMBER STATUS
------ ------- ------------- ------------- ---------------- --------
10 12498 fnak0a1mdxbmr INACTIVE
625 10264 fnak0a1mdxbmr 5zprqh5ydng78 0 ACTIVE
SET LONG 200000
SELECT SQL_ID, SQL_FULLTEXT FROM V$SQL WHERE SQL_ID IN ('fnak0a1mdxbmr','5zprqh5ydng78');
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
fnak0a1mdxbmr SELECT * FROM B_ROW
5zprqh5ydng78 UPDATE B_ROW SET STRING='001' WHERE DIGIT=1
For the blocking session, if a transaction issued several statements you will see the latest one. It may or may not contain the blocking statement.
With DBMS_ROWID and V$SESSION it’s easy to find the locked row for the waiting session
COL OBJECT_NAME FOR A30
COL "ROWID" FOR A20
SELECT DO.OBJECT_NAME,
DBMS_ROWID.ROWID_CREATE(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) "ROWID"
FROM V$SESSION SES, DBA_OBJECTS DO
WHERE SES.ROW_WAIT_OBJ#=DO.OBJECT_ID AND SID=625;
OBJECT_NAME ROWID
------------------------------ --------------------
B_ROW AAAS+2AAHAAAACFAAA
SELECT * FROM TESTCASE.B_ROW WHERE ROWID = 'AAAS+2AAHAAAACFAAA';
DIGIT STRING
---------- ----------
1 ONE
To find out all required details about the lock
------------------------------------------------------------
-- Victor Khalturin
-- Version 12:37 16.10.2020 (001)
--
-- Display both a blocking and a blocked sessions with rowid of a blocked row.
--
------------------------------------------------------------
SET LINES 300
SET PAGES 999
COL BLOCKING FOR A20
COL WAITING FOR A20
COL ON_OBJECT FOR A15
COL WAITING_SQLID FOR A15
SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || SES1.USERNAME || ')' "BLOCKING",
SES2.SID || ',' || SES2.SERIAL# || '(' || SES2.USERNAME || ')' "WAITING",
SES2.SECONDS_IN_WAIT,
DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#,
SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
SES2.SQL_ID "WAITING_SQLID"
FROM V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO
WHERE SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND
SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;
BLOCKING WAITING SECONDS_IN_WAIT ON_OBJECT LOCKED_ROWID WAITING_SQLID
-------------------- -------------------- --------------- --------------- ------------------ ---------------
10,12498(TESTCASE) 625,10264(TESTCASE) 43 TESTCASE.B_ROW AAAS+2AAHAAAACFAAA 5zprqh5ydng78
To release the lock the session should complete its work or it can be terminated by ALTER SYSTEM
TESTCASE@SES_1 > COMMIT;
Commit complete.
TESTCASE@SES_2 > SET TIMING ON
TESTCASE@SES_2 > UPDATE B_ROW SET STRING='001' WHERE DIGIT=1;
1 row updated.
Elapsed: 00:00:43.44
alter system kill session 'sid,serial' immediate;
Note : if the session has done lots of work when it’s been terminated all unsaved work will be rollbacked. Rolling back could take lots of time.
RELATED
Mostly it’s impossible to know which statement (sql_id) is holding a lock. If you are lucky it could be a value of prev_sql_id column in v$session of a blocking session. Also, you can try to find a blocking sql_id in the library cache v$sql view by querying the table name and a dml keyword (update/delete) but if the cache is flushed nothing is to be there.
This discussion gives more details about a locking sql_id.
Modified : 12:41 16.10.2020
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (65)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (4)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Tablespaces (1)
- Temporary Tablespace (2)