mysql insert into...select のロックの問題を理解する

mysql チュートリアルこのコラムでは、mysql insert into...select のロックの問題を紹介します。

引用:

最近、データベースのデッドロックの問題に遭遇しました。解決プロセスの記録をここに示します。

問題が発生します:

システム内の mysql にはいくつかのイベントがあり、統計データなどの関数のために数分ごとに実行され、このイベントはテーブルに追加されます。中のデータ。 一般的な内容: b から必須フィールドを選択してから a に置き換えます。 一般的な構造は次のとおりです。select from b で必要なフィールドはここでの略称です。実際には非常に複雑で、多くのテーブル結合操作が含まれます。データ量が多い場合、このイベントは 1 分ごとに実行されます。 1分では終わらないかもしれません。次に、テーブル b に対して他のさまざまな挿入および更新操作を実行します。この時点で、バックエンド ログにデッドロック エラーと待機ロック タイムアウト エラーが頻繁に記録されることがわかります。 最終テストでは、イベントをオフにすると問題が解消されることがわかり、問題はこのイベントにあることが基本的に確認されました。

問題分析:

実際、最も時間がかかるのは、それがイベントの問題であることを見つけることです。データをクエリして問題を解決するのにそれほど時間はかかりません。 。 1. まず、バックエンド ログのエラー情報に基づいて、デッドロックの原因となったテーブルと、ロック タイムアウトを待機したテーブルを特定します。次に、これらのテーブル名と出力された SQL に基づいて、デッドロックが発生した場所を見つけます。問題の原因はイベント
3 の SQL であることが大まかに確認されました。アイデアを再度検証してください。イベントをオフにした後、ログにロックの問題がないことがわかりました
4。確認してください。イベント内のステートメントを調べてみると、おそらく a from select requiredfields from b に置き換えられていることがわかります;

ここでの主な理由は、どのような状況で mysql がロックされるかが明確ではないことです。操作はテーブル b の挿入と更新などのために共有ロックのみを取得します。操作は排他ロックをロックすることです。 これら 2 つは互換性があり、一方は読み取り、もう一方は書き込みが可能であり、競合はありません。しかし、タイムアウト現象から判断すると、select from bで必要なフィールドもbテーブルをロックしているようです。 したがって、挿入と更新の両方がロックを待機しています。

最後に、Stack Overflow で興味深い情報とリンク アドレスを見つけました。 ここでは、読み取りコミットレベルに設定する必要があると言われています。次に、mysql 設定パラメータ innodb_locks_unsafe_for_binlog も導入されます。

そこで、この情報に従って公式 Web サイトで確認すると、次の段落を見つけました:

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.复制代码

つまり、INSERT INTO T SELECT ... FROM S WHERE ... この状況は最初に何よりも、T テーブルにどのレコード ロック (行レベルのロック) があるかがわかり、ギャップ ロックがありません。 テーブル S の場合、ロックが発生しない 2 つの状況があります:

1. トランザクション分離レベルが READ COMMITTED の場合
2. または、innodb_locks_unsafe_for_binlog が有効で、トランザクション分離レベルが SERIALIZABLE

ではない場合それ以外の場合、InnoDB は S の行に共有ネクストキーを設定します。 next-key についてよくわからない場合は、公式 Web サイトのこの紹介文とリンク アドレスを読んでください。

したがって、待機タイムアウトを解決する方法は比較的明確で、S テーブルがロックされないようにすることですが、ロックを回避するには、公式 Web サイトに記載されている 2 つの方法を使用できます。 。 これらはどちらも可能ですが、innodb_locks_unsafe_for_binlog パラメータの導入によると、方法 1 を使用し、トランザクション分離レベルを読み取りコミットに設定するのが最善です。

理由は次のとおりです:

1. パラメータ innodb_locks_unsafe_for_binlog は静的です。有効にするには、innodb_locks_unsafe_for_binlog = 1 行を my.cnf に追加し、データベースを再起動する必要があります。 mysql に次のコマンドを入力します:

show variables like "%innodb_locks_unsafe_for_binlog%"复制代码

ON であることが確認できれば、正常にオンになっています。

2. トランザクションの分離レベルは比較的きめ細かく、特定のセッションに対して設定できます。セッションごとに異なる分離レベルを使用でき、このパラメーターは動的であり、mysql コマンド ラインで直接変更できます。
3. mysql5.7 のパラメータの導入では、パラメータ innodb_locks_unsafe_for_binlog が後続の mysql バージョンで廃止されると述べています。これは本当で、mysql8.0 のパラメータの詳細説明を確認したところ、このパラメータは存在しないことがわかりました。

したがって、制御にはトランザクション分離レベルを使用することをお勧めします。

#その他の関連する無料学習の推奨事項:

mysql チュートリアル(ビデオ)

以上がmysql insert into...select のロックの問題を理解するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。