Oracle死锁解决方式

死锁是指在Oracle数据库中，两个或多个事务相互等待对方持有的锁资源，导致它们无法继续执行下去，从而形成死锁现象

解决方式如下：

1. 查询死锁信息：

   1. select SID,USERNAME,LOCKWAIT,STATUS,MACHINE,PROGRAM,EVENT from V$SESSION where SID in (select SESSION_ID from V$LOCKED_OBJECT);

2. 查看到被死锁的语句：

   1. select SID,SERIAL#,SQL_TEXT from V$SQL vsql, V$SESSION vsession, V$LOCKED_OBJECT lockedObject where HASH_VALUE = SQL_HASH_VALUE and SID = SESSION_ID;

3. 查看被锁的表

   1. select objects.OWNER,objects.OBJECT_NAME,lockedObject.SESSION_ID,lockedObject.LOCKED_MODE 
      from V$LOCKED_OBJECT lockedObject, DBA_OBJECTS objects 
      where objects.OBJECT_ID = lockedObject.OBJECT_ID;

4. 查看哪个用户哪个进程造成的死锁

   1. 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;

5. 查出锁定表的sid, serial#, 锁的类型

   1. SELECT vsession.SID, vsession.SERIAL#, vsession.USERNAME, vsession.SCHEMANAME, vsession.OSUSER, 
             vsession.PROCESS, vsession.MACHINE, vsession.TERMINAL, vsession.LOGON_TIME, vlock.TYPE
      FROM V$SESSION vsession, V$LOCK vlock 
      WHERE vsession.SID = vlock.SID AND vsession.USERNAME IS NOT NULL ORDER BY SID;

6.  解决方式

   1. 杀死被死锁的session：alter system kill session 'SID,SERIAL#';,SID,SERIAL# 取值对应被死锁的sql的SID,SERIAL#
   2. 释放死锁：alter system kill session 'SID,SERIAL#' immediate ;,SID,SERIAL# 取值对应死锁的sql的SID,SERIAL#
   3. 重启数据库