Open7
mysql レコードロック
syymysql> show create table a ;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from a;
+----+---------+------+
| id | user_id | a |
+----+---------+------+
| 1 | 1 | 1 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 20 | 20 | 20 |
+----+---------+------+
4 rows in set (0.00 sec)
存在するレコードの排他ロックを取得。
他トランザクションは同じレコードの排他ロックが取れない。
A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where id = 1;
+----+---------+------+
| id | user_id | a |
+----+---------+------+
| 1 | 1 | 1 |
+----+---------+------+
1 row in set (0.01 sec)
mysql> select * from a where id = 1 for update;
+----+---------+------+
| id | user_id | a |
+----+---------+------+
| 1 | 1 | 1 |
+----+---------+------+
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
存在しないレコードの排他ロックを取得。
他トランザクションでも同じidで排他ロックが取得できる。
ほかトランザクションで同じidにinsertは待たされる。updateはできる。
A
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
B 同じidで排他ロック取得できる
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
B insertとupdate試す。insertは競合。updateは実行できた。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(2,2,2);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update a set a = 2 where id = 2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from a;
+----+---------+------+
| id | user_id | a |
+----+---------+------+
| 1 | 1 | 1 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 20 | 20 | 20 |
+----+---------+------+
4 rows in set (0.00 sec)
存在しないレコードの排他ロックを取得しあうとデットロックが走る。
A
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
mysql> insert into a values (2,2,2);
# BがデットロックによりロールバックされるとAのinsertが通るようになる
Query OK, 1 row affected (8.64 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
mysql> insert into a values (2,2,2);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
存在しないレコードの排他ロックを取得すると、
他トランザクションではindexのギャップにinsertできない。
A :
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a;
+----+---------+------+
| id | user_id | a |
+----+---------+------+
| 1 | 1 | 1 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 20 | 20 | 20 |
+----+---------+------+
4 rows in set (0.00 sec)
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
B :
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values (3,3,3);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
select for updateやupdate、deleteで対象が取得できないと代わりにギャップロックをくれるのか?
syyupdateやdelete時は他のトランザクションがinsertしたレコードの影響を受けることがある。
repeatable readではなくread committedのような挙動に似ている。
update時にindexがはられていないparamを条件に使うと、テーブルがロックされる。他のトランザクションでinsertされているとそれがコミットされるまで待つ。条件に使用したカラムにindexが貼られていて、ロックがかぶらない場合はロック待ちは発生しない。
+----+-------+
| id | param |
+----+-------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
+----+-------+
A。update
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from b;
+----+-------+
| id | param |
+----+-------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
+----+-------+
3 rows in set (0.00 sec)
## ここでBがinsertする。
mysql> update b set param = true where param = false;
## Bがコミットされるまで待つ
Query OK, 4 rows affected (11.91 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from b;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+----+-------+
4 rows in set (0.00 sec)
B。insertする
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into b values (4, false);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)