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