Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
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-lReceived on Thu Jul 03 2014 - 16:55:12 CEST