宇宙仮面の
C# Programming

 
SQL 2008

同時実行

複数のユーザーが同時にデータベースにアクセスすると、ダーティ リード、ファントム読み取りなどが発生する場合があり、十分注意して設計する必要があります。

1.目次

1.目次
2.同時実行の問題
3.分離レベル
4.ロックのヒント
5.ロック状況
6.ロック_モード

.同時実行の問題

複数のユーザーが同時にデータベースにアクセスすると、次の問題が発生する場合があります。このような同時実行の問題を回避するためにロックが必要になります。

 

同時実行の問題 説明
更新データ損失 複数のトランザクションが同じ行を選択し、その行の元の値を更新すると発生します。

例: 同じ文書DのコピーをD1作成したとします。Aさん、Bさんが自分のコピーD1を変更し、AさんはDAを、BさんはDBという文書を作成したとします。まず、AさんがDAをDとして保存し、次にBさんがDBをDとして保存すると、AさんのDAという文書は、損失することになります。

非コミット依存
(ダーティ リード)
トランザクションによって更新中の行を別のトランザクションが選択するときに発生します。

例: 文書Dがあります。Aさんが文書Dを変更し、未完成の文書D1を作りました。Bさんが文書D1を配布しました。その後、Aさんは文書D1の誤りに気がつき、D1を廃棄しました。

不一致分析
(反復不能読み取り)
別のトランザクションが同じ行に数回アクセスし、それぞれの場合で異なったデータを読み取るときに発生します。

例: 文書DをAさんが執筆中です。Bさんが最初に読んだ文書Dは、文学小説でした。次にCさんが読んだ文書Dは、SFに変わっていました。

ファントム読み取り
(ファントム)
トランザクションによって読み取られる一連の行のどれかについて挿入または削除が行われるときに発生します。

例: Aさんが執筆した文書DをBさんが編集しD1とします。次にCさんが文書D1を編集しD2としようとしています。このとき、Aさんから文書DがD'に変更されました。

SQL Server 2000 では、オプティミスティックな同時実行制御とペシミスティックな同時実行制御が提供されています。

同時実行の問題 説明
オプティミスティックな同時実行制御 複数のユーザー間でリソースの衝突が起こらないことを想定しています。ロックせずにトランザクションを実行できます。データを変更しようとしている場合だけ、衝突がないかどうかリソースを調べます。衝突が起きた場合、アプリケーションはデータを読み取り、変更を試みる必要があります。
ペシミスティックな同時実行制御
(デフォルト)
トランザクションの間だけ必要なリソースにロックがかかります。デッドロックが発生しない限り、トランザクションは正常に完了します。

3.分離レベル

すべてのSELECT ステートメントに対して、既定のトランザクション ロックの動作を制御します。分離レベルを指定すると、別の分離レベルを設定しない限り、セッションの終了時まで変更されません。分離レベルは、SELECT ステートメントでテーブル レベルのロック ヒントを指定することによって無効にできます。テーブル レベルのロック ヒントを指定しても、セッション内のほかのステートメントには影響を与えません。

構文

SET TRANSACTION ISOLATION LEVEL
    
{ READ COMMITTED
        | READ UNCOMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
    }

分離レベル ダーティ リード 反復不可能読み取り ファントム
READ UNCOMMITTED
READ COMMITTED 不可
REPEATABLE READ 不可 不可
SERIALIZABLE 不可 不可 不可

 

4.ロックのヒント

TRANSACTION ISOLATION LEVELでは、規定のトランザクションロックレベルを設定しますが、各SELECT、INSERT、UPDATE、DELETE 文でロックの種類を指定することができます。これらのロック ヒントは、セッションの現在のトランザクション分離レベルより優先します。

例:

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)


ここでの SELECT * FROM xyz WITH (ロックのヒント)では、次のロックのヒントを与えることができます。

ロックのヒント 説明
NOLOCK ダーティ リードが可能です。SELECT ステートメントにだけ適用されます。
READUNCOMMITTED NOLOCK と同じ。
PAGLOCK 通常はテーブル、ページ単位のロック。
READCOMMITTED READ COMMITTED と同じ。
SQL Serverの規定値。
READPAST ロックされた行をスキップします。SELECT ステートメントにだけ適用されます。
REPEATABLEREAD REPEATABLE READ と同じ。
ROWLOCK 行レベルのロック。
SERIALIZABLE SERIALIZABLE の分離レベルで動作するトランザクションと同じロック セマンティクスでスキャンを実行します。HOLDLOCK に相当します。
HOLDLOCK SERIALIZABLE と同じです。トランザクションの完了時まで保持します。
TABLOCK テーブル レベルのロック。SQL Server はステートメントの終了まで保持する。
TABLOCKX テーブルに排他ロックを使用。このロックは、ほかのユーザーがテーブルを読み込んだり更新したりすることを防止し、ステートメントまたはトランザクションの終了まで維持されます。
UPDLOCK テーブルの読み出しの間、共有ロックの代わりに更新ロックを使用し、ステートメント終了またはトランザクション終了まで保持します。UPDLOCK には、ほかのユーザーがデータを読むことをブロックせずにデータを読み取ることができます。また、読み取り以来データは変更されていないという保証があるため、後にそのデータを更新できるという利点があります。
XLOCK トランザクションの終了まで保持される排他ロックを使用する。このロックは、PAGLOCK または TABLOCK で指定できます。

.ロック状況

現在のロックの状況は、sp_lockにより確認できます。

GO
EXEC sp_who
GO
EXEC sp_lock
GO

Image

このときの Typeは、

Type 意味
DB データベース
FIL ファイル
IDX インデックス
PG ページ
KEY キー
TAB テーブル
EXT エクステント
RID 行識別子

.ロック モード

SQL Server のリソース ロック モードは次のとおりです。

ロック モード 説明
共有 (S) データを変更や更新しない読み取り専用。
更新 (U) 更新可能なリソースで使用します。共通の状況で起こるデッドロックを防ぎます。デッドロックは、複数のセッションがリソースを読み取り、ロックして、後で更新する可能性がある場合に発生します。
排他 (X) UPDATE、INSERT、DELETE などの1 つのリソースを同時に更新してはいけない処理で使用。
インテント ロック階層を設定するのに使用します。次の種類がある。
  • インテント共有 (IS)
  • インテント排他 (IX)
  • インテント排他の共有 (SIX)
スキーマ テーブルのスキーマに依存する処理を行うときに使用。次の2 種類がある。
  • スキーマ安定度 (Sch-S) ロック
    クエリをコンパイルする間だけ使用されます。
  • スキーマ修正 (Sch-M)
    テーブル データ定義言語 (DDL) の処理 (列の追加やテーブルの削除) が行われているときに使用されます。
一括更新 (BU) データを一括でテーブルにコピー