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
Comments
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. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovsummary: Unexpected "object TABLE is in use" error on DDL command => Triggers accessing a table prevent concurrent DDL command from dropping that table |
Commented by: @dyemanov Should be fixed in v4, please test the next (tomorrow's) snapshot build. |
Commented by: prenosil (prenosil) Unfortunately the behavior is still the same. (Firebird-4.0.0.2154-0_x64.7z from 2020-08-14) |
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. |
Modified by: @dyemanovFix Version: 4.0 RC 1 [ 10930 ] |
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 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 (3) in second instance of ISQL try to drop table PILULKA_OBJ_PRIO. Result depends on AUTODDL and SET TRANSACTION: SQL> CONNECT ...; SQL> CONNECT ...; |
Modified by: @dyemanovFix Version: 4.0 RC 1 [ 10930 ] => |
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
The text was updated successfully, but these errors were encountered: