Q:

MySQLのThe total number of locks exceeds the lock table size問題への対処方法

どうしても問題が解決できなく、ここで皆様のお知恵を拝借したく投稿しております。
現在MySQL（Ver 14.14 for linux）を使っています。ある一つの大きなテーブルを、小さなテーブルに分割したいのですが、ERROR 1206 (HY000): The total number of locks exceeds the lock table sizeが出てしまい、それの対処方法をお尋ねしたいです。
具体的には、
select count(*) from TABLE1 where DATE = '2019' and MODE = 1;

では
+----------+
| count(*) |
+----------+
|  9659724 |
+----------+

がデータの分割対象であり、
select count(*) from TABLE1 where DATE = '2018' and MODE = 1 and SIGNATURE1 = 'option1';

では、
+----------+
| count(*) |
+----------+
|   620984 |
+----------+

のデータが分割対象です。
行ったことは、以下の2つのコマンドを用いて、新しいtableを２つ作成しました。
1. create table smalltable_2019 as select * from TABLE1 where DATE = '2019' and MODE = 1;
2. create table smalltable_2018 as select * from TABLE1 where DATE = '2018' and MODE = 1 and SIGNATURE1 = 'option1';

結果としては、1のコマンドは問題なく新しいテーブルが作成できましたが、2のコマンドがエラーを発生します。
2のエラーは「ERROR 1206 (HY000): The total number of locks exceeds the lock table size」です。
webで対処方法を調べて、「innodb_buffer_pool_size=20G」をmy.cnfの中に追記しましたが、まだ同じエラーが出てしまいます。
質問ですが、
1. innodb_buffer_pool_size以外の方法でThe total number of locks exceeds the lock table sizeへの対処はありますでしょうか？
2. smalltable_2019はsmalltable_2018に比べて１０倍のデータを含んでいるのに問題なく作成される。しかし、なぜより小さなテーブル(smalltable_2018)を作成するのに、エラーが出るのかがわかりません。
もし解決方法がわかりましたら、ご教授をお願いします。
よろしくお願いします。

A:

https://stackoverflow.com/q/10253482/3090068
例えば上記のスレッドをみていると、当該 ERROR 1206 (HY000): The total number of locks exceeds the lock table size のエラーが発生するのは、裏で行ロックされるレコードがバッファプールの上に存在する必要があり、それがバッファプールに乗り切らなくなったから、だと推察できます。
今回のケースですと、「更新(create)に伴う select」ですので、 repeatable read 以上の consistency だと、 select 分の内部クエリは、 shared lock をそれぞれの行に対してかけることになります。
MySQL が更新時にかける行ロックは、実際にセレクトされたレコードだけではなく、 MySQL が走査(スキャン)した全てのレコードにかかります。 explain を行うとどのような走査が行われているかの確認ができます。
1. create table smalltable_2019 as select * from TABLE1 where DATE = '2019' and MODE = 1;
が実行できていることから、おそらく少なくとも DATE 列については index が貼ってあるかな、と思います。
例えば、 DATE の index が今回のそれぞれの create 文の select において実行されていた、とすると、 1 番の create 文と 2番の create 文でロックされる行の総数は、それぞれ DATE = 2018 と DATE = 2019 を満たす行の総数になります。おそらく、 DATE = 2019 の総数の方が多いのではないかな、と思っています。
一番手っ取り早い方法は、２つ目のクエリでも全てのロックするレコードがバッファプールに乗るように、バッファプールのサイズをあげることです。innodb_buffer_pool_size=20G を記載した、とのことですが、これがデータベースに反映されていることは確認されましたでしょうか。
メモリが足りないなどの理由でこれが実現できない場合としては、 DATE, MODE, SIGNATURE1 に対する複合インデックスを作成すると、おそらく走査の範囲が絞られて、今回のクエリは実行できるようになるかと思っています。

