Re: delete cascade

"jonnie" <jsavell_at_gmail.com> wrote in message news:1176914861.859933.26680_at_p77g2000hsh.googlegroups.com...
> Could you please point me to any information about deadlock caused by
> cascade delete.
>

I don't have a reference for you, but over the years I have run into the problem a couple times. The deadlock occurs because you give up control over the order in which locks are obtained to the system. Before a cascading delete can occur, exclusive locks must be obtained on every targeted row in the cascade chain. When another transaction executing under the serializable isolation level obtains shared locks in one order on the rows you're trying to delete, but the system is trying to obtain exclusive locks on the rows to be deleted in a different order, you get a deadlock. This happens a lot more frequently for cascading updates than deletes (I've seen that happen numerous times.), and the probability that it will occur depends on the number of tables in the chain and the volume of transactions. It doesn't happen very often, but when it does, finding the problem is a monumental task. That's why I think it's best to avoid it whenever possible.

> I have never heard of this and I am more than a little bit paranoid.
>
> Thank you.
>
> Sincerely,
> Jon Savell
>
> On Apr 15, 4:41 am, "Brian Selzer" <b..._at_selzer-software.com> wrote:
>
>> Personally, I avoid using referential actions whenever possible--mainly
>> because they increase the probability that a deadlock will occur, unless
>> of
>> course you add code to obtain locks beforehand in the correct order.
>> It's
>> very easy to forget to add that code since deadlocks are usually rare.
>> Out
>> of sight, out of mind: greater potential for bugs. It's also easier to
>> come
>> back to a procedure and figure out what it does if everything in the
>> database that may be touched by that procedure is accessed directly
>> within
>> it. Again, out of sight, out of mind: greater potential for bugs.
>> Another
>> issue is that in some systems there can only be one cascade path between
>> tables in a database. This leads to inconsistent code: along one path
>> you
>> allow the system to handle it; along another the application must. It
>> should be apparent that inconsistent code can also increase the risk for
>> bugs.
>>
>> > (just trying to keep Marshall interested.)
>>
>> > p
>
>
Received on Wed Apr 18 2007 - 20:14:05 CEST