oracle死锁语句查询

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

Oracle 10g, PL/SQL version 9.2

[sql]  view plain copy

1. CREATE TABLE testLock(  
2. ID NUMBER,  
3. test VARCHAR(100)  
4. )  
5. COMMIT  
6.   
7. INSERT INTO testLock VALUES(1,'test1');  
8. INSERT INTO testLock VALUES(2,'test2');  
9. COMMIT;  

[sql]  view plain copy

1. SELECT * FROM testLock   
2.   
3.         ID TEST  
4. ---------- ----------------------------------  
5.          1 test1  
6.          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)死锁查看：

[sql]  view plain copy

1. <p> </p><p>  
2. SQL>  select s.username,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;</p><p>USERNAME    SESSION_ID  SERIAL#    LOCKWAIT STATUS   MACHINE                 PROGRAM  
3. ----------  ----------  ---------- -------- -------- ----------------------  ------------  
4. SYS         146         104                 INACTIVE WORKGROUP\J-THINK       PLSQLDev.exe  
5. SYS         144         145        20834474 ACTIVE   WORKGROUP\J-THINK       PLSQLDev.exe</p><p> </p>  

字段说明：
Username：死锁语句所用的数据库用户；
SID: session identifier， session 标示符，session 是通信双方从开始通信到通信结束期间的一个上下文。
SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。越后面执行的sql语句serial#的越大
Lockwait：可以通过这个字段查询出当前正在执行的sql语句已经等待的时间。
Status：用来判断session状态。Active：正执行SQL语句，就是被死锁住的语句，无法继续执行下去。Inactive：等待操作，未提交的操作。Killed：被标注为删除。

其中
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。

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

[sql]  view plain copy

1. SQL>  select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));  
2.   
3. SQL_TEXT  
4. ------------------------------------------------------------  

[sql]  view plain copy

1. delete from testLock where  ID = 1  

5)死锁的处理：

[sql]  view plain copy

1. SQL> alter system kill session '144,145';  
2.   
3. System altered  
4.   
5. Executed in 1.061 seconds  

session中两个参数分别是session_id和serial#

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

[sql]  view plain copy

1. SQL> delete from testLock where  ID = 1;  
2.   
3. delete from testLock where  ID = 1  
4.   
5. ORA-00028: 您的会话己被终止  

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

[sql]  view plain copy

1. SQL>  select s.username, 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;  
2.   
3. USERNAME                 SESSION_ID SERIAL#  LOCKWAIT STATUS   MACHINE             PROGRAM  
4. ------------- ---------- ---------- -------- -------- ---------------------------  ----------------  

[sql]  view plain copy

1. SYS                      146        104               INACTIVE WORKGROUP\J-THINK   PLSQLDev.exe  
2.   
3. Executed in 0.032 seconds  

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

引用：

教您如何检查oracle死锁

Oracle死锁的处理

Oracle 动态性能表 v$session & v$process