儲存過程中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中建立資料表；