oracle死锁原因及解决办法

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';

oracle死锁原因及解决办法

相关推荐