[若葉マークブログ] 第 2 回 : ロックとは

みなさん、こんにちは。若葉マークブログの第 2 回は、ロックの基本についてお話したいと思います。

若葉マークブログの第 1 回は、トランザクションの基本についてお話しました。
トランザクションについてまだ十分に理解されていない方は、本 Blog をお読みになる前に [若葉マークブログ] 第 1 回 : トランザクションとは をご参照いただくことをお勧めいたします。

トランザクションと同様に、「ロック」という言葉も SQL Server を扱っていると日常的によく耳にすると思います。

■ロック (Lock) とは

トランザクション (関連する複数の処理を 1 つの処理単位としてまとめたもの) は、 1 つずつ実行されるのであれば、データは矛盾のない状態に保たれます。
しかし、SQL Server では同時に複数のユーザーが接続し、複数のトランザクションが並行して実行されています。
そのため、同じデータが同時に更新される可能性があり、もしこのような状況が発生するとデータに矛盾が生じてしまいます。

若葉マークブログ第 1 回 のトランザクションの場合と同様に、銀行口座の例を考えてみましょう。

A さんは翌日、銀行の ATM を使って、再度自分の 2 つの口座 の「口座 1 」 から「口座 2 」に 50,000 円 を振り込むことにしました。
口座 1 には現在は  250,000 円 の預金があり、口座 2 には 150,000 円の預金があります。

振り込みをおこなうと、口座 1 は 50,000 円引かれるので 200,000 円、口座 2 は 50,000 円足されるのでこちらも 200,000 円になるはずです。

ところが A さんが振り込み完了後 に  2 つの口座の預金額を確認したところ、口座 1 は 190,000 円、口座 2 は  200,000 円になっていました。
口座 1 から 10,000 円が余計に引かれてしまったのです。

しかし通帳記入をしてみて、A さんは納得しました。

振り込みの操作をしている間に、本日引き落とし予定だった通販で購入した毛布の代金が引き落とされていたのです。

ここに、 「ロック」 の活躍があります。
ロックは、データを参照または更新しているときに、他のトランザクションから同時に更新されないようにデータを保護する機能です。

振り込み処理と毛布代の引き落とし処理は、下図のようにおこなわれます。

振り込みトランザクションが更新対象のデータにロックをかけます。

毛布代トランザクションも同じデータを更新するためにロックをかけようとしますが、該当のデータは既に振り込みトランザクションによってロックされているため、毛布代トランザクションはロック待ち状態 (ブロッキング) が発生します。

振り込みトランザクションが更新を完了するとロックが解放され、ロック待ち状態となっていた毛布代トランザクションが、更新のためにロックをかけられるようになります。

このロックのおかげで、振り込み処理によって口座 1 から 50,000 円が引かれて 200,000 円になった後で毛布代の引き落とし処理がおこなわれ、口座 1 は最終的に 190,000 円という正しい預金額になったのです。

もしロックが存在しなかったら、口座 1 は 240,000 円、口座 2 は 200,000 円の預金額になる可能性があります。

振り込みトランザクションが口座 1 の現在の預金額 (250,000 円) を読み込みます。
毛布代トランザクションも口座 1 の現在の預金額 (250,000 円)を読み込みます。

振り込みトランザクションが現在の預金額から 50,000 円を引き、預金額を 200,000 円に更新します。
その後、毛布代トランザクションが現在の預金額から 10,000 円を引き、預金額を 240,000 円に更新します。

その結果、振り込みの 50,000 円は口座 1 から引かれずに、毛布代の 10,000 円のみが引かれた状態になり、データに矛盾が発生してしまいます。
( A さんとしては 10,000 円のみが引かれた状態になったほうが嬉しいかもしれませんが。。。)

このように、ロック機能は複数のユーザーが同時に利用するデータベースにとって欠かせない機能です。

■ブロッキング

上記の例では、毛布代トランザクションが同じデータを更新するためにロックをかけようとした際に、該当のデータが既に振り込みトランザクションによってロックされているため、毛布代トランザクションはロック待ち状態になりますが、このロック待ち状態のことを 「ブロッキング」 といいます。

ブロッキングは複数のトランザクションが並列実行されるデータベースにおいて、データを矛盾のない状態に保つための必要不可欠な事象ですが、SQL Server を使用するにあたり、ブロッキングは最小限に留めるように意識する必要があります。
その理由は、長時間のブロッキングはシステム全体のパフォーマンスに悪影響が出るためです。

また、ブロッキングが複数構成されることにより、 「デッドロック」 という事象が発生しやすくなります。
デッドロックについては本 Blog では取り上げませんが、複数のトランザクションがお互いにロックの解放を待っている状況であり、SQL Server では Deadlock Monitor がこの状況を検出し、解消する役割を担っています。

ブロッキングを最小限に留めるためには、 「必要最小限のデータのみにアクセスすること」 を心がけることが重要です。

このことを意識することによって、ロックを獲得する範囲を最小にし、アクセスするデータ量が少なくなってロックを保持している時間も短くなり、ブロッキングが発生する可能性を低くすることができます。

そしてデッドロックが発生する可能性も低くなり、トランザクションの実行時間も短くなって、結果としてシステム全体のパフォーマンス向上につながっていきます。

<参考情報>

○デッドロックを避けるコツ
https://blogs.msdn.com/b/jpsql/archive/2011/03/31/tips.aspx

○[SQL Troubleshooting] 第6回:ブロッキング情報を採取する (SQL Server 2000 ～ 2008 R2)
https://blogs.msdn.com/b/jpsql/archive/2012/07/24/sql-troubleshooting-6-sql-server-2000-2008-r2.aspx