Enqueue – PK, FK or Bitmap Index problem?
If one is seeing waits for enq: TX – row lock contention then 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
- Primary/Unique Key: inserting a unique key when someone else has already inserted that key but not committed
- Foreign Key: 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: 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) that will generate output to help us distinguish between the 3 different cases
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# /
Here is the output from the 3 different cases
Unique Index OBJECT OTYPE FILEN BLOCKN BLOCKER ------ ------ ----- ------ ------- -1 0 0 158 -1 0 0 158 -1 0 0 158 -1 0 0 158 Foreign Key OBJECT OTYPE FILEN BLOCKN BLOCKER ------ ------ ----- ------ ------- CHILD TABLE 1 60954 1 CHILD TABLE 1 60954 1 CHILD TABLE 1 60954 1 Bitmap Index OBJECT OTYPE FILEN BLOCKN BLOCKER ------ ------ ----- ------ ------- I1 INDEX 0 0 144 I1 INDEX 0 0 144 I1 INDEX 0 0 144 I1 INDEX 0 0 144
Each 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
https://github.com/khailey/ashmasters
