スナップショット分離レベルを使った、ブロックされないSQLの確認

SQLServer 2005からスナップショット分離レベルという分離レベルが追加された。

これは、Oracleのようなロックの

あるユーザーによるロック中に、別のユーザーがロック対象を読み取ろうとしたときに、

「ロック前のスナップショットを見せる」ことで、ブロックされてしまうことを防ぐというものだ。

この仕組みを理解するのには、以下の説明が非常にわかりやすい

排他ロックにブロックされない読み取りの実現「READ_COMMITTED_SNAPSHOT」と「スナップショット分離レベル」

「READ COMMITTED SNAPSHOT」と「スナップショット分離レベル」は、排他ロックにブロックされない読み取りを可能とする SQL Server 2005 からの新機能です。これにより、テーブル スキャンによるロック待ちも回避できるようになります。具体的には、次のような動作が可能になります。

内部的にテーブル スキャンが発生しても、排他ロックにブロックされずに読み取りが可能になります。
拡大図を見る

READ_COMMITTED_SNAPSHOT スナップショット分離レベルは、正しくは「読み取り一貫性」を提供する機能です。排他ロックのかかっている更新中のデータは、まだ確定していない未コミットのデータであり、それを参照させないようにし、更新前のデータ (その時点での正しいデータ) を参照させることで一貫性を保ちます。

■SQL Server 2005 Tips and Tips　第 2 回 排他ロックにブロックされない読み取りの実現

Snapshot分離レベルを使うために「ALLOW_SNAPSHOT_ISOLATION」をONに設定する必要がある。

ALTER DATABASE MYDB 
SET ALLOW_SNAPSHOT_ISOLATION ON

ただし、このように設定した場合、すべてのトランザクション処理の開始時に

tempdbにスナップショットが取られるため、サイズには気をつけなくてはいけない。

おためし用のクエリを流してみる。

ロックする側

set transaction isolation level serializable;
begin tran 
select * from MYDB.dbo.tbl
update dbo.tbl set col1 = 'aa1' where col1 ='aaa';

読み取る側

set transaction isolation level snapshot
begin tran 
select * from MYDB.dbo.tbl 

結果の添付は、面倒なので割愛するけど、

読み取る側のクエリで、ロック前のテーブルが読み取れた。