mysqlロック

ロックの種類

楽観的ロック

確実性に欠けるが事実上は問題が出ないで”あろう”というレベルのロック制御

update時にエラーとなるロック

ex ポイントシステム

悲観的ロック

更新対象のデータを読み出してから更新を終えるまでの間、他のユーザーがそのデータに触れないようロック

select時にかけるロック

ex ショッピングカートシステム

ロックをかける例(MYSQL)

楽観的ロック

テーブル構造

CREATE TABLE `point_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_version` (`user_id`,`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

初期データ

insert into point_log(user_id,version,value,created) values(1,1,1,now());

更新のエラーの例

クライアントA

mysql> START TRANSACTION;
mysql> insert into point_log(user_id,version,value,created) values(1,2,1,now());
mysql> commit

クライアントB

mysql>  insert into point_log(user_id,version,value,created) values(1,2,1,now());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

悲観的ロック

テーブル構造

CREATE TABLE `mission_counter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mission_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_501E0035BE6CAE90` (`mission_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

初期データ

insert into mission_counter(mission_id,count,created,updated) values(1,2,now(),now());
insert into mission_counter(mission_id,count,created,updated) values(2,2,now(),now());

READロック

クライアントA

mysql> START TRANSACTION;
mysql> LOCK TABLES mission_counter READ;
Query OK, 0 rows affected (0.00 sec)
mysql> UNLOCK TABLES;
mysql> COMMIT;

クライアントB

mysql> select * from mission_counter;
+----+------------+---------------------+---------------------+-------+
| id | mission_id | created             | updated             | count |
+----+------------+---------------------+---------------------+-------+
|  1 |          1 | 2013-02-03 09:23:41 | 2013-02-03 09:23:41 |     2 |
|  2 |          2 | 2013-02-03 09:24:15 | 2013-02-03 09:24:15 |     2 |
+----+------------+---------------------+---------------------+-------+
mysql> insert into mission_counter(mission_id,count,created,updated) values(3,2,now(),now());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

writeロック

クライアントA

mysql> START TRANSACTION;
mysql> LOCK TABLES mission_counter WRITE;
mysql> UNLOCK TABLES;
mysql> COMMIT;

クライアントB

mysql> select * from mission_counter;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into mission_counter(mission_id,count,created,updated) values(3,2,now(),now());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select LOCK IN SHARE MODE

共有モードで読み取りを行うというのは、まず最新の有効なデータを読み取り、そして読んだ行に共有モードを設定するという意味です。共有モード ロックは、読み取った行が別の人によって更新されたり、削除されたりする事を防ぎます。また、もし最新データが別のクライアント接続のコミットされていないトランザクションに属していたら、そのトランザクションがコミットされるまで待ちます。先行クエリが親 'Jones' を返すのを確認した後、child テーブルに子レコードを安全に追加し、トランザクションをコミットする事ができます。

クライアントA

 mysql> START TRANSACTION;
 mysql> SELECT * FROM mission_counter WHERE mission_id = 1 LOCK IN SHARE MODE;
 +----+------------+---------------------+---------------------+-------+
 | id | mission_id | created             | updated             | count |
 +----+------------+---------------------+---------------------+-------+
 |  1 |          1 | 2013-02-03 09:23:41 | 2013-02-03 09:23:41 |     3 |
 +----+------------+---------------------+---------------------+-------+
 mysql> COMMIT;

クライアントB

mysql> SELECT * FROM mission_counter WHERE mission_id = 1 LOCK IN SHARE MODE;
+----+------------+---------------------+---------------------+-------+
| id | mission_id | created             | updated             | count |
+----+------------+---------------------+---------------------+-------+
|  1 |          1 | 2013-02-03 09:23:41 | 2013-02-03 09:23:41 |     3 |
+----+------------+---------------------+---------------------+-------+
1 row in set (0.00 sec)
mysql> update mission_counter set count = 4 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select FOR UPDATE;

クライアントA

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mission_counter WHERE mission_id = 1 FOR UPDATE;
+----+------------+---------------------+---------------------+-------+
| id | mission_id | created             | updated             | count |
+----+------------+---------------------+---------------------+-------+
|  1 |          1 | 2013-02-03 09:23:41 | 2013-02-03 09:23:41 |     2 |
+----+------------+---------------------+---------------------+-------+
mysql> COMMIT;

クライアントB

mysql> SELECT * FROM mission_counter WHERE mission_id = 1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update mission_counter set count = 2 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction