mysql5.7のInnoDBテーブルに対する、delete文によるセカンダリインデックスカラムへの排他ネクストキーロック(exclusive next-key lock)のロック範囲
前提・実現したいこと
mysql5.7のInnoDBテーブルをノンユニークなセカンダリインデックスカラムで、並列処理にて行を一括削除したいが、排他ネクストキーロックのおそらく前方へのギャップロックにより、WHERE句条件の対象外のレコードもロックされ、並列処理で異なるWHERE句条件を指定してもロック待ちとなってしまう(最終的には、削除後にinsertまで実行しデータを入れ替えしたい)
上記を、並列処理でロック待ちを発生させずに処理したい。
※テーブルイメージ:売上明細情報テーブル
シーケンスID INT、年月 INT(YYYYMM)、、、その他の売上明細単位のカラム、、、
⇒ IDではもちろんユニークだが、年月ではユニークにはならない。
それを、並列処理にて、年月カラム(セカンダリインデックスあり)で削除して、データを入れ替えたい。
発生している問題・エラーメッセージ
Aプロセスが、トランザクションを開始
201709を削除(delete from 売上明細情報テーブル where 年月 = 201709)、OK
(この後insert用のデータ加工に時間がかかる)
Aプロセスのトランザクションの完了を待たずに、Bプロセスが、トランザクションを開始
201708を削除(delete from 売上明細情報テーブル where 年月 = 201708)、ここでロック待ち★
試したこと
トランザクション分離レベルは
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
これだと、リファレンスを見る限り難しそうだったので、、
トランザクション分離レベルを、"READ COMMITTED"に変更して打開策を探す。
デフォルトのautocommitを念のため、変更しておく
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from 売上明細情報テーブル where 年月 = 201709;
Query OK, 8345 rows affected (2.18 sec)
ここで、trx_rows_lockedを確認、16690(倍になる、、嫌な予感)
mysql> use information_schema;
Database changed
mysql> SELECT trx_rows_locked FROM INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 16690 |
+-----------------+
1 row in set (0.00 sec)
以下、別窓から
デフォルトのautocommitを念のため、変更しておく
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from 売上明細情報テーブル where 年月 = 201708;
ロック待ち★
疑問点:トランザクション分離レベルを"READ COMMITED"に変更することで
ギャップロックが無効化されるのを期待し、つまり排他ネクストキーロック
のインデックスレコードに対するレコードロックのみが適用されることを
意図した(その場合、異なるWHERE句条件で削除ができるはず)が依然として
ギャップロックされているように見える。。