mysql

MySQLのロックは、大きく分けてグローバルロック、テーブルレベルロック、行ロックの 3 つに分類されます。

グローバルロック

グローバルロックはデータベースインスタンス全体に対するロックです。MySQLでは、Flush tables with read lockコマンドでグローバル読み取りロックを追加する方法を提供しています。このコマンドを使用できるのは、データベース全体を読み取り専用状態にしておく必要があるときで、その後、他のスレッドからの次のステートメントがブロックされます。グローバル・ロックの典型的な使用例は、ライブラリ全体の論理バックアップを作成することです。

なぜバックアップにグローバルロックを追加する必要があるのですか？

バックアップデータは、まだデータを変更することが許可されていると仮定すると、2つのテーブルの操作は、プラステーブルbマイナス、データbプラスの操作の変更でバックアップを完了していると仮定し、その後、bが削減されていない、この時点でバックアップ操作のa、bの2つのテーブルを完了している、バックアップの背面は、データエラーになります。公式の論理バックアップツールはmysqldumpですが、mysqldumpはパラメータ-シングル-トランザクションを使用すると、それはデータの一貫性のあるビューを取得することを保証するためにトランザクションを開始します。MVCC サポートのおかげで、このプロセス中にデータを正常に更新することができます。データベース全体を読み取り専用にしたい場合は、set global readonly=trueを使用してはいかがでしょうか。

• 1つには、システムによっては、ライブラリがプライマリかバックアップかの判断など、他のロジックにreadonly値が使用されることがあります。そのため、グローバル変数を変更することはより大きな影響を与えるので、使用することはお勧めしません。
• 次に、例外処理メカニズムに違いがあります。FTWRL コマンドの実行後にクライアントが例外によって切断された場合、MySQL は自動的にグローバルロックを解除し、ライブラリ全体が通常の更新状態に戻ります。ライブラリ全体を読み取り専用に設定した後、クライアントが異常切断された場合、データベースは読み取り専用のままとなり、同時にライブラリ全体が書き込み不可状態となり、リスクが高くなります。

テーブルレベルのロック

MySQL には 2 種類のテーブルレベルロックがあります。

1. テーブル・ロックの一種に、テーブル・ロック構文（ lock tables ...read/write）があります。 FTWRLと同様に、ロックはアンロックテーブルでアクティブに解除することも、クライアントが切断したときに自動的に解除することもできます。ロックテーブル構文は、他のスレッドへの読み取りと書き込みを制限するだけでなく、スレッド内で次に実行できることも制限することに注意してください。スレッド A が lock tables t1 read, t2 write; というステートメントを実行すると、t1 を書き込み、t2 を読み書きする他のスレッドはブロックされます。同時に、スレッド A がロック解除テーブルを実行すると、t1 の読み取り、t2 の読み取りと書き込みのみが可能になります。t1の書き込みも禁止されているので、当然他のテーブルにはアクセスできません。
2. ロックの1つのタイプはメタデータ・ロックです。MDLは明示的に使用する必要はなく、テーブルがアクセスされると自動的に追加されます。

テーブルtが小さなテーブルだとします。

注：ここでの実験環境は MySQL 5.6 です。

1. セッションAが最初に開始され、その時点でテーブルtにMDL読み取りロックが追加されます。
2. セッションBも同様にMDL読み取りロックが必要なので、正常に実行できます。
3. セッションAのMDL readロックはまだ解放されておらず、セッションCはMDL writeロックを必要とするので、Cはブロックされます。
4. セッション C 自身だけがブロックされても問題ありませんが、テーブル t に新しい MDL リードロックを適用する後続のリクエストもすべて、セッション C によってブロックされます。前述したように、テーブルへのすべての追加、削除、変更は、最初にMDL読み取りロックを適用する必要があり、その後、それらはすべてブロックされ、テーブルが完全に読めなくなり、書けなくなることを意味します。
5. テーブルに対するクエリが多く、クライアントがタイムアウト後に新しいセッションを開始して再リクエストするリトライメカニズムを持っている場合、ライブラリのスレッドはすぐにいっぱいになってしまいます。

小さなテーブルにフィールドを安全に追加するには？

