- PR -

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

投稿者

投稿内容

AKKEY: 会議室デビュー日: 2005/11/26; 投稿数: 19

投稿日時: 2005-12-08 14:49

かなり基本的な質問ですが、Transact-SQLでトランザクション管理を行う方法
について教えてください。
ストアドプロシージャでテーブルの全件更新処理を行うため、下記のような処理を
記述したのですが、エラー時(下記ではエラーフラグが立った場合)ロールバックが
正しく行われませんでした(①まで処理が戻らず、全てコミットされた状態で
原因についてご存知でしたらご指摘頂けますでしょうか。

BEGIN
-- 変数宣言
DECLARE ・・・

-- トランザクション開始
BEGIN TRANSACTION

-- 全件更新対象テーブルを全件DELETE　①
DELETE FROM 更新先テーブル

-- 更新元データをセレクトしてカーソル定義
DECLARE cur01 CURSOR FOR
SELECT ・・・・ FROM 更新元テーブル

-- カーソルをオープンして更新元からレコードをFETCHする
OPEN cur

FETCH NEXT FROM cur01
INTO 変数

WHILE @@FETCH_STATUS = 0
BEGIN

-- 更新元テーブルのデータをカラム単位でチェックする(参照処理のみ)
-- もしデータに不整合があれば、エラーログを出力し、エラーフラグを
-- 立てる。
IF チェックエラー
SET ERRFLG = 1
エラー出力(テーブル変数へ書き出し)

-- チェックが済んだデータを更新先テーブルへ登録する
INSERT INTO 更新先テーブル VALUES ( カーソル上のデータ)

-- 登録処理でエラーが発生した場合はトランザクションをロールバック
する。
IF @@ERROR <> 0
ROLLBACK TRANSACTION

-- 次のレコードを読み出す
NEXT NEXT FROM cur01
INTO 変数
END

カーソルループ処理で一件でもエラーがあれば、トランザクションを
ロールバックする
IF ERRFLG = 1
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END

らい: 大ベテラン; 会議室デビュー日: 2005/08/02; 投稿数: 159; お住まい・勤務地: 東京都と千葉県のさかいめ

投稿日時: 2005-12-08 17:46

らいと申します。
記述ミスの可能性もありますが、いくつか。

まず、全体的に
変数には『@』をつけましょう。

で、本編。

引用:

AKKEYさんの書き込み (2005-12-08 14:49) より:
コード:
-- 更新元データをセレクトしてカーソル定義
	DECLARE cur01 CURSOR FOR
	SELECT	・・・・ FROM 更新元テーブル

SET @cur01 = Cursor For
SELECT ・・・・ FROM 更新元テーブル
かと思われます。

引用:

コード:

		-- 更新元テーブルのデータをカラム単位でチェックする(参照処理のみ)
		-- もしデータに不整合があれば、エラーログを出力し、エラーフラグを
		-- 立てる。
		IF チェックエラー
			SET ERRFLG = 1
			エラー出力(テーブル変数へ書き出し)

２行以上にわたるIF文は、
『Begin～End』
でくくりましょう。

引用:

コード:
-- 次のレコードを読み出す NEXT NEXT FROM cur01 INTO 変数

おそらく記述ミスだとは思いますが、
FETCH NEXT ～
です。

多分、こんなところです。
あとは見えていないところで、変数の型には注意です。

_________________
一寸先は闇
安定してるシステムって言ったじゃん（泣）

Anthyhime: ぬし; 会議室デビュー日: 2002/09/10; 投稿数: 437

投稿日時: 2005-12-09 11:29

ループとか書いてあるのでおそらくループ内でERRFLGを正常処理のSQLの@@ERRORで上書いているんでしょうな。
たぶんプログラムミスですね。

AKKEY: 会議室デビュー日: 2005/11/26; 投稿数: 19

投稿日時: 2005-12-09 12:35

らいさん

迅速な回答ありがとうございました。以下コメントします。

引用:
らいさんの書き込み (2005-12-08 17:46) より: らいと申します。記述ミスの可能性もありますが、いくつか。まず、全体的に変数には『@』をつけましょう。

失礼しました。書き込み用に記載したものなので、
文法を無視してしまいました。混乱させてしまいました。

引用:
SET @cur01 = Cursor For SELECT ・・・・ FROM 更新元テーブルかと思われます。

カーソルについては、SQLServerのBooksOnlineの記述を利用したのですが上記
の記述の方が正しいのでしょうか。

引用:
２行以上にわたるIF文は、『Begin～End』でくくりましょう。

失礼しました。実際のコードは括っています。

引用:
おそらく記述ミスだとは思いますが、 FETCH NEXT ～です。

失礼しました。こちらも転記の際のミスです。

引用:
多分、こんなところです。あとは見えていないところで、変数の型には注意です。

有難うございました。
記載したコードでは、カーソルをループさせる箇所では全く
COMMITせず、ループを抜けた後にコミットするようにしております。
クエリアナライザでデバックしたのですが、ループ中は
COMMITのステップへは移っておりません。
しかし実行結果をクエリアナライザのメッセージタブで参照すると、
ループ回数分(1 件処理されました)というメッセージが出ています。

意図的に途中でロールバックするようなデータを試してみたのですが、
ロールバックはされていませんでした。

SQLServerでの記述は初めてなのですが、暗黙的にCOMMITされてしまう
ということはありませんでしょうか。

らい: 大ベテラン; 会議室デビュー日: 2005/08/02; 投稿数: 159; お住まい・勤務地: 東京都と千葉県のさかいめ

投稿日時: 2005-12-09 12:37

らいです。

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

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

引用:
Anthyhimeさんの書き込み (2005-12-09 11:29) より: ループとか書いてあるのでおそらくループ内でERRFLGを正常処理のSQLの@@ERRORで上書いているんでしょうな。たぶんプログラムミスですね。

どうでしょうかねぇ。
ただ、ループでも変数は初期化されないだろうし、
ERRFLGを書き換えているのは
エラーチェックでエラーと判断された場合（？）のみみたいだし。
# 見えてないところでやっているんだったらお手上げですが。（笑）

これから後は、質問者さんがもう一回出てきてからでしょうね。

_________________
一寸先は闇
安定してるシステムって言ったじゃん（泣）

AKKEY: 会議室デビュー日: 2005/11/26; 投稿数: 19

投稿日時: 2005-12-09 12:44

Anthyhimeさん

ご回答ありがとうございます。
コードを全て転記しておらず、記載ミスもあったので、分かりにく
かったかと思います。すみませんでした。

引用:
Anthyhimeさんの書き込み (2005-12-09 11:29) より: ループとか書いてあるのでおそらくループ内でERRFLGを正常処理のSQLの@@ERRORで上書いているんでしょうな。たぶんプログラムミスですね。

ERRFLGの値を@@ERRORで上書きするような記述はありませんでした。
私もプログラムミスだと思うのですが、上述の通りカーソルループ
内にはコミットするような記述を記載していないにもかかわらず、
ループ毎にコミットするような挙動が見受けられます。

同じようなご経験はありませんでしょうか。

らい: 大ベテラン; 会議室デビュー日: 2005/08/02; 投稿数: 159; お住まい・勤務地: 東京都と千葉県のさかいめ

投稿日時: 2005-12-09 13:20

らいです。
度々すいません。

えと、もしかしたら、トランザクションの制御かもしれません。
もし、エラーが起こってしまった場合は

引用:
IF @@ERROR <> 0

の下でロールバックしています。
そうすると、トランザクションが終わって
自動コミットになってしまうのでは？

ということで、
FETCHでカーソルをグリグリやってるところの一番初めで
BEGIN TRANSACTION
を宣言してみてはいかがでしょうか。
もちろん、ELSE文を作成して、
COMMIT TRANSACTION
をする必要もありますが。

だめかなぁ。
でも、ここでエラーが出てないってのは別にいいのかなぁ。
# トランザクションの数が合わないと、怒られるような記憶が。

あと、

引用:
カーソルについては、SQLServerのBooksOnlineの記述を利用したのですが上記の記述の方が正しいのでしょうか。

ですが、どうなんだろうなぁ。
私の場合、BooksOnlineを読んだことないので。(^^;;

_________________
一寸先は闇
安定してるシステムって言ったじゃん（泣）

unibon: ぬし; 会議室デビュー日: 2002/08/22; 投稿数: 1532; お住まい・勤務地: 美人谷　　　　　　　　良回答(20pt)

投稿日時: 2005-12-09 13:39

ストアドプロシージャーはどのように呼び出されているでしょうか？もし ADO などのミドルウェアを使ってプログラム中から呼ばれているとなにか副作用があるのかもしれません。isql や osql などで呼ばれても同じ現象でしょうか。
なお、SQL Server は Oracle(?) などと違って、暗黙的にコミットするということは基本的になく、わりと素直です。BEGIN TRANSACTION したのならば COMMIT TRANSACTION しない限り、コミットされることは絶対に(?)なかったと思います。
試しに、ストアドプロシージャー中の COMMIT TRANSACTION を完全に取り除いてみて、それでもコミットされるかどうかを調べられてはどうでしょうか。

1|2 次のページへ»

＠IT SpecialPR

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

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