杀掉死锁的进程

本文介绍了一个SQL Server存储过程,用于检测并处理死锁情况,包括显示死锁信息及选择性地终止死锁进程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

create   proc   [dbo].[p_lockinfo]
@kill_lock_spid   bit=1,     --是否杀掉死锁的进程,1   杀掉,   0   仅显示
@show_spid_if_nolock   bit=1   --如果没有死锁的进程,是否显示正常进程信息,1   显示,0   不显示
as
declare   @count   int,@s   nvarchar(max),@i   int
select   id=identity(int,1,1),标志,
  进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
  数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
  登陆时间=login_time,打开事务数=open_tran,   进程状态=status,
  工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
  域名=nt_domain,网卡地址=net_address
into   #t   from(
  select   标志= '死锁的进程 ',
    spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
    status,hostname,program_name,hostprocess,nt_domain,net_address,
    s1=a.spid,s2=0
  from   master..sysprocesses   a   join   (
    select   blocked   from   master..sysprocesses   group   by   blocked
    )b   on   a.spid=b.blocked   where   a.blocked=0
  union   all
  select   '|_牺牲品_> ',
    spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
    status,hostname,program_name,hostprocess,nt_domain,net_address,
    s1=blocked,s2=1
  from   master..sysprocesses   a   where   blocked <> 0
)a   order   by   s1,s2

select   @count=@@rowcount,@i=1

if   @count=0   and   @show_spid_if_nolock=1
begin
  insert   #t
  select   标志= '正常的进程 ',
    spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
    open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
  from   master..sysprocesses
  set   @count=@@rowcount
end

if   @count> 0
begin
  create   table   #t1(id   int   identity(1,1),a   nvarchar(max),b   Int,EventInfo   nvarchar(max))
  if   @kill_lock_spid=1
  begin
    declare   @spid   varchar(max),@标志   varchar(max)
    while   @i <=@count
    begin
      select   @spid=进程ID,@标志=标志   from   #t   where   id=@i
      insert   #t1   exec( 'dbcc   inputbuffer( '+@spid+ ') ')
      if   @标志= '死锁的进程 '   exec( 'kill   '+@spid)
      set   @i=@i+1
    end
  end
  else
    while   @i <=@count
    begin
      select   @s= 'dbcc   inputbuffer( '+cast(进程ID   as   varchar)+ ') '   from   #t   where   id=@i
      insert   #t1   exec(@s)
      set   @i=@i+1
    end
  select   a.*,进程的SQL语句=b.EventInfo
  from   #t   a   join   #t1   b   on   a.id=b.id
end

 

