mysql5.7のInnoDBテーブルに対する、delete文によるセカンダリインデックスカラムへの排他ネクストキーロック（exclusive next-key lock）のロック範囲

Question

###前提・実現したいこと

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句条件で削除ができるはず）が依然として
ギャップロックされているように見える。。

Answer 1

すでに解決済みにされてしまったのですが、ちょっとだけ補足を。
私が提案したのは副問い合わせではなく、クエリをMySQLに投げるプログラム側で売上明細情報テーブルのプライマリキーを取得してからwhere inのSQLを生成してdeleteをかけるという意味です。

select id from 売上明細情報テーブル where 年月 = 201709;

上記のクエリでidを取得してから、

delete from 売上明細情報テーブル where id in (1,3,5,6,10,...);

のように、IN句の中身を作ってクエリを投げると。
実際にやってみたところ、デフォルトのREPEATABLE-READでもokでした。
あとは一度にdeleteする件数を現実的な値(1000件ずつとか)にしてあげればいいでしょう。

対象テーブルinfo

mysql> select * from info;
+----+----------+-------+
| id | date     | value |
+----+----------+-------+
|  1 | 20171025 | aaa   |
|  2 | 20171025 | bbb   |
|  3 | 20171024 | ccc   |
|  4 | 20171024 | ddd   |
+----+----------+-------+
4 rows in set (0.00 sec)

接続その1(対象：date=20171025)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from info where id in (1,2);
Query OK, 2 rows affected (0.00 sec)

接続その2(対象：date=20171024)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from info where id in (3,4);
Query OK, 2 rows affected (0.01 sec)

Answer 2

対象レコードのプライマリキーを取得してから、プライマリキーでdeleteしたらどうでしょうか？
プライマリインデックスに対するwhere inならレコードロックになるので、デッドロックは発生しないと思います。
試してないので憶測ですが。

Comments

[tannukimaru]

ご回答ありがとうございます。
PKカラムの指定でユニークに1件ずつ削除すれば、他方でロック待ちは発生しないようです（分離レベルがREPEATABLE-READであっても）。
また、where in句で動的（... where id in (select id from (select id from hoge where non_unique_secondary_index_column = value) tmp)）にPKカラムを取得指定して削除しようとすると、全行ロックされているような動きとなりました。
個別削除だと、削除に時間がかかりすぎてしまうのと、別問題としてその後のinsertで再度ロック待ちが発生してしまい、、結果、del&insertでデータの入れ替えを並列で行うのは難しいという結論となりました。
重ねてご回答感謝いたします。