Commented by: @dyemanov

Make sure you finished the transaction used to perform that query. Also, make sure that the query handle is actually released upon close (some components could require an extra operation for that).

Commented by: Pascal (webpac)

I verified and the the query is closed et destructed, only the connection to the database is subsisting.
The same applications work without problem with Firebird 1.5.x and is freezed with Firebird 2.0.3 until the other application close the connection of the database.

I develop an ETL and it's a big problem for me.
Thanks,

Commented by: @dyemanov

Provided that I cannot reproduce this issue with two ISQL instances, I still believe the problem is on your side (handle management, etc).

Commented by: @hvlad

Pascal, get Firebird 2.1 and use monitoring tables to ensure you didn't release query handle.

Note, you need to call isc_dsql_free with DSQL_drop option (DSQL_close is not enough)

Commented by: Pascal (webpac)

I installed Firebird 2.1 but I don't know where is the monitoring.
Have you got a documentation to use th monitoring table ?
Where do I have to call isc_dsql_free with DSQL_drop ?

Thanks,

Commented by: @dyemanov

What connectivity layer do you use to access FB?

Commented by: Pascal (webpac)

I work with Delphi 7 and the dbExpress componants developped by Borland (TSQLConnection, TSQLQuery) and the IBX componants( TIBDatabase, TIBQuery).
If a sample Delphi code can help you to test, I make and give you it.

Commented by: @dyemanov

Please make a test case for Delphi.

Commented by: Pascal (webpac)

Hi,
I made the test case : http://pascal.libaud.free.fr/Firebird/TestFirebird.zip
there is the Delphi 7 source code and the executable.
The test is simple, launch the Project1.exe twice, with the first instance, clic on "Open and close query", with the second instance, clic on "Delete and create table".
The second one freezes when it try to delete the table.
Thanks,

Commented by: Pascal (webpac)

Do you reproduce the problem ?
What do you think about it ?

Commented by: @dyemanov

DBX doesn't seem to close the statement handle properly. Also, I don't know how it manages transactions. I've changed your example to use IBX and the test works fine with no lockups.

Commented by: Pascal (webpac)

Hello,
I don't work with just Firebird but also with others databases, so I have to make a generic code. I tried to use IBX components but I got some problems with big databases.
The problem is the code source of my test project is right and works well without any problem with Oracle, SQL Server, MySQL AND Firebird 1.5.
The statement handle is not closed properly, but are you sure, the problem is in my source code and not in the firebird 2.0 source code ? If my code is wrong, why does it work with others databases and with firebird 1.5 ?
What's the difference in statement handles in firebird 1.5 and firebird 2.0 that can explain the diffrence of reaction by the database ?
We have to find a solution, it's very important.
Thanks,

Commented by: Pascal (webpac)

What is scheduled for this issue ? Thanks

Commented by: @dyemanov

After more testing your test case, I tend to conclude that the issue is actually SS vs CS and not v1.5 vs 2.0. In all versions, CS don't allow you to drop the table. Can you confirm that?

Commented by: Pascal (webpac)

What does CS and SS mean ?

Commented by: @dyemanov

classic server / superserver

Commented by: Pascal (webpac)

I made the tests and theses the results :

1.5.4 Classic Server : table dropped.
1.5.4 Super Server : table dropped.

2.0.3 Classic Server : can't drop the table.
2.0.3 Super Server : table dropped.

2.1.0 Classic Server : can't drop the table.
2.1.0 Super Server : table dropped.

So Cs allows to drop the table in 1.5.4 version but doesn't allow it in 2.0.3 version.

Most of clients have installed classic server because most have hyperthreading or multi processors or dual core computers.
With 1.5.4 Classic Server, the table is dropped without any problem.
We have to distribute the 2.0 version because the 1.5 bugs with configuration panel on Vista computers and with 2.0 Classic server version, the table is not dropped and the application is freezed.
So it's a big problem to change the 1.5 version to 2.0 version for me.
Any solution ?

Commented by: @dyemanov

Your issues are caused by two independent factors:

1) Buggy FB 1.5 which doesn't maintain usage counters for tables and hence allows to drop objects actually being used by compiled requests.
2) Weird transaction management by the Borland DBX driver. It does start a transaction for select but doesn't commit it thus locking the table.

I have found two solutions:

1) Use Upscene DBX driver instead of Borland one. It does auto-commit the select properly.
2) Use IBX with explicit StartTransaction / Commit calls around select.

Both these solutions work without problems for all FB versions and architectures.

BTW, I've noticed that your test case doesn't actually work with v2.0 / 2.1 SuperServer either. The engine returns the "object in use" error on commit for DROP TABLE and it is proved to be delivered to the client side, but Borland's DBX driver seems to ignore it. If you would comment out the CREATE TABLE call, then you'll get a message box "Table deleted" but in fact the table still exists in the database.

The only real issue with FB I've found is that v2.x SS reports the "object in use" error immediately while CS locks up until the concurrent resource is released. But this is a known issue, registered as CORE1032.