Dealing with lock issues in Oracle RAC environnement

When moving a database to RAC, a special care must taken about application locks. The main reasons are the following :

  • on the contrary of when using single instance databases, deadlocks are not immediately detected ; it can last up to 60s before the deadlock is resolved by the global enqueue service ; this can be an eternity on highly loaded databases were the snowball effect can easyly lead to application hang ;
  • when you want to diagnose the lock issue in your database, you’ve got the following problems : first, the dba_blockers view is not RAC aware : if one session is blocked by another session on another instance, it will not tell it to you ; second, the utllockt script provided by Oracle which provides you a beautiful lock tree in RAC needs to create a temporary table and then drops it; this often prevents you to run it in production.

Then here is a query to generate a RAC-aware locking tree that will help you to diagnose lock issues in RAC environnements. It is a transcription of utllockt but removes the create/drop table and performs well. Please note that the « with clause » is necessary to deal effectively with the gv$ views.
WITH
-- global lock view
gl AS (
select
inst_id || '-' || sid instsid, id1, id2,
ctime, lmode, block, request
from
gv$lock
),
-- joins the global lock view on itself to identify locks
l AS (
SELECT
l1.instsid holding_session,
l2.instsid waiting_session
FROM
gl l1,
gl l2
WHERE
l1.block > 0
AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2
),
-- result view (tree of locked sessions)
rs AS (
SELECT
lpad(' ',3*(level-1),' ') || waiting_session running_session
FROM (
-- first insert as in utllockt
(SELECT
'-' holding_session, holding_session waiting_session
FROM
l
MINUS
SELECT
'-', waiting_session
FROM
l
)
UNION ALL
-- second insert as in utllockt
SELECT
holding_session, waiting_session
FROM
l
)
CONNECT BY PRIOR
waiting_session = holding_session
START WITH
holding_session = '-'
),
-- useful session informations
s AS (
SELECT
inst_id, sid, machine, osuser, username,
nvl(sql_id, '-') sql_id, event, wait_class
FROM gv$session
)
-- final tree
SELECT
*
FROM
rs
JOIN
s ON ltrim(rs.running_session)=s.inst_id || '-' || s.sid;

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *