Friday, 28 June 2013
Locking issues
Testing.
User session 1
MANZ SES_1>delete from emp where empno = 1;
1 row deleted.
--- Not commited.
User Session 2
MANZ_SES_2>delete from emp where empno = 1;
--- Session hanged...
DBA Session.
SQL> select a.sid, a.serial#, a.sql_id, a.event, a.p1text, a.p2text, a.p3text, a.p1, a.p2, a.p3, b.sql_text from V$session a, V$sql b where
a.sql_hash_value = b.hash_value and a.sql_address = b.address;
SID SERIAL# SQL_ID EVENT P1TEXT
---------- ---------- ------------- ---------------------------------------------------------------- ----------------------------------------------------------------
P2TEXT P3TEXT P1 P2 P3
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
47 11 1u6pm5aay1kcm enq: TX - row lock contention name|mode usn<<16 | slot sequence 1415053318 524313 2611
delete from emp where empno = 1
-- Show the second session is waiting cause of row lock contention.
As per the document.
id1
The first identifier (id1) of the enqueue or global lock takes its value from P2 or
P2RAW. The meaning of the identifier depends on the name (P1).
id2
The second identifier (id2) of the enqueue or global lock takes its value from P3 or
P3RAW. The meaning of the identifier depends on the name (P1).
select * from dba_lock where session_id = 47;
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------- -------------------------- -------------------- -------------------- -------------------- -------------------- ------------ --------------------
47 Transaction None Exclusive 524313 2611 3382 Not Blocking
47 AE Share None 100 0 13242 Not Blocking
47 DML Row-X (SX) None 76334 0 3382 Not Blocking
47 DML Row-X (SX) None 76335 0 3382 Not Blocking
Blocking session:-
SQL> select * from dba_lock where blocking_others = 'Blocking';
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------- -------------------------- -------------------- -------------------- -------------------- -------------------- ------------ --------------------
49 Transaction Exclusive None 524313 2611 3474 Blocking
SQL> select p1, p2, p3 , p1text, p2text, p3text from v$session where sid = 47;
P1 P2 P3 P1TEXT P2TEXT P3TEXT
---------- ---------- ---------- -------------------- -------------------- --------------------
1415053318 524313 2611 name|mode usn<<16 | slot sequence
-- From the above for the haning session we could see that
SQL> select p1, p1raw from V$session where sid = 47;
P1 P1RAW
---------- ----------------
1415053318 0000000054580006
p1 denotes name|mode ( Type of the lock and lock mode -- 5458 denotes the type and 0006 the mode which is exclusive).
The name or "type" of the enqueue or global lock can be determined by looking at the
two high order bytes of P1 or P1RAW. The name is always two characters. Use the
following SQL statement to retrieve the lock name.
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535) "Lock"
from v$session where sid = 47;
Lock
--------
TX
p2 denotes usn<<16 | slot ( USN is the UNDO SEGMENT NUMBER AND | SLOT IS THE UNDO SLOT) this can be identified using the value of p2
p2 value is 524313
SQL> select trunc(524313/power(2,16)) USN, mod(524313,power(2,16)) SLOT from dual;
USN SLOT
---------- ----------
8 25
Using the above we can find the blocking session.
SQL> select a.sid, a.serial#, a.event, b.xidusn, b.xidslot from V$session a, V$transaction b where a.taddr = b.addr and b.xidusn = 8 and b.xidslot = 25;
SID SERIAL# EVENT XIDUSN XIDSLOT
---------- ---------- ---------------------------------------------------------------- ---------- ----------
49 179 SQL*Net message from client 8 25
SQL> select
(select username from v$session where sid=a.sid) blocker, a.sid,' is blocking ',
(select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------- ------------------------------ ----------
MANZOOR 49 is blocking MANZOOR 47
select 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
and v$session.username = 'MANZOOR'
/
USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MANZOOR 49 8 25 2611 6 0
MANZOOR 47 8 25 2611 0 6
select
(select username from v$session where sid=a.sid) BLOCKER,
a.sid Blocker_SID,
(select serial# from V$session where sid=a.sid) SERIAL#,
' Is Blocking ',
(select username from V$session where sid=b.sid) Blocked_User,
b.sid Blocked_SID
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
BLOCKER BLOCKER_SID SERIAL# 'ISBLOCKING' BLOCKED_USER BLOCKED_SID
------------------------------ ----------- ---------- ------------- ------------------------------ -----------
MANZOOR 49 179 Is Blocking MANZOOR 47
Blocking session using ASH
SQL> select count(*) cnt,
session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
from v$active_session_history ash,
all_objects o
where
event like 'enq: T%'
and o.object_id (+)= ash.current_obj#
group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
order by count(*)
/
CNT SID EVENT LM SQL_ID OBJ TYPE FILE# BLOCK# BSID
---------- ---------- ------------------------------------------------------------------------------------------------------------------------ ---------- ------------- ----------------------------------------------------------------------- ------------------- ---------- ---------- ----------
1552 47 enq: TX - row lock contention 4 5hnbsyk0zh8p6 -1 0 0 49
4386 47 enq: TX - row lock contention 6 1u6pm5aay1kcm 76335 EMP TABLE 5 142 49
SQL> select a.sample_id,a.sample_time,a.session_id,a.event,
a.session_state,a.event,a.sql_id,
a.blocking_session,a.blocking_session_status
from v$active_session_history a, dba_users u
where u.user_id = a.user_id
and u.username = 'MANZOOR';
SAMPLE_ID SAMPLE_TIME SESSION_ID EVENT SESSION
---------- --------------------------------------------------------------------------- ---------- ---------------------------------------------------------------- -------
EVENT SQL_ID BLOCKING_SESSION BLOCKING_SE
---------------------------------------------------------------- ------------- ---------------- -----------
202272 28-JUN-13 10.27.53.538 PM 47 enq: TX - row lock contention WAITING
enq: TX - row lock contention 1u6pm5aay1kcm 49 VALID
202271 28-JUN-13 10.27.52.538 PM 47 enq: TX - row lock contention WAITING
enq: TX - row lock contention 1u6pm5aay1kcm 49 VALID
some useful site.
https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-enqueues#TOC-enq:-TX---row-lock-contention-mode-6
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment