メニューを閉じる

テクノモバイルグループ

メニューを開く

2019.06.13

DB

MySQL5.6のINSERT…SELECTでSELECT元データへのロック設定

【現象】
MySQL5.6でINSERT INTO T SELECT … FROM S WHERE …
を実行するとSELECT元のSのデータにロックが設定される。

【参考】MySQL5.6リファレンス
https://dev.mysql.com/doc/refman/5.6/ja/innodb-locks-set.html

・INSERT INTO T SELECT … FROM S WHERE … は、
T に挿入された各行に、ギャップロックなしの排他インデックスレコードロックを設定します。
トランザクション分離レベルが READ COMMITTED である場合、
または innodb_locks_unsafe_for_binlog が有効になっていて、トランザクション分離レベルが SERIALIZABLE でない場合、
InnoDB は一貫性読み取り (ロックなし) として S 上で検索を実行します。
それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。

【環境】
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.40 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_locks_unsafe_for_binlog%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+
1 row in set (0.00 sec)

⇒トランザクション分離レベルはREPEATABLE-READで、innodb_locks_unsafe_for_binlogはOFFなので
MySQL5.6リファレンスより「InnoDB は S から取得した行に共有ネクストキーロックを設定します」

【確認】
■テスト用データ登録

mysql> CREATE TABLE t1 (
-> id int(11) NOT NULL,
-> name varchar(64) NOT NULL DEFAULT '',
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t2 (
-> id int(11) NOT NULL,
-> name varchar(64) NOT NULL DEFAULT '',
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1, '1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(2, '2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(3, '3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(4, '4');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(5, '5');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

■SELECT元データ(t1)への共有ロックの設定を確認する
・セッション1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 SELECT id, name FROM t1 WHERE id >= 2 AND id <= 3;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

・セッション2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

⇒ロック待ちタイムアウトが発生

■セッションのトランザクション分離レベルをREAD COMMITTEDに変更して実行
・セッション1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO t2 SELECT id, name FROM t1 WHERE id >= 2 AND id <= 3;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

・セッション2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

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

mysql> UPDATE t1 SET name = '20' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

mysql> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 20 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)

⇒t1のデータへ共有ロックが設定されることなく更新完了。

※共有ネクストキーロックの範囲
t1にはidが1~5のデータが登録されていて、
INSERT INTO t2 SELECT id, name FROM t1 WHERE id >= 2 AND id <= 3;
ではid=2,3を対象としていますので、id=2,3には共有ロックが設定されますが、
次のid=4までがロックの対象となります。

■確認
・セッション1
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO t2 SELECT id, name FROM t1 WHERE id >= 2 AND id <= 3;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

・セッション2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT * FROM t1 WHERE id = 5 FOR UPDATE;
+----+------+
| id | name |
+----+------+
| 5 | 5 |
+----+------+
1 row in set (0.00 sec)


【テクノモバイルではエンジニア/デザイナーを積極採用中です!】

下記項目に1つでも当てはまる方は是非、詳細ページへ!
  • 自分でアプリを作ってみたい
  • ITで世の中にワクワクを生み出したい
  • 使いやすさ、デザインにこだわったWebサイトを開発したい

採用情報の詳細はこちら


Qangaroo(カンガルー)

  • 徹底した見やすさと優れた操作性で、テストの「見える化」を実現。
  • テストの進捗が見える。開発がスマートに進む。
  • クラウド型テスト管理ツール『Qangaroo(カンガルー)』
https://qangaroo.jp/

最近の記事

SNS共有

X CLOSE
X CLOSE
X CLOSE