MySQL InnoDBのロックの挙動

(2018-03-03)

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 MODEFOR 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; -- 止まる