Subject Re: SELECT FOR UPDATE WITH LOCK and Connections
Author Adam
--- In, "sasidhardoc"
<madhusasidhar@...> wrote:
> I have a Firebird DB with remote clients connecting via TCP/IP. The
> clients use ADO.NET
> In the DB, there is a SP which is similar to:
> Client 1 starts a transaction, and then retrieves using the SP.
> If Client 2 now starts a transaction and retrieves using the same SP,
> I get a "locked record error" - which is the expected behavior.
> If client 1 starts, and I pull the network cable on client 1 after
> transaction has started, then Client 2 runs a transaction to retrieve
> the SP, I still get a "locked record error".
> With a similar scenario in MS SQL, transaction 1 will get rolled back
> (and the lock released) when I pull the network cable. Is the behavior
> in Firebird different by design? What can I do to roll back the
> transaction and release the lock when a client drops a connection?
> I am using superserver. The transactions run with options NoWait,
> ReadCommitted, RecVersion. TIA

The timeout value until a TCP/IP connection is considered dropped is
an OS setting. On most OS, this defaults to 2 hours. So Firebird will
not be told by the OS that the client is dead until that time. As soon
as Firebird is informed the connection is abandoned, the transaction
will be flagged as rolled back. You can of course change the 2 hours
to something else. Look in the firebird.conf under Client Connection
Settings (Basic) for links to the settings on your specific OS.

Is it different by design? Probably, MSSQL must poll the clients on a
regular basis to know about this before the OS flags the connection as
closed. The downside must be that brief outages would cause the
connection to fail on MSSQL that would be simply resumed on Firebird.