【MySQL】InnoDBの行ロックとテーブルロックの違いを検証してみる

データベース

MySQLのデータベースエンジンにはMyISAMとInnoDBがあります。

InnoDBにはレコード単位のロック『行ロック』が実装されています。
行ロックが実行されているテーブルにアクセスする場合、行ロック中のレコード以外はロック待ちをせずにデータが取得できます。

しかしインデックスを利用していない検索の場合は行ロックではなくテーブルロックとなってしまいます。

今回はInnoDBの行ロックとテーブルロックの検証について紹介します。

行ロックとテーブルロックの挙動確認

以下のようなusersテーブルを作成し、行ロックとテーブルロックの挙動を確認してみます。

> SELECT * FROM users \G;
*************************** 1. row ***************************
        id: 1
 last_name: Franecki
first_name: Cherri
       age: 22
*************************** 2. row ***************************
        id: 2
 last_name: Conroy
first_name: Columbus
       age: 18
2 rows in set (0.00 sec)

行ロックになるケース

last_nameにインデックスが作成されている場合UPDATE users SET age = 22 WHERE last_name = 'Franecki';は行ロックになります。
トランザクション内でUPDATEを実行するターミナルを『セッションA』と呼ぶことにします。

セッションA

-- Primaryキーとlast_nameにインデックスが作成されている
> SHOW INDEX FROM users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_users_on_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

-- 『BEGING』のかわりに『START TRANSACTION』でもOK
> BEGIN;
Query OK, 0 rows affected (0.01 sec)

-- インデックスが作成されているカラムを検索条件に利用
> UPDATE users SET age = 22 WHERE last_name = 'Franecki';

『セッションA』のUPDATEは行ロックのため、『セッションA』が実行中でも別レコードであればusersテーブルにアクセスができます。

セッションB

> SELECT * FROM users WHERE last_name = 'Conroy' FOR UPDATE \G;
*************************** 1. row ***************************
        id: 2
 last_name: Conroy
first_name: Columbus
       age: 18

テーブルロックになるケース

last_nameにインデックスが作成されていない場合UPDATE users SET age = 22 WHERE last_name = 'Franecki'はテーブルロックになります。

セッションA

-- Primaryキーにのみインデックスが作成されている
> SHOW INDEX FROM users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

-- 『BEGING』のかわりに『START TRANSACTION』でもOK
> BEGIN;
Query OK, 0 rows affected (0.01 sec)

-- インデックスが作成されていないカラムを検索条件に利用
> UPDATE users SET age = 22 WHERE last_name = 'Franecki';

『セッションA』のUPDATEはテーブルロックのため、『セッションA』が実行中の場合はusersテーブルの全レコードがロック待ち対象になります。

セッションB

> SELECT * FROM users WHERE last_name = 'Conroy' FOR UPDATE \G;
-- 結果が返ってこない

まとめ

テーブルロックが発生する事象に遭遇した場合はまず『インデックスがきちんと使われているか』を確認するようにしましょう。
また、意図しないテーブルロックを防ぐためにもトランザクション内で実行する更新クエリの検索条件には留意しましょう。

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！

参考

RECOMMEND