存储过程中DDL错误一例

 

偶然想到在过去一个项目组写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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-691163/