Oracle常见死锁发生的原因以及解决方法

Oracle常见死锁发生的原因以及解决方法

Oracle常见死锁发生的原因以及解决办法

一，删除和更新之间引起的死锁

造成死锁的原因就是多个线程或进程对同一个资源的争抢或相互依赖。这里列举一个对同一个资源的争抢造成死锁的实例。

Oracle 10g, PL/SQL version 9.2

CREATE TABLE testLock( ID NUMBER,

test VARCHAR(100) )

COMMIT

INSERT INTO testLock VALUES(1,'test1');

INSERT INTO testLock VALUES(2,'test2');

COMMIT;

SELECT * FROM testLock

1. ID TEST

2.---------- ----------------------------------

3. 1 test1

4. 2 test2

死锁现象的重现：

1)在sql 窗口执行：SELECT * FROM testLock FOR UPDATE; -- 加行级锁并对内容进行修改，

不要提交

2)另开一个command窗口，执行：delete from testLock WHERE ID=1;

此时发生死锁（注意此时要另开一个窗口，不然会提示：POST THE CHANGE RECORD TO THE DATABASE. 点yes 后强制commit）：

3)死锁查看：

1.SQL> select ername,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,

s.program from v$session s,v$locked_object l where s.sid = l.session_id;

USER NAME SESSION_ID SERIAL# LOCKWAIT STATUS MACHINE PROGRAM

2.---------- ---------- ---------- -------- -------- ---------------------- ------------

3.SYS 146 104 INACTIVE WORKGROUP\J-THINK PLSQLDev.exe

4.SYS 144 145 20834474 ACTIVE WORKGROUP\J-THINK PLSQLDev.

exe

字段说明：

Username：死锁语句所用的数据库用户；

SID: session identifier，session 标示符，session 是通信双方从开始通信到通信结束期间的一个上下文。

SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。

Lockwait：可以通过这个字段查询出当前正在等待的锁的相关信息。

Status：用来判断session状态。Active：正执行SQL语句。Inactive：等待操作。Killed：被标注为删除。

Machine：死锁语句所在的机器。

Program：产生死锁的语句主要来自哪个应用程序。

4)查看引起死锁的语句：

SQL> select sql_text from v$sql where hash_value in (select sql_hash_value from v$se ssion where sid in (select session_id from v$locked_object));

1.

2.SQL_TEXT

3.------------------------------------------------------------

1.delete from testLock where ID = 1

5)死锁的处理：

SQL> alter system kill session '144,145';

1.

2.System altered

3.

4.Executed in 1.061 seconds

此时在执行delete语句的窗口出现：

SQL> delete from testLock where ID = 1;

1.

2.delete from testLock where ID = 1

3.

4.ORA-00028: 您的会话己被终止

再查看一下死锁，会发现已经没有stauts为active的记录了：

SQL> select ername, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

1.

ERNAME SESSION_ID SERIAL# LOCKWAIT STATUS MACHINE P

ROGRAM

3.------------- ---------- ---------- -------- -------- --------------------------- ----------------

1.SYS 146 104 INACTIVE WORKGROUP\J-THINK PLSQLDev.

exe

2.

3.Executed in 0.032 seconds

发生死锁的语句已经被终止。

二，在外键上没有加索引引起的死锁