トランザクションの一貫性を保証するロック：SQL実践講座（27）

» 2002年12月21日 00時00分公開

ロックの仕組み

　第25回、26回と2回にわたりトランザクションの話をしてきました。第25回でも簡単に触れましたが、トランザクションの一貫性を保証するために、データベースサーバはロックという仕組みを利用しています。今回と次回にわたって、このロックの仕組みについて解説することで、トランザクションの裏側を解明したいと考えています。

　では早速、例題を実行しながら、SQLの確認をしていきましょう。

今回登場するトランザクションのコマンド群は、SQL Server 2000を題材に解説しています

トランザクション中の最新データを確認する

　初めに、第25回で実行した例題1と同じような例題を実行してみましょう。第25回の例題1は、1人のユーザーがデータを更新中には、もう1人のユーザーはデータの参照ができないことを確認する例題でした。第25回の例題と同様に、2つのクエリアナライザを起動して、片方はログイン名「sa」で、もう片方はログイン名「yamada」でログインをします。

　では、まず例題1でログイン名「yamada」のクエリアナライザから、実行前のデータの状況を確認しておきましょう。

【例題1】

1 SELECT CustomerID, CompanyName, Country
2 FROM Customers
3 WHERE Country = 'UK' or Country = 'UKA'

画面1　「yamada」でログインする（画面をクリックすると拡大表示します）

　画面1でCountryが「UK」のデータが7件あるのが分かります。では、ログイン名「sa」のクエリアナライザから、例題2のSQLを実行してみましょう。

【例題2】

1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2 
3 BEGIN TRANSACTION
4 
5 UPDATE Customers
6 SET Country = 'UKA'
7 WHERE Country = 'UK'

画面2　「sa」でログインする（画面をクリックすると拡大表示します）

　さらに続けて、ログイン名「yamada」のクエリアナライザから、例題2のSQLを実行してみましょう。

【例題3】

1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2 SELECT CustomerID, CompanyName, Country
3 FROM Customers
4 WHERE Country = 'UK' or Country = 'UKA'

画面3　更新後の値「UKA」が表示される（画面をクリックすると拡大表示します）

　第25回の例題1のときとは違い、例題3においてはSELECT文が無事に実行されたことを確認できると思います。また、Country列は、例題2においてまだCOMMITがされていないにもかかわらず、更新後の値である「UKA」が表示されているのが確認できますね。では、SQLの解説に移りましょう。

トランザクション中の分離レベル

　例題2、例題3ともに、1行目で「SET TRANSACTION ISOLATION LEVEL」というSQL文を発行しています。これは、「トランザクションの分離レベル」というトランザクションのタイプを指定するSQL文です。では、トランザクションの分離レベルとは何でしょうか。

　実行するトランザクションが、RDBMSによってどの程度のデータ保証をされるべきかについては、トランザクションの種類によっていくつかのタイプに分けることができます。先ほどの例題1～3のように、更新途中でも構わないからとにかく最新のデータが欲しい、というときもあるでしょう。特に夜間バッチで更新されるデータは通常のトランザクションで利用される項目ではなく、日次の締め処理用の特殊なフィールドである場合も多いので、バッチ終了まで長時間待たなくとも参照には特に支障がないことも多いでしょう。

　逆に、その日の受注高の集計を実行するプログラムは、受注高の集計を終えるまでは対象となるデータの更新のみならず、新規データの追加も実施されては受注高の計算が狂ってしまうような処理もあります。

　このようなトランザクションのタイプに対応するために、RDBMSでは「トランザクションの分離レベル」を指定することが可能になっています。SQL Serverで用意されているトランザクションの分離レベルは、次の4つです。

非コミット読み取り（READ UNCOMMITTED）
コミット済み読み取り（READ COMMITTED）
再読み込み可能読み取り（REPEATABLE READ）
直列化（SERIALIZABLE）

　1つ目の「非コミット読み取り」とは、対象となるオブジェクトに対するトランザクションが完了していなくても、現在の最新状態を読み取るモードです。例題3では、「非コミット読み取り」を指定したため、例題2で終了していないトランザクションの対象としているデータを読み取ることが可能になりました。

　例題3の場合は、例題2が対象とするデータが少数であったため、すべてが「UKA」に変更された状態で読み取りが行われましたが、件数が膨大で時間のかかるUPDATE文を発行最中の場合は、途中までは「UKA」、途中からは「UK」のままと、非常に中途半端な状態でデータが読み込まれることになります。

　このような不完全な状態のデータの読み取りを「ダーティリード」と呼びます。「データの一貫性」というRDBMSにおけるデータ保持の基本性能が保証されないため、先ほど説明した例のように、利用者が「どのデータが更新中だから、利用するには注意が必要だ」といったように十分に理解したうえで使用する必要があります。

　2つ目の「コミット済み読み取り」とは、コミット済みのデータしか読み取らないモードです。ほかのトランザクションが対象となるオブジェクトを更新中の場合は、コミットされるまで待ちます。SQL Serverのデフォルトは、「コミット済み読み取り」モードです。

　「コミット済み読み取り」モードでは、対象のテーブルのダーティリードを回避できますが、1つのトランザクション中で2回以上そのテーブルを参照する場合、1回目に読み取るデータと2回目に読み取るデータが同じであることは保証されません。これを回避するのが、「再読み込み可能読み取り」と「直列化」です。この2つのモードの動作については、後の例題で1つずつ確認していきましょう。

　さてその前に、例題2と例題3で使用したSQLの確認に戻りましょう。トランザクション分離レベルを指定するには、次のSQL文を使用します。

