Oracle 死锁及解决办法
一、构建死锁
1.1、使用sys用户登陆并更新scott.emp表，但是不要提交
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 11 15:46:03 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 120
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> update scott.emp set sal=0;
14 rows updated.
SQL>1.2、使用scott用户执行delete操作
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 11 15:44:19 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter user scott account unlock identified by oracle;
User altered.
SQL> conn scott/oracle;
Connected.
SQL>
SQL>
SQL> set linesize 120
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> delete from emp where sal='5000';
至此，死锁产生了！
二、通过等待时间来找出死锁的原因
2.1、通过查看等待事件来找出问题原因：select * from v$system_wait_class order by time_waited desc;
SQL> select * from v$system_wait_class order by time_waited desc;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- ---------------------------------------------------------------- ----------- ----------- -------------- --------------
2723168908 6 Idle 596534 23315995 3042 1930587
4217450380 1 Application 1106 59652 1035 59628
1740759767 8 User I/O 17015 36980 5396 23605
1893977003 0 Other 301901 10255 2938 3089
4108307767 9 System I/O 19135 9716 244 420
3875070507 4 Concurrency 31145 1357 2502 553
3871361733 11 Cluster 2983 1326 1331 509
4166625743 3 Administrative 3 70 2 20
3386400367 5 Commit 9 15 8 14
3290255840 2 Configuration 22 2 6 1
2000153315 7 Network 1926 0 1876 0
11 rows selected.
SQL>/
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- ---------------------------------------------------------------- ----------- ----------- -------------- --------------
2723168908 6 Idle 599583 23488205 3050 1936358
4217450380 1 Application 1112 62408 1041 62384
1740759767 8 User I/O 17072 37002 5396 23605
1893977003 0 Other 302696 10299 2941 3107
4108307767 9 System I/O 19247 9739 244 420
3875070507 4 Concurrency 31186 1357 2502 553
3871361733 11 Cluster 2983 1326 1331 509
4166625743 3 Administrative 3 70 2 20
3386400367 5 Commit 9 15 8 14
3290255840 2 Configuration 22 2 6 1
2000153315 7 Network 1934 0 1884 0
11 rows selected.
SQL> /
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- ---------------------------------------------------------------- ----------- ----------- -------------- --------------
2723168908 6 Idle 605844 23751088 3066 1954101
4217450380 1 Application 1124 68170 1053 68146
1740759767 8 User I/O 17177 37056 5396 23605
1893977003 0 Other 304295 10306 2941 3107
4108307767 9 System I/O 19432 9784 244 420
3875070507 4 Concurrency 31258 1359 2502 553
3871361733 11 Cluster 2995 1326 1331 509
4166625743 3 Administrative 3 70 2 20
3386400367 5 Commit 9 15 8 14
3290255840 2 Configuration 22 2 6 1
2000153315 7 Network 1950 0 1900 0
11 rows selected.
SQL>
2.2、确定了是应用程序这个大类后，然后再来根据WAIT_CLASS#找出具体小类
SQL> select event from v$SYSTEM_EVENT where wait_class#=1;
EVENT
----------------------------------------------------------------
enq: TM - contention
enq: TX - row lock contention
enq: RC - Result Cache: Contention
SQL*Net break/reset to client
enq: UL - contention
SQL>
2.3、进一步确认等待是由哪个小类造成的：
select EVENT,TOTAL_WAITS,TIME_WAITED from V$SYSTEM_EVENT where wait_class#=1;SQL> select EVENT,TOTAL_WAITS,TIME_WAITED from V$SYSTEM_EVENT where wait_class#=1;
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
enq: TM - contention 87 7
enq: TX - row lock contention 1 101602
enq: RC - Result Cache: Contention 1 19
SQL*Net break/reset to client 1108 18
enq: UL - contention 1 0
SQL>2.4、再通过事件去搜寻是哪个会话
select sid,event from v$session where event like '%enq%row%';SQL> select sid,event from v$session where event like '%enq%row%';
SID EVENT
---------- ----------------------------------------------------------------
133 enq: TX - row lock contention
SQL>
2.5、再看看133号会话是谁
select sid,username,sql_id from v$session where sid=133;SQL> select sid,username,sql_id from v$session where sid=133;
SID USERNAME SQL_ID
---------- ------------------------------ -------------
133 SCOTT d6u0atr2tnby0
SQL>2.6、通过SQL_ID找到SQL语句
select sql_text from v$sql where sql_id='d6u0atr2tnby0';SQL> select sql_text from v$sql where sql_id='d6u0atr2tnby0';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from emp where sal='5000'
SQL>2.7、确定是谁和他发生了冲突
select SID,SERIAL#,USERNAME,status from V$SESSION where SID in (select BLOCKING_SESSION from v$SESSION);SQL> select SID,SERIAL#,USERNAME from V$SESSION where SID in (select BLOCKING_SESSION from v$SESSION);
SID SERIAL# USERNAME
---------- ---------- ------------------------------
12 33 SYS
SQL>2.8、杀掉和他冲突的用户
alter system kill session '12,33' immediate;SQL> alter system kill session '12,33' immediate;
System altered.
SQL>2.9、再看看被阻塞的scott用户，已经delete成功了
SQL> delete from emp where sal='5000';
1 row deleted.
SQL>2.10、如果要快速释放资源，执行如下语句
select PADDR from v$session where SID=12 and SERIAL#=33;select SPID,USERNAME from v$process where ADDR='XXXX'; kill -9 SPID至此，死锁问题已解决！
三、如果嫌上面太啰嗦，也可以这样来做
执行如下SQL
select SID,SERIAL#,USERNAME,status from V$SESSION where SID in (select BLOCKING_SESSION from v$SESSION);或者：
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;然后直接
alter system kill session 'SID,SERIAL#' immediate;这样就可以了！