猫型iPS細胞研究所

Windows、Linux、iOS、DB、Network

DataBase MySQL

MySQL:エラー:The total number of locks exceeds the lock table size

投稿日:2013年2月4日 更新日:

The total number of locks exceeds the lock table sizeは、大量にデータを削除する場合等に発生する可能性があります。

見ての通り、ロックするメモリー不足です。
なお、InooDBは行ロック、MyIsamはテーブルロックのようです。

メモリーの拡張

一般的にはinnodb_buffer_pool_sizeを拡張することで対応可能です。
しかしinnodb_buffer_pool_sizeは動的システム変数ではありません。
そのため一応再起動が必要です。

また、innodb_buffer_pool_sizeは、
グローバル変数であるため、
(セッション変数は接続数に応じて消費されるので大量の割り当てはさけるべきです)
データベースサーバーの場合、総メモリーの7~8割を当ててもよいとされています。
その割には、初期値が8M(8388608)しかありません・・・。

さらに、innodb_log_file_sizeも合わせて変更しなければ、パフォーマンスの向上が望めず、
別の問題を引き起こすかもしれません。
(innodb_log_file_size×innodb_log_files_in_group > innodb_buffer_pool_size)

こうした点を踏まえてinnodb_buffer_pool_sizeを変更する分には、これが直接的かつ唯一の対応策となるでしょう。

SQLの見直し

先程も述べたように、innoDBは行ロックを行います。
SQL Serverのようなロックエスカレーション(あまりに多い行ロックはメモリー効率が悪くなるのでテーブルロックになる)
もありません。

そのため、削除の場合は単純に条件を十分に絞り込むことや、削除を複数に分けることでこのエラーを回避することも可能です。
もちろん、こうした対応ができない場合はinnodb_buffer_pool_sizeの変更が必用となります。

削除や更新系のSQLの条件で、explainを先頭につければ実行計画をみて条件を検討できます。

delete from table where id < 100;
を
explain select * from table where id < 100;

検討してみてはいかがでしょうか。

Gooleアドセンス用336

Gooleアドセンス用336

-DataBase, MySQL

執筆者:

関連記事

sqlserver

SQLSERVER「Login failed for user ‘xxxxxxx’. 理由: 指定された名前に一致するログインが見つかりませんでした」

Windows認証でAdministratorを指定しているのにログインできない。 そんな場合は以下の手順でAdministratorユーザーを作成します。 INDEX1 起動時のパラメーターの変更2 …

sqlserver

SQL Server Management Studio の「上位200行の編集」を変更したい

恐らく私が十分に使用できていないだけで、機能はたくさんありそうなSQL Server Management Studio。最近新しいものに入れ替えたら(OS毎再インストールするハメになったのですが)、 …

sqlserver

SQLSERVER インストール後にDataをDドライブ(別ドライブへ)移動する方法

通常OSはCドライブ、データはDドライブなどの別ドライブを使用します。 SQLSERVERではデータの肥大化が起こりうる実データファイル(mdfやldf)をCドライブ以外に変更したい場面は多いはずです …

mysql

MySQLのバイナリログを見てスレーブ停止の原因を調査する

以前スレーブ側の更新がとまっていることに気付きました :x001: その時のトラブルシューティングの手順です。 INDEX1 1.スレーブ側で、SlaveS tatusを確認する2 2.スレーブ側のロ …

mysql

MySQLでフラグの列でSELECTする。(複合インデックスの使用条件)

status=’1’とかdelete=’0’といった条件はよく使用することでしょう。 そこでフラグにはどのデータ型がベストなのでしょうか。 INDEX1 …