监控一个大事务的回滚

我们在大的事务失败时往往面临长时间的回滚,在回滚期间表会被加以TM-3 SX sub-exclusive锁,此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心,不过我们还是有办法预估何时回滚能够完成的,参考中的脚本<Script:when transaction will finish rollback>中的脚本,注意该脚本需要访问x$ktuxe内部视图,所以需要以sysdba身份方能执行。

SQL> select * from v$lock where type in ('TM','TX');
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0ED0F30C 0ED0F33C          9 TM      13865          0          3          0       3757          0
2C3975FC 2C39763C          9 TX      65557        677          6          0       3757          0
SQL> select object_name,object_type from dba_objects where object_id=13865;
OBJECT_NAM OBJECT_TYPE
---------- -------------------
SAMPLE     TABLE
因为表上存在TM-3锁,所以此时是无法对表执行需要持有TM-6 exclusive排它锁的DDL操作的
SQL> drop table sample;
drop table sample
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> select * from sample where rownum=1 for update nowait;
select * from sample where rownum=1 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
rollback完成之前相关行上的row level lock不会被释放,因此dml操作会被block
SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
XIDUSN    XIDSLOT     XIDSQN STATUS
---------- ---------- ---------- ----------------
1         21        677 ACTIVE
从v$tranasction视图中可以看到事务1.21.677处于active状态
使用set time on命令显示当前时间
SQL> set time on;
20:54:26 SQL>
set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt
declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe  x,
sys.v_$transaction  t,
sys.v_$session  s
where
x.inst_id = userenv('Instance') and
x.ktuxesta = 'ACTIVE' and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name  varchar2(30);
xid_usn    number;
xid_slot   number;
xid_sqn    number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv('Instance') and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn  || '.' ||
xid_slot || '.' ||
xid_sqn  ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;
/
MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:46:45 25-MAY-2011

以上脚本给出了估计的1.21.677事务的回滚结束时间为21:46:45,即还有50分钟,
注意这只是根据之前单位时间内rollback的进度所得到的一个估算值,所以并不精确,但还是可以做为一个参考
再次运行以上脚本,会发现2次的结果不一定一致
SQL> /
MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:35:47 25-MAY-2011

在产品数据库中应当尽可能避免出现大事务回滚的现象,因为除去lock的影响外,large transaction rollback还会导致CPU使用率大幅上升并产生往往比事务本身所产生的更多的redo重做日志记录。

沪ICP备14014813号

沪公网安备 31010802001379号