SQL Serverで安全なUPSERTをする方法
あまりちゃんと知られていない(?)ようなので書いておきます(・∀・)
SQL Serverで安全にUPSERT(INSERT or UPDATE)する方法について。
とりあえず、こんなストアドを用意すれば良いですよ(・ω・)
CREATE PROCEDURE spXxx_Upsert @Id int, @Data NVARCHAR(MAX), ... AS BEGIN DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END IF ( NOT EXISTS ( SELECT * FROM xxx WITH ( UPDLOCK, HOLDLOCK ) WHERE Id = @Id ) ) BEGIN INSERT ... END ELSE BEGIN UPDATE ... END IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0 Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END
Id列がPKで、Dataとかがその他の列だと思ってくださいな。
なぜ安全なのかを簡単に説明すると、HOLDLOCKの指定によりトランザクションがSERIALIZABLEと同様になるので、SELECTとINSERTの間に、他のトランザクションによって同じキーが挿入されるのを防ぐことができるからです。
ちなみに、ASP.NETのMembership用に用意されるストアドとかもこんな感じですよね。
で、上記処理のSQLをアプリ中から個々に発行するのは面倒なので、こういうストアドで実装するのが楽ちんぽん(・∀・)
まあ、SQL Server 2008からはMERGEがあるんだけどさ(・ω・)
来月ですね?
オフィシャルマニュアルの2005用はよさげなカンジだったので、2008用が出たら買おうかな(・∀・)
Microsoft SQL Server 2005オフィシャルマニュアル 上 (マイクロソフト公式解説書)
- 作者: Edward Whalen/Marcilina Garcia/Burzin Patel/Stacia Misner/Victor Isakov,株式会社トップスタジオ
- 出版社/メーカー: 日経BP社
- 発売日: 2007/08/30
- メディア: 単行本
- クリック: 2回
- この商品を含むブログ (3件) を見る
Microsoft SQL Server 2005オフィシャルマニュアル 上 (マイクロソフト公式解説書)
- 作者: Edward Whalen/Marcilina Garcia/Burzin Patel/Stacia Misner/Victor Isakov,株式会社トップスタジオ
- 出版社/メーカー: 日経BP社
- 発売日: 2007/08/30
- メディア: 単行本
- クリック: 2回
- この商品を含むブログ (3件) を見る