Oracle事务锁导致事务无法提交的问题

今天在修改bug的过程中,一条更新数据的sql执行怎么也执行不了,进入貌似“排队”的情况,一直处于等待的状态,第一直觉是事务锁起到坏作用,后在项目组高手的协助下找到了原因,是数据被锁住了。一般查询是否被锁住可以做如下操作:

以系统数据库管理员的身份登录

1
2
3
4
5
--无连接启动sqlplus
sqlplus /nolog;
--以sysdba的身份连接数据库
conn /as sysdba;

查看被锁的数据对象

1
2
3
4
5
--查询被锁的数据对象
select t2.username, t2.sid, t2.serial#, t2.logon_time
from v$locked_object t1, v$session t2
where t1.session_id = t2.sid
order by t2.logon_time;

执行上述语句后,得到如下的结果:

表明的确是有用户的数据对象(因为当前数据库实例中只有图中的用户处于活动状态)处于锁定状态,然后需要把该用户的进程kill掉即可。

停止被锁事务数据对象的进程

1
2
3
--同一个sid可能会被不同的session使用,所以通过sid和serial number可以唯一定位;
--下面的sid,和serial#替换成上图中对应的值即可
alter system kill session 'sid,serial#';

其实,上面的截图只是一部分,总共有27个被锁的数据对象,如果一行一行进行kill,岂不烦死本宝宝,因此我使用存储过程来实现了:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
declare
cursor kill_lockedobj_crs is(
select t2.sid,t2.serial# serial from v$locked_object
t1,v$session t2 where t1.session_id=t2.sid
);
v_sql varchar2(500);
v_session varchar2(30);
begin
for cont in kill_lockedobj_crs loop
exit when kill_lockedobj_crs%notfound;
v_obj := cont.sid ||','||cont.serial;
v_sql := 'alter system kill session' || v_obj;
execute immediate v_sql;
end loop;
end;
/
commit;

望我的解决方法对大家有所帮助。如有错误,欢迎指正和交流!