Subject FireBird locks
Author chris_delphi

we are asking for help as we completely and utterly stumped. We
cannot work out why a read only dataset should cause locks in
firebird, Our gut feel is that there is an incompatibility between two
drivers that is causing firebird to choke. (or we are missing
something really obvious) But we've got no idea how to prove/test this
is the case, any ideas are more than welcome as we have run into a
brick wall and are unsure of how to continue.

short version

We have a (read /write) server which uses IBExpress and a (read
only) test client using ODBC via ADO (fbclient.dll) that seem to lock
each other, when both try to access the same row in the same table in
the database at the same time. The only clues we have is that the lock
always lasts exactly sixty seconds and it does not appear to be a time
out we have set in any of the programmes we have written. We can get
it to lock when both programmes have performed a select statement so
it appears that it's not the write operations in the server that are
causing the problem.

Long version

We have a server side (written in Delphi 7) program that uses
Interbase Express 7.08 components with the Transaction Properties on
the TIBTransaction component set to read committed (The settings box
reads read_committed, rec_version, nowait).

Normally the client is a web page that uses coldfusion and dlls to
read the data but to simplify and prove that our problem is not
coldfusion we have written a simple dummy programme that simply opens
and closes a dataset on a particular row in the firebird database
(Firebird version Firebird- running on windows
2003 server)

The test program connects to the database (SELECT only) via an ODBC
System DSN using the FireBird/Interbase driver.
It uses an ADO Connection object and an ADO Dataset to loop round and
check when a timestamp field on the relevant record has changed
All of the connection settings are taken from this ODBC DSN and are as
Driver: IscDbc
Client: fbclient.dll
Charset: ASCII
Initializing Transaction: Read
Dialect: 3
Extended Identifier Properties: Quoted Identifier

The isolation level on the connection is ilChaos, but we have also
tried ilReadCommited. The connection properties are set using an IDL
file which does little more than connect directly to the ODBC DSN
described above. The BeginTrans method of the connection is executed
before every Dataset Open call and CommitTrans is executed after every
Dataset.Close .

The problem is that after a few of these add operations we reach a
situation where the DLL tries for 60 seconds just to open an
ADODataset pointing to the record and then times out. At the same time
the server also locks for sixty seconds at which point both programmes
unlock and continue to process correctly. We can find no logical
reason for this are we being stupid and missing something obvious ??

thanks in advance for any replies/advice in this.

Chris Barrington