Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Aug 7, 2020 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE1032 [ CORE1032 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3766 [ CORE3766 ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 RC 1 [ 10930 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment