At 08:05 AM 2/11/2006, you 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:
>
>SELECT f1, f2 FROM T1 FOR UPDATE WITH LOCK
>
>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?

Sure is. Firebird doesn't use two-phase locking to effect
transaction isolation. Search Google for "multi-generational
architecture" and "MGA".

>What can I do to roll back the transaction and release the lock when
>a client drops a connection?

You can't. The client that started the transaction is the only
client that can end it. The server will roll it back eventually,
once it knows that the client connection is dead. It won't know that
for about 2 hours if you have your TCP/IP socket timeout at the
default setting.

More pertinent to your actual scenario is this: if you are not just
"mucking about" but genuinely have a statement like this in a
production application, CHANGE IT FAST!!

Please read *carefully* the implementation and usage notes about FOR
UPDATE WITH LOCK in the Firebird 1.5.3 release notes. It was *not*
designed to be a workaround for developers who can't be bothered
handling lock conflicts. It's purpose is to apply strict
serialization on a record-by-record basis. If you ever have this
(rare) requirement, the scope of the query specification should be
limited to a very few records (ideally, one record) by a very tight
WHERE clause.

./heLen