質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.98%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,345

tannukimaru

score 11

前提・実現したいこと

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句条件で削除ができるはず)が依然として
ギャップロックされているように見える。。
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/24 22:29

    ご回答ありがとうございます。
    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でデータの入れ替えを並列で行うのは難しいという結論となりました。
    重ねてご回答感謝いたします。

    キャンセル

+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)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.98%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • MySQLに関する質問
  • mysql5.7のInnoDBテーブルに対する、delete文によるセカンダリインデックスカラムへの排他ネクストキーロック(exclusive next-key lock)のロック範囲