死锁

<pre name="code" class="sql">  如果你有两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。例如,如果我的数据库中有两个A和B,每个表都只有一行,就可以很容易地展示什么是死锁。我要做的知识打开两个回话(例如,两个SQL*PLUS会话).在会话A中更更新A表,在会话B中更新B。现在,如果我想在会话B中更新A,就会堵塞。会话A已经锁定了这一行,这不是死锁:只是堵塞而已。如果我再回到会话A,试图更新表B,这就会导致一个死锁。要在这个会话中选择一个作为牺牲品,让它的语句回滚。创建2张测试表:SQL> select * from t1;ID NAME---------- ---------- 1 a 2 bSQL> select * from t2;ID NAME---------- ---------- 1 a 2 b第一种情况 :两个会话持有同一对象的不同记录---SESSION 2187SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------      2187    0       0SQL> select * from t1;ID NAME---------- ---------- 1 a 2 bSQL> update t1 set id=3 where id=1 and 1=1;1 row updated.--SESSION 98SQL> show userUSER is "DWF"SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------98    0       0SQL> update t1 set id=4 where id=2 and 2=2;1 row updated.SESSION 2187 执行:SQL> update t1 set id=4 where id=2 and 3=3;hang住SESSION 98 执行SQL>  update t1 set id=3 where id=1 and 4=4;此时SESSION 2187报死锁错误SQL> update t1 set id=4 where id=2 and 3=3;update t1 set id=4 where id=2 and 3=3       *ERROR at line 1:ORA-00060: deadlock detected while waiting for resource查看trace文件Deadlock graph:                       ---------Blocker(s)--------  ---------Waiter(s)---------Resource Name          process session holds waits  process session holds waitsTX-000a0008-000033cd        23    2187     X             25      98           XTX-00090011-0000155c        25      98     X             23    2187           Xsession 2187: DID 0001-0017-000003E4    session 98: DID 0001-0019-000000F1session 98: DID 0001-0019-000000F1      session 2187: DID 0001-0017-000003E4Rows waited on:  Session 2187: obj - rowid = 00019B31 - AAAZsxAAxAAA5jWAAB  (dictionary objn - 105265, file - 49, block - 235734, slot - 1)  Session 98: obj - rowid = 00019B31 - AAAZsxAAxAAA5jWAAA  (dictionary objn - 105265, file - 49, block - 235734, slot - 0)----- Information for the OTHER waiting sessions -----Session 98:  sid: 98 ser: 347 audsid: 103737 user: 91/DWF    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-    flags2: (0x40009) -/-/INC  pid: 25 O/S info: user: oracle, term: UNKNOWN, ospid: 11545    image: oracle@dwh1 (TNS V1-V3)  client details:    O/S info: user: oracle, term: pts/4, ospid: 11544    machine: dwh1 program: sqlplus@dwh1 (TNS V1-V3)    application name: SQL*Plus, hash value=3669949024  current SQL:  update t1 set id=3 where id=1 and 4=4[oracle@dwh1 trace]$ grep update /oracle/app/diag/rdbms/dwh1/dwh1/trace/dwh1_ora_11486.trc | grep t1  update t1 set id=3 where id=1 and 4=4update t1 set id=4 where id=2 and 3=3        ObjectName:  Name=update t1 set id=4 where id=2 and 3=3   ObjectName:  Name=update t1 set id=4 where id=2 and 3=3 update t1 set id=4 where id=2 and 3=3sql=update t1 set id=4 where id=2 and 3=3trc文件里只有引起死锁的会话和产生死锁的会话第二种情况:两个会话持有不同对象的记录--SESSION 2187SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------      2187    0       0SQL> update t2 set id=3 where id=1 and 1=1;1 row updated.--SESSION 98SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------98    0       0SQL> update t1 set id=3 where id=1 and 2=2;1 row updated.SESSION 2187执行:SQL>  update t1 set id=3 where id=1 and 3=3;此时HANGSESSION 98 执行SQL> update t2 set id=3 where id=1  and 4=4;此时SESION 2187SQL>  update t1 set id=3 where id=1 and 3=3; update t1 set id=3 where id=1 and 3=3        *ERROR at line 1:ORA-00060: deadlock detected while waiting for resourceSQL> SQL> SQL>