MySQL DBロック自由自在！

対象読者

MySQLでFOR UPDATEを使用する際に、適切にロックがかけられない方へ向けた記事です。以下のような悩みをお持ちの方に役立ちます。

• 意図しない箇所までロックされてしまう
• なぜかINSERTができなくなる
• JOINが入るとどこがロックされているのかわからない
• ロックしているつもりの場所がロックされていない
• 条件を絞って適切な範囲だけをロックするのが難しい
• FOR UPDATEを書くとスロークエリになる
  これらのお悩みを解決する方法を提案します。

前置き

この記事は、特定の資料を参考にしたものではなく、私が実験して得られた結果を基に書かれたものです。MySQLのロックの内部的な仕組みを深く理解しているわけではありませんが、実用的な記事を目指して執筆しています。間違った情報が含まれている可能性があることをご了承ください。

なお、MySQLのバージョンは5.7を前提にしていますが、他のバージョン（例えばMySQL 8.0）では挙動が異なる可能性がありますので、その点もご留意ください。

SQLの例

伝票テーブル

id	伝票番号	購入者	購入価格
1	A001	佐藤テクノロジー株式会社	100,000
2	A002	田中イノベーションズ株式会社	200,000
3	A003	佐藤テクノロジー株式会社	1,000,000
4	A004	鈴木ソリューションズ株式会社	2,000,000

インデックスは以下の通りです。

キー名	カラム	ユニークか
PRIMARY	id	TRUE
伝票番号idx	伝票番号	TRUE
購入者idx	購入者	FALSE

では、idが1〜3のレコードを取得し、ロックするSQLを考えてみます。

SELECT * FROM `伝票` WHERE id IN (1,2,3) FOR UPDATE;

このクエリでは、id=1,2,3だけがロックされ、id=4は編集や追記が可能です。これが基本的な考え方です。

FOR UPDATEの基本

FOR UPDATEを書くときの重要なポイントは、プライマリーキーかユニークキーを使用し、明示的に示されるクエリを書くことです。

つまり、次のようにPRIMARYキーかユニークキーをFORCE INDEXして書いても、適切な速度で動作する必要があります。

SELECT * FROM `伝票` FORCE INDEX(PRIMARY) WHERE id IN (1,2,3) FOR UPDATE;

FOR UPDATEは、「どのキーが使われるか」が重要な場面ですので、FORCE INDEXを常に指定すると安心です。

不安が残るSQLの例

下記のような例では、ほぼ確実にPRIMARY INDEXが採用されますが、絶対ではありませんので、やや不安が残ります。

SELECT * FROM `伝票` WHERE id IN (1,2,3) FOR UPDATE;

USE INDEXは、おすすめのインデックスを指示しているだけなので、確実に使ってくれるとは限りません。

SELECT * FROM `伝票` USE INDEX(PRIMARY) WHERE id IN (1,2,3) FOR UPDATE;

安心できるSQLの例

このように、FORCE INDEXを指定すると、意図した範囲にインデックスが貼られます。
さらに、FORCE INDEXを指定しても、テーブル全体の件数が少ないと、FORCE INDEXが無視されることがありました。
その場合は、STRAIGHT_JOINを指定すると、PRIMARYが使われるようになりました。

SELECT STRAIGHT_JOIN * FROM `伝票` FORCE INDEX(PRIMARY) WHERE id IN (1,2,3) FOR UPDATE;

SELECT STRAIGHT_JOIN * FROM `伝票` FORCE INDEX(伝票番号idx) WHERE 伝票番号 IN ('A001', 'A002', 'A003') FOR UPDATE;

PRIMARY、ユニークではないカラムでのロック

インデックスのないカラムで条件を絞り込みたい場合はどうすれば良いでしょうか？
検索条件が、いつもPRIMARYキーがユニークキーであるとは限りません。

たとえば、購入金額には、現時点では何もインデックスを張っていません。

SELECT * FROM `伝票` WHERE 購入価格 = 1000000 FOR UPDATE;

このクエリでは、適切なインデックスがないため、全行がロックされてしまいます。

修正案としては下記の2パターンが考えられます。

SELECT STRAIGHT_JOIN * FROM `伝票` FORCE INDEX(PRIMARY) WHERE id IN (
    SELECT id FROM `伝票` WHERE 購入価格 = 1000000
) FOR UPDATE;

SELECT STRAIGHT_JOIN * FROM (
    SELECT * FROM `伝票` WHERE 購入価格 = 1000000
) TEMP
INNER JOIN `伝票` FORCE INDEX(PRIMARY) ON TEMP.id = `伝票`.id
FOR UPDATE;

JOINを使用したロック

FOR UPDATEの効力はサブクエリ内には及びません。
そのため、条件取得用のサブクエリとロック用の外側クエリを分けることで、適切な範囲をロックできます。
IN句に指定する方法より、FROM句に指定する方法の方が、LIMITが使えるためおすすめです。

SELECT STRAIGHT_JOIN
    TEMP.伝票id,
    TEMP.明細行番号,
    TEMP.購入価格
FROM (
    SELECT 
        `伝票`.id AS 伝票id,
        `明細`.id AS 明細id,
        `明細`.行番号 AS 明細行番号,
        `伝票`.購入価格
    FROM `伝票`
    INNER JOIN `明細` ON `伝票`.id = `明細`.伝票id
    WHERE 購入価格 = 1000000
    ORDER BY `伝票`.購入価格
    LIMIT 500
) TEMP
INNER JOIN `伝票` FORCE INDEX(PRIMARY) ON TEMP.伝票id = `伝票`.id
INNER JOIN `明細` FORCE INDEX(PRIMARY) ON TEMP.明細id = 明細.id
FOR UPDATE;

このようにして、適切な範囲をロックすることで、効率的なクエリを実行することができます。
このクエリの外側のINNER JOINは、あってもなくても取得される結果は同じです。
ロック範囲を指定するだけのためにJOINをしています。

結論

FOR UPDATEを使用する際には、プライマリーキーやユニークキーを明示的に使用すること、インデックスを適切に指定することが重要です。
適切なインデックスを使用することで、意図しないロックやパフォーマンス低下を避けることができます。
また、JOINやサブクエリを使用する場合は、クエリの工夫によって効率的にロックをかけることが可能です。

とりあえずPRIMARY KEYに対してロックをかけることができれば、概ね安心だと考えています。

この記事が、皆さんのMySQLでのロック処理の改善に役立つことを願っています。