最近客户遇到了for update回退导致业务阻塞的情况，下面是在自己的环境中模拟for update死事务的回退及回退过程中占用的行锁与是否影响其它的dml语句。
1 环境介绍
|
www.htz.pw > select * from v$version where rownum=1;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
www.htz.pw > !uname -a Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux |
2 创建测试表
这里使用dba_objects视图来创建测试表，并大量插入数据
|
CREATE TABLE htz.htz ( ID NUMBER, OWNER VARCHAR2 (30), OBJECT_NAME VARCHAR2 (128), SUBOBJECT_NAME VARCHAR2 (30), OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER, OBJECT_TYPE VARCHAR2 (30), CREATED DATE, LAST_DDL_TIME DATE, TIMESTAMP VARCHAR2 (30), STATUS VARCHAR2 (30), TEMPORARY VARCHAR2 (30), GENERATED VARCHAR2 (30), SECONDARY VARCHAR2 (30), NAMESPACE VARCHAR2 (30), EDITION_NAME VARCHAR2 (30) ) www.cdhtz.com > alter system set “_disable_logging”=true scope=spfile; System altered. www.cdhtz.com > startup force; /* Formatted on 2014/11/13 13:38:39 (QP5 v5.240.12305.39446) */ BEGIN FOR i IN 1 .. 2000 LOOP INSERT INTO htz.htz SELECT htz.htz_sequence.NEXTVAL, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME FROM dba_objects; COMMIT; END LOOP; END; / 表的大小为18G，有点大哦。 www.cdhtz.com > select segment_name,bytes/1024/1024/1024 from dba_segments where owner=’HTZ’ and segment_name=’HTZ’;
SEGMENT_NA BYTES/1024/1024/1024 ———- ——————– HTZ 18.7705078 www.cdhtz.com > alter database tempfile 1 autoextend on maxsize 31g; www.cdhtz.com > alter database datafile 4 autoextend on maxsize 31g; |
3 kill for update进程
|
www.cdhtz.com > select /*+ parallel(a 4) */ * from htz.htz a where mod(id,2)=0 for update; 等SQL还回结果后，KILL掉for update的进程，事务等待smon进程来回退。 |
4 现象分析
4.1 smon持有锁情况
|
www.cdhtz.com > oradebug setospid 13491 Oracle pid: 13, Unix process pid: 13491, image: oracle@orcl9i (SMON) www.cdhtz.com > oradebug tracefile_name; /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_smon_13491.trc www.cdhtz.com > oradebug event 10704 trace name context forever,level 12; Statement processed. 在trace文件中，可以看到下面的信息，这里可以看到很多TX级别的锁，模式都为6，但是在整个TRACE文件中，并找到在对象HTZ上面的任何TM级别的锁，说明SMON在事务回退过程中，只占用行级别的锁，不占用TM级别的锁，但是正常情况下获取TX级别的锁之前必须获取TM级别的锁。另外为什么说这么的TX级别说就是之前的for update语句占用的TX锁，这个问题会在另外说明的。 *** 2014-11-14 10:27:08.161 ksqcmi: TX,100020,ad mode=6 timeout=0 ksqcmi: returns 51 ksqgtl: RETURNS 51
*** 2014-11-14 10:27:08.161 ksqgtl *** TX-00100020-000000ad mode=6 flags=0x10001 timeout=0 *** ksqgtl: no transaction ksqgtl: use existing ksusetxn DID ksqgtl: ksqlkdid: 0001-000D-00000002
*** 2014-11-14 10:27:08.161 *** ksudidTrace: ksqgtl ksusesdi: 0001-000D-00000003 ksusetxn: 0001-000D-00000002
*** 2014-11-14 10:27:08.161 ksqcmi: TX,100020,ad mode=6 timeout=0 ksqcmi: returns 51 ksqgtl: RETURNS 51
*** 2014-11-14 10:27:08.161 ksqgtl *** TX-00100020-000000ad mode=6 flags=0x10001 timeout=0 *** ksqgtl: no transaction ksqgtl: use existing ksusetxn DID ksqgtl: ksqlkdid: 0001-000D-00000002
*** 2014-11-14 10:27:08.161 *** ksudidTrace: ksqgtl ksusesdi: 0001-000D-00000003 ksusetxn: 0001-000D-00000002
*** 2014-11-14 10:27:08.161 ksqcmi: TX,100020,ad mode=6 timeout=0 ksqcmi: returns 51 ksqgtl: RETURNS 51 |
4.2 是否阻塞其它的DML语句
|
www.cdhtz.com > update htz.htz set owner=’HTZ’ where id=2; 1 row updated. www.cdhtz.com > update htz.htz set owner=’HTZ’ where id=4; 1 row updated. www.cdhtz.com > rollback; Rollback complete. www.cdhtz.com > update htz.htz set owner=’HTZ’ where id=82163330; 1 row updated. 这里看到单行的DML语句是没有受到影响的 www.cdhtz.com > update htz.htz set owner=’HTZ’ where mod(id,2)=0; HANG住了，为什么这里HANG住了呢？这里我们可以查询到update处理的行号是一直在变化的，整个进程不是被真的HANG住了，而是执行相当的慢的，因为update在更新行的时候会获取TX6级别的锁，而SMON进程在回退的时候同样会获取TX为6级别的锁，所以就导致出现TX级别的等待。 所以通过DML测试可以看到SMON在回退死事务的时候是不会阻塞其它的DML事务，除非SMON回退行时，持有正在回退行的TX 6级别的锁，如果此时事务要修改此行，就会出现TX等待，但是很快就应该消失。 |
4.3 查询死事务回退的对象与SQL语句
由于事务在v$transaction里面已经没有记录，所以不可能通过v$transaction查询死事务信息与被KILL SESSION的信息包括SQLID信息
|
新开一个会话，并记录下会话的SPID www.cdhtz.com > @tran_dead.sql KTUXECFL COUNT(*) ———————— ———- DEAD 2 NONE 470 ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ —————- ———- ———- ———- ———- 0000002A97FC5278 15 4 135 0 0000002A97FC5C18 16 32 173 556643 www.cdhtz.com > select * from v$rollname where usn=16;
USN NAME ———- —————————— 16 _SYSSMU16$ 上面查看到死事务的XID与UNDO段的名字，下面通过dump undo段的信息，这里注意到xid的信息可以跟前面提到 SMON持有的tx锁的ID1相关联。ID1就是由ktuxeusn,ktuxeslt两部分组成，可以通过下面SQL来转换
www.cdhtz.com > Alter system dump undo block ‘_SYSSMU16$’ xid 16 32 173; 在dump文件中我们可以看到如下的信息，这里可以看到对象的object_id表空间信息。 *—————————– * Rec #0x1c slt: 0x20 objn: 82523(0x0001425b) objd: 82523 tblspc: 9(0x00000009) * Layer: 11 (Row) opc: 1 rci 0x1b Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x01081e1b.03dc.1b KDO Op code: LKR row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00db3d2b hdba: 0x00c01d9a itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 69 to: 根据对象号就可以找到对象的信息了 www.cdhtz.com > @object_by_id.sql Enter Search Object Id (i.e. 1235) : 82523 OWNER OBJECT_NAME ——————– ———— HTZ HTZ 查看被KILL会话的信息与SQL的信息，这里可以通过ASH的信息来查询 由于记录丢失，这里就不写了，在v$active_session_history中，可以通过下面几个段对定位，下面几个值都可以在dump的undo块中找到，上面默认的部分标注了下面的几个值，具体可以见：undo块的rowid值 CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW# |
4.4 计算死事务回退时间
|
www.cdhtz.com > @tran_dead.sql
KTUXECFL COUNT(*) ———————— ———- DEAD 4 NONE 468
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ —————- ———- ———- ———- ———- 0000002A97FE77C8 15 4 135 0 0000002A97FE7EA8 17 24 128 0 0000002A97FE8060 21 29 119 627341 0000002A97FE76C0 24 1 131 0 DO YOU COMPUTE RECOVERY TIME :DO ENTER ,NOT CTRL+C: 21 Enter Search Usn (i.e. 3) : 21 Enter Search Slt (i.e. 4) : 29 time cost Hours:1.34(单位小时) |
测试FOR UPDATE事务的回退：等您坐沙发呢！