ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 存储过程中DDL错误一例

存储过程中DDL错误一例

原创 Linux操作系统 作者:realkid4 时间:2011-03-28 21:58:47 0 删除 编辑

 

偶然想到在过去一个项目组写SP时候遇到的问题。不敢说分享知识,就是一起分享下经历而已。

 

 

问题概述

 

开发的一段存储过程代码,在调试中遇到一些诡异的问题。下面是代码片段,用来模拟情况。

 

 

  (coding....)--DML操作序列

 

  p_third_party_method() --第三方方法

 

  (coding....)--DML操作序列

 

  if (succ) then

     commit; --提交事务

  else

     rollback; --失败,就进行回滚

  end if;

 

 

为了说明问题,进行了很大程度的省略简化。简单的说,存储过程在执行一系列DML操作后,调用了其他模块开发团队提供的存储过程。之后根据操作成功或者失败进行提交。

 

但是在调试阶段,笔者发现了一个诡异现象。当发生操作错误的时候,即使布尔类型变量succ设置为false,进行了rollback。前面的一些DML操作还是会被commit提交。通过代码走查,发现代码中(包括第三方方法)也没有显示的进行commit/rollback操作。

 

 

疑点线索出现

 

当这种问题不断出现之后,笔者决定好好进行走查(代码量很大,整个作业超过万行)。使用若干次搜索commit/rollback之后,在一个第三方方法中发现一段值得怀疑的语句。

 

  execute immediate 'truncate table abc';

 

 

根据该方法的注释和沟通开发人员,该语句的作用是清空一个数据表,为了加快速度才考虑使用truncate方法。

 

Truncate方法的作用虽然是快速删除数据,但是是很有特殊性的语句。最大的特殊性就在于它本质上是一种DDL(数据定义语句),而不是DML(数据操作)语句。那么,是不是因为在一个DML事务中使用这个方法,才引起了事务提交呢?下面通过实验证明猜想。

 

 

实验记录

 

我们进行试验的项目归纳为:在一个DML事务中,如果使用了一个DDL语句,是否会发生事务的意外提交?DDL语句是自身事务原子属性的,也就是事务级别在语句本身。

 

 

SQL> create table t as select * from dba_objects where 1=0;

 

Table created

 

SQL> create table m as select * from emp;

 

Table created

--事务开始

SQL> select count(*) from t;

 

  COUNT(*)

----------

         0

 

SQL> insert into t select * from dba_objects where rownum<5;

 

4 rows inserted

 

SQL> truncate table m; --进行一次DDL操作,即使是与当前事务无关的DDL操作

 

Table truncated

 

--事务结束,要求进行rollback操作

SQL> rollback;

 

Rollback complete

--即使已经rollback了事务,还是将DDL之前的DML操作默认进行提交。

SQL> select count(*) from t;

 

  COUNT(*)

----------

         4

 

整个过程已经比较明确的说明问题了。在一个DML事务中,如果中间插入了Truncate语句,语句前进行的DML操作会自动提交。

 

那么,其他DDL语句是否有相同的效果呢?

 

SQL> insert into t select * from dba_objects where rownum<5;

 

4 rows inserted

 

SQL> drop table m;

 

Table dropped

 

SQL> rollback;

 

Rollback complete

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

         4

 

 

Drop数据表可以有效。

 

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

         0

 

SQL> insert into t select * from dba_objects where rownum<5;

 

4 rows inserted

 

SQL> create table m (id number);

 

Table created

 

SQL> rollback;

 

Rollback complete

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

         4

 

Create Table操作有效。

 

经过试验,我们证明了进行DDL操作(create、drop和truncate),是会隐式的进行事务提交。

 

 

问题解决

 

解决该问题就变得相对容易。联系相关同事进行代码改写,采用delete操作删除数据表中原有数据。此外还有一种额外的方法,如果要进行truncate操作的数据表是一个中间操作表,可以考虑使用临时表。临时表有比较好的session间数据操作独立特性,并且可以保证在session结束的时候,数据自动清除。

 

 

这个问题很简单,但是给我们的经验有如下:

 

1、在日常作业的存储过程中,特别是有出现事务的环境下,绝不要轻易使用DDL语句。因为这样做可能会引起事务的隐式提交,从而带来数据不一致的问题。尽量全部使用DML操作;

2、如果数据表只是一个中间过程处理存储使用,可以考虑使用临时表。Oracle会提供很好的数据独立和会话结束清理保证。不要尝试手工实现临时表的功能,更不要尝试在SP中建立数据表;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-691163/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7745723