Oracle 死锁情况总结

死锁情况一：

SQL> create table TA(COLA number,colb number);

表已创建。

 

SQL> insert into ta values(1,11);

已创建 1 行。

 

SQL> insert into ta values(2,22);

已创建 1 行。

 

SQL> commit

  2  /

提交完成。

 

SQL> select * from ta;

      COLA       COLB

---------- ----------

         1         11

         2         22

 

在Session1中，更新第1行：

SQL>  update ta set colb=33 where cola=1;

已更新 1 行。

 

在Session2中，更新第2行：

SQL> update ta set colb=44 where cola=2;

已更新 1 行。

 

在Session1中，更新第2行：

SQL> update ta set colb=55 where cola=2;

 

在Session2中，更新第1行：

SQL> update ta set colb=33 where cola=1;

 

结果在Session1中，更新第2行报错：

update ta set colb=55 where cola=2

       *

第 1 行出现错误:

ORA-00060: 等待资源时检测到死锁

 

死锁情况二：

创建表TB：

SQL> create table tb as select * from ta;

表已创建。

 

SQL> select * from tb;

      COLA       COLB

---------- ----------

         1         11

         2         22

 

在Session1中，更新TA表：

SQL> update ta set  colb=44 where cola=1;

已更新 1 行。

 

在Session2中，更新TB表：

SQL> update tb set colb=33 where cola=1;

已更新 1 行。

 

再在Session1中，更新TB表：

SQL> update tb set colb=33 where cola=1;

 

再在Session2中，更新TA表：

SQL> update ta set  colb=44 where cola=1;

 

结果在Session1中的TB表：

update tb set colb=33 where cola=1

       *

第 1 行出现错误:

ORA-00060: 等待资源时检测到死锁

 

解决方法：

查看Oracle告警文件：

Fri Apr 12 16:58:05 2013

ORA-00060: Deadlock detected. More info in file d:\oracle\product\10.2.0\admin\lobomb\

udump\lobomb_ora_5640.trc.

查看.trc文件：

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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

TX-00090025-00002d62       627     823     X            741     801           X

TX-003f0002-000029c8       741     801     X            627     823           X

session 823: DID 0001-0273-00000003  session 801: DID 0001-02E5-00000004

session 801: DID 0001-02E5-00000004  session 823: DID 0001-0273-00000003

这里明确指出了发生死锁的两个session的ID

Rows waited on:

Session 801: obj - rowid = 0000290B - AAACmFAAFAAAAMnAAC

  (dictionary objn - 10507, file - 5, block - 807, slot - 2)

Session 823: obj - rowid = 0000290B - AAACmFAAFAAAANkAAB

  (dictionary objn - 10507, file - 5, block - 868, slot - 1)

Information on the OTHER waiting sessions:

Session 801:

  pid=741 serial=13639 audsid=81790 user: 28/CELLPHONE

  O/S info: user: administrator, term: , ospid: 1234, machine: WIN-DHD4U0C5L39

            program:

  Current SQL Statement:

  update T_Rcvd_Sale_Ticket set flag_1=1 where id in(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44)

这里是导致死锁的SQL语句1

End of information on OTHER waiting sessions.

Current SQL statement for this session:

update T_Rcvd_Sale_Ticket set flag_3=1 where id in(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80,:81,:82,:83,:84,:85,:86,:87,:88,:89,:90,:91,:92,:93,:94,:95,:96,:97,:98,:99,:100,:101,:102,:103,:104,:105,:106,:107,:108,:109,:110,:111,:112,:113,:114,:115,:116,:117,:118,:119,:120,:121,:122,:123,:124,:125,:126,:127,:128,:129,:130,:131,:132,:133,:134,:135,:136,:137,:138,:139,:140,:141,:142,:143,:144,:145,:146,:147,:148)

这里是导致死锁的SQL语句2

===================================================