oracle死锁原因及解决方法

数据库死锁原因

如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会发生死锁。
用下面实验来说明死锁的产生原因和解决办法。
SESSION1:
SQL> create table t2 as select * from emp;
SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00        800                    20

SQL> update t2 set sal=sal+200 where empno=7369;
SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00       1000                    20

SESSION2:
SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30

SQL> update t2 set sal=sal+200 where empno=7900;
1 row updated.
SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00       1150                    30

SESSION1:
SQL> select * from t2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30

SESSION2:
SQL> select * from t2 where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00        800                    20

SESSION1:
SQL> update t2 set sal=sal-200 where empno=7900; 
出现等待

SESSION2:
SQL> update t2 set sal=sal-200 where empno=7369;
死锁发生，session2被阻塞
系统回滚session1跟死锁有关的sql。
update t2 set sal=sal-200 where empno=7900
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SESSION1:
SQL> select * from t2 where empno in (7369,7900);

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217 00:00:00       1000                    20
      7900 JAMES      CLERK           7698 19811203 00:00:00        950                    30
看到系统只回滚了对empno=7900的修改。
而此时SESSION2的update t2 set sal=sal-200 where empno=7369;依然被SESSION1的第一条语句所阻塞。

 

死锁的解决方法

打开第三个会话以DBA身份登录
SQL> select * from v$lock;

 

用下面的SQL语句可以直接得出谁阻塞谁
SQL> select ( select username from v$session where sid=a.sid) blocker, a.sid,
        ' is blocking ',
        (select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block=1 and b.request>0 and a.id1=b.id1 and a.id2=b.id2;

BLOCKER                               SID 'ISBLOCKING'  BLOCKEE                               SID
------------------------------ ---------- ------------- ------------------------------ ----------
STONE                                 144  is blocking  STONE                                 153

发生死锁的时候会在alert文件里记录下信息。
--查看操作系统下$ORACLE_BASE/admin/orcl/bdump/alert_orcl.log文件

 

可以用管理员账号执行kill命令杀死导致阻塞的会话SESSION1来解决死锁。
sqlplus / as sysdba
从上面查过的信息可以知道产生阻塞的sid=144
通过查v$session视图可以得出serial#

SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME       COMMAND    OWNERID TADDR
-------- ---------- ---------- ---------- -------- ---------- ----------- ---------- ---------- -----
29F0BED4        144          3     210009 29E24174         64 STONE                0 2147483644

杀死这个会话用下面的命令
alter system kill session '144,3';