It is a well known fact that in an Oracle database it is not possible to make a transaction out of multiple DDL statements.
However, is there any way to lock a specific set of database objects within the current connection so that after a DDL query is executed, all locks are held until they are explicitly released?
An obvious solution of this kind doesn't work, because executing the DDL statement automatically commits the transaction, and with it, the locks are released:
LOCK TABLE x ....;
LOCK TABLE y ....;
ALTER TABLE x ....; -- Does not work properly since table locks are released here
ALTER TABLE y ....;
ALTER TABLE x ....;
COMMIT;
The DBMS_LOCK option doesn't work either, because it is an advisory lock, and the concurrent thread must respect this lock and at least be aware of its existence.
Moreover, it is not controlled which statements can be executed by concurrent threads/sessions. It is possible to execute a query only in the current session, and it must be ensured that no intermediate queries on tables X and Y are executed from other sessions until the current session has ended.
Are there any ideas how this can be implemented?
PS: Please don't mention the high-level task or XY problem. There is no high-level task. The question is posed exactly as it is.
CodePudding user response：
There are a few options you can try to achieve the desired behavior in an Oracle database:
Use the DBMS_DDL.LOCK_TABLE_DDL procedure to lock the tables you want to alter before executing the DDL statements. This procedure acquires a DDL lock on the specified table, which prevents other sessions from modifying the structure of the table until the lock is released. Note that this is not a transactional lock, so it will not be released if the transaction is rolled back. Here's an example of how you can use this procedure:
BEGIN DBMS_DDL.LOCK_TABLE_DDL('X'); DBMS_DDL.LOCK_TABLE_DDL('Y'); -- Execute your DDL statements here ALTER TABLE x ....; ALTER TABLE y ....; -- Release the locks when you're done DBMS_DDL.UNLOCK_TABLE_DDL('X'); DBMS_DDL.UNLOCK_TABLE_DDL('Y'); END;
Use the SET TRANSACTION command to specify the SERIALIZABLE isolation level for your transaction. This will cause Oracle to lock the rows being modified in exclusive mode, which prevents other sessions from modifying the same rows until the transaction is committed or rolled back. Note that this will also prevent other sessions from reading the locked rows until the transaction is committed, which may not be desirable in all cases. Here's an example of how you can use this option:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN -- Execute your DDL statements here ALTER TABLE x ....; ALTER TABLE y ....; COMMIT; END;
Use the SELECT FOR UPDATE statement to lock the rows in the tables you want to alter before executing the DDL statements. This will cause Oracle to lock the rows being modified in exclusive mode, which prevents other sessions from modifying the same rows until the transaction is committed or rolled back. Note that this will also prevent other sessions from reading the locked rows until the transaction is committed, which may not be desirable in all cases. Here's an example of how you can use this option:
BEGIN SELECT 1 FROM x WHERE ... FOR UPDATE; SELECT 1 FROM y WHERE ... FOR UPDATE; -- Execute your DDL statements here ALTER TABLE x ....; ALTER TABLE y ....; COMMIT; END;
CodePudding user response：
I'm pretty sure what you're trying to do isn't possible with Oracle's native transaction control. DDL will always end a transaction, so no lock on that object is going to survive it. Even if you immediately attempted to lock it after the DDL, another waiting session could slip in and obtain the lock before you do.
You can, however, serialize access to the table by utilizing another dummy table or row in a dummy table, assuming you control the code of any process wishing to access the table. If this is the case, then before accessing the table, attempt to lock the dummy table or a row in it first, and only if it succeeds continue with accessing the main table. Then the process that does DDL can take out that same lock (preventing other processes from proceeding), then do the DDL in a subroutine (named PL/SQL block) with PRAGMA AUTONOMOUS_TRANSACTION. That way the DDL ends the autonomous transaction rather than the main one, which still holds the lock on the dummy table.
You have to use a dummy table because if you tried to use the same table you want to modify you'll deadlock yourself. Of course, this only works if you can make all other processes do the lock-the-dummy-table safety check before they can proceed.
Lastly, albeit what I said above should work, it is likely that you're trying to do something you shouldn't do. DDL against hot objects isn't a good idea. Whatever you're trying to do, there is probably a better way than modifying objects on the fly like this. Even if you are able to keep other locked out, you are likely to cause object reference errors, package invalidations, SQL cursor invalidations, etc.. it can be a real mess.
CodePudding user response：
A bit of a joke (breaks all dependent PL/SQL), but... ;)
ALTER TABLE x RENAME TO x__my_precious;
ALTER TABLE y RENAME TO y__my_precious;
ALTER TABLE x__my_precious ...;
ALTER TABLE y__my_precious ...;
ALTER TABLE x__my_precious RENAME TO x;
ALTER TABLE y__my_precious RENAME TO y;