Identifying Locking Issues

Identifying locking issues is instrumental in locating the user who is waiting for someone or something else. You can use this strategy to identify users who are currently being locked in the system. This allows DBAs to ensure whether an Oracle-related process is truly locked or just running slow. You can also identify the current statement that the locked user(s) are currently
executing. The next listing provides an example of identifying locking issues.

select /*+ ordered */ b.username, b.serial#, d.id1, a.sql_text
from v$lock d, v$session b, v$sqltext a
where b.lockwait = d.kaddr
and a.address = b.sql_address
and a.hash_value = b.sql_hash_value;

You also need to identify the user in the system who is causing the problem of locking the previous user, as shown in this listing. (Usually this is the user/developer who presses CTRL-ALT-DEL as you approach his or her desk.)

select /*+ ordered */ a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$lock b, v$session a, v$sqltext c
where b.id1 in
(select /*+ ordered */ distinct e.id1
from v$lock e, v$session d
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

SERIAL# SID USERNAME ID1 SQL_TEXT
------- --- -------- ------ -------------------------------------------
18 11 JOHNSON 393242 update authuser.emp set salary=90000

 

JOHNSON will make everyone happy by forgetting a crucial WHERE clause. Unfortunately,JOHNSON has locked the authorized user of this table.

JOHNSON has locked the authorized user of this table.You can also look at locking in more detail to see exactly what’s running and blocking. In Chapter 9, we look at block-level tuning; there we describe some of these columns and also perform queries to V$TRANSACTION (which shows all DML [update/insert/delete] transactions currently running). In the following listing, we can see four transactions all running at the same time to the same block of information. There is no blocking because the initrans is set to handle (at least set to 4 ITL slots) all four changes within the same block at the same time. If there was a problem, the LMODE would have been 0 and the REQUEST would have been 6 (TX6) as in the third query that follows.

 

Four Users are updating different rows in the same block:
select /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq, lmode, request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid;

USERNAME SID RBS SLOT SEQ LMODE REQUEST
---------- ---------- ---------- ---------- ---------- ---------- ----------
SCOTT 146 6 32 85 6 0
SCOTT 150 4 39 21557 6 0
SCOTT 151 5 34 1510 6 0
SCOTT 161 7 24 44 6 0

select xid, xidusn, xidslot, xidsqn, status, start_scn
from v$transaction
order by start_scn;
XID XIDUSN XIDSLOT XIDSQN STATUS START_SCN
---------------- ---------- ---------- ---------- ---------------- ----------
0600200055000000 6 32 85 ACTIVE 16573480
0400270035540000 4 39 21557 ACTIVE 16573506
05002200E6050000 5 34 1510 ACTIVE 16573545
070018002C000000 7 24 44 ACTIVE 16574420

Three Users are trying to update the exact same row:

select /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq, lmode, request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid;

USERNAME SID RBS SLOT SEQ LMODE REQUEST
---------- ---------- ---------- ---------- ---------- ---------- ----------
SCOTT 146 4 47 21557 0 6
SCOTT 150 4 47 21557 6 0
SCOTT 161 4 47 21557 0 6

select xid, xidusn, xidslot, xidsqn, status, start_scn
from v$transaction
order by start_scn;

XID XIDUSN XIDSLOT XIDSQN STATUS START_SCN
---------------- ---------- ---------- ---------- ---------------- ----------
04002F0035540000 4 47 21557 ACTIVE 16575501

Two Users are blocked:

SELECT sid, blocking_session, username, blocking_session_status
FROM v$session
WHERE username='SCOTT'
ORDER BY blocking_session;

SID BLOCKING_SESSION USERNAME BLOCKING_SESSION_STATUS

---------- ---------------- ---------- -----------------------
146 150 SCOTT VALID
161 150 SCOTT VALID
150 SCOTT NO HOLDER