--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 11:19 AM 5/08/2008, you wrote:
> >hello
> >
> >i have recently implemented some calculation logic into our database.
> >the basic flow is like this:
> >
> >> INSERT RECORD STORED PROCEDURE ON TABLE 'ENTRY'
> >> CALLS AFTER INSERT TRIGGER FOR TABLE 'ENTRY'
> >> CALLS STORED PROCEDURE TO UPDATE TABLE 'CALCS1'
> >> CALLS AFTER UPDATE TRIGGER FOR TABLE 'CALCS1'
> >> CALLS STORED PROCEDURE TO UPDATE TABLE 'CALCS2'
>
> Theoretically OK.
>
>
> >i have discovered this is causing a deadlock when several people try
> >to enter entries for the same types concurrently.
>
> Actually, it's a lock conflict. "Deadlock" is the high-level

SQLCODE catch-all lock exception that encompasses all locking
exceptions, not just deadlocks. Genuine deadlocks are in fact rare
and are more likely to occur where you have dependencies running both
ways, which seems not the case here. You would be better informed of
the conflict type if you read the lower level ISC_ exception (a
9-digit code).

>
> >i have a few
> >questions relating to this, and would be grateful for any assistance.
> >
> >1. I assume that even though the above operations contains both
> >triggers and stored procedure calls, they are wrapped in the same
> >transaction. therefore a subsequent rollback will rollback changes
> >made by both the SP's and the triggers?
>
> Yes. But failure at any point does not *cause* a rollback. If you

don't act on the locking exception at the client side by rolling back
the transaction then the transaction remains unresolved.

>
>
> >2. my conclusion is that i must handle this situation by either
> >changing the transaction isolation level, or by handling the resulting
> >deadlock exception and performing a retry. is this correct?
> >
> >3. we are using the standard .NET ReadCommitted transaction isolation
> >level so far.
>
> It depends. The transaction isolation does not *cause* the lock

conflict. However, it can affect the resolution strategy. Since you
have The Book, study up the comparative effects of
Record_Version/No_Record_Version, which is resolution strategy
applicable *only* to Read Committed. Wait/No Wait settings might also
impact on the "do-ability" of simultaneous updates.

>
> >this has not caused any deadlocks before we did this
> >calculation logic because the other transactions are all quick and
> >simple.
>
> Well, lack of quickness and simplicity isn't the issue. You now

have multiple transactions concurrently hitting the same tables and
potentially attempting to update the same records. You're doing it on
cascading record sets. All you have done is to increase the chances
of collisions - I'll leave you to figure out the permutations. ;-) In
general, the oldest transaction doing this stuff will win and everyone
else will get a lock conflict.

>
> >yet even on these my thinking is that i should still be
> >handling potential deadlocks and doing retries? (this is why Q1 is so
> >important because i dont want duplicated data due to retries with
> >partial rollbacks).
>
> There is no such thing as a "partial rollback". However, in Read

Committed, if overwrites are verboten then don't be tempted to use
WAIT to speed things up for those who are currently being hit with
locking conflicts. Go the Retry route. But get your head around
what's happening in Read Committed transactions in accordance with
your Record_Version setting if it is imperative for your retries not
to overrule the latest committed record version.

>
> ./heLen
>

hello thanks for this help. i got pulled away onto another job in the
meantime so i apologise for the delay in my response. im gonna
investighate this further this weekend and will post back with my
proposed solution to run by you guys if you dont mind. thanks again.