mysql大事物监控_监控一个大事务的回滚

我们在大的事务失败时往往面临长时间的回滚，在回滚期间表会被加以TM-3 SX sub-exclusive锁，此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心，不过我们还是有办法预估何时回滚能够完成的，参考中的脚本中的脚本，注意该脚本需要访问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重做日志记录。