Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Thu, 03 Jul 2014 16:55:12 +0200
Message-ID: <53B56ED0.4070006_at_mgm-tp.com>



Hello all,

I'm investigating a somewhat slow UPDATE statement that takes an average of 4 seconds even though it is based on the PK.

The statement looks like this:

  update CUSTOME

     set PREPAYMENT_IBAN=:1 , IS_GUEST=:2 , HAS_PREFERRED_ADDRESS=:3 , TOKEN=:4 , TOKEN_CREATED_AT=:5 , PREFERRED_PAYMENT_TYPE_ID=:6   where CUSTOMER_ID=:7

The table in question has approx. 4 million rows, customer_id is the PK

Checking v$active_session_history I can see that there are many "enq: TX - row lock contention" wait events on an index with mode = "Share (4)"

About half of the wait events show up like this:

EVENT         : enq: TX - row lock contention
ENQ_MODE      : Share (4)                    
P1TEXT        : name|mode                    
P1            : 1415053316                   
P2TEXT        : usn<<16 | slot               
P2            : 720899                       
P3TEXT        : sequence                     
P3            : 1217581                      
CURRENT_OBJ#  : 451463                       

CURRENT_OBJ# references the index on the column PREFERRED_PAYMENT_TYPE_ID which is a FK to another table. That FK is defined as DEFERRABLE INITIALLY DEFERRED.

So far my understanding was that a row lock contention in "Share (4)" mode for an index usually happens for either bitmap or unique indexes.

But the index in question is neither of those and the only thing out of the ordinary that I can see is the fact that the FK is defined as deferrable.

There is also a substantial amount of "enq: TX - row lock contention" in "Share (4)" mode with CURRENT_OBJ# = -1. I'm unsure what exactly that means.

This is an Oracle 11.2.0.2.0 server running on CentOS 64bit

Any ideas?

Thanks in advance
Thomas

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 03 2014 - 16:55:12 CEST

Original text of this message