refirio.org

Memo

■ロックによる排他制御

MySQLのプロセス確認と強制終了（デッドロック解除）の方法は、サーバメモの Command.txt の「MySQLでプロセスを確認＆強制終了」を参照
ロックについては、このファイル内の「トランザクション」についても参照（トランザクション分離レベルについては注意が必要）

以下の記事は以前に試したときのメモ
FOR UPDATE による行ロックの具体例がある
（この内容以外にも、トランザクション分離レベルについては注意が必要。またボタンを連打されなように非活性化するなどの処理も入れておくべき）

Webアプリケーションへの同時アクセス対策メモ | refirio.org
http://refirio.org/view/367

CREATE TABLE や TRUNCATE TABLE を実行した場合は、暗黙的にコミットされてロックが外れる（テンポラリテーブルの場合は外れない）
…など例外的な挙動もあるようなので注意する。要勉強

デッドロックが発生した場合、「SHOW ENGINE INNODB STATUS;」で詳細を確認できるみたい

なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳
https://soudai.hatenablog.com/entry/2017/12/20/030013

以下、ロックの挙動を検証したときのメモ
主に以下のページを参考にしている

DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG
https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000

デッドロックについては、後述の「デッドロックの具体例」も参照

■前提

以下のテーブルを作成し、データを登録しているものとする
（トランザクション分離レベルは、MySQLデフォルト設定の REPEATABLE READ としている）

CREATE TABLE test (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    text VARCHAR(80),
    PRIMARY KEY(id)
) ENGINE=InnoDB;

INSERT INTO test VALUES(1, 'TEST1');
INSERT INTO test VALUES(2, 'TEST2');
INSERT INTO test VALUES(3, 'TEST3');
INSERT INTO test VALUES(4, 'TEST4');
INSERT INTO test VALUES(5, 'TEST5');

以下のとおり、データが登録されていることを確認できる

> SELECT * FROM test;
+----+-------+
| id | text  |
+----+-------+
|  1 | TEST1 |
|  2 | TEST2 |
|  3 | TEST3 |
|  4 | TEST4 |
|  5 | TEST5 |
+----+-------+


以下で値の編集を確認できる
複数端末から実行しても編集できる

> SELECT * FROM test;
> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2;
> SELECT * FROM test;


この前提で、トランザクション・共有ロック・排他ロックについて確認する
2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する

■トランザクションの確認mysql1> BEGIN;
mysql2> BEGIN;

mysql1> SELECT * FROM test;
mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;
mysql1> SELECT * FROM test;                                     … 更新を確認できる

mysql2> SELECT * FROM test;                                     … 他端末からは更新を確認できない

mysql1> COMMIT;
mysql2> SELECT * FROM test;                                     … 他端末からは更新を確認できない
mysql2> COMMIT;
mysql2> SELECT * FROM test;                                     … 他端末からも更新を確認できる
■共有ロックの確認（LOCK IN SHARE MODE）

トランザクションが終了されるまで共有ロックを継続して取得し続ける
別のトランザクションからの読み取りを許可するが、書き込みは許可しない状態になる

mysql1> BEGIN;
mysql2> BEGIN;

mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE;
mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;

mysql2> SELECT * FROM test WHERE id = 2;                        … 他端末からは更新を確認できない
mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE;     … 他端末からは共有ロックがブロックされる
mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;   … 他端末からは更新もブロックされる

mysql1> COMMIT;
mysql2> COMMIT;
mysql2> SELECT * FROM test WHERE id = 2;                        … コミットされたので、他端末からも更新を確認できる
■排他ロックの確認（FOR UPDATE）

トランザクションが終了されるまで排他ロックを継続して取得し続ける
別のトランザクションからの読み取り＆書き込みの両方を許可しない状態になる
※ただしInnoDBでトランザクション分離レベルが REPEATABLE READ の場合、操作もとでコミットされると読み取りできる
  REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値なので、実質変更途中のデータを取得されることが無いためだと思われる

mysql1> BEGIN;
mysql2> BEGIN;

mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE;
mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;

mysql2> SELECT * FROM test WHERE id = 2;                        … 他端末からは更新を確認できない（参照自体はできる）
mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE;             … 他端末からは排他ロックがブロックされる
mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;   … 他端末からは更新できない

