RE: Unindexed FK Cause Deadlock or Only Share Lock?

Ethan, I don't think the cardinality makes any difference (somebody please correct me if I'm wrong). When you update/delete from the parent table of a FK relationship with no index on the FK in the child table, the *entire* child table is locked. Also, in your example, the index with 2 distinct values could prove to be very useful if they are unevenly distributed, for example if you have 10 "N"s and 1000000 "Y"s, the index would be very efficient for finding the "N"s.

Regards,
Brandon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Post, Ethan
Sent: Thursday, June 30, 2005 1:37 PM
To: jonathan_at_jlcomp.demon.co.uk; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

I was hoping one of the more brilliant scienticians from the other side of the pond would chime in. If I may beg you to waste a little more time with my humble request. What happens when there is an index, and let's assume the index only contains 2 distinct values over zillions of records. Have we eliminated the deadlock scenario below entirely? I am all for indexing my FK's but was not aware such a otherwise useless index (as described in previous sentence) could be so useful?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, June 30, 2005 3:21 PM
To: Oracle-L_at_Freelists. Org (E-mail)
Subject: Re: Unindexed FK Cause Deadlock or Only Share Lock?

If you attempt to delete a parent row,
or update the key value of a parent
row in a parent/child relationship where the child DOES NOT HAVE an index
on the foreign key, then Oracle will
attempt to acquire a mode 4 lock
on the child table (or mode 5 if it
has previously modified the child
table).

If any other sessions are currently
modifying the child table, your session
will have to wait before it can acquire
its mode 4/5 as the other sessions will
be holding mode 3 and therefore will
be blocking you.

Anyone who tries to start a new transaction on the child table (and therefore need to acquire a mode 3) will be blocked behind your request for a mode 4.

To engineer a deadlock:

    session A

        delete child row C1
        acquires mode 3 on child table

    session B
        delete child row C2
        acquires mode 3 on child table

    session A
        attempts to delete parent of C1
        attempts to convert mode 3 to mode 5
        blocked by session B holding mode 3
            therefore starts to wait

    session B
        attempts to delete parent of C2
        attempts to convert mode 3 to mode 5
        blocked by session A in the converters queue
            (viz: holding 3, and waiting to convert to 5)
            therefore start to wait

    Three seconds or less later, session a gets     an ORA-00060 Deadlock detected.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated June 22nd 2005

--
http://www.freelists.org/webpage/oracle-l

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l