死锁

  如果你有两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。例如,

如果我的数据库中有两个A和B,每个表都只有一行,就可以很容易地展示什么是死锁。

我要做的知识打开两个回话(例如,两个SQL*PLUS会话).在会话A中更更新A表,在会话B中更新B。

现在,如果我想在会话B中更新A,就会堵塞。会话A已经锁定了这一行,这不是死锁:只是堵塞而已。

如果我再回到会话A,试图更新表B,这就会导致一个死锁。要在这个会话中选择一个作为牺牲品,

让它的语句回滚。


SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	35	    0	       0

SQL> create table t3(id int);

Table created.

SQL> insert into t3 values(1);

1 row created.

SQL> insert into t3 values(2);

1 row created.

SQL> commit;

Commit complete.


SQL> update t3 set id=10 where id=1;

1 row updated.

SQL>  update t3 set id=30 where id=2;
 update t3 set id=30 where id=2
        *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource



###############################################################
SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	36	    0	       0


SQL> update t3 set id=20 where id=2;

1 row updated.

SQL> update t3 set id=40 where id=1;

hang-------


查看trace 文件:

----- Information for the OTHER waiting sessions -----
Session 36:
  sid: 36 ser: 145 audsid: 1531179 user: 91/TEST flags: 0x45
  pid: 28 O/S info: user: oracle, term: UNKNOWN, ospid: 29718
    image: oracle@june (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/0, ospid: 29714
    machine: june program: sqlplus@june (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update t3 set id=10 where id=1
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=27jv5jpf0wh5z) -----
 update t3 set id=20 where id=2