https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
トランザクション分離レベルはデフォルトのREPEATABLE-READ。
> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
準備
DBを立ち上げてテーブルとレコードを入れる。
$ cat schema_and_data.sql
CREATE TABLE a (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) NOT NULL
);
CREATE TABLE b (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
a_id BIGINT UNSIGNED NOT NULL,
FOREIGN KEY (a_id) REFERENCES a (id)
);
INSERT INTO a (id, name) VALUES (1, '1');
INSERT INTO a (id, name) VALUES (2, '2');
INSERT INTO a (id, name) VALUES (3, '3');
INSERT INTO a (id, name) VALUES (8, '8');
INSERT INTO a (id, name) VALUES (9, '9');
INSERT INTO a (id, name) VALUES (10, '10');
$ cat start.sh
docker rm -f try-mysql-lock
docker run --name try-mysql-lock -p 4306:3306 -e "MYSQL_ALLOW_EMPTY_PASSWORD=yes" -e "MYSQL_DATABASE=try-mysql-lock" -e "TZ=Asia/Tokyo" --health-cmd='mysqladmin ping --silent' -d mysql:5.6 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
while [[ $(docker inspect --format "{{.State.Health.Status}}" try-mysql-lock) != "healthy" ]]; do printf "."; sleep 1; done
mysql -u root -h 0.0.0.0 -P 4306 -D try-mysql-lock < schema_and_data.sql
共有(Shared)ロック
その行を他がSELECTしたり共有ロックを取ることは許す。
-- Tx1
> SELECT * FROM a WHERE id = 2 LOCK IN SHARE MODE;
-- Tx2
> SELECT * FROM a WHERE id = 2 LOCK IN SHARE MODE; -- 他も共有ロックは取れる
> UPDATE a SET name='b' WHERE id = 3; -- これは通る。ロックは行単位なのでid=2だけロックされている。
> UPDATE a SET name='a' WHERE id = 2; -- これは止まる
たしかに1行ロックされている。
> SHOW ENGINE INNODB STATUS \G;
...
---TRANSACTION 2345, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f5de21d8700, query id 147 172.17.0.1 root init
SHOW ENGINE INNODB STATUS
...
ただのSELECTは分離レベルがSERIALIZABLEでない限りロックをとらない。 ロックしないと、他のトランザクションのCOMMITで 追加されたデータ(ファントム)を読んでしまったりするかというと、 MySQLのREPEATABLE-READではそうはならず、最初にSELECTしたときのスナップショットを返すようになっている。 ただし、LOCK IN SHARE MODEやFOR UPDATEを付けるとREAD-COMMITEDのように最新の結果を返す。
漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点
排他(eXclusive)ロック
その行に対して他がロックを取ることを許さない。SELECTできるかは分離レベルにより、REPEATABLE READではできる。
-- Tx1
> SELECT * FROM a WHERE id = 2 FOR UPDATE;
-- Tx2
> SELECT * FROM a WHERE id = 2 FOR UPDATE; -- 止まる。同じ行の排他ロックは取れない
> SELECT * FROM a WHERE id = 2 LOCK IN SHARE MODE; -- 止まる。共有ロックも取れない
逆に、共有ロックが取られている行に排他ロックも取れない。 ロックを取る順番によってはDeadlockになる。
ギャップロック
存在しない範囲のindexのレコードを一部でもロックすると、行ではなく範囲がロックされる。
-- Tx1
> SELECT * FROM a WHERE id > 5 FOR UPDATE;
-- Tx2
> INSERT a (id, name) VALUE (6, 'a'); -- 止まる。
> INSERT a (id, name) VALUE (4, 'a'); -- WHEREには含まれないがこれも止まる。
ギャップロックの場合、同じ範囲を排他ロックできてしまう。
-- Tx1
> SELECT * FROM a WHERE id = 5 FOR UPDATE;
-- Tx2
> SELECT * FROM a WHERE id = 5 FOR UPDATE; -- 止まらない
> INSERT a (id, name) VALUE (5, 'a'); -- 止まる(Tx1でも同じことをやるとDeadlock)
ネクストキーロック
範囲でロックすると一つ先のキーまでロックされる。 ギャップにファントムを発生させないためのもの。
-- Tx1
> SELECT * FROM a WHERE id < 6 FOR UPDATE; -- ギャップロック(id=4~7)
-- Tx2
> UPDATE a SET name = 'b' WHERE id = 8; -- 止まる
更新系クエリでかかるロック
INSERT
排他ロックがかかる。重複キーエラーになった場合は共有ロックがかかる。
-- Tx1
> INSERT INTO a (id, name) VALUE (5, 'b');
> INSERT INTO a (id, name) VALUE (3, 'd'); -- 重複キーエラー
-- Tx2
> INSERT INTO a (id, name) VALUE (5, 'c'); -- 止まる
> UPDATE a SET name = 'e' WHERE id = 3; -- 止まる
INSERT IGNOREしても同じ。そもそもこれは重複キー以外のエラーも無視した結果、変なデータが入ってしまうことがあるため、使わない方がよい。
> INSERT INTO a (name) VALUE (NULL); -- Column 'name' cannot be null
> INSERT IGNORE INTO a (name) VALUE (NULL); -- Query OKで空文字が入ってしまう
UPDATE, DELETE
排他ネクストキーロックがかかる。INSERTでの ON DUPLICATE KEY UPDATE も同様。
-- Tx1
> UPDATE a SET name='a' WHERE id BETWEEN 8 AND 9;
-- Tx2
> UPDATE a SET name='a' WHERE id = 9; -- 止まる
> UPDATE a SET name='a' WHERE id = 10; -- これも止まる
外部キー制約
さらに外部キー制約がかかるレコードを追加/更新/削除すると、参照するレコードも共有ロックされる。
-- Tx1
> INSERT INTO b (a_id) VALUES (1);
-- Tx2
> SELECT * FROM a WHERE id = 1 LOCK IN SHARE MODE; -- 通る
> SELECT * FROM a WHERE id = 1 FOR UPDATE; -- 止まる