ゲーム開発日記

楽しいよね。

mysqlのロックまとめ

サマリー

・レコードがない状態でロックをとるとギャップロックが発生し、キーにしたもののレコードの間で共有ロックをとる
  • >レコードが存在しない可能性がある場合は、ロックをとらない
・ロックは値でとるので、INDEXで複数同じ値が入る時は注意 ->ロックはPRIMARY KEYで基本とる

・分離レベル

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

・テーブル構造

CREATE TABLE `user_ticket` (
  `id`                        bigint(20)  UNSIGNED NOT NULL, -- シーケンシャルid
  `user_id`                int(10)       UNSIGNED NOT NULL, -- チケット送りもと
  `to_user_id`           int(10)       UNSIGNED NOT NULL, -- チケット送り先
  `is_received`          tinyint(3)   UNSIGNED NOT NULL, -- 受け取ったかどうか
  PRIMARY KEY (`id`),
  KEY `i1` (`user_id`, `to_user_id`, `is_received`),
  KEY `i2` (`to_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

・レコード

for i in {1..100}; do mysql -e "INSERT INTO user_ticket VALUES(${i}, ${i}, 101 - ${i}, 0);"; done;

for i in {201..300}; do mysql -e "INSERT INTO user_ticket VALUES(${i}, ${i}, 301 - ${i}, 0);"; done;
mysql> select * from user_ticket limit 10;
+----+---------+------------+-------------+
| id | user_id | to_user_id | is_received |
+----+---------+------------+-------------+
|  1 |       1 |        100 |           0 |
|  2 |       2 |         99 |           0 |
|  3 |       3 |         98 |           0 |
|  4 |       4 |         97 |           0 |
|  5 |       5 |         96 |           0 |
|  6 |       6 |         95 |           0 |
|  7 |       7 |         94 |           0 |
|  8 |       8 |         93 |           0 |
|  9 |       9 |         92 |           0 |
| 10 |      10 |         91 |           0 |
+----+---------+------------+-------------+
10 rows in set (0.00 sec)

・PRIMARY KEY のロック検証
trxA

mysql> begin;

mysql> select * from user_ticket where id = 1 for update;

trxB

mysql> begin;

# 同じ値ならロック競合
mysql> select * from user_ticket where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# 違うなら競合しない
mysql> select * from user_ticket where id = 2 for update;
+----+---------+------------+-------------+
| id | user_id | to_user_id | is_received |
+----+---------+------------+-------------+
|  2 |       2 |         99 |           0 |
+----+---------+------------+-------------+

・PRIMARY KEY のギャップロック

trxA

mysql> begin;

mysql> select * from user_ticket where id = 105 for update;
Empty set

trxB

mysql> begin;

mysql> select * from user_ticket where id = 100 for update;
+-----+---------+------------+-------------+
| id  | user_id | to_user_id | is_received |
+-----+---------+------------+-------------+
| 100 |     100 |          1 |           0 |
+-----+---------+------------+-------------+

mysql> select * from user_ticket where id = 105 for update;
Empty set

# id 101 - 200ロック
mysql> INSERT INTO user_ticket VALUES(101, 101, 1, 0);
Lock wait timeout exceeded; try restarting transaction

# 間にレコードがあるので、301 以上にロックはかかってない
mysql> INSERT INTO user_ticket VALUES(301, 101, 1, 0);
Query OK, 1 row affected

indexの場合

trxA

mysql> begin;

mysql> select * from user_ticket where user_id = 1 and to_user_id = 100 and is_received = 0 for update;

trxB

mysql> begin;

# user_id同じだとロックウェイト
mysql> select * from user_ticket where user_id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# to_user_idだけで同じ値でもロックウェイト
mysql> select * from user_ticket where to_user_id = 100 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# 先頭のuser_idが違えば、残り同じでもロック競合しない
mysql> select * from user_ticket where user_id = 2 and to_user_id = 100 and is_received = 0 for update;
Empty set

# to_user_id違えば、ロック競合しない
mysql> select * from user_ticket where to_user_id = 1 for update;
+-----+---------+------------+-------------+
| id  | user_id | to_user_id | is_received |
+-----+---------+------------+-------------+
| 100 |     100 |          1 |           0 |
+-----+---------+------------+-------------+

# user_idが同じだとロックウェイト
mysql>  INSERT INTO user_ticket VALUES(500, 1, 500, 1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# 先頭のuser_idが違えば、残り同じでも競合しない
mysql>  INSERT INTO user_ticket VALUES(501, 10, 100, 0);
Query OK, 1 row affected

trxA

mysql> begin;

mysql> select * from user_ticket where user_id = 200 for update;

trxB

mysql> begin;

mysql>  INSERT INTO user_ticket VALUES(102, 10, 100, 0);

# user_idの100 - 200の間でギャップロックしてる
mysql> INSERT INTO user_ticket VALUES(103, 150, 100, 0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# 間でなければ入る
mysql> INSERT INTO user_ticket VALUES(102, 10, 100, 0);
Query OK, 1 row affected