死锁的处理实验

最新推荐文章于 2023-11-19 11:05:30 发布

转载最新推荐文章于 2023-11-19 11:05:30 发布 · 347 阅读

文章标签：

#数据库

1.死锁是什么

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

下面直接看实验！

2.实验环境准备

----先创建一张表，并插入数据

shall@ORCL>create table t(locks int);

Table created.

shall@ORCL>insert into t values (100);

1 row created.

shall@ORCL>commit;

Commit complete.

shall@ORCL>select * from t;

LOCKS

----------

100

3.死锁环境

----会话一

shall@ORCL>select userenv('sid') from dual;

USERENV('SID')

--------------

140

shall@ORCL>update t set locks=101 where locks=100;

1 row updated.

shall@ORCL>select * from t;

LOCKS

----------

101

----会话二

shall@ORCL>select userenv('sid') from dual;

USERENV('SID')

--------------

----产生死锁

shall@ORCL>update t set locks=102 where locks=100;

4.查看死锁表

----查看哪些表被锁定

SELECT A.OWNER,A.OBJECT_NAME,

B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,

C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM

FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C

WHERE A.OBJECT_ID = B.OBJECT_ID

AND B.PROCESS = C.PROCESS

ORDER BY 1,2;

/*----查看死锁表

select b.owner,b.object_name,a.session_id,a.locked_mode

from v$locked_object a,dba_objects b

where b.object_id = a.object_id;

----查看session

select b.username,b.sid,b.serial#,logon_time

from v$locked_object a,v$session b

where a.session_id = b.sid order by b.logon_time;

sys@ORCL>set linesize 999

sys@ORCL>SELECT A.OWNER,A.OBJECT_NAME,

2 B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,

3 C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM

4 FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C

5 WHERE A.OBJECT_ID = B.OBJECT_ID

6 AND B.PROCESS = C.PROCESS

7 ORDER BY 1,2;

OWNER OBJECT_NAME XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE MACHINE STATUS SERVER SID SERIAL# PROGRAM

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ ----------- ---------------------------------------------------------------- -------- --------- ---------- ---------- ------------------------------------------------

SHALL T 0 0 0 11 SHALL oracle 31919 3 zyx.test.com ACTIVE DEDICATED 11 8853 sqlplus@zyx.test.com (TNS V1-V3)

SHALL T 21 7 208 140 SHALL oracle 28309 3 zyx.test.com INACTIVE DEDICATED 140 32037 sqlplus@zyx.test.com (TNS V1-V3)

----查看未提交的事务

sys@ORCL>select a.sid, a.blocking_session, a.last_call_et,a.event, object_name, dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" , c.sql_text, c.sql_fulltext from v$session a,v$sqlarea c ,dba_objects,v$datafile where a.blocking_session is not null and a.sql_hash_value = c.hash_value and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

SID BLOCKING_SESSION LAST_CALL_ET EVENT OBJECT_NAME rowid

---------- ---------------- ------------ ---------------------------------------------------------------- ------------------

SQL_TEXT

------------------------------------------------------------------------------------------------

SQL_FULLTEXT

--------------------------------------------------------------------------------

11 140 326 enq: TX - row lock contention T AAAWHOAAEAAAOvXAAA

update t set locks=102 where locks=100

5.死锁处理方法

----方法1：会话一提交

shall@ORCL>select userenv('sid') from dual;

USERENV('SID')

--------------

140

shall@ORCL>commit;

Commit complete.

---会话二：

shall@ORCL>update t set locks=102 where locks=100;

0 rows updated.

----查看未提交事务

sys@ORCL>select a.sid,

2 a.blocking_session,

3 a.last_call_et,a.event,

4 object_name,

5 dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" ,

6 c.sql_text,

7 c.sql_fulltext

8 from v$session a,v$sqlarea c ,dba_objects,v$datafile

9 where a.blocking_session is not null

10 and a.sql_hash_value = c.hash_value

11 and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

no rows selected

----查看哪些表被锁定

sys@ORCL>SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C

5 WHERE A.OBJECT_ID = B.OBJECT_ID

6 AND B.PROCESS = C.PROCESS

7 ORDER BY 1,2;

OWNER OBJECT_NAME XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE MACHINE STATUS SERVER SID SERIAL# PROGRAM

SHALL T 0 0 0 11 SHALL oracle 31919 3 zyx.test.com INACTIVE DEDICATED 11 8853 sqlplus@zyx.test.com (TNS V1-V3)

----方法2：kill会话进程

------环境准备

shall@ORCL>select * from t;

LOCKS

----------

101

----会话一

shall@ORCL>update t set locks=102 where locks=101;

1 row updated.

----会话二

shall@ORCL>update t set locks=1000 where locks=101;

-------查找死锁语句

sys@ORCL>select a.sid,a.blocking_session,a.last_call_et,a.event,object_name,dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" , c.sql_text,c.sql_fulltext from v$session a,v$sqlarea c ,dba_objects,v$datafile where a.blocking_session is not null and a.sql_hash_value = c.hash_value and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

SID BLOCKING_SESSION LAST_CALL_ET EVENT OBJECT_NAME rowid

---------- ---------------- ------------ ---------------------------------------------------------------- ------------------

SQL_TEXT

------------------------------------------------------------------------------------------------

SQL_FULLTEXT

--------------------------------------------------------------------------------

11 140 120 enq: TX - row lock contention T AAAWHOAAEAAAOvXAAA

update t set locks=1000 where locks=101

sys@ORCL>SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE, C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C WHERE A.OBJECT_ID = B.OBJECT_ID AND B.PROCESS = C.PROCESS ORDER BY 1,2;

OWNER OBJECT_NAME XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE MACHINE STATUS SERVER SID SERIAL# PROGRAM

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------------------------------------

SHALL T 0 0 0 11 SHALL oracle 31919 3 zyx.test.com ACTIVE DEDICATED 11 8853 sqlplus@zyx.test.com (TNS V1-V3)

SHALL T 16 5 204 140 SHALL oracle 28309 3 zyx.test.com

------根据情况，kill某个会话即可。kill之前确定是否可以kill

sys@ORCL>alter system kill session '11,8853';

System altered.

------kill之后，会话11抛出报错，进行事务的回滚

shall@ORCL>update t set locks=1000 where locks=101;

ERROR:

ORA-03114: not connected to ORACLE

update t set locks=1000 where locks=101

ERROR at line 1:

ORA-00028: your session has been killed

----方法3：kill系统进程

------环境准备

--------接上

shall@ORCL>conn shall/shall

Connected.

shall@ORCL>select userenv('sid') from dual;

USERENV('SID')

--------------

shall@ORCL>update t set locks=2000 where locks=101;

------查找死锁语句

sys@ORCL>select a.sid,a.blocking_session,a.last_call_et,a.event,object_name,dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" , c.sql_text,c.sql_fulltext from v$session a,v$sqlarea c ,dba_objects,v$datafile where a.blocking_session is not null and a.sql_hash_value = c.hash_value and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

SID BLOCKING_SESSION LAST_CALL_ET EVENT OBJECT_NAME rowid

---------- ---------------- ------------ ----------------------------------------------------------------

SQL_TEXT

---------------------------------------------------------------------------------------------------------------------------------

SQL_FULLTEXT

--------------------------------------------------------------------------------

11 140 33 enq: TX - row lock contention T AAAWHOAAEAAAOvXAAA

update t set locks=2000 where locks=101

sys@ORCL>SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE, C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C WHERE A.OBJECT_ID = B.OBJECT_ID AND B.PROCESS = C.PROCESS ORDER BY 1,2;

OWNER OBJECT_NAME XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE MACHINE STATUS SERVER SID SERIAL# PROGRAM

SHALL T 0 0 0 11 SHALL oracle 31919 3 zyx.test.com ACTIVE DEDICATED 11 8898 sqlplus@zyx.test.com (TNS V1-V3)

SHALL T 16 5 204 140 SHALL oracle 28309 3 zyx.test.com INACTIVE DEDICATED 140 32037 sqlplus@zyx.test.com (TNS V1-V3)

------确定需要kill的会话，然后去找对应的系统进程

sys@ORCL>select sid,process,spid from v$session s,v$process p where s.paddr=p.addr and sid=11;

SID PROCESS SPID

---------- ------------------------ ------------------------

11 31919 33498

------kill系统进程（linux下）

sys@ORCL>ho kill -9 33498

------此时会话11，抛出报错

shall@ORCL>update t set locks=2000 where locks=101;