mysqlのロックまとめ
サマリー
・レコードがない状態でロックをとるとギャップロックが発生し、キーにしたもののレコードの間で共有ロックをとる
- >レコードが存在しない可能性がある場合は、ロックをとらない
・分離レベル
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