MySQL 8.0 のSQL実行時のロック仕様についてまとめました。
当記事はストレージエンジンがInnoDB であることを前提としています。
DMLのロック仕様
DMLとは、データベースを管理・操作するための言語のことです。
種類としては、SELECT/UPDATE/DELETE/INSERTが該当します。
この中でSELECTについては基本的にロックは発生しませんが、意図的に共有ロックを取得することもできます。（ SELECT ... FOR UPDATE および SELECT ... FOR SHARE等）
UPDATE/DELETE/INSERTが実行されると排他ロックが取得され、他のSQLの実行を一部制限します。
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000#
共有ロックと排他ロック
排他ロック: 他のSQLの実行を読み取りも書き込みもブロックする
共有ロック: 読み取りを許可するが、排他ロックを行おうとするクエリはブロックする
参考サイト：
https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000#
ロックの範囲
SQLを実行した際、基本的にはテーブル単位か行単位でロックが取得されます。
MySQLのデータベースエンジンにはMyISAMとInnoDBがあります。
InnoDBにはレコード単位のロック「行ロック」が実装されています。
InnoDBでは設定と抽出条件が合えば基本的には「行ロック」となります。（そうでなければテーブルロック）
ここでいう設定とは、インデックスの設定となります。
抽出条件はWHEREです。（※正確にはSQL実行時にスキャンされた行が対象となる）
インデックスを利用していない検索の場合は行ロックではなくテーブルロックが発生します。
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
https://nishinatoshiharu.com/table-row-lock-difference/
SELECT文のロック範囲
SELECT ... FROMのみであれば基本的に行ロックを取得しません。(※1)
SQL文で行ロックを取得したい場合は、SELECT ... FOR SHARE およびSELECT ... FOR UPDATE ステートメントが利用できます。
これらのステートメントはスキャンされた行のロックを取得し、結果に含まれない行のロックを解除します 。(WHERE 句で抽出されていない行はロックされない)
SELECT ... FOR SHARE
SELECT ... FOR SHAREは読み取られる行に共有ロックを設定します。
ほかのセッションもその行を読み取ることができますが、トランザクションがコミットするまで変更することはできません。 これらの行のいずれかがコミットされていない別のトランザクションによって変更された場合、クエリーはそのトランザクションが終了するまで待機してから、最新の値を使用します。
SELECT ... FOR UPDATE
検索で検出されたインデックスレコードについては、それらの行に対して 排他ロックがかかる。
他のトランザクションは、これらの行の更新、SELECT ... FOR SHARE の実行、または特定のトランザクション分離レベルでのデータの読取りをブロックします。
一貫性読み取りでは、読み取られたビュー内に存在するレコードに設定されたロックはすべて無視されます。 (古いバージョンのレコードはロックできません。
レコードのインメモリーコピー上の Undo ログに適用することで、再構築されます。)
※1
トランザクションの分離レベルが SERIALIZABLE に設定されていれば、検索で見つかったインデックスレコード上に共有ネクストキーロックが設定されます。( InnoDB のデフォルトの分離レベルは REPEATABLE READ です。ネクストキーロックは行単位)
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
MySQL 8.0 リファレンスマニュアル 115.7.2.1 トランザクション分離レベル
MySQL 8.0 リファレンスマニュアル 15.7.4 ファントム行（ネクストキーロックの話）
MySQL 8.0 リファレンスマニュアル 15.7.2.4 読取りのロック（FOR SHARE、FOR UPDATEの話）
UPDATE文のロック範囲
UPDATE ... WHERE ... は、検索で見つかったすべてのレコード上に排他ロックを設定します。
ただし検索条件（WHERE）で指定した行にインデックスがない場合はテーブルロックとなります。
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
DELETE文のロック範囲
DELETE FROM ... WHERE... は、検索で見つかったすべてのレコード上に排他ロックを設定します。
ただし検索条件（WHERE）で指定した行にインデックスがない場合はテーブルロックとなります。
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
INSERT文のロック範囲
INSERT は、挿入される行に排他ロックを設定します。
つまり、挿入しようとしているデータにのみ排他ロックがかかります。
同じデータを同時に挿入しようとすると、受付が早い方で排他ロックが取得され、受付が遅い方の挿入では処理がエラーとなります。
参考サイト：
MySQL 8.0 リファレンスマニュアル 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック
