May 24, 2012

RDBでレコードロックしなくともうまく排他制御できる場合がある

レコードロックによる排他制御は、RDBの常識として語られます。
でもそうでもありませんよ、という話です。

---------------------------------------------------------

よくある例は、銀行口座の、多重同時引き落としの例です。
残高確認と引き落としが入り乱れるといけないというものです。

---------------------------------------------------------

いま、私の口座の残金が、1000円
電力会社が、電気料金 1000円を引き落とし、
NHKが、視聴料 1000円を引き落とす
と仮定します。

引き落とし方法は、口座残高を確認して、残金があれば引き落とすということにします。

先に、電力会社が引き落としをすると残高が 0円になります。
続いて、NHKが引き落としをすると、残金不足となり、督促状を出します。

排他制御の失敗例は、こうなります。

電力会社とNHKがほぼ同時に引き落としをします。

まず、電力会社が口座残高を確認して、残金が1000円ありと確認し、引き落とし可能と判断します。
次に、NHKが口座残高を確認して、残金が1000円ありと確認し、引き落とし可能と判断します。
続いて、電力会社が口座金額を0円に変更します。
続いて、NHKが口座金額を0円に変更します。

現実では、銀行の係りさんが、NHKの引き落としのときに、残高不足を告げて矛盾は起きません。

でも、プログラムの世界では、電力会社とNHKは、独立した別タスクに相当し、矛盾が起きます。

---------------------------------------------------------

RDBのSQLで説明します。

口座残高を確認するSQL例

"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私"

引き落とし可能か判定する

"if(口座残高 >= 料金1000円){}"

新残金を計算する式

"新残金 = 口座残高 - 料金1000円"

引き落としを実施するSQL例

"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私"

電力会社タスクとNHKタスクが時間を置いて引き落とししたのでうまく行く場合

"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by 電力会社タスク
"if(口座残高 >= 料金1000円){" by 電力会社タスク
"新残金 = 口座残高 - 料金1000円" by 電力会社タスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by 電力会社タスク
"}" by 電力会社タスク

(時間)

"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by NHKタスク
"if(口座残高 >= 料金1000円){" by NHKタスク
"新残金 = 口座残高 - 料金1000円" by NHKタスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by NHKタスク
"}" by NHKタスク

電力会社タスクとNHKタスクがほぼ同時に引き落としをして失敗する例。

"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by 電力会社タスク
"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by NHKタスク
"if(口座残高 >= 料金1000円){" by 電力会社タスク
"新残金 = 口座残高 - 料金1000円" by 電力会社タスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by 電力会社タスク
"}" by 電力会社タスク
"if(口座残高 >= 料金1000円){" by NHKタスク
"新残金 = 口座残高 - 料金1000円" by NHKタスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by NHKタスク
"}" by NHKタスク

従来のレコードロック排他制御の説明例

"レコードロック開始 口座番号 = 私" by 電力会社タスク
"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by 電力会社タスク
"レコードロック開始 口座番号 = 私" by NHKタスク ==> NHKタスクは待機させられる
"if(口座残高 >= 料金1000円){" by 電力会社タスク
"新残金 = 口座残高 - 料金1000円" by 電力会社タスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by 電力会社タスク
"}" by 電力会社タスク
"レコードロック終了 口座番号 = 私" by 電力会社タスク

NHKタスクの待機が解除となり再開(レコードロックはしている)
"SELECT 口座残高 FROM 口座テーブル WHERE 口座番号 = 私" by NHKタスク
"if(口座残高 >= 料金1000円){" by NHKタスク
"新残金 = 口座残高 - 料金1000円" by NHKタスク
"UPDATE 口座テーブル SET 口座残高 = 新残金 WHERE 口座番号 = 私" by NHKタスク
"}" by NHKタスク
"レコードロック終了 口座番号 = 私" by NHKタスク

-------------------------------------------------------------------
[レコードロック排他制御の欠点]

レコードロック排他制御は、標準SQLでは、書けない
レコードロック排他制御を安易に多用すると、性能が著しく低下することがある
レコードロック排他制御を安易に多用すると、致命的なエラー、デッドロックを招くことがある

-------------------------------------------------------------------

で、ここからが、今回の主題。
この程度の例なら、引き落としSQLを変更すると、レコードロック排他制御が不要になります。

-------------------------------------------------------------------

引き落としを実施する良いSQL例

"UPDATE 口座テーブル SET 口座残高 = 口座残高 - 料金1000円 WHERE 口座番号 = 私 AND 口座残高 >= 料金1000円"

-------------------------------------------------------------------

このSQLの長所は、二つあります。

1. 変更対象のレコードに、口座番号だけでなく、口座残高にも条件をつけていること
2. 口座残高の計算をSQL内部で完成していること

したがって、レコードロック排他制御の必要はなくなります。
この良いSQLがエラーになれば、残高不足ということです。

No comments:

Post a Comment