Mysqlの表級ロックを理解する--『mysqlロックを深く追究する』

一、定義

がロックするたびに1枚のテーブルのロックメカニズムがテーブルレベルロック(table-level)である.MySQLの各ストレージエンジンで最も粒度の大きいロックメカニズムです.

二、長所と短所

の利点

は論理が簡単で、オーバーヘッドが小さい.

ロックの取得と解放の速度が速い.

は、テーブルレベルのロックが一度にテーブル全体をロックするため、デッドロックの問題を回避することができます.

の欠点

は,ロック粒度が最も大きいため,ロックされたリソースを競合させる確率も最も高く,同時性が非常に低下する.

三、ストレージエンジンをサポートする

表レベルロックを使用しているのは、主にMyISAM、MEMORY、CSVなどの非トランザクションストレージエンジンです.

四、表レベルのロックタイプ
MySQLのテーブルレベルロックには、テーブル共有リードロック(Table Read Lock)とテーブル独占ライトロック(Table Write Lock)の2種類があります.
ロックモードの互換性:

MyISAMテーブルに対する読み取り操作は、他のユーザの同じテーブルに対する読み取り要求をブロックすることはないが、同じテーブルに対する書き込み操作をブロックする.

MyISAMテーブルに対する書き込み操作は、他のユーザの同じテーブルに対する読み取りと書き込み操作をブロックする.

MyISAMテーブルの読み取り操作と書き込み操作の間、および書き込み操作の間はシリアルである.1つのスレッドが1つのテーブルに対する書き込みロックを取得すると、ロックを持つスレッドだけがテーブルを更新できます.ロックが解除されるまで、他のスレッドの読み取り、書き込み操作が待機します.
五、時計のロック方法

クエリー文(select)を実行する前に、関連するすべてのテーブルに自動的にリードロック

が追加されます.

は、更新操作(update、delete、insertなど)を実行する前に、関連するテーブルに自動的に書き込みロックをかけます.このプロセスはユーザの介入を必要としないため,lock tableコマンドを直接MyISAMテーブルに明示的にロックする必要はない.

書き込みロックを表示:

//                ，                  。
//       、       ，        。
// test      ，       select * from test where id = 3;      ，  test   
LOCK TABLE test WRITE; 

読み込みロックの表示

// test      ，       select * from test where id = 3;    
//   UPDATE test set name='peter' WHERE id = 4;      ，  test   
LOCK table test READ;

リリースロックを表示:

UNLOCK TABLES;

同じSQLセッションで、テーブルのロックがすでに取得されている場合は、ロックされていないテーブルに対して何の操作もできません.そうしないと、エラーが発生します.

//   test 
LOCK table test WRITE;

//         
SELECT * from test where id = 4;

//           
SELECT * from bas_farm where id =1356

エラー:[Err]1100-Table'bas_farm' was not locked with LOCK TABLES.これは、MyISAMがsql文を一度に取得するために必要なすべてのロックを望んでいるためです.これもmyisamテーブルにデッドロックが発生しない理由です.
もちろん、心配する必要はありません.MyISAMエンジンのデフォルトの方法は、同じセッションのすべてのテーブルに鍵をかけます.自分で操作を表示するのは面倒ではありません.
六、表レベルのロック競合状況の表示
実行:show status like‘table%’;

mysql> show status like 'table%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Table_locks_immediate      | 20708     |
| Table_locks_waited         | 0         |
+----------------------------+-----------+

Table_locks_immediate:テーブルレベルのロックが発生した回数;Table_locks_waited:テーブルレベルのロック競合が発生して待機する回数;Table_locks_waitedステータス値が高い場合は、システム内の表レベルのロック競合が深刻であることを示し、なぜロックリソース競合が多いのかをさらに分析する必要があります.
七、表レベルロックの最適化
テーブル・レベルのロックを最適化する際の最大の問題は、同時性の向上です.
1.クエリ時間を短縮することでロック時間を短縮
ロック時間を短縮する全体的な原則は、Queryの実行時間をできるだけ短くすることです.

できるだけ大きい、複雑なQueryを減らして、複雑なQueryをいくつかの小さいQueryに分割して段階的に実行します;

できるだけ効率的なインデックスを確立し、データの検索をより迅速にする.

MyISAMストレージエンジンのテーブルに必要な情報だけを格納させ、フィールドタイプを制御する.

は、適切な機会を利用してMyISAMテーブルデータファイルを最適化する.

2.同時挿入の設定:concurrent_insert=2
MyISAMのテーブルロックは読み書きがブロックされているが,並列動作が可能である.MyISAMストレージエンジンには、Concurrent Insert(同時挿入)機能をオンにするかどうかを制御するパラメータオプションがあります.concurrent_Insert、値範囲は0,1,2です.

concurrent_insert=0で、同時挿入は許可されません.

concurrent_Insert=1で、MyISAMテーブルに空洞がない場合(すなわち、テーブルの真ん中に削除されていない行)、MyISAMは、あるスレッドがテーブルを読みながら、別のスレッドがテーブルの最後からレコードを挿入することを許可する.これはMySQLのデフォルト設定です.

concurrent_Insert=2は、MyISAMテーブルに空洞があるかどうかにかかわらず、テーブルの最後に記録を同時に挿入することを許可する.

concurrent_を設定することでInsert=2は、同時に定期的にシステムの空き時間帯にoptimize table tableName文を実行して空間の断片化を整理し、削除記録によって本当に解放されていない空間を回収し、同時性を向上させる.optimize参考:mysqlにおけるOPTIMIZE TABLEの役割と使用
3.読み書き優先度の合理的な設定
MyISAMストレージエンジンのデフォルトでは、書き込み優先度が読み取り優先度より大きい.書き込み要求後に到着しても、書き込みロックは読み取りロック要求の前に挿入されます.
しかし、文章のクリック数を修正する操作はそれほど重要ではありません.私たちはもっと速く読むことを望んでいます.このとき、私たちはこのようにすることができます.

UPDATE  LOW_PRIORITY  article SET click_num=134 WHERE id = 823

LOW_PRIORITYにより、updateオペレーションの最適化レベルがリードオペレーションよりも低いと判断され、リードオペレーションと上記の更新オペレーションが同時に発生した場合、リードオペレーションが優先的に実行される.
MySQLでは、スケジュールポリシーを変更できるいくつかの文調整子が用意されています.

LOW_PRIORITYキーワードは、DELETE、INSERT、LOAD DATA、REPLACE、UPDATEに適用されます.

HIGH_PRIORITYキーワードは、SELECT、INSERT文に適用されます.

delayed(遅延)キーワードは、INSERT、REPLACE文に適用されます.

すべてのLOWをサポートしたいならPRIORITYオプションの文はデフォルトで優先度が低い場合、low-priority-updatesオプションを使用してサーバを起動することができます.そしてinsert HIGH_を使うことでPRIORITY table.....個々のINSERT文を通常の書き込み優先度に上げます.
テキストリンクhttps://blog.csdn.net/zcl_love_wx/article/details/81977447