Hi Ann,

We use FB CS 1.5 on Linux. I will give a short explanation of how the
program works to understand the issue better.

Our program opens a customizable list of columns from the database. We build
a stored proc called OPEN_ITEMS_userno, the userno is the integer value on
the database so for userno 7 a stored proc called OPEN_ITEMS_7 is created.
We chose to go this route for two reasons:
1) the number of columns returned needs to be only what the user wants
(network traffic issues and the like) and
2) on FB 1 this was our way of creating dynamic where clauses in our stored
procs.

The problem is that sometimes from creating the stored proc to selecting
data from it something goes wrong on the client side (e.g. user lost
connection). Now when he opens the program again and redoes the process and
at the point the code tries to create the stored proc again the following
error gets thrown:
Lock conflict on no wait transaction unsuccessful metadata update object
OPEN_ITEMS_7 is in use.

This error (I think) is because there is still a transaction alive
implicating the stored proc and thus the error occurs. The question is about
how do we fix this. At the moment when this occurs the fix is nasty. Due to
us running CS on Linux we do not know which of the current processes running
are the ones that are "lost". So we get everybody to log of the system and
then kill the ones left over. The users are very distributed and this
process can take several hours, needless to say when this happens it is a
major disruption in our and the client's live.

To answer your question, yes we are seeing some other behavior. I read on
the FB list that there is a timeout for transactions build into FB 2, and
that's the kind of thing that will really make a big difference in our
lives.

Peter Ypenburg

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Tuesday, June 21, 2005 8:24 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Lost transactions that causes deadlocks

Peter Ypenburg wrote:

>
> I need to understand how to handle lost transactions that causes

deadlocks.

Err... Interesting question. If the transaction is lost, it shouldn't
be causing deadlocks. In theory, when one transaction finds itself
blocks by another, the first thing it tries to do is determine if the
blocking transaction is alive or dead. The involves signaling it and
is, I think, implemented in SuperServer with a check that the connection
is currently active and working at both ends. If not, the blocking
transaction is declared dead and rolled back, undoing the block.

Are you seeing some other behavior?

>

Regards,


Ann



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net 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