DDL Implicit Commit

We all know that DDL implicitly commits transactions. However, I was not aware that it commits both BEFORE and AFTER the DDL.

I ran the following code from session 1:

SQL> create table t1 (x number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace procedure p1 as
2 begin
3 update t1 set x=2;
4 commit;
5 end;
6 /

Then in session 2:

SQL> update t1 set x=3;

1 row updated.

Now back in session 1:
SQL> exec p1

Session 1 promptly hangs, due to session 2 locking the row in t1.

Now in session 2, I run:

SQL> drop procedure p1;

I expected session 2 to hang as well, which makes it a deadlock.

However, what I saw was:
Procedure dropped.
in session 2 and
PL/SQL procedure successfully completed.
in session 1.

Which could only happen if the DDL drop procedure commited session 2, which caused session 1 to finish running the procedure, which enabled the drop to work.

Coffman et al (1971) showed that four conditions are necessary for a deadlock. The third condition is – no preemption – resources granted cannot be taken away from a process. By causing DDLs to force a commit before they run, Oracle forced preemption and avoided deadlocks.

I like that. Sometimes I hate the fact that DDLs force a commit, but now I see the benefits.
I’ve heard that in Postgres DDLs are transactional and can be rolled back. I wonder if my scenario would deadlock in Postgres.

Advertisements

8 Comments on “DDL Implicit Commit”

  1. Asif Momen says:

    Chen,

    Good one.

    So, you missed this thread on asktom.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7072180788422

  2. prodlife says:

    The thread is from 2002! I’m not sure if I was even born back then 😉

  3. Yong Huang says:

    Hi Chen, I’m not sure why you say there’s another commit AFTER the DDL. “drop procedure p1;” is like
    (1) commit;
    (2) drop procedure p1;
    Right after (1), the lock is released. The waiter on the queue, session 1, gets the lock and updates i.e. locks the row and then commits so releases the lock. Then it’s session 2’s turn to run (2) to drop p1.

    What you said as follows is correct:
    “the DDL drop procedure commited session 2, which caused session 1 to finish running the procedure, which enabled the drop to work.”
    But there’s no second commit in session 2 after all that.

  4. Yong Huang says:

    Now I see your logic. Oracle doesn’t expose those implicit commits for some DDLs to any statistic or SQL trace. But ‘log file sync’ wait seems to correspond to the ending moment of the DDL. My test is, create a procedure which only does sleep. Run it in one session. Drop it in another session which hangs. A monitoring session keeps checking the second session for ‘log file sync’ wait. When the drop eventually runs, the wait increments by 1 (not 2).

    Interestingly, some DDLs, such as grant a role to a user (e.g. grant dba to yong) does increment ‘user commit’ statistic once. But grant on to … does not.

  5. Yong Huang says:

    That “grant on to …” in my last message should be “grant on to “. For a list of DDLs I tested, see the bottom of
    http://yong321.freeshell.org/oranotes/RollbackCommitStats.txt

  6. Iggy Fernandez says:

    Hi, Chen,

    My understanding is that a number of databases allow DDL to be rolled back but not Oracle. The scenario you describe would probably cause a deadlock in those databases. But there are definitely good uses for “transactional DDL.” For example, consider an application upgrade involving DML as well as DDL; if any statement fails, whether DML or DDL, one would like to be able to roll back the changes.

    Refer to http://www.mydatabasesupport.com/forums/oracle-server/427452-why-oracle-does-not-allow-rollback-ddl-statements.html for an animated discussion of Oracle’s idiosyncratic treatment of DDL. One person remarked:

    “Database is the only thing I know in Computer Science, which has concept of transaction where by a group of statements can be rolled back in case of error. When students learn databases, they immediately think that everything in database can be rolled back. When they find that their database cannot rollback DDL, they are really surprised. Having worked in Oracle for many years, I thought no relation database provides this feature. When I started learning SQL Server, I was pleasantly surprised that SQL Server can do it.”

    Iggy

  7. prodlife says:

    @Iggy,

    Good to see you here 🙂

    Thanks for the link, it is indeed a good discussion.
    The original poster is wrong though, SQLServer does not allow rolling back DDL.

    DDL rollback would be great in some situations, and can probably cause huge mess in others. I think that just like dirty-reads, it is something that other databases do, but Oracle would never consider.


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