DDL Implicit CommitPosted: February 11, 2009
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);
SQL> insert into t1 values (1);
1 row created.
SQL> create or replace procedure p1 as
3 update t1 set x=2;
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:
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.