oracle回退的办法,【案例】Oracle for update回退导致业务阻塞的解决办法

【案例】Oracle for update回退导致业务阻塞的解决办法

时间:2016-11-02 20:53   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oralce数据库在执行for update回退导致业务阻塞的情况。

Oralce for update回退导致业务阻塞的情况，下面是在环境中模拟for update死事务的回退及回退过程中占用的行锁与是否影响其它的dml语句。

1 环境介绍

oracleplus.net> select * from v$version where rownum=1;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

oracleplus.net> !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)

)

oracleplus.net> alter system set “_disable_logging”=true scope=spfile;

System altered.

oracleplus.net> 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，有点大哦。

oracleplus.net> 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

oracleplus.net> alter database tempfile 1 autoextend on maxsize 31g;

oracleplus.net> alter database datafile 4 autoextend on maxsize 31g;

3 kill for update进程

oracleplus.net> select /*+ parallel(a 4) */ * from htz.htz a where mod(id,2)=0 for update;

等SQL还回结果后，KILL掉for update的进程，事务等待smon进程来回退。

4 现象分析

4.1 smon持有锁情况

oracleplus.net> oradebug setospid 13491

Oracle pid: 13, Unix process pid: 13491, image: oracle@orcl9i (SMON)

oracleplus.net> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_smon_13491.trc

oracleplus.net> 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-1Oracle oracleplus.net4 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语句

oracleplus.net> update htz.htz set owner=’HTZ’ where id=2;

1 row updated.

oracleplus.net> update htz.htz set owner=’HTZ’ where id=4;

1 row updated.

oracleplus.net> rollback;

Rollback complete.

oracleplus.net> update htz.htz set owner=’HTZ’ where id=82163330;

1 row updated.

这里看到单行的DML语句是没有受到影响的

oracleplus.net> 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

oracleplus.net> @tran_dead.sql

KTUXECFL COUNT(*)

———————— ———-

DEAD 2

NONE 470

ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ

—————- ———- ———- ———- ———-

0000002A97FC5278 15 4 135 0

0000002A97FC5C18 16 32 173 556643

oracleplus.net> select * from v$rollname where usn=16;

USN NAME

———- ——————————

16 _SYSSMU16$

上面查看到死事务的XID与UNDO段的名字，下面通过dump undo段的信息，这里注意到xid的信息可以跟前面提到 SMON持有的tx锁的ID1相关联。ID1就是由ktuxeusn,ktuxeslt两部分组成，可以通过下面SQL来转换

select trunc(id1 / 65536) usn, mod(id1 / 65536) slot, id2 wrap, Imode

from v$lock

where type = ‘TX’;

oracleplus.net> 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:

根据对象号就可以找到对象的信息了

oracleplus.net> @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 计算死事务回退时间

oracleplus.net> @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(单位小时)

本文固定链接: http://www.htz.pw/2014/11/22/%e6%b5%8b%e8%af%95for-update%e4%ba%8b%e5%8a%a1%e7%9a%84%e5%9b%9e%e9%80%80.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle for update回退导致业务阻塞的解决办法