連載

2002年10月24日

トランザクションでデータの不整合を防ぐ：SQL実践講座（25）

LINE

Hatena

トランザクションの構成と実行

　今回は、トランザクションの構成と実行に挑戦します。トランザクションは、複数のユーザーが同時にデータベースを操作する状況において、データベースに対する複数の操作（選択、更新、削除など）を実行している途中で仮にエラーが発生したとしても、データの不整合が起きないことを保証するリレーショナルデータベースシステム（RDBMS）のメカニズムです。

　これまでの連載において紹介してきた例題では、同時にデータベースを操作するユーザーは1人であるという暗黙の前提において説明をしてきました。しかし、RDBMSを利用したアプリケーションでは、複数ユーザーによる同時利用を前提とすることがほとんどだと思います。このため、RDBMSにはデータベースを同時に更新した場合にデータの不整合が起きないように制御をするための仕組みが用意されています。

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

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

トランザクションの仕組み

　初めに簡単な例として、1人のユーザーがデータベースを更新している途中で、もう1人のユーザーがデータベースを参照しようとしたときに、どのような状況になるか確認してみましょう。SQL Server Enterprise Managerでデータベースの状況を確認しつつ、2つのクエリアナライザから同じデータベースへの操作を実行することで、疑似的な複数ユーザーによる利用環境を作成します。2つのクエリアナライザは同じマシンから実行しても構いません。

　ではまず、1つ目のクエリアナライザを起動し、「sa」としてログオンした後、例題1のSQLを実行してみましょう。

【例題1】

BEGIN TRANSACTION
UPDATE Customers
SET Country = 'UKA'
WHERE Country = 'UK'

画面1　トランザクションを開始する（画面をクリックすると拡大表示します）

　続いて、「yamada」としてログオンしたもう1つのクエリアナライザより、例題2のSQLを実行します。「yamada」というSQL Serverログイン名は、連載第18回「ロールを利用したグループ単位での権限設定」で作成したSQL Serverログインですが、データベースから削除してしまった場合は、再度SQL Serverログインを作成してから実行してみてください。

【例題2】

SELECT * FROM Customers

画面2　SELECT文で実行待機の状態にする（画面をクリックすると拡大表示します）

　ウィンドウの右下に表示される地球儀のアイコンが回転し、SELECT文の結果が表示されず、待機状態になったことが確認できたでしょうか。

　では、いま、起きている現象を確認していきましょう。

複数の処理を1つにまとめるトランザクション

　まず、例題1の1行目で、「BEGIN TRANSACTION」というSQL文を発行しています。これは、トランザクションの開始を指定するSQL文です。対となるトランザクションの終了は、例題6で紹介する「COMMIT TRANSACTION」文、もしくは、「ROLLBACK TRANSACTION」文です。「BEGIN TRANSACTION文とCOMMIT TRANSACTION文の間に実行したSQL文による操作が、エラーによりデータの不整合を起こすことなく完了すること」を保証するRDBMSのトランザクション機能を、BEGIN TRANSACTION文により明示的に開始します。

　トランザクションとは、論理的には1つの処理のことです。トランザクションの説明によく引用されるのが、銀行口座間の送金の例です。口座Aの預金を管理するデータベースAと、口座Bの預金を管理するデータベースBがあるとします。口座Aから口座Bへ預金を送金する場合、口座Aから預金を引き出す処理と、口座Bへ預金を入金する処理の2つが必要になります。

　これら2つの処理は、本来であれば「送金する」という1つのデータベースに対する変更処理として実行すべきですが、RDBMSでは2つのデータベースに対する2つの操作として実行する必要があります。これら2つの操作中にエラーが起こることなく実行が完了すれば問題ないのですが、口座Aからの引き出し処理は正常に実行されても、口座Bへの入金処理はネットワーク障害などで正常に完了しない事態が発生することも考えられます。

　この場合、データベースAとデータベースBの間で、「送金されたはずの預金が、どの口座にも残高として記録されていない状態」となり、データの欠損した状態が発生してしまいます。

　このような状況を回避するために、口座Aからの引き出し処理と口座Bへの入金処理を、1つにまとめる機能が必要になります。これが、トランザクションです。

　1つのトランザクションとして定義した複数の操作は、トランザクションが完了した時点で、次の2つのうち必ずどちらかの状態で終了します。

・複数の操作がすべて正常に実行完了した状態

・複数の操作のうち、いずれかの処理が失敗したので、データベースの状態をトランザクション処理開始前に復元した状態

　つまり、途中まで実行して、途中までデータの更新が反映された中間状態で終了しないことをRDBMSが保証するのです。先ほどの銀行口座間の送金の例では、もし口座Aからの引き出し処理と口座Bへの入金処理を1つのトランザクションとして実行すれば、次の2つの状態の必ずどちらかで終了します。

・口座Aから口座Bへ預金が正常に送金された状態

・まったく送金処理がされなかった状態

　口座Aから引き出し処理はされたが、口座Bへの入金処理はされていない、という中途半端な状態で終了する事態は起きないということですね。

トランザクションを実行するSQL文の解説

　SQL文ではどのようになるかというと、先ほど説明したBEGIN TRANSACTION文でトランザクションの開始を指示します。その後実行される複数の操作は、どの操作もいったん仮の状態で実行され、正常な実行の確認にとどまります。この段階では、データベースにデータの変更は反映されません。そして、COMMIT TRANSACTION文でトランザクションの確定を指示すると、それまで仮の状態だった変更操作が、一気にデータベースへ反映されます。この処理を、「コミット処理」と呼びます。

　逆に、何らかの理由でデータベースの変更が不可能である場合には、ROLLBACK TRANSACTION文でそれまでのトランザクション中のすべての操作を中止し、元の状態に戻します。この処理を「ロールバック処理」と呼びます。

