死锁的产生与解决

1. 死锁：操作双方共同抢夺对方的资源

 1 下列语句按从上到下的先后顺序执行
 2 
 3 SESSION 1：
 4 SQL> update test set sal=8888 where empno=7566;
 5 
 6 1 row updated.
 7 
 8 SESSION 2:
10 SQL> update test set sal=8888 where empno=7788;
11 
12 1 row updated.
13 
14 SESSION 1:
15 SQL> update test set sal=8888 where empno=7788;
16 
17 SESSION 2:
18 SQL> update test set sal=8888 where empno=7566;
19 
20 此时在SESSION 1就会出现死锁现象
21 SQL> update test set sal=8888 where empno=7788;
22 update test set sal=8888 where empno=7788
23                 *
24 ERROR at line 1:
25 ORA-00060: deadlock detected while waiting for resource

2. 查看哪一条语句被死锁，哪一台机器被死锁（用DBA用户运行）

 1 SQL> select username,lockwait,status,program,machine from v$session where sid in (select session_id from v$locked_object);
 2 
 3 USERNAME   LOCKWAIT              STATUS       PROGRAM                                          MACHINE
 4 ---------- -------------------- ---------- -------------------------------------------------- --------------------
 5 SCOTT                           INACTIVE     sqlplus@oracle12c (TNS V1-V3)                     oracle12c
 6 SCOTT      000000006EAD89F0     ACTIVE       sqlplus@oracle12c (TNS V1-V3)                     oracle12c
 7 
 8 
 9 如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。
10 字段说明：
11 Username：死锁语句所用的数据库用户；
12 LockWait：死锁的状态，如果有内容表示被死锁。
13 Status：  状态，active表示被死锁
14 Machine： 死锁语句所在的机器。
15 Program： 产生死锁的语句主要来自哪个应用程序。

　3. 查看被死锁的SQL语句（用DBA用户运行）

1 SQL> select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select sid from v$locked_object));
2 
3 SQL_TEXT
4 ------------------------------------------------------------------------------------------------------------------------------------
5 select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select sid from v$locked_object))
6 update test set sal=8888 where empno=7566  ----- 死锁语句

4. 解决方法：

　　1. 有一方回滚即可（ROLLBACK）

　　2. 管理员杀进程

查看死锁进程
1 SQL> l
2   1  SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
3   2  FROM V$LOCKED_OBJECT l,V$SESSION S
4   3* WHERE l.SESSION_ID=S.SID
5 
6 USERNAME    OBJECT_ID SESSION_ID    SERIAL# ORACLE_USERNAME OS_USER_NAME    PROCESS
7 ---------- ---------- ---------- ---------- --------------- --------------- ----------------
8 SCOTT        73602          63      52931    SCOTT           oracle          71835
9 SCOTT        73602          67        610    SCOTT           oracle          24321

KILL掉这个进程即可，其中（sid=l.session_id）--------#alter system kill session 'sid,serial#';

SQL> alter system kill session '63,52931';

System altered.

SQL>

被kill掉进程的会话，需重新登录
SQL> select * from test;
select * from test
                *
ERROR at line 1:
ORA-00028: your session has been killed


SQL> conn scott/tiger@erp
Connected.

　　3. 如果还不能解决：

SQL> select p.spid from v$session s, v$process p where s.sid=XXX and s.paddr=p.addr; ---- 其中'XXX'用死锁的sid替换:

SQL> exit

　　[oracle@oracle12c ~]$ ps -ef | grep spid

　　oracle 76216 72538 0 11:20 pts/4 00:00:00 grep --color=auto spid

其中spid是这个进程的进程号，kill掉这个Oracle进程