I have created a test procedure like that:
CREATE OR ALTER PROCEDURE tmp_fab
RETURNS (dummy INTEGER) AS
BEGIN
dummy = 1;
suspend;
END
Then a first client ("client A") uses the procedure by executing the following SQL:
SELECT *
FROM tmp_fab
If another client ("client B") tries to alter the procedure (while the "client A" transaction is still active and not committed/rolledback), I see a different behaviour based on the way the "client B" is connecting to the database.
For example:
Trying to alter the procedure as follows:
CREATE OR ALTER PROCEDURE tmp_fab
RETURNS (dummy INTEGER) AS
BEGIN
dummy = 2;
suspend;
END
While committing the "client B" transaction...
It fails, with the following error:
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
Lock conflict on no wait transaction.
Unsuccessful metadata update.
Object PROCEDURE "TMP_FAB" is in use.
- Using FireDAC Delphi components (DriverName = FB)
- Using FIBPlus Delphi components
- Using IBExpert (It uses it's own firebird's dll)
It succeeds, correctly altering the procedure...
- Using isql
- Using DBeaver (It uses JDBC)
- Using FireDAC Delphi components (DriverName = ODBC)
Which is the cause of these two different behaviors? (Maybe some transaction settings?)