模拟Oracle锁等待与死锁

模拟Oracle锁等待与死锁

1、用途

本文描述在Oracle数据库发生锁等待时，如果查看锁信息，以及如何解锁。

2、模拟过程

2.1、创建测试表

-- 第1个程序 ---------------------------------------------
-- 创建测试表
CREATE TABLE t1(
id INT,
name VARCHAR(10)
)
;

-- 写入测试数据，并提交
DELETE FROM t1;
insert into t1(id,name) values(1,'a');
insert into t1(id,name) values(2,'b');
commit;

2.2、模拟锁等待

-- 第1个sqlplus程序 ---------------------------------------------
-- 查看当前session，并更新数据：
select userenv('sid') from dual;
update t1 set name = 'a1' where id = 1;

-- 第2个sqlplus程序 ---------------------------------------------
-- 再打开一个sqlplus程序，执行sql，此时处于锁等待状态：
select userenv('sid') from dual;
update t1 set name = 'a2' where id = 1;

2.3、模拟死锁

-- 第1个sqlplus程序 ---------------------------------------------
-- 查看当前session，并更新数据：
select userenv('sid') from dual;
update t1 set name = 'a1' where id = 1;

-- 第2个sqlplus程序 ---------------------------------------------
-- 打开第2个sqlplus程序，执行sql：
select userenv('sid') from dual;
update t1 set name = 'b2' where id = 2;

-- 第1个sqlplus程序 ---------------------------------------------
-- 回到第1个sqlplus，执行sql，此时处于锁等待状态：
update t1 set name = 'b1' where id = 2;

-- 第2个sqlplus程序 ---------------------------------------------
-- 回到第2个sqlplus，执行sql，此时处于锁等待状态：
update t1 set name = 'a2' where id = 1;

-- 第1个sqlplus程序 ---------------------------------------------
-- 回到第1个sqlplus，发现提示检测到死锁；
update t1 set name = 'b1' where id = 2
              *
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁

回滚事务,释放锁。
rollback;

2.4、分析锁

-- 当发生锁等待时，使用如下sql查询锁等待详情：
select distinct s.sid
    , s.serial#
    , s.blocking_session /*当前被哪个sid阻塞*/
    , s.client_info /*客户端信息（ip）*/
    , s.machine /*主机名称*/
    , s.osuser  /*操作系统用户*/
    , s.program /*程序名称*/
    , s.seconds_in_wait /*等待时长（秒）*/
    , s.event /*事件*/
    , s.status /*状态*/
    , s.wait_class /*等待类*/
    , lo.oracle_username /*oracle用户*/
    , lo.locked_mode /*锁定模式*/
    , ao.object_name /*锁定的对象*/
    , s1.sql_text as sql_current  /*当前执行的sql*/
    , s2.sql_text as sql_previous /*前一个执行的sql*/
from v$locked_object lo
    , dba_objects ao
    , v$session s
    left join v$sql s1 on s1.sql_id = s.sql_id
    left join v$sql s2 on s2.sql_id = s.prev_sql_id
where ao.object_id = lo.object_id
    and lo.session_id = s.sid
;

锁等待情况如下图所示：

2.5、解除锁等待

可以通过以下2种方式进行锁处理
1、提交或回滚第1个程序

commit;
rollback;

2、手工结束第1个session

alter system kill session 'sid,serial#';

观察第2个程序可以继续执行。

2.6、小结

本文仅用于模拟锁等待的发生与解锁过程。实际项目中，可以通过sql查询锁等待列表，导出问题数据，并及时解锁，事后再进行问题分析。