Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 3 Jul 2014 10:25:22 -0700
Message-ID: <1404408322.73768.YahooMailNeo_at_web124703.mail.ne1.yahoo.com>
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_at_mgm-tp.com> 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 -- http://www.freelists.org/webpage/oracle-l
Date: Thu, 3 Jul 2014 10:25:22 -0700
Message-ID: <1404408322.73768.YahooMailNeo_at_web124703.mail.ne1.yahoo.com>
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_at_mgm-tp.com> 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 -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 03 2014 - 19:25:22 CEST