　先ほどの例題1では、1行目のBEGIN TRANSACTION文でトランザクション処理を開始し、3行目から5行目のUPDATE文によるデータベースへの変更処理を仮に実行した状態で終了しています。この状態ではまだ、トランザクションがCOMMIT TRANSACTION文により完了していないため、データベースへの変更は反映されていない状態です。

　この中途半端なデータベースの状態で、ほかのユーザーがデータを参照しようとすると、変更されるべきデータと変更されないデータとが混在して照会されてしまうため、不整合のあるデータを参照しかねません。不整合のあるデータを参照することを避けるため、RDBMSはデータの参照や更新などの操作を待ち行列に入れてトランザクションが完了するのを待たせます。例題2でSELECT文が実行待機の状態になったのは、このためです。

　トランザクション中のデータベースへのアクセスを制御するために、RDBMSはデータベースに対して「ロック」を掛けます。RDBMSがデータベースをロックしている状態は、SQL Server Enterprise Managerで確認することができます。次のステップを実行してみましょう。

Enterprise Managerでロック状況を確認する

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

1. Microsoft SQL Servers
2. SQL Server グループ
3. サーバー名
4. 管理
5. 現在の利用状況
6. プロセス状況

画面3　プロセス状況を確認する（画面をクリックすると拡大表示します）

　まず、上のステップでプロセス状況を確認します。プロセスとは、RDBMSサーバで起動されるプログラムの単位で、クライアントから1つのセッションが起動するごとに、RDBMSサーバ上で1つのプロセスが起動されます。

　1つのセッションとは、データベースコネクションの単位で、同じクライアントからでも複数のクエリアナライザでデータベースサーバへ接続した場合は、複数のセッションが結ばれることになります。

　ここでは、「データベース」欄に「Northwind」と表示されているプロセスが2つあり、それぞれ51番と52番であることが確認できます。「データベース」欄は、プロセスが現在アクセスしているデータベースの名称です。「ユーザー」欄を確認すると、51番はsaで、52番はyamadaであり、クライアントからクエリアナライザで接続した2つのプロセスであることが確認できますね。

　では、プロセス51番がsa、プロセス52番がyamadaであることを覚えておき、次のステップを実行してみましょう。

Enterprise Managerでブロッキング状況を確認する

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

7. Microsoft SQL Servers
8. SQL Server グループ
9. サーバー名
10. 管理
11. 現在の利用状況
12. ロック　／　プロセスID

画面4　プロセス状況を確認する（画面をクリックすると拡大表示します）

　上のステップを実行すると、spid51、spid52、spid53の3つのプロセスが表示されているのが確認できるでしょう。spidはサーバプロセスIDの略です。このうち、先ほどの51番と52番に着目すると、51番が52番をブロッキングしていることが分かります（日本語訳は多少変ですが……）。これはつまり、例題1のトランザクション（spid51）が実行途中のため、例題2のSELECT文（spid52）が実行待機しているのです。

　続いて、次のステップを実行して、どのデータベースをロックしているのかを確認してみましょう。

Enterprise Managerでロックしているデータベースを確認する

(3) Enterprise Managerを起動する

(4) 左端に表示される「ツリー」で、以下を順に展開する

13. Microsoft SQL Servers
14. SQL Server グループ
15. サーバー名
16. 管理
17. 現在の利用状況
18. ロック　/　プロセスID
19. spid　51

画面5　ロックされているプロセスオブジェクトを参照する（画面をクリックすると拡大表示します）

　上のステップを実行すると、プロセスがロックしているオブジェクトの状態を参照することができます。「オブジェクト」欄を参照すると、「Northwind.dbo.Customers」が複数行にわたって表示されていることが確認できます。これは、例題1がUPDATE文で更新を掛けようとしているNorthwindデータベースのCustomersテーブルですね。ここでオブジェクトが複数行表示されている理由は、先ほど「データベースに対してロックを掛ける」と説明しましたが、実はデータベースがロックを掛ける単位はデータベースではなく、もっと細かな単位だからです。

　ロックを掛ける単位には、行単位、複数行をまとめたページやエクステントという単位、テーブル単位、データベース単位など、幾つかのレベルがあります。これは、データベース全体をロックしてしまうと、ほかのユーザーがアクセスできない（トランザクション実行待ちになる）可能性が高くなり、必要最小限の範囲をロックする方式をSQL Serverが採用しているためです。ロックする範囲をSQL文で明示的に指定することも可能ですが、通常はデータベースが最適なロック範囲を選択して自動的にロックをしますので、ユーザーは通常気にすることはありません。

　例題1でUPDATE文により更新を掛けようとしているCustomersテーブルのデータは、Countryが「UK」の列のみですので、そのデータが含まれる行だけがいくつかの範囲に分かれてロックが掛けられるため、上のステップのとおりオブジェクトが複数表示されています。

トランザクションのコミット

　では最後に、例題1のsaとしてログオンしたクエリアナライザで例題6を実行し、トランザクションを完了しましょう。

【例題3】

COMMIT TRANSACTION

　例題3を実行すると、トランザクションが完了して、データベースにデータの更新が反映されます。トランザクションをコミットしたタイミングで、データベースのロック状態は解除され、例題2のSELECT文の実行が再開されます。実際に、SQL Server Enterprise Managerで「現在の利用状況」を右クリックし「最新の状態に更新」を選択することで、ロック状態が解除されていることが確認できます。

画面6　トランザクションを完了する

　また、例題2の結果グリッドには、SELECT文の実行結果が表示されているのが確認できますね。

次回の予定

今回は、トランザクションの構成と実行について解説しました。次回は、さらに複雑なトランザクション処理とロックの詳細を紹介する予定です

「次回」へ

「SQL実践講座」バックナンバー