We often encounter customers reporting application is hung/
not responding. Upon checking most of the time we observe blocking sessions. Deadlocks
occur when two or more sessions block each other while waiting for resources,
causing a standstill in database operations. This solution automates the
detection and resolution of such conflicts, significantly reducing downtime and
ensuring seamless database performance.
Database deadlocks and prolonged locks can cause performance degradation, especially when critical tables are involved. To address this, I’ve implemented a DBMS_SCHEDULER job that automatically identifies and terminates sessions causing contention on specific tables. Here's how I achieved this:
1. Creating the Procedure
The first step is to define a PL/SQL procedure that
identifies and kills sessions based on specific criteria, such as duration of
the lock, session status, and the affected tables. This procedure will check
for locked session for more than 2 mins.
|
CREATE OR REPLACE PROCEDURE kill_blocked_sessions AS BEGIN FOR rec IN ( SELECT
s.sid,
s.serial#,
ROUND((SYSDATE - s.logon_time) * 24 * 60) AS logon_time_minutes,
o.object_name FROM v$session s JOIN v$session_wait w ON
s.sid = w.sid JOIN v$locked_object l ON
s.sid = l.session_id JOIN dba_objects o ON
l.object_id = o.object_id WHERE w.event IN ('enq: TX - row lock contention',
'enq: TM - contention') AND s.status = 'ACTIVE' AND (SYSDATE - s.logon_time) * 24 * 60 >= 2
-- Lock duration exceeds 2 minutes -- AND
o.object_name IN ('SESSION_LOG_INFO', 'USER_LOG_INFO') -- AND o.owner = 'TESTUSER' ) LOOP -- Kill
the session EXECUTE
IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || '''
IMMEDIATE';
DBMS_OUTPUT.PUT_LINE('Killed session: SID=' || rec.sid || ', Serial#='
|| rec.serial# || ', Table=' || rec.object_name); END LOOP; END; / |
This procedure:
- Targets
sessions holding locks on specific tables (SESSION_LOG_INFO, USER_LOG_INFO
etc.).
- Filters
for sessions with a lock duration exceeding 2 minutes and an active
status.
- Terminates these sessions using the ALTER SYSTEM KILL SESSION command.
2. Scheduling the Job
To ensure this procedure runs periodically, I created a DBMS_SCHEDULER
job that executes it every minute. The job definition is as follows:
|
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'AUTO_KILL_BLOCKED_SESSIONS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
kill_blocked_sessions; END;',
start_date =>
SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', -- Runs every 1
minute
enabled => TRUE,
comments => 'Automatically
kills blocked sessions on specific tables.' ); END; / |
This setup ensures that deadlocks or blocked sessions are
identified and resolved promptly, maintaining smooth database operations.
3. Verifying the Job
Once the job is created, it’s crucial to monitor its status
and execution details to ensure it’s running as expected. The following queries
can help:
Check Job Status:
SELECT job_name, state, last_start_date, last_run_duration
FROM
dba_scheduler_jobs
WHERE job_name = 'AUTO_KILL_BLOCKED_SESSIONS';
Check Job Logs:
SELECT LOG_ID, LOG_DATE, OWNER, JOB_NAME, STATUS,
REQ_START_DATE, ACTUAL_START_DATE, OUTPUT
FROM
dba_scheduler_job_run_details
WHERE job_name = 'AUTO_KILL_BLOCKED_SESSIONS';
These queries provide insights into the job's execution
history and whether it successfully resolves blocked sessions.
Conclusion
By leveraging the power of Oracle's DBMS_SCHEDULER, I
automated the resolution of deadlocks and blocked sessions on critical tables.
This approach not only reduces manual intervention but also ensures high
availability and responsiveness of the database. If you're facing similar
challenges, this solution can be adapted to your environment with minimal
changes.
Let me know your thoughts or share your experiences with
handling deadlocks in Oracle databases!
