Oracle 死锁及解决办法

最新推荐文章于 2024-04-07 15:26:22 发布

daf380

最新推荐文章于 2024-04-07 15:26:22 发布

阅读量466

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/daf380/article/details/89212803

版权

数据库专栏收录该内容

28 篇文章 0 订阅

订阅专栏

一、构建死锁

1.1、使用sys用户登陆并更新scott.emp表，但是不要提交

[oracle@or11g1 ~]$ 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操作

[oracle@or11g1 ~]$ 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;

这样就可以了！

daf380

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle 死锁及解决办法

一、构建死锁1.1、使用sys用户登陆并更新scott.emp表，但是不要提交[oracle@or11g1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 11 15:46:03 2019Copyright (c) 1982, 2013, Oracle. All rights r...
复制链接

扫一扫