mysql1> COMMIT;
mysql2> COMMIT;
mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2;  … コミットされたので、他端末からも更新できる
■排他ロックの確認（READ COMMITTED + FOR UPDATE）

トランザクション分離レベルを READ COMMITTED にし、更新の前には FOR UPDATE で排他ロックをかける方法
予約処理の定員チェックなど、厳密性を求められる場面では基本的にこの処理が良さそう

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql1> BEGIN;
mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql2> BEGIN;

mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE;
mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;

mysql2> SELECT * FROM test WHERE id = 2;                        … 他端末からは更新を確認できない（参照自体はできる）
mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE;             … 他端末からはロックできない
mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2;   … 他端末からは更新できない

mysql1> COMMIT;
mysql2> SELECT * FROM test WHERE id = 2;                        … 操作もとでコミットされた時点で、他端末からも更新を確認できる
mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2;  … 操作もとでコミットされた時点で、他端末からも更新できる
■引き続きの勉強中メモ

・トランザクション内でロックしたら、コミットしてから次の処理が進む
  ロックは、定員オーバーチェックの最初だけ行うといい。同じ処理の中にfor updateが2回あると問題。1回だとデッドロックは理論的に起こらない。はず
  予約者をINSERTしていくような行が増えるテーブルはロックできない
・ダミーテーブルをロック、という手法は無いか
  「予約者をINSERTしていくような行が増えるテーブルはロックできない」があるので、適当なテーブルで行うべきでは無いか
  データが1行で「ロックしているか否か」の値を持つだけのテーブルをロックするのなら有効か
・「ダミーテーブルをロック」が有効なら、ダミーファイルを配置しておいてファイルロックをかけ、それによって排他制御を行うのは有効か
  サーバが複数台構成の場合は使えないので、かえってややこしくなるだけか
・ミドルウェアの設定で「デッドロックを検知した瞬間にエラーで落ちる」とかできないか
  以下などでは「スレッドを特定して強制終了」となっているので、そのような設定は無さそうだが

MySQLでロックを特定し、強制終了する - Qiita
https://qiita.com/RyutaKojima/items/a76f4cd4c94d1989a4a5

【障害対応】MySQLでデットロックが発生した時の対応方法 - 気ままに
https://unot13.hatenablog.com/entry/2018/08/07/174951

MySQLでロックを特定、強制終了してみた｜SHIFT Group 技術ブログ｜note
https://note.com/shift_tech/n/n808984951f92

以下は参考になりそうなサイト

嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記
https://blog.tokumaru.org/2015/05/blog-post.html

MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance
http://d.hatena.ne.jp/LukeSilvia/20110123/p1

MySQL - InnoDBのロック関連まとめ - Qiita
https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8

MySQL テーブルのロック - とみぞーノート
http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E...

デッドロックを回避するために - 技術ブログ | 株式会社クラウディア
https://cloudear.jp/blog/?p=1335

MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary
https://ichirin2501.hatenablog.com/entry/2015/12/24/164916

doc/innodb.md at master - ichirin2501/doc
https://github.com/ichirin2501/doc/blob/master/innodb.md

MySQLでSELECT FOR UPDATEと行ロックの挙動を検証してみた - JUST FOR FUN
http://taiga.hatenadiary.com/entry/2018/02/12/170109

MySQLのトランザクション処理中にALTERするとコミットされる - ペチパーノート
http://butterbull.hatenablog.com/entry/2014/06/12/150756

ソシャゲエンジニアの自分が開発に必須だなと思った知識（MySQL編） - Qiita
https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb

第75回　MySQLのさまざまなタイムアウトオプションについて：MySQL道普請便り｜gihyo.jp … 技術評論社
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075

InnoDB の行レベルロックについて解説してみる - あらびき日記
https://abicky.net/2016/11/30/082130/

MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ
https://tech.mti.co.jp/entry/2017/12/27/190733

MySQL(InnoDB)の行ロック - フリエン生活
https://free-engineer.life/mysql-innodb-record-locks/

MySQL １つのテーブルでデッドロックさせる│システムガーディアン株式会社
https://sys-guard.com/post-15568/

データベースのロックの基礎からデッドロックまで
https://zenn.dev/gibjapan/articles/1d8dfb7520dabc
Memo

Advertisement
