占有ロック(FOR UPDATE)と共有ロック(LOCK IN SHARE MODE)

2022/11/13

トランザクション中で「UPDATE」や「DELETE」を行うとロックがかけられます。また、SELECTについても「占有ロック(FOR UPDATE)」と「共有ロック(LOCK IN SHARE MODE)」をかけることができます。ここでは、ロック周りの動作を確認します。
(動作確認は、MySQL5.7 InnoDBで行っています。)

占有(排他)ロックと共有ロック

UPDATE

トランザクション内で UPDATE を行った場合、占有(排他)ロック がかけられます。

SELECT

トランザクション内で FOR UPDATE を利用した SELECT を行った場合、占有(排他)ロック がかけられます。
トランザクション内で LOCK IN SHARE MODE を利用した SELECT を行った場合、共有ロック がかけられます。

関係 (ロックの解放待ち)

ロック種別	共有ロック	占有ロック
共有ロック	○ (※1)	× (※2)
占有ロック	× (※3)	× (※3)

※1
共有ロック中の場合、他のトランザクションからも共有ロックによるSELECTが可能です。

※2
共有ロック中の場合、他のトランザクションから UPDATE や 占有ロックによるSELECT はできません。
(ロックの解放待ち)

※3
占有ロック中の場合、他のトランザクションから UPDATE や 共有、占有ロックによるSELECT はできません。
(ロックの解放待ち)

動作確認
(共有ロック中の共有ロック)

セッション1

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM users WHERE `id` = 1 LOCK IN SHARE MODE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.01 sec)

セッション2

共有ロック同士なのでロック待ちになりません。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 LOCK IN SHARE MODE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

動作確認
(共有ロック中の占有ロック)

セッション1

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM users WHERE `id` = 1 LOCK IN SHARE MODE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.01 sec)

セッション2

ロック待ちになりました。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `users` SET `name` = "xxx" WHERE `id` = 1;

ロック待ちの状態でしばらく時間が立つと下記エラーが発生しました。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

動作確認
(占有ロック中の共有ロック・占有ロック)

セッション1

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 FOR UPDATE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

セッション2

占有ロック中なので共有ロックできません。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 LOCK IN SHARE MODE;

セッション3

占有ロック中なので占有ロックできません。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 FOR UPDATE;

行ロックとテーブルロック

占有(排他)ロック、共有ロックを行う際、対象カラムにINDEX または UNIQUE が設定されている場合、行ロックになります。

占有(排他)ロック、共有ロックを行う際、対象カラムにINDEX または UNIQUE が設定されていない場合、テーブルロックになります。

動作確認
(行ロック)

プライマリーキーの id に対してロックをかけます。

mysql> SHOW COLUMNS FROM `users`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

セッション1

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 FOR UPDATE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

セッション2

異なる行に対してロックをかけられますが、セッション1でロック済みの行に対してのロックはロック待ち状態になりました。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SELECT * FROM users WHERE `id` = 2 FOR UPDATE;
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users WHERE `id` = 1 FOR UPDATE;

動作確認
(テーブルロック)

セッション1

INDEX UNIQUE ともに設定されていない name を指定してロックをかけます。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `name` = "aaa" FOR UPDATE;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

セッション2

usersテーブル に対してロックがかけられているため、異なる行に対してのロックもロック待ちになりました。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users WHERE `name` = "bbb" FOR UPDATE;

ロック待ちクエリの確認

SELECT * FROM sys.innodb_lock_waits\G; でロック待ちクエリを確認できます。

mysql> SELECT * FROM sys.innodb_lock_waits\G;
*************************** 1. row ***************************
                wait_started: 2019-09-28 01:12:07
                    wait_age: 00:00:07
               wait_age_secs: 7
                locked_table: `sample`.`users`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 2395
         waiting_trx_started: 2019-09-28 00:59:11
             waiting_trx_age: 00:13:03
     waiting_trx_rows_locked: 4
   waiting_trx_rows_modified: 0
                 waiting_pid: 6
               waiting_query: SELECT * FROM users WHERE `id` = 1 FOR UPDATE
             waiting_lock_id: 2395:33:3:2
           waiting_lock_mode: X
             blocking_trx_id: 2403
                blocking_pid: 5
              blocking_query: SELECT * FROM sys.innodb_lock_waits
            blocking_lock_id: 2403:33:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2019-09-28 01:12:05
            blocking_trx_age: 00:00:09
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 5
sql_kill_blocking_connection: KILL 5
1 row in set, 3 warnings (0.01 sec)

ERROR: 
No query specified