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.

Advertisement

---