Triggers accessing a table prevent concurrent DDL command from dropping that table [CORE6382]

[firebird-automations]

Submitted by: prenosil (prenosil)

Is related to CORE1032
Is related to CORE3766

In Firebird 2.5 SuperClassic I sometimes get "object TABLE ... is in use" error when modifying database structure,
even if I am sure the table was not used. The same DDL command execute ok on FB2.5 Superserver.
Unfortunately the situation is worse in Firebird 4 because I get that error even on Superserver.

This is how the test scripts look like (two instances of isql are required):
(I will send full version of scripts and database privately)

----------

CONNECT ...;
UPDATE OR INSERT INTO Table01 ...;
COMMIT;

----------

CONNECT ...;
SET AUTODDL OFF;
SET TRANSACTION NO WAIT;
DROP TABLE Table02;
COMMIT;

->

Statement failed, SQLSTATE = 40001
lock conflict on no wait transaction
-unsuccessful metadata update
-object TABLE "TABLE02" is in use

Note - without "SET AUTODDL OFF;SET TRANSACTION NO WAIT;" the second script freezes until the first one is closed.

Commits: ac107bd ffb9f22

[firebird-automations]

Commented by: @dyemanov

I believe TABLE02 was actually used by your prior queries, maybe implicitly (via computed fields or nested procedures/triggers, etc).

Seems to duplicate CORE1032 and CORE3766. You may send me the test case if you want a confirmation.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE1032 [ CORE1032 ]

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3766 [ CORE3766 ]

[firebird-automations]

Commented by: @dyemanov

"object in use" error in NO WAIT transaction is not a bug, rather a design pitfall. See the other tickets mentioned in my prior comment. To workaround that, all DDL statements should be executed in WAIT mode, perhaps using LOCK TIMEOUT if you want some control.

However, in the WAIT mode, DROP + COMMIT should not hang in your specific case and this is indeed a bug (and a very old one, AFAIU it exists since the very beginning). The problem here is that Table02 is accessed by a trigger on Table01 and triggers never release their existence locks (until metadata cache is cleared, in particular during disconnection).

v3 and v4 SS are also affected due to per-attachment metadata cache implementation.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

summary: Unexpected "object TABLE is in use" error on DDL command => Triggers accessing a table prevent concurrent DDL command from dropping that table

[firebird-automations]

Commented by: @dyemanov

Should be fixed in v4, please test the next (tomorrow's) snapshot build.

[firebird-automations]

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: No test => Done successfully

[firebird-automations]

Commented by: prenosil (prenosil)

Unfortunately the behavior is still the same. (Firebird-4.0.0.2154-0_x64.7z from 2020-08-14)

[firebird-automations]

Commented by: @dyemanov

I tested the sample you sent me privately and it does not hang anymore in the WAIT mode. If you confirm that, then I need another test case that still shows the problem.

[firebird-automations]

Modified by: @dyemanov

Fix Version: 4.0 RC 1 [ 10930 ]

[firebird-automations]

Commented by: prenosil (prenosil)

The sample I sent now works correctly, unfortunately there is still something left. If the table contains Foreign Key constraint which references the same table (and has On Update), the error "object in use" will raise again. Here is script for testing:

(1) create test db
CREATE TABLE PILULKA_OBJ_PRIO (
ID_PILULKA_OBJ_PRIO INTEGER NOT NULL,
ID_ZBOZI INTEGER);

CREATE TABLE ZBOZI (
ID_ZBOZI INTEGER NOT NULL,
ID_ZBOZI_PRED INTEGER,
CONSTRAINT ZBOZI_PK PRIMARY KEY (ID_ZBOZI));

ALTER TABLE PILULKA_OBJ_PRIO ADD CONSTRAINT PIL_OBJ_PRIO_FK_ZBOZI FOREIGN KEY (ID_ZBOZI) REFERENCES ZBOZI (ID_ZBOZI) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ZBOZI ADD CONSTRAINT ZBOZI_FK_ID_ZBOZI_PRED FOREIGN KEY (ID_ZBOZI_PRED) REFERENCES ZBOZI (ID_ZBOZI) ON UPDATE CASCADE;

(2) in one instance of ISQL do
SQL> CONNECT ...;
SQL> UPDATE ZBOZI SET ID_ZBOZI = ID_ZBOZI;
SQL> COMMIT;

(3) in second instance of ISQL try to drop table PILULKA_OBJ_PRIO. Result depends on AUTODDL and SET TRANSACTION:

SQL> CONNECT ...;
SQL> SET AUTODDL ON;
SQL> DROP TABLE PILULKA_OBJ_PRIO;
-> isql freezes

SQL> CONNECT ...;
SQL> SET AUTODDL OFF;
SQL> SET TRANSACTION WAIT LOCK TIMEOUT 1;
SQL> DROP TABLE PILULKA_OBJ_PRIO;
SQL> COMMIT;
->
Statement failed, SQLSTATE = 40001
lock time-out on wait transaction
-unsuccessful metadata update
-object TABLE "PILULKA_OBJ_PRIO" is in use

[firebird-automations]

Modified by: @dyemanov

Fix Version: 4.0 RC 1 [ 10930 ] =>