martinthrelly 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'
>>
>
> i have discovered this is causing a deadlock when several people try
> to enter entries for the same types concurrently. i have a few
> questions relating to this, and would be grateful for any assistance.
>

In fact it's not a really deadlock...

> 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

> 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?
>

Isolation level won't solve the problem, a retry logic will solve. but
let me first ask a question.

In this scenario:

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'

The only command sent by the application is an insert right ? (the other as triggered by the database)

Are you doing something like this pseudocode:

StartTransaction
Insert into Entry values (1, 2, 3)
CommitTransaction

If you do this way the time the transaction would stay open will be very small (theorically ! because I don't know how much time the triggers and procedures needs to the the job), so the "deadlock" potential should be minimal.

But if you don't do the following: start transction, do something (fast ! without waiting for user input, etc.), commit right after, the transaction would stay open for a long time (perhaps 2 seconds is too long in your case) so the "deadlock" would be more frequent.

> 3. we are using the standard .NET ReadCommitted transaction isolation
> level so far. this has not caused any deadlocks before we did this
> calculation logic because the other transactions are all quick and
> simple. 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).
>

If you had found a "deadlock" in you "normal" operations, you will get
an exception, and in a way or another, you are handling it.

But, you can be sure... a rollback, rolls back all the work done in a
single transaction, be it by statements sent by the application
directly, by triggers or stored procedures.

> i have purchased the firebird book and this is my assessment of the
> situation. i would be grateful for confirmation. thanks.
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br