Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
- From: Rich <richa03@xxxxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Thu, 3 Jul 2014 11:32:24 -0700
Hi Thomas,
Are there LOB(s) in the table being updated?
I think CURRENT_OBJ# is set equal to -1 before being updated by the
process with the actual object number (some of the sampling just caught it
before it was populated).
Are the rows being updated "clean"?
(thinking it might be deferred block cleanout and/or transaction rollbacks?)
Can you trace the update in question?
HTH,
Rich
On Thu, Jul 3, 2014 at 10:25 AM, David Fitzjarrell <
dmarc-noreply@xxxxxxxxxxxxx> wrote:
> 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:
