Tips: Detecting and Resolving Locking Conflicts

/*Detecting and Resolving Locking Conflicts and Ora-00060 errors [ID 15476.1]
Collected by KhanhND
15/11/2012 */
— To find the blocked process.
select * from v$lock where request!=0;

— where &1 and &2 are the ID for the lock we are waiting on from above.
select * from v$lock where type=’TX’ and id1=’&1′ and id2=’&2;

/* Show DML lock*/
SELECT session_id,
lock_type,
mode_held,
lock_id1
FROM dba_lock l
WHERE lock_type = ‘DML’;
— Show lock user
select inst_id, sid, serial#, username, blocking_session_status, state, service_name,
schemaname, osuser, machine, port, terminal, program, sql_id, module, action, logon_time
from gv$session where sid = ‘1764’;
select distinct
a.sid “waiting sid”
, d.sql_text “waiting SQL”
, a.ROW_WAIT_OBJ# “locked object”
, a.BLOCKING_SESSION “blocking sid”
, c.sql_text “SQL from blocking session”
from v$session a, v$active_session_history b, v$sql c, v$sql d
where a.event=’enq: TX – row lock contention’
and a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#;
— When a session is waiting on a resource, it can be found waiting on the enqueue wait event
–Example:
SELECT * FROM V$SESSION_WAIT WHERE EVENT = ‘enqueue’;

–Show all sessions waiting for any lock:
SELECT event, p1, p2, p3
FROM v$session_wait
WHERE wait_time= 0
AND event = ‘enqueue’;
–From 10g a different more descriptive event name exists for the more frequent enqueues
–and you can query the TX wait event as follows:
SELECT sid, p1raw, p2, p3
FROM v$session_wait
WHERE wait_time = 0
AND event = ‘enq: TX – row lock contention’;
–Show sessions waiting for a TX lock:
SELECT * FROM v$lock WHERE type=’TX’ AND request>0;
–Show sessions holding a TX lock:
SELECT * FROM v$lock WHERE type=’TX’ AND lmode > 0;
–Show which segments have undergone the most row lock waits:
SELECT owner, object_name, subobject_name, value
FROM v$segment_statistics
WHERE statistic_name=’row lock waits’
AND value > 0
ORDER BY 4 DESC;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s