SET TRANSACTION ISOLATION LEVEL
（トランザクション分離レベル）

　トランザクション分離レベルには、「READ UNCOMMITTED」「READ COMMITTED」「REPEATABLE READ」「SERIALIZABLE」のいずれかを指定します。

　トランザクション分離レベルの指定は、そのセッションの中では有効です。つまり、SQLプログラムの最初に指定しておけば、トランザクションごとに指定する必要はありません。

「再読み込み可能読み取り」

　次の例題を順に実行して、「再読み込み可能読み取り」と「直列化」の確認をしてみましょう。例題を実行する前に、取りあえず例題2のトランザクションをロールバックしておきます。ログイン名「sa」のクエリアナライザから、例題4のSQL文を実行します。

【例題4】

1 ROLLBACK TRANSACTION

　では、例題の実行に入ります。次の例題5を、ログイン名「yamada」のクエリアナライザから実行します。実行するときに、「ここでいったん実行を停止する」というラベルの前までを選択し、SQLの実行をしましょう。

【例題5】

1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2 
3 BEGIN TRANSACTION
4 SELECT CustomerID, CompanyName, Country
5 FROM Customers
6 WHERE Country = 'UK' or Country = 'UKA'
7 SELECT COUNT(*) FROM Customers
8 
9 /** ここでいったん実行を停止する **/
10 
11 SELECT CustomerID, CompanyName, Country
12 FROM Customers
13 WHERE Country = 'UK' or Country = 'UKA'
14 SELECT COUNT(*) FROM Customers
15 
16 COMMIT

画面4　ログイン名「yamada」から実行（画面をクリックすると拡大表示します）

　続けて、ログイン名「sa」のクエリアナライザから例題6のSQLを実行します。

【例題6】

1 INSERT INTO Customers
2 (CustomerID, CompanyName, ContactName, Country)
3 VALUES
4 ('YAMAD', 'YAMADA', 'YAMADA', 'UK')
5 
6 UPDATE Customers
7 SET Country = 'UKA'
8 WHERE Country = 'UK'

画面5　ログイン名「sa」から実行（画面をクリックすると拡大表示します）

　実行結果が出ずに、待ちの状態になりましたね。では、ログイン名「yamada」のクエリアナライザの残りの部分を実行しましょう。

画面6　ロック部分は実行待ちに（画面をクリックすると拡大表示します）

　ここで確認していただきたいのは、例題5の前半で実行したときには表示されなかったCustomerIDに「yamda」のデータが表示されていること、CountryはUKのままであることです。これは、例題6のSQL文の前半のINSERT文は実行が完了しているが、後半のUPDATE文は「yamada」がロックを掛けたので実行待ちになっているということです。

　このように、トランザクションの分離レベル「再読み込み可能読み取り」は、トランザクションにおいて対象となるすべてのテーブルの対象データがトランザクション実行中に変更されないことを保証します。ただし、トランザクションの対象となるテーブルに新規にデータが追加されたり、対象ではないデータが削除されたりすることは防止しません。

　同様にして、「SERIALIZABLE」の場合を確認してみましょう。

「直列化」

　では、例題5の先頭のSET文を「SERIALIZABLE」に変更して、例題5・例題6と同じ手順で実行してみましょう。まず、テーブルに対して実行した変更を元に戻します。

【例題7】

1 DELETE FROM Customers WHERE CustomerID = 'YAMAD'
2 UPDATE Customers SET Country = 'UK' WHERE Country = 'UKA'

　ログイン名「yamada」のクエリアナライザから、次の例題のSQL文の前半を実行します。

【例題8】

1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2 
3 BEGIN TRANSACTION
4 SELECT CustomerID, CompanyName, Country
5 FROM Customers
6 WHERE Country = 'UK' or Country = 'UKA'
7 SELECT COUNT(*) FROM Customers
8 
9 /** ここでいったん実行を停止する **/
10 
11 SELECT CustomerID, CompanyName, Country
12 FROM Customers
13 WHERE Country = 'UK' or Country = 'UKA'
14 SELECT COUNT(*) FROM Customers
15 
16 COMMIT

　次に例題6を実行します。そして、例題8のSQL文の後半を実行すると、実行結果は次の図のようになるはずです。

画面7　対象オブジェクトを完全にロック（画面をクリックすると拡大表示します）

　ここで確認していただきたいのは、先ほどの「再読み込み可能読み取り」の場合には表示されていた、CustomerIDに「yamada」の行が追加されておらず、Countryも「UK」のままであるということです。これは、例題6において実行したINSERT文とUPDATE文がどちらも実行されていないことを示しています。

　このように、トランザクションの分離レベル「直列化」は、トランザクションが完全に実行を完了するまで、対象となったオブジェクトを完全にロックし、ほかのトランザクションからのデータの挿入や更新を不可能にします。トランザクションが並行することなく順に実行されることから、「直列化」とと呼ばれています。

トランザクションの分離レベルの利用方法

　これまでの例のとおり、トランザクションの分離レベルには4つのタイプがあることが分かりました。この4つのトランザクションの分離レベルにおいては、トランザクション中にどの程度の期間オブジェクトがロックされるかに違いが出てきます。「READ UNCOMMITTED」→「READ COMMITTED」→「REPEATABLE READ」→「SERIALIZABLE」の順に、オブジェクトがロックされる期間が長くなります。

　これはすなわち、ほかのユーザーが待たされる時間が長くなることを意味しています。トランザクションにおいてどの程度データが保証される必要があるかを十分に検討したうえで、どのトランザクション分離レベルを利用すべきかを決める必要がありますね。