Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
- From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
- To: "thomas.kellerer@xxxxxxxxxx" <thomas.kellerer@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 Jul 2014 10:25:22 -0700
To build such a foreign key constraint when referenced values are missing it's
necessary to also specify NOVALIDATE to avoid the "ORA-02298: cannot validate
(string) - parent keys not found" error. When such a constraint is
successfully created it does take longer to perform updates on the child table.
However in 11.2.0.3 no such waits are listed when the child table is updated
and an index exists for the foreign key column according to my tests.
It could be 11.2.0.2 specific behavior ('bug').
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"
On Thursday, July 3, 2014 8:56 AM, Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
wrote:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