MySQL の information_schema ライブラリの innodb_trx テーブルで現在実行中のトランザクションを調べることができます。DDL を変更したいテーブルで保留中のトランザクションが実行されている場合は、まず DDL を一時停止するか、保留中のトランザクションを kill してください。変更したいテーブルがホットスポット・テーブルで、データ量は多くありませんが、頻繁にリクエストがあり、フィールドを追加しなければならない場合、どうすればいいでしょうか？この時点では、新しいリクエストが来るので、killは機能しないかもしれません。理想的な仕組みは、alter table文で待ち時間を設定し、この指定された待ち時間内にMDL書き込みロックを取得できればベストですが、取得できなければ、後のビジネス文をブロックせず、あきらめましょう。 MariaDBはこの機能を提供するためにAliSQLと合併し、両方のオープンソースブランチがDDL NOWAIT/WAIT n構文をサポートするようになりました。この構文は

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

mysql

MySQL の行ロックは、各エンジンによってエンジンレベルで実装されます。例えば、MyISAM エンジンは行ロックをサポートしておらず、MyISAM が InnoDB に取って代わられた主な理由の 1 つは InnoDB が行ロックをサポートしていることです。

二段階ロックプロトコル

InnoDBのトランザクションでは、行ロックは必要な時に追加されますが、不要になった時にすぐに解放されるのではなく、トランザクションが終了するまで解放されません。これが2段階ロックプロトコルです。**トランザクション内で複数の行をロックする必要がある場合、ロックの競合が発生しやすく、同時実行性に影響を与える可能性の高いロックは、できるだけ後ろに置いてください。**これはどういう意味でしょうか？例えば、更新操作と新しいデータを追加する操作を行うトランザクションがあるとします。もし今、更新操作を実行し、挿入操作を実行すると、この時点では、トランザクションの最後にトランザクションの更新データから動作するように更新されているデータ上の別のトランザクションbを実施することができます。最初の挿入操作、および更新操作を実行する場合、トランザクションbは、トランザクションの操作の最後に操作の終了を待って更新データから開始する必要があります。挿入操作が最初に実行され、その後更新操作が実行された場合、トランザクションbは、トランザクションaがデータを更新してからトランザクションaの操作が終了するまで待機する必要があります。

デッドロックとデッドロック検知

トランザクションAはトランザクションBが行id=2のロックを解放するのを待っており、トランザクションBはトランザクションAが行id=1のロックを解放するのを待っています。 トランザクションAとBはお互いのリソースが解放されるのを待っており、デッドロック状態にあることを意味します。デッドロックが発生した場合、2つの戦略があります。

• 一つの方法はタイムアウトまで待機することです。このタイムアウトはパラメータ innodb_lock_wait_timeout で設定できます。
• もう1つの戦略は、デッドロックの検出を開始し、デッドロックが見つかると、デッドロックチェーンの1つのトランザクションを積極的にロールバックして、他のトランザクションが実行を継続できるようにすることです。パラメータ innodb_deadlock_detect を on に設定すると、このロジックが有効になります。

InnoDBでは、innodb_lock_wait_timeoutのデフォルト値は50秒です。これは、最初のストラテジーを使用した場合、デッドロックが発生すると、他のスレッドが実行を継続できるようになるまでに、最初にロックされたスレッドがタイムアウトするまで50秒かかることを意味します。オンラインサービスでは、この待ち時間はしばしば受け入れられません。この時間を1秒など非常に小さな値に設定して、デッドロックが発生したときに実に素早く元に戻せるようにすることはできませんが、デッドロックではなく単なるロック待ちの場合はどうでしょうか？通常、2番目の戦略はアクティブデッドロック検知を使うことで、innodb_deadlock_detectのデフォルト値はonです。 アクティブデッドロック検知は、デッドロックが発生した時に素早く検知して対処することができますが、余分な負担がかかります。新しいスレッドが入ってきてブロックされるたびに、そのスレッド自身の参加によってデッドロックが発生するかどうかを判断しなければなりません。同時に同じ行を更新するスレッドが1000個あると仮定すると、デッドロックの検出は100万個のオーダーになります。最終的にはデッドロックは発生しませんが、CPUリソースを大量に消費します。この問題を解決するには？デッドロックが発生しない業務が確保できるのであれば、デッドロック検出を一時的にOFFにするという愚策があります。 ミドルウェアがあれば、ミドルウェアに実装することも考えられます。