If one is seeing ‘enq: TX – row lock contention’ there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4) it’s typically going to be
inserting a unique key when someone else has already inserted that key but not committed
Inserting a foreign when then parent value has been inserted but not committed or deleted and not commited (not to be confused with locks due to un-indexed foreign key which cause a “enq: TM – contention” wait not a TX wait)
bitmap index chunk contention
Now how to tell which of these is happening? Well here is a query on ASH (I’ve commented out some of the useful fields to limit the output) and a results cheat sheet:
col object for A15
col otype for A10
select
substr(event,0,20) lock_name,
--ash.session_id waiter,
--mod(ash.p1,16) lmode,
--ash.p2 p2,
--ash.p3 p3,
o.object_name object,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
--ash.SQL_ID waiting_sql,
BLOCKING_SESSION blocker
--,ash.xid
from
v$active_session_history ash,
all_objects o
where
event like 'enq: TX%'
and mod(ash.p1,16)=4
and o.object_id (+)= ash.CURRENT_OBJ#
/
uniq index
OBJECT OTYPE FILEN BLOCKN BLOCKER
------ ------ ----- ------ -------
-1 0 0 158
-1 0 0 158
-1 0 0 158
-1 0 0 158
FK
OBJECT OTYPE FILEN BLOCKN BLOCKER
------ ------ ----- ------ -------
CHILD TABLE 1 60954 1
CHILD TABLE 1 60954 1
CHILD TABLE 1 60954 1
bitmap
OBJECT OTYPE FILEN BLOCKN BLOCKER
------ ------ ----- ------ -------
I1 INDEX 0 0 144
I1 INDEX 0 0 144
I1 INDEX 0 0 144
I1 INDEX 0 0 144Each case has a different footprint.
unique key index issue object of “-1”
foreign key case has a blocker of “1”
bitmap index case as filen and blockn “0”
These cases were run on 10.2.0.3 thus the “footprint” could change on other versions.
The above ASH query and many other useful ASH queries are maintained on GitHub at
Kommentit