My point wasn't to pick any particular detail of any particular example . I was merely making the point that whilst the concept of DDL without commits seems to be straightforward, the requirement for designing something that could analyse and handle all the consequent errors that might be a non-trivial problem.
Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March ____USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 24 January 2003 16:40 > >Take your first example : >insert into t1 values (1); >drop table t1; > -- how to deal with self-deadlock ? >insert into t1 values (2); >commit; > >Why does Oracle HAVE to commit when the DROP TABLE is issued ? >What if the INSERT had been issued by another session ? Would >the DROP TABLE go through in this session ? >The "self-deadlock" could be handled as an Error -- a Transaction error, >with a message like "cannot drop table when transaction is active in >current session". >A duhveloper who has written a very long-winded .SQL file or procedure >and tries to drop a Table when he has an active transaction should be >caught and "errored" -- he shouldn't be allowed to drop his own table, he >has made a logical error. > >Take the second example : >insert into t1 values (1); >drop table t2; > -- how to deal with lock by other user ? >insert into t1 values (2); >commit; > >If the DROP TABLE may have to wait for another user who has a lock on T2, >why should the INSERT INTO T1 be committed ? Our user hasn't completed >his transaction yet. > >It all depends on what you mean by a "Transaction". The way Oracle has >written DDLs, a "Transaction" ends and is committed when the next DDL >is issued. But that may not be a logical transaction in the real world. > >My point is not that the DROP TABLE should be "roll-backable". The DROP >TABLE itself must always commit it's own statement. --- it's own statement. >That is to ensure that a third user does not see inconsistency when running >a transaction accessing T1. But why should the DROP TABLE explicitly >commit the previous statement ? In your example, you have shown that >an inconsistency may arise when a DROP is issued on a table with an >outstanding transaction. > >But take the case where : >INSERT INTO T10 values ('a'); >-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... >DROP TABLE XYZ ; >-- oops, my INSERT INTO T10 should be rolled back >-- but the Drop XYZ is independent >ROLLBACK; > >Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT >INTO T10. >I didn't want that ! >It would have been better if this DROP TABLE XYZ was an Autonomous Transaction. >But in the first example, the DROP TABLE T1 should return an error. >So, the Oracle Kernel must have some more complexity to see if there's any >outstanding >transaction in the current session [it already checks for other sessions >because they >hold TM locks on the Table !] are pending against the same table being dropped. > >Hemant > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
