- PR -

Transact-SQLでのトランザクション管理について

«前のページへ 1|2
投稿者投稿内容
GUCCHI
会議室デビュー日: 2005/10/27
投稿数: 17
投稿日時: 2005-12-09 14:04
どうもこんにちは。

このプログラムですと、ループ内の登録処理でエラーが発生した場合、その時点でロールバックします。
１度ロールバックをしていますので、ERRFLG=1なので再度ロールバックを行おうとそてもROLLBACK TRANSACTIONに対するBEGIN TRANSACTIONがないため、ロールバック処理が行われません。
結果、ストアドが終了すると暗黙的にコミット処理が行われてしまいます。
（クエリアナライザで実行すると、エラーメッセージが表示されるはずです。）

回避策として、ラベルを使用して、文末にコミット処理をしてみたらどうでしょうか。

(中略)
SET @ERRFLG = 1
BEGIN
(エラー出力)
GOTO Error
END
(中略)
IF @@ERROR <> 0 GOTO Error
(中略)
で、一番最後にERRFLGで判定する処理の代わりに

COMMIT TRAN
RETURN 0
Error:
ROLLBACK TRAN
RETURN 1

ざっとこんな感じです。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-12-09 14:29
begin transaction で 1度しか明示的トランザクションを開始していないのに、ループの中で複数回 rollback transaction が呼ばれているのが原因じゃないかな。

1度目の rollback transaction で明示的トランザクションが終了するので、SQL Server は自動コミットモードに戻ってしまう。自動コミットモードだと、ステートメント単位で自動的にコミットされるので、全体としてみるとデータが戻らないという症状になる。

根本的な解決策は、トランザクションの開始数と終了数を合わせること。自分で @@ERROR を判定してフロー制御して数合わせしてもいいけど、それよりも SET XACT_ABORT ON を指定して厳密なトランザクションにしておいたほうが楽だし安心できると思います。@@ERROR を判定する必要もなくなるし。
AKKEY
会議室デビュー日: 2005/11/26
投稿数: 19
投稿日時: 2005-12-09 17:16
返答が遅れてしまいました。

引用:

引用:

AKKEYさんの書き込み (2005-12-08 14:49) より:
<省略>
SET ERRFLG = 1

ここですが、
SELECT @ERRFLG = 1
でもいいかと思うのですが、なぜSETなのでしょうか。
つーか、昨日の時点で気づけよ、自分。orz


すみません。Transact-SQLでのコーディングが初めてなので教えて
下さい。
Transact-SQLでは変数へ値をセットする方法として、SELECT文を
使用するのが一般的なのでしょうか。
MSサイトなどを見ていると、SETを用いた記述が例文として挙げられて
います(勿論SELECT文を用いた形もあります)。
PL/SQLでは代入演算子":="を用いますので、同じノリでSETを使用して
いました。

もしこのような"定石"的な記述方法があれば、また教えてください。
先のご指摘も非常に参考になりました。


AKKEY
会議室デビュー日: 2005/11/26
投稿数: 19
投稿日時: 2005-12-09 17:33
unibonさん

コメント有難うございました。

引用:

unibonさんの書き込み (2005-12-09 13:39) より:
ストアドプロシージャーはどのように呼び出されているでしょうか？もし ADO などのミドルウェアを使ってプログラム中から呼ばれているとなにか副作用があるのかもしれません。isql や osql などで呼ばれても同じ現象でしょうか。

ご指摘の通りプロシージャはDTSツール上のActiveX(VBScript)からADO経由で呼び出して
います。
osqlを使用しても現象は変わりませんでした。やはりループ回数分(1件処理されました)
と表示されます。

引用:

なお、SQL Server は Oracle(?) などと違って、暗黙的にコミットするということは基本的になく、わりと素直です。BEGIN TRANSACTION したのならば COMMIT TRANSACTION しない限り、コミットされることは絶対に(?)なかったと思います。
試しに、ストアドプロシージャー中の COMMIT TRANSACTION を完全に取り除いてみて、それでもコミットされるかどうかを調べられてはどうでしょうか。


ご指摘頂いた通り、START TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION全て外してみましたが、結果は変わらずループの度にコミットされていました。
また、単純にFETCHしてINSERTするのみ、エラー処理も何もしないプロシージャも同じ結果でした。
AKKEY
会議室デビュー日: 2005/11/26
投稿数: 19
投稿日時: 2005-12-09 17:44
GUCCHIさん

ご回答ありがとうございました。

引用:

このプログラムですと、ループ内の登録処理でエラーが発生した場合、その時点でロールバックします。
１度ロールバックをしていますので、ERRFLG=1なので再度ロールバックを行おうとそてもROLLBACK TRANSACTIONに対するBEGIN TRANSACTIONがないため、ロールバック処理が行われません。


ご指摘頂いたコードは後で試して、結果を報告します(ちょっと今時間が
とれないので)。
ただ、下記に記載したようにROLLBACK文を一つも記載しないシンプルな
カーソルループ文(ループの中ではINSERTのみです)を実行しても、やはり
ループ毎にコミットされているようです。
AKKEY
会議室デビュー日: 2005/11/26
投稿数: 19
投稿日時: 2005-12-09 17:49
コメント有難うございました。

引用:

begin transaction で 1度しか明示的トランザクションを開始していないのに、ループの中で複数回 rollback transaction が呼ばれているのが原因じゃないかな。

1度目の rollback transaction で明示的トランザクションが終了するので、SQL Server は自動コミットモードに戻ってしまう。自動コミットモードだと、ステートメント単位で自動的にコミットされるので、全体としてみるとデータが戻らないという症状になる。

根本的な解決策は、トランザクションの開始数と終了数を合わせること。自分で @@ERROR を判定してフロー制御して数合わせしてもいいけど、それよりも SET XACT_ABORT ON を指定して厳密なトランザクションにしておいたほうが楽だし安心できると思います。@@ERROR を判定する必要もなくなるし。


このような事情があったのですか・・
BOOKS ONLINEを見てみたのですがSET XACT_ABORT ONは確かに一番安全かもしれません
ね。後で試してみて、また報告します。

有効な情報誠に有難うございました。
らい
大ベテラン
会議室デビュー日: 2005/08/02
投稿数: 159
お住まい・勤務地: 東京都と千葉県のさかいめ
投稿日時: 2005-12-09 18:01
らいです。

引用:

AKKEYさんの書き込み (2005-12-09 17:16) より:
Transact-SQLでは変数へ値をセットする方法として、SELECT文を
使用するのが一般的なのでしょうか。

あ、別に一般的ではないですよ？（多分）
や、私の場合はじめてみた参考書（のようなもの）に
SELECT 変数=〜
ってなってたので今でも使ってるだけで。
# SETはSQL文の環境設定用だと長い間思っていたので。（汗
# おかげで未だにあまり使用しません。（笑）

何か明示的な理由があるのかな〜と思って聞いてみただけです。
他意はありませんのでご容赦を。

_________________
一寸先は闇
安定してるシステムって言ったじゃん（泣）
«前のページへ 1|2

スキルアップ／キャリアアップ（JOB@IT）