Recently I was working on one row locking contention where the a particular row was locked in exclusive mode caused all subsequent sessions trying to modify that row went in to waiting mode and were were waiting on ‘enq: TX row lock contention‘ wait event. This was a classic pessimistic row locking scenario which was happening due to application design problem.
The situation is not new for most of the DBAs, and they know what is causing the block, the relationship of parent and child blockers, the blocking and block SIDs, lock modes etc., but things are sometimes difficult for non-DBA users who don’t know where to go, what to call and what to check where there is locking in the database stopping their program to finish and they are scratching their head.
I have tried to write a Python script which connects with the Oracle Database using cx_Oracle module using connection details and start executing blocking specific SQL statements embedded inside the python code. I have even tried to add exception handling for the cases when there is any syntax errors or any grammar issues. Tried to add color coding too with result seperators to make the output easy to read. The embedd code makes it self-reliant and complete and makes it very easy to run on any system.
We only have to make sure that we have Python, PIP and Python Module cx_Oracle installed on the server/host.
import cx_Oracle
# Database connection details
db_username = "system"
db_password = "xxxx"
db_host = "hostname-fqdn"
db_port = "1521"
db_service = "xxxx"
bold_start = "\033[1m"
color_green = "\033[32m"
reset_format = "\033[0m"
def print_colored(text, color_code):
colored_text = "{}{}{}".format(color_code, text, reset_format)
print(colored_text)
# Define the SQL statements
sql_statements = [
'''
SELECT rpad(instance_name, 17) current_instance, status, STARTUP_TIME, HOST_NAME, version, DATABASE_STATUS FROM v$instance
''',
"""
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
""",
"""
SELECT
OUTPUT || CHR(10) || RPAD('-', LENGTH(OUTPUT) - LENGTH(REPLACE(OUTPUT, CHR(10), '')), '-') AS OUTPUT
FROM (
SELECT
'INST_ID --> '||x.INST_ID || CHR(10) ||
'Serial ID --> '||x.sid || CHR(10) ||
'Serial Num --> '||x.serial# || CHR(10) ||
'User Name --> '||x.username || CHR(10) ||
'Session Status --> '||x.status || CHR(10) ||
'Program --> '||x.program || CHR(10) ||
'Module --> '||x.Module || CHR(10) ||
'Action --> '||x.action || CHR(10) ||
'Machine --> '||x.machine || CHR(10) ||
'OS_USER --> '||x.OSUSER || CHR(10) ||
'Process --> '||x.process || CHR(10) ||
'State --> '||x.State || CHR(10) ||
'EVENT --> '||x.event || CHR(10) ||
'SECONDS_IN_WAIT --> '||x.SECONDS_IN_WAIT || CHR(10) ||
'LAST_CALL_ET --> '||x.LAST_CALL_ET || CHR(10) ||
'SQL_PROFILE --> '||SQL_PROFILE || CHR(10) ||
'ROWS_PROCESSED --> '||ROWS_PROCESSED || CHR(10) ||
'BLOCKING_SESSION_STATUS --> '||BLOCKING_SESSION_STATUS || CHR(10) ||
'BLOCKING_INSTANCE --> '||BLOCKING_INSTANCE || CHR(10) ||
'BLOCKING_SESSION --> '||BLOCKING_SESSION || CHR(10) ||
'FINAL_BLOCKING_SESSION_STATUS --> '||FINAL_BLOCKING_SESSION_STATUS || CHR(10) ||
'SQL_ID --> '||x.sql_id || CHR(10) ||
'SQL_TEXT --> '||SQL_TEXT || CHR(10) ||
'Logon Time --> '||TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24MISS') || CHR(10) ||
'RunTime --> '||ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) || CHR(10) AS OUTPUT,
x.LAST_CALL_ET AS RUNT
FROM gv$sqlarea sqlarea
,gv$session x
WHERE x.sql_hash_value = sqlarea.hash_value
AND x.sql_address = sqlarea.address
AND x.status='ACTIVE'
AND x.event like '%row lock contention%'
AND SQL_TEXT not like '%SELECT OUTPUT || CHR(10)%'
AND x.USERNAME IS NOT NULL
AND x.SQL_ADDRESS = sqlarea.ADDRESS
AND x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
)
ORDER BY RUNT DESC
""",
"""
with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
select lpad(' ',2*(level-1))||waiter lock_tree from
(select * from lk
union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root'
""",
"""
SELECT DECODE (l.BLOCK, 0, 'Waiting', 'Blocking ->') user_status
,CHR (39) || s.SID || ',' || 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.inst_id
,s.osuser
,s.machine
,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD', 'Dictionary'
,'TM', 'DML','TS', 'Temp Segments','TX', 'Transaction'
,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type
,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.'
,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'
,LTRIM (TO_CHAR (lmode, '990'))) lock_mode
,ctime
,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
""",
"""
select l1.sid, ' IS BLOCKING ', l2.sid
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
""",
"""
select s2.inst_id,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 s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id
""",
"""
SELECT sid,
TYPE,
DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER,
decode(LMODE,1,' ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') lmode,
decode(REQUEST,1,' ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') request,
TRUNC(CTIME/60) MIN ,
ID1,
ID2,
block
FROM gv$lock
where request > 0 OR block =1
""",
"""
select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn,
v$lock m ,
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS
and t.HASH_VALUE = sn.SQL_HASH_VALUE
and ((sn.SID = m.SID and m.REQUEST != 0)
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
(select s.ID1, s.ID2
from gv$lock S
where REQUEST != 0
and s.ID1 = m.ID1
and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE
"""
]
banner = """
=========================================================
Locking Stats Report
Author: Prashant Dixit
Version : 1.0
Date : 2023-August-11
========================================================
"""
print_colored(banner, color_green)
try:
# Establishing a database connection
dsn = cx_Oracle.makedsn(db_host, db_port, service_name=db_service)
connection = cx_Oracle.connect(user=db_username, password=db_password, dsn=dsn)
# Executing each SQL statement
cursor = connection.cursor()
for idx, statement in enumerate(sql_statements):
statement = statement.strip()
if statement:
try:
cursor.execute(statement)
if statement.upper().startswith("SELECT"):
result = cursor.fetchall()
if result:
column_names = [desc[0] for desc in cursor.description]
print_colored("Column Headers: " + ", ".join(column_names), color_green)
print("Results:")
for row in result:
print("Row:")
for col_name, col_value in zip(column_names, row):
print("{}: {}".format(col_name, col_value))
print("\n")
else:
print("No results.")
# Add a newline after the output of the first two SQL statements
#if idx == 1:
# print("\n")
#print("\n" * 1) # Add a gap of one lines
except Exception as e:
print("Error executing statement:", statement)
print("Error details:", str(e))
connection.commit()
print("SQL statements execution completed.")
except Exception as e:
connection.rollback()
print("An error occurred:", str(e))
finally:
if connection:
connection.close()
…..
The output will look like this.
[oracle@fatdbatest ~]$ python locking.py
=========================================================
Locking Stats Report
Author: Prashant Dixit
Version : 1.0
Date : 2023-August-11
========================================================
Column Headers: CURRENT_INSTANCE, STATUS, STARTUP_TIME, HOST_NAME, VERSION, BLOCKED, DATABASE_STATUS
Results:
Row:
CURRENT_INSTANCE: fatdba
STATUS: OPEN
STARTUP_TIME: 2023-06-03 19:42:57
HOST_NAME: fatdba.com
VERSION: 19.0.0.0.0
BLOCKED: NO
DATABASE_STATUS: ACTIVE
Column Headers: OUTPUT
Results:
Row:
OUTPUT: INST_ID --> 1
Serial ID --> 5065
Serial Num --> 17982
User Name --> SYS
Session Status --> ACTIVE
Program --> sqlplus@fatdba.com (TNS V1-V3)
Module --> sqlplus@fatdba.com (TNS V1-V3)
Action -->
Machine --> fatdba.com
OS_USER --> oracle
Process --> 6873
State --> WAITING
EVENT --> enq: TX - row lock contention
SECONDS_IN_WAIT --> 3
LAST_CALL_ET --> 4
SQL_PROFILE -->
ROWS_PROCESSED --> 0
BLOCKING_SESSION_STATUS --> VALID
BLOCKING_INSTANCE --> 1
BLOCKING_SESSION --> 5062
FINAL_BLOCKING_SESSION_STATUS --> VALID
SQL_ID --> vdksq7js9b0cp
SQL_TEXT --> update locking set id=100
Logon Time --> 08-11-2023 215112
RunTime --> 00:00:04
-------------------------
Column Headers: USER_STATUS, SID_SERIAL, CONN_INSTANCE, SID, PROGRAM, INST_ID, OSUSER, MACHINE, LOCK_TYPE, LOCK_MODE, CTIME, OBJECT_NAME
Results:
Row:
USER_STATUS: Blocking ->
SID_SERIAL: '5062,50729'
CONN_INSTANCE: fatdba
SID: 5062
PROGRAM: sqlplus@fatdba.com (TNS V1-V3)
INST_ID: 1
OSUSER: oracle
MACHINE: fatdba.com
LOCK_TYPE: Transaction
LOCK_MODE: Exclusive
CTIME: 9241
OBJECT_NAME: LOCKING
Row:
USER_STATUS: Waiting
SID_SERIAL: '5065,17982'
CONN_INSTANCE: fatdba
SID: 5065
PROGRAM: sqlplus@fatdba.com (TNS V1-V3)
INST_ID: 1
OSUSER: oracle
MACHINE: fatdba.com
LOCK_TYPE: Transaction
LOCK_MODE: None
CTIME: 5
OBJECT_NAME: LOCKING
Column Headers: SID, 'ISBLOCKING', SID
Results:
Row:
SID: 5062
'ISBLOCKING': IS BLOCKING
SID: 5065
Column Headers: INST_ID, BLOCKING_STATUS
Results:
Row:
INST_ID: 1
BLOCKING_STATUS: SYS@fatdba.com ( SID=5062 ) is blocking SYS@fatdba.com ( SID=5065 )
Column Headers: SID, TYPE, BLOCKER, WAITER, LMODE, REQUEST, MIN, ID1, ID2, BLOCK
Results:
Row:
SID: 5062
TYPE: TX
BLOCKER: YES
WAITER: NO
LMODE: X
REQUEST: NONE
MIN: 154
ID1: 327688
ID2: 5988830
BLOCK: 1
Row:
SID: 5065
TYPE: TX
BLOCKER: NO
WAITER: YES
LMODE: NONE
REQUEST: X
MIN: 0
ID1: 327688
ID2: 5988830
BLOCK: 0
Column Headers: USERNAME, SID, SERIAL#, TYPE, LOCK_TYPE, LOCK_REQUESTED, ID1, ID2, SQL_TEXT
Results:
Row:
USERNAME: SYS
SID: 5065
SERIAL#: 17982
TYPE: TX
LOCK_TYPE: None
LOCK_REQUESTED: Exclusive
ID1: 327688
ID2: 5988830
SQL_TEXT: update locking set id=100
Tales From A Lazy Fat DBA 