同一张表不同SESSION相互持有对方记录引发的死锁

锁产生的原因:如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会发生死锁。 
同一张表不同SESSION持有不同记录
SQL> create table t1(id int);

Table created.



SQL> create table t2(id int);

Table created.



SQL> select * from t1;

	ID
----------
	 1
	 2

SQL> select * from t2;

	ID
----------
	 2
	 1


开始测试:

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

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	48	    0	       0

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

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	38	    0	       0

SQL> select * from v$lock where sid in (48,38) and type in ('TM','TX');

no rows selected

SESSION 1:
SQL> update t1 set id=3 where id=1;

1 row updated.


SESSION 2:

SQL>  update t1 set id=4 where id=2;

1 row updated.

查看此时行锁情况:
SQL>  select * from v$lock where sid in (48,38) and type in ('TM','TX');

ADDR	 KADDR		 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
006EBE84 006EBEB4	  48 TM      76908	    0	       3	  0	    33		0
006EBE84 006EBEB4	  38 TM      76908	    0	       3	  0	    15		0
336F4CB0 336F4CF0	  48 TX     327711	12767	       6	  0	    33		0
331415AC 331415EC	  38 TX     262152	12576	       6	  0	    15		0


SESSION 1:
SQL>  update t1 set id=4 where id=2;
此时SESSION 1HANG住:

查看此时行锁信息:
SQL> select * from v$lock where sid in (48,38) and type in ('TM','TX');

ADDR	 KADDR		 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3500122C 35001258	  48 TX     262152	12576	       0	  6	    17		0
006EBE84 006EBEB4	  48 TM      76908	    0	       3	  0	    68		0
006EBE84 006EBEB4	  38 TM      76908	    0	       3	  0	    50		0
336F4CB0 336F4CF0	  48 TX     327711	12767	       6	  0	    68		0
331415AC 331415EC	  38 TX     262152	12576	       6	  0	    50		1

SESSION 2执行:
 update t1 set id=3 where id=1;


此时SESSION1报:
SQL>  update t1 set id=4 where id=2;
 update t1 set id=4 where id=2
        *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

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


SESSION 1(48):
SQL> update t1 set id=3 where id=1;

1 row updated.

SQL>  update t1 set id=4 where id=2;
 update t1 set id=4 where id=2
        *

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SESSION 2 (38):

SQL>  update t1 set id=4 where id=2;

1 row updated.

 update t1 set id=3 where id=1;


查看trace 文件信息:


Session 38:
  sid: 38 ser: 190 audsid: 1440036 user: 91/TEST flags: 0x45
  pid: 27 O/S info: user: oracle, term: UNKNOWN, ospid: 5535
    image: oracle@june (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/3, ospid: 5534
    machine: june program: sqlplus@june (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
   update t1 set id=3 where id=1
 
----- End of information for the OTHER waiting sessions -----    
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=2377z63nmj7ps) -----
 update t1 set id=4 where id=2