--- In firebird-support@yahoogroups.com, "imtiaz_ally"
<imtiaz_ally@...> wrote:

>
> When I try to execute a Stored procedure simultaneaously (by the
> same application located on 2 different PCs on a LAN), I get a
> deadlock Exception from one instance of the application while the
> other one successfully proceeds. The application is compiled on .NET
> 2.0 using the the IB Free OLEDB 1.5 Driver accessing a Firebird 1.5.3
> DB on a Windows 2003 server on a LAN.
> I really need BOTH transactions to proceed (even if ONE
> waits) and WITHOUT my application getting an exception i.e. the DBMS
> killing the transaction completely because of locking, instead of

waiting.

>
> At first I thought it was an OLE DB Driver problem and when
> I tried to upgrade to the latest version and/or setting Isolation
> levels in .NET, this does not reslove the problem at all. Then, I
> tried to see the Firebird Config file to see about Deadlock params,
> but I did not find anything interesting. Finally, I made the same test
> but by executing the SP straight from a SQL Window in IBExpert (on the
> same 2 PCs & simultaneously), and again I got this Deadlock error on 1
> PC while the other 1 successfully proceeds (see below):
>
> "Unsuccessful execution caused by system error that does not preclude
> successful execution of subsequent statements.
> lock conflict on no wait transaction.
> deadlock.
> update conflicts with concurrent update."
>
> Can you please help me in understanding & solving the problem??? Thank
> you.
>
> Platform : WinXP/2003 - FireBird 1.5.3
>

This is normal and correct behaviour (although the error message is a
bit misleading because it is not really a deadlock IMO). You have
stated that you want both transactions to succeed, but this is
impossible, and not what the wait parameter is about.

All wait means is that the second transaction will cross its fingers
in the hope that the first rolls back. If the first transaction
commits, you will still get the exception. The only difference between
wait and nowait is that nowait is the pessimist that gives up immediately.

But your approach is wrong. Instead of expecting a database engine to
ignore the dirty update (which is part of a fundamental attribute
called isolation), if you honestly don't care about what change the
first transaction made, catch the exception, commit, delay a few
moments then try again in a new transaction. If you use wait
transactions, there is no need for any delay between as it will not
return until the first transaction is committed.

Adam