MySQL_悲観ロックと楽観ロック #434
データベースの整合性を保つための排他制御として、手法の違いから悲観ロックと楽観ロックがあります。
今回はそれぞれの概要と、MySQLでのクエリについて整理します。
悲観ロック (Pessimistic Locking)
悲観ロックは、データベースの競合を回避するために、データが読み込まれる時点でロックをかける手法です。一つのトランザクションがデータをロックし、他のトランザクションが待機します。
使用シーン
データベースが頻繁に更新され、競合が起きやすいと予想される場合に適しています。
動作
トランザクションが特定のデータにアクセスする際、そのデータは他のトランザクションからアクセスできなくなります。ただし単なるSELECTは出来てしまうので、その点は注意です (MySQLのSELECT FOR UPDATEはできない)。
MySQLでの使用方法
こちらのブログの検証内容を引用します。
nameとcachというカラムを持つ、口座情報のデータベースがあるとします。
まずトランザクション1で口座情報を読み取ります。beginでトランザクションを開始し、その中で「SELECT FOR UPDATE」を実行することで、排他制御が開始されます。
prompt1> begin;
Query OK, 0 rows affected (0.00 sec)
prompt1> select * from accounts where name like 'foo' for update;
+----+------+------+
| id | name | cash |
+----+------+------+
| 1 | foo | 100 |
+----+------+------+
1 row in set (0.00 sec)この状態だと、他のトランザクションからのSELECT FOR UPDATEがブロックされます。
prompt2> begin;
Query OK, 0 rows affected (0.00 sec)
prompt2> select * from accounts where name like 'foo' for update;
...他がブロックされている間に、トランザクション1で口座情報を更新して内容をコミットできます。
prompt1> update accounts set cash = 150 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
prompt1> commit;
Query OK, 0 rows affected (0.01 sec)コミットが完了すると、他のトランザクションの処理が再開されます。先ほどトランザクション1で更新した内容がSELECTされ、その内容をもとにデータを更新できます。
prompt2> select * from accounts where name like 'foo' for update;
+----+------+------+
| id | name | cash |
+----+------+------+
| 1 | foo | 150 |
+----+------+------+
1 row in set (13.00 sec)
prompt2> update accounts set cash = 250 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
prompt2> commit;
Query OK, 0 rows affected (0.00 sec)つまり悲観ロックとは、変更中のデータへのSELECTやUPDATEをブロックすることによって排他制御する手法です。
ただしMySQLでは悲観ロック中も単なるSELECTは出来てしまうので、データ更新前はSELECT FOR UPDATEを使う前提にしておく必要はあります。
楽観ロック (Optimistic Locking)
楽観ロックは、競合が稀であると予想される場合に、データの整合性を保つために使用される手法です。
複数のトランザクションが同時にデータベースにアクセスし、それぞれがデータのバージョンをチェックしてから更新をかけます。同時にアクセスしたうち一つのトランザクションが成功し、他のトランザクションが競合により再試行する形になります。
使用シーン
競合が少ないと予想される読み取り多用のアプリケーションで効果的です。
動作
トランザクションがコミットされる際、そのデータのバージョンをチェックします。もし他のトランザクションによってデータが変更されていた場合、競合が発生し、トランザクションは再試行または失敗します。
バージョンは単純な連番であることもあれば、タイムスタンプを使うこともあります。
MySQLでの使用方法
こちらのブログの検証内容を引用します。
nameとcachに加えて、versionというカラムを持つ口座情報のデータベースがあるとします。
まず、トランザクション 1 が口座残高と現在のバージョン番号を確認します。
prompt1> begin;
Query OK, 0 rows affected (0.00 sec)
prompt1> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
| 1 | 1 | foo | 100 |
+----+---------+------+------+
1 row in set (0.00 sec)ここでは排他制御はかかっていないので、トランザクション2でも口座残高と現在のバージョン番号を確認できます。
prompt2> begin;
Query OK, 0 rows affected (0.00 sec)
prompt2> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
| 1 | 1 | foo | 100 |
+----+---------+------+------+
1 row in set (0.00 sec)トランザクション1が先んじて口座残高に 50 を加算した内容でupdate文を発行します。ポイントは where 条件でバージョン番号を指定している点と、バージョン番号を新しいものに更新している点です。
prompt1> update accounts set version = 2, cash = 150 where name like 'foo' and version = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0コミットする前に、トランザクション2で口座残高に100を加算するupdate 文を発行してみます。しかし、トランザクション1の update 文によって暗黙の排他ロックが行にかかっているためブロックされます。
prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1;
...ここでトランザクション1をコミットし、updateを確定させます。
prompt1> commit;
Query OK, 0 rows affected (0.00 sec)次が楽観ロックのポイントです。トランザクション1の排他ロックがcommit によって解除され、トランザクション2の処理が継続します。 しかし、トランザクション1がバージョン番号を更新しており、トランザクション2のwhere条件に引っ掛からず、Rows matched: 0 になります。つまりこれによって、自分が変更しようとした行が既に別のトランザクションで変更されていることに気付くことができる、ということです。
prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1;
Query OK, 0 rows affected (12.56 sec)
Rows matched: 0 Changed: 0 Warnings: 0この時点で、トランザクション 2 は一旦変更内容を rollback で破棄します。
prompt2> rollback;
Query OK, 0 rows affected (0.00 sec)この後は再度処理を試みるなりが可能です。
ここまでお読みいただきありがとうございました！
参考
この記事が気に入ったらサポートをしてみませんか？