<think>我们被要求监控Oracle数据库中的死锁杀掉死锁进程。根据提供的引用内容,我们可以总结出以下步骤: 1. 监控死锁:通过查询相关动态性能视图来检测死锁。 2. 获取死锁会话信息:包括SID(会话ID)、SERIAL#(序列号)等。 3. 终止死锁进程:使用`alter system kill session`命令终止会话,如果失败则使用更彻底的方法(如从操作系统层面终止进程)。 我们将按照以下步骤进行: ### 步骤1:监控死锁 我们可以通过查询`v$locked_object`和`v$session`视图来查找死锁相关的会话。此外,也可以查询`v$lock`视图来获取锁信息。 ### 步骤2:获取死锁会话的详细信息 我们需要获取死锁会话的SID和SERIAL#,以便终止会话。 ### 步骤3:终止死锁进程 使用`alter system kill session`命令终止会话。如果该命令无法终止(例如会话处于“KILLED”状态但未释放资源),则需要从操作系统层面终止对应的进程。 ### 具体操作 #### 1. 查询死锁会话 以下查询可以获取当前死锁的会话信息(包括SID、SERIAL#、用户名、锁定的对象等): ```sql SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.logon_time, lo.object_id, o.object_name FROM v$locked_object lo JOIN v$session s ON lo.session_id = s.sid JOIN dba_objects o ON lo.object_id = o.object_id; ``` #### 2. 查询死锁会话正在执行的SQL 我们可以通过以下查询获取死锁会话正在执行的SQL语句,这有助于分析死锁原因: ```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 ) ); ``` #### 3. 终止死锁会话 使用以下命令终止会话(将`<sid>`和`<serial#>`替换为实际值): ```sql ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE; ``` 注意:`IMMEDIATE`选项会立即终止会话,而不等待事务回滚完成(但Oracle仍会进行回滚)。 #### 4. 如果上述方法无效,则从操作系统层面终止进程 如果会话无法被终止(例如,状态一直为“KILLED”),我们需要找到对应的操作系统进程并终止它。 首先,通过以下查询获取操作系统的进程ID(SPID): ```sql SELECT p.spid, s.sid, s.serial#, s.username FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = <sid> AND s.serial# = <serial#>; ``` 然后在操作系统层面(Linux/Unix)使用`kill`命令终止进程: ```bash kill -9 <spid> ``` 注意:在Windows系统中,可以通过任务管理器终止对应的线程。 ### 自动监控脚本 我们可以编写一个PL/SQL脚本或Shell脚本来自动监控死锁并终止会话。以下是一个简单的Shell脚本示例(需要连接到数据库执行): ```bash #!/bin/bash # 监控死锁并终止会话的脚本 # 设置Oracle环境变量 export ORACLE_SID=your_sid export ORACLE_HOME=/path/to/oracle/home export PATH=$ORACLE_HOME/bin:$PATH # 查询死锁会话并生成kill命令 sqlplus -s / as sysdba <<EOF SET HEADING OFF SET FEEDBACK OFF SET PAGESIZE 0 SET LINESIZE 1000 SPOOL kill_sessions.sql SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command FROM v\$locked_object lo JOIN v\$session s ON lo.session_id = s.sid; SPOOL OFF @kill_sessions.sql EOF ``` 注意:此脚本会生成一个包含所有终止会话命令的SQL文件,并立即执行。请谨慎使用,确保只在需要时运行。 ### 死锁预防建议 根据引用[4],我们可以采取以下措施预防死锁: - 合理设计应用程序,确保以相同的顺序访问多个表。 - 使用短事务,减少锁定时间。 - 在事务中按固定顺序访问对象。 - 使用SELECT ... FOR UPDATE NOWAIT或WAIT选项来避免无限期等待。 - 适当使用索引,减少锁冲突。 ### 总结 1. **监控死锁**:通过查询`v$locked_object`和`v$session`视图。 2. **终止会话**:使用`ALTER SYSTEM KILL SESSION`命令。 3. **彻底终止**:如果上述命令无效,则通过操作系统终止进程。 4. **预防死锁**:优化应用程序设计和事务管理。 通过以上步骤,我们可以有效地监控和解决Oracle数据库中的死锁问题。 §§相关问题§§ 1. 如何预防Oracle数据库死锁? 2. Oracle中除了`v$locked_object`,还有哪些视图可以用于监控锁和死锁? 3. 在Oracle中,如何分析死锁产生的根本原因?</think>### 监控Oracle死锁并终止进程的完整方案 在Oracle数据库中监控死锁并终止相关进程需要系统权限(建议使用SYSDBA账户)。以下是逐步操作指南: --- #### **1. 实时监控死锁会话** 使用以下SQL查询检测当前死锁会话(需要`SELECT ANY DICTIONARY`权限): ```sql SELECT s.sid AS "会话ID", s.serial# AS "序列号", s.username AS "用户名", s.osuser AS "系统用户", s.machine AS "客户端机器", l.object_id AS "锁定对象ID", o.object_name AS "锁定对象名", s.sql_id AS "阻塞SQL ID" FROM v$locked_object l JOIN v$session s ON l.session_id = s.sid JOIN dba_objects o ON l.object_id = o.object_id WHERE s.status = 'ACTIVE'; ``` **输出示例**: ``` 会话ID | 序列号 | 用户名 | 系统用户 | 客户端机器 | 锁定对象ID | 锁定对象名 | 阻塞SQL_ID ------|-------|--------|---------|------------|-----------|------------|----------- 131 | 241 | APP_USER | oracle | SERVER01 | 123456 | ORDERS | 7x2g9b8a ``` --- #### **2. 终止死锁会话** 根据监控结果终止进程(需`ALTER SYSTEM`权限): ##### **方法1:常规终止(推荐)** ```sql ALTER SYSTEM KILL SESSION '131,241' IMMEDIATE; -- 格式: 'sid,serial#' ``` - ✅ **优点**:安全释放资源,事务自动回滚 - ⚠️ **注意**:`IMMEDIATE`参数强制立即终止 ##### **方法2:彻底终止(当常规方法失败时)** ```sql -- 查询操作系统进程ID SELECT p.spid AS "系统进程ID" FROM v$session s, v$process p WHERE s.sid = 131 AND s.serial# = 241 AND s.paddr = p.addr; -- 在操作系统层终止(Linux示例) $ kill -9 12345 -- 替换为实际SPID ``` > 📌 **适用场景**:会话状态长期为"KILLED"但未释放资源时使用[^1][^3] --- #### **3. 自动化监控脚本** 创建定时任务脚本(保存为`monitor_deadlock.sql`): ```sql BEGIN FOR rec IN ( SELECT s.sid, s.serial#, s.username FROM v$locked_object lo JOIN v$session s ON lo.session_id = s.sid WHERE s.status = 'ACTIVE' AND s.last_call_et > 300 -- 阻塞超过5分钟 ) LOOP EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE'; DBMS_OUTPUT.PUT_LINE('Killed session: ' || rec.sid || ',' || rec.serial#); END LOOP; END; / ``` 通过crontab每小时执行: ```bash 0 * * * * sqlplus / as sysdba @/path/to/monitor_deadlock.sql ``` --- #### **4. 死锁预防关键措施** 1. **事务设计优化** - 所有事务按相同顺序访问表(例如先更新A表再更新B表) - 避免长事务,批量操作分多次提交 2. **索引策略** ```sql CREATE INDEX idx_orders_customer ON orders(customer_id); -- 减少全表扫描锁冲突 ``` 3. **应用程序层控制** - 使用`SELECT ... FOR UPDATE NOWAIT`(非阻塞锁) - 设置锁等待超时:`ALTER SYSTEM SET ddl_lock_timeout=30;` 4. **资源限制** ```sql CREATE PROFILE app_user_limit LIMIT IDLE_TIME 30 SESSIONS_PER_USER 10; ``` --- #### **5. 死锁分析工具** - **查看死锁历史**: ```sql SELECT * FROM dba_hist_active_sess_history WHERE event = 'enq: TX - row lock contention'; ``` - **生成死锁图**: ```sql ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level 10'; -- 跟踪文件位置:$ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace ``` > ⚠️ **重要提示**:生产环境操作前务必在测试环境验证,终止会话可能导致事务回滚[^2][^4]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值