September 2, 2012

Blocker=Waiter: Self Dead Lock caused within one session

We know very well about deadlock. We know deadlock happens when multiple sessions compete some resources.

And in some particular case, dead lock can also be caused within the same session, below is a example:

SQL> create index obj_ind4$ on obj$(name);
create index obj_ind4$ on obj$(name)
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource

Currently there is no lock on obj$, but when we try to create a index on it, it failed with dead lock issue.

Below is from the trace file:

*** 2012-07-02 09:13:45.155
*** SERVICE NAME:(SYS$USERS) 2012-07-02 09:13:45.154
*** SESSION ID:(135.46) 2012-07-02 09:13:45.154
[Transaction Deadlock]
Current SQL statement for this session:
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace
=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00000012-00000000        21     135     S             21     135          SX
session 135: DID 0001-0015-0000001D     session 135: DID 0001-0015-0000001D
Rows waited on:
Session 135: obj - rowid = 00000025 - AAAAAlAABAAAADhAAA
  (dictionary objn - 37, file - 1, block - 225, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

From above we can clearly see the blocker is session 21 as well as waiter, which means the deadlock is caused by the session 21 itself.

From the current SQL the reason is obviously:
to create a object, oracle need to insert a row of the object's detail into obj$ table. The Insert is an DML which require level 3 TM lock on obj$ table.

And since we are creating the index on obj$ table, the session also require exclusive lock on the table which means no DML is allowed on ojb$.
That's where the dead lock comes.


Post a Comment