oracle问题之死锁 （一）

【前言】

遇到 oracle 异常 和 解决实践 系列文章 整理分享

杂症一、oracle死锁

一、症状：

执行SQL或程序时，程序没有响应或SQL执行一直处于执行状态，没有成功，也没有报错。

 

二、病理：

当对数据库某个表的某一记录做更新或删除等操作，执行完毕后该条语句不提交事务，假如其他人同时也对该数据库执行一条对于这一记录做更新操作的语句。则在执行的时候就会处于等待状态，便陷入死锁，一直没有执行成功，也没有报错。

 

三、病因定位：

1）检查数据库确定 是否 真实存在死锁，若有 哪台机器哪个程序。

SQL>select username, lockwait, status, machine, program

 from v$session

where sid in (select session_id from v$locked_object);

 

Username：死锁语句所用的数据库用户；

Lockwait   ：死锁的状态，如果有内容表示被死锁。

Status       ：状态，active表示被死锁

Machine   ：死锁语句所在的机器。

Program   ：产生死锁的语句主要来自哪个应用程序。

 

2）确定死锁后，还可以检查是哪个语句产生死锁等待。

SQL>select sql_text

 from v$sql

where hash_value in

      (select sql_hash_value

         from v$session

        where sid in (select session_id from v$locked_object))

 

四、治疗：

 

方式一：查询未提交事务的SQL，大概率是其引起。

SQL>select s.sid,

      s.username,

      s.osuser,

      s.program,

      to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss') as LOGON_TIME,

      to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss') as START_DATE,

      s.status,

      (select q.SQL_TEXT

         from v$sql q

        where q.LAST_ACTIVE_TIME = t.START_DATE

          and rownum <= 1) as SQL_TEXT

 from v$session s, v$transaction t

where s.sADDR = t.SES_ADDR;

 

可以检查到是谁在什么时候执行哪条SQL 没有提交事务，找到对应的人，让其提交事务，提交完成便问题解决。

 

方式二：若找不到对应的人，则可以通过kill掉死锁的session进程

SQL>SELECT l.SESSION_ID,

      l.OS_USER_NAME,

      s.USERNAME,

      l.OBJECT_ID,  

      l.ORACLE_USERNAME

 FROM v$locked_object l , v$session s

WHERE l.SESSION_ID = s.SID

 

其中

进程 SID=10 的SQL为 未提交事务的SQL；

进程 SID=133 的SQL为 死锁中的SQL

 

SQL>select sql_text

 from v$sql

where hash_value in

      (select sql_hash_value

         from v$session

        where sid in (133))

 

SQL> alter system kill session '10,133';

 

方式三：获取oracle进程，直接kill掉oracle进程然后重启不建议（或 直接重启数据库）

# ps -ef|grep ora_dbw0_$Oracle_SID

# kill -9 sid

重启数据库

# su oracle

# sqlplus /nolog

SQL> conn /as sysdba

SQL> shutdown immediate;

SQL> startup;

转自：https://blog.csdn.net/heshushun/article/details/80422137