Oracle Scratchpad

August 25, 2015

Truncate – 2

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 6:25 pm BST Aug 25,2015

Following on from my earlier comments about how a truncate works in Oracle, the second oldest question about truncate (and other DDL) appeared on the OTN database forum“Why isn’t a commit required for DDL?”

Sometimes the answer to “Why” is simply “that’s just the way it is” – and that’s what it is in this case, I think.  There may have been some historic reason why Oracle Corp. implemented DDL the way they did (commit any existing transaction the session is running, then auto-commit when complete), but once the code has been around for a few years – and accumulated lots of variations – it can be very difficult to change a historic decision, no matter how silly it may now seem.

This posting isn’t about answering the question “why”, though; it’s about a little script I wrote in 2003 in response to a complaint from someone who wanted to truncate a table in the middle of a transaction without committing the transaction. Don’t ask why – you really shouldn’t be executing DDL as part of a transactional process (though tasks like dropping and recreating indexes as part of a batch process is a reasonable strategy).

So if DDL always commits the current transaction how do you truncate a table without committing ? Easy – use an autonomous transaction. First a couple of tables with a little data, then a little procedure to do my truncate:


create table t1 (n1 number);
insert into t1 values(1);

create table t2 (n1 number);
insert into t2 values(1);

create or replace procedure truncate_t1
as
        pragma autonomous_transaction;
begin
        execute immediate 'truncate table t1';
end;
/

Then the code to demonstrate the effect:


prompt  ======================================
prompt  In this example we end up with no rows
prompt  in t1 and only the original row in t2,
prompt  the truncate didn't commit the insert.
prompt  ======================================

insert into t2 values(2);

execute truncate_t1;
rollback;

select * from t1;
select * from t2;


According to my notes, the last time I ran this code was on 9.2.0.3 but I’ve just tested it on 12.1.0.2 and it behaves in exactly the same way.

I’ve only tested the approach with “truncate” and “create table” apparently, and I haven’t made any attempt to see if it’s possible to cause major disruption with cunningly timed concurrent activity; but if you want to experiment you have a mechanism which Oracle could have used to avoid committing the current transaction – and you may be able to find out why it doesn’t, and why DDL is best “auto-committed”.

13 Comments »

  1. Jonathan, thank you for good post.
    I just want to point out the most intresting thing I noticed while messing up with commits caused by DDL, is that your local transaction gets commited even if you’re executing a DDL statement in a remote database. E.g. if you’re calling a remote procedure that creates a user in the remote database then you’ll get your local transaction commited anyway, so if you do not take it into account, then you will not be able to rollback any changes made to your local DB prior to this remote call.

    Comment by Viacheslav Andzhich — August 25, 2015 @ 7:31 pm BST Aug 25,2015 | Reply

    • Viacheslav,

      Thanks for highlighting that detail – it would be very easy to be miss something that’s going on in another database.
      As the song goes in The Lion King:


      There’s more to see than can ever be seen
      More to do than can ever be done
      There’s far too much to take in here
      More to find than can ever be found

      Comment by Jonathan Lewis — August 26, 2015 @ 9:05 am BST Aug 26,2015 | Reply

  2. As to “why DDL doesn’t require a commit”; what comes to mind is that leaving a transaction pending in the data dictionary could be most interesting should the perpetrator leave for lunch prior to issuing a ‘commit’ following DDL.

    Comment by jkstill — August 25, 2015 @ 10:49 pm BST Aug 25,2015 | Reply

    • I may be wrong, but I believe SQL*Server manages to cope with needing a commit for DDL (not that that’s intended as an argument for why Oracle ought to do it). I’d also point out that the only problem that the lunching perpetrator could produce would be to cause another session with a conflicting DDL (which would have to be a collision on referential integrity, a space requirement, or an attempt to access data that was in mid-drop) to suffer some sort of hang problem but Oracle already has mechanisms for DDL timeouts, space problems, and cross-DDL read-consistency.

      From another perspective, a commit on DDL could be beneficial for complex DDL like: “create two tables, three (extra) indexes, and two referential integrity constraints – then commit.” The “create schema” command comes close, but doesn’t cover the entire requirement.

      Comment by Jonathan Lewis — August 26, 2015 @ 9:00 am BST Aug 26,2015 | Reply

  3. Oracle Commits DDL so that state of objects is in known state, below is more meaningful

    Let’s say we have table t with same structure as that of all_objects

    Insert into t select * from all_objects :

    <>

    DROP TABLE t;

    I’m not sure if Oracle can change this in coming releases ?

    Comment by Atul Gupta (@atulgg) — August 26, 2015 @ 11:33 am BST Aug 26,2015 | Reply

  4. UPDATE EMP

    SET SAL= 1000

    WHERE EMPNO = 1;

    if oracle does not commit then State of EMP is not know and will lead to deadlock.

    ALTER TABLE EMP DROP column SAL;

    Comment by Atul Gupta (@atulgg) — August 26, 2015 @ 12:07 pm BST Aug 26,2015 | Reply

    • Atul,

      Your comments may have identified a problem with assuming that a simple change to the code could work, but they don’t justify a strategy of commiting the current transaction before doing DDL; after all, it would probably be better to raise an error of the form “can’t apply DDL to table X – uncommitted changes may exist”, rather than committing changes in a piece of user code that appears to be doing something that it (quite possibly) wasn’t intended to do.

      Remember, if the “drop table t” of your first example is done from a second session that has then that session will receive Oracle error 54 (resource busy and acquire with NOWAIT specified or timeout expired) as it tries to lock table t in exclusive mode nowait – and by that time it has already committed its transaction in the middle of a call that was doomed to fail.

      With that example in hand (and needing improvement anyway), one could imagine inventing a “level 6+” lock with the compatibility matrix saying that you can only acquire a level 6+ lock if no-one (including your own session) has any lock at any level on the object, and no-one can acquire any lock if anyone has a level 6+; then start any DDL with a call to lock the relevant object at level 6+. The point I made in my article, of course, was that there may be too many related details that have to change at the same time – especially after 30 years of accumulated code – so the risk of error far outweighs the potential benefit of the change.

      Comment by Jonathan Lewis — August 26, 2015 @ 1:49 pm BST Aug 26,2015 | Reply

  5. Thanks Jonathan for explanation. It will be difficult and near impossible for Oracle to do the change as many Scripts would need to be change for many deployments, across many customers. Some Programmers might have coded scripts in such a way that they might be committing previous transaction/DML using DDL auto commit feature/bug in same script.

    Comment by Atul Gupta (@atulgg) — August 27, 2015 @ 8:09 am BST Aug 27,2015 | Reply

  6. Jonathan, I think you can simplify your example by eliminating “create or replace procedure” and using a mere anonymous pl/sql block:

    declare 
      pragma autonomous_transaction;
    begin
      execute immediate 'truncate table t1';
    end;
    /
    

    Comment by Vladimir Sitnikov — September 8, 2015 @ 10:59 am BST Sep 8,2015 | Reply

    • I could have used an anonymous block, but then Steve Fueurstein would have been after me for not encapsulating the code in a procedure ;)

      Comment by Jonathan Lewis — September 8, 2015 @ 10:58 pm BST Sep 8,2015 | Reply

  7. […] 8. Truncate – 2 […]

    Pingback by Catch Up | Oracle Business Intelligence — October 7, 2015 @ 9:14 pm BST Oct 7,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.