Ann W. Harrison wrote:

> = m. Th = wrote:
>
>> Hi,
>>
>> From some reason outside the scope of this eMail, an application enters
>> in a deadlock. Ok, this isn't desirable, but it happens. But once it
>> happens how can we solve it?
>>
>
> Rollback the affected transaction and proceed? When there's a deadlock,
> one transaction will get an error message from an update or delete
> request. When that transaction rolls back, the deadlock is gone and
> processing will continue.
>

In deed (theoretically). How the things are in practice:
- We develop some applications and sometimes, because, no application is
perfect, especially in the applications in which the 'optimistic'
paradigm doesn't reflect the reality (as in a communication program who
uses FB for storing different things like users, logs, off-line messages
aso). sometimes deadlock will occur. As much as we test the app in lab
we cannot prevent all the deadlock situation which can arise in a real
world LAN with many more users, so our 'lab' is extended using as
'beta-testers' the users from the entire LAN. One (or some) of them
tells me that's a problem with his communication client. I run my app
and I see from the debugger that, in deed, there is a deadlock when
issuing a DELETE FROM USERS WHERE ... But with who? (Here I mean that
are more users implied in the deadlock process - in fact there are a
'deadly embrace' which is causing by the factors that, I presume, are
tied with suspend/sleep states, Windows winsock implementation aso.).
And why? (because I use the IBO's 'AutoCommit mode' and I execute these
commands in a try...finally block). Of course I'll (must) fix the app
but until then... I cannot send a message (the client doesn't work) or
call all the users from the LAN. Then I try to shut down the
communication database, hoping that this will resolve the things. But
the SQL Manager stalls and nothing happen. So, the two solutions which I
have are the bellow ones.

> Perhaps I don't understand what you mean by a deadlock, but nothing
> should require either of these steps:
>
>
>> 1. Log-off all the clients of the application (by closing it) which are
>> scattered on the network. This implies phone-calls, messages aso.
>> 2. Brutally shut down the server. But this kills also the other
>> databases attached on it.
>>
>
>
> Regards,
>
>
> Ann
>
>
>

As an aside, I put the problem more generally because, IMHO, if this
happens in a production environment perhaps is better for an DBA to have
a tool (like fb-lock_print) or much better a SQL Select from a (virtual)
RDB$ table who shows who users (identified by their IP addresses) are
involved in a deadlock.

Thanks for your support,

m. th.

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>