mysqlのmyisamエンジンロックの問題
8446 ワード
一、概説
他のデータベースに比べてmysqlのロックメカニズムは比較的簡単で、その最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることである.例えば、myisamとmemoryストレージエンジンは表レベルロックを採用し、bdbはページロックを採用しているが、表レベルロックもサポートされており、innodbストレージエンジンは行レベルロックも表レベルロックをサポートしているが、デフォルトでは行レベルロックである.
3つのロックの特性は、以下のようにまとめられています.
(1)表級ロック:オーバーヘッドが小さく、ロックが速い;デッドロックは発生しません.ロック粒度が大きく,衝突が発生する確率が高く,同時度が低い.
(2)行レベルロック:オーバーヘッドが大きく、ロックが遅い;ロック死が発生します.ロック粒度が最も小さく,ロック衝突が発生する確率が最も低く,同時度も高い.
(3)ページロック:オーバーヘッドとロック時間が表ロックと行ロックの間にある;デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
したがって、テーブル・レベル・ロックは、クエリーを主とし、インデックス条件でデータを更新するアプリケーションが少ない場合に適しています.また、レベル・ロックは、インデックス条件に従って少量の異なるデータを同時に更新し、同時に同時クエリーを実行するアプリケーションに適しています.
innodbロックに関する質問は、次のとおりです.http://blog.csdn.net/fuzhongyu2/article/details/52743680
二、myisamテーブルロック
1,ステータス変数を表示することでシステム上のテーブルロック競合を解析する
以上の2つのデータ、table_locks_waitedの値が高い場合は、深刻なテーブル・レベルのロック競合があることを示します.
2,テーブルレベルロックのロックモード
mysqlのテーブルレベルロックには、テーブル共有リードロック(table read lock)とテーブル独占ライトロック(table write lock)の2つのモードがあります.ロックモードの互換性は次のとおりです.
mysqlテーブルロック互換性
none
リードロック
書き込みロック
リードロック
はい
はい
いいえ
書き込みロック
はい
いいえ
いいえ
myisamテーブルの読み取り操作は、他のユーザの同じテーブルに対する読み取り操作をブロックすることはないが、同じテーブルに対する書き込み操作をブロックすることがわかる.
myisamテーブルの書き込み操作は、同じテーブルに対する他のユーザーの読み取りと書き込み操作をブロックします.
myisamテーブルの読み取り操作と書き込み操作の間、および書き込み操作の間はシリアルです.
次に例を示します.
まず、テーブルを作成し、いくつかのデータを挿入し、テーブルの構造を見てみましょう.
書き込みブロックの読み取り例
session_1
session_2
表t 18のwriteロック
現在のセッションは、ロックされたテーブルのクエリー、更新、挿入操作に対して
ロックされた他のセッションのテーブル操作がブロックされている
セッション2はロックを取得し、クエリー結果
リードブロック書込み例
session_1
session_2
t 18のreadロック
現在のセッションでは、テーブルレコード
session_2このテーブルレコード
現在のセッションでは、ロックされていないテーブルはクエリーできませんが、ロックされたテーブル(このセッションでロックされているテーブル)
他のセッションでは、ロックされていないテーブル
現在のセッションでデータを挿入または更新すると、
他のセッションがロックを更新するテーブルは、ロック
リリースロック
取得ロック実行
例:
テーブル自体の接続を行い、クエリーが間違っています.正しい方法は次のとおりです.
3、時計のロック方法
myisamはクエリー文(select)を実行する前に、関連するすべてのテーブルに自動的にリードロックを追加し、更新操作(update,delete,insert)を実行する前に、関連するテーブルに自動的にライトロックを追加します.このプロセスにはユーザーの介入は必要ありません.そのため、ユーザーは通常、lock tableコマンドのmyisamテーブルを直接ロックする必要はありません.ここでロックをかけるのは、トランザクション操作をシミュレートし、ある時点で複数のテーブルの一貫性読み取りを実現するためです.
2つのテーブルを同時にロックする必要がある場合:
(1)上記の例lock tablesにlocalを加えた場合,myisamテーブルの同時挿入条件を満たす場合に,他のユーザがテーブルの最後にレコードを同時挿入できるようにする役割を果たす.
(2)lock tablesでテーブルに明示的にロックをかける場合は、テーブルに関連するすべてのロックを同時に取得する必要があり、mysqlはロックアップグレードをサポートしていない.すなわち、lock tablesを実行した後に明示的にロックをかけたテーブルにのみアクセスでき、ロックをかけていないテーブルにアクセスできない.自動ロックの場合、myisamは常にsql文を一度に読み込むために必要なすべてのロックであり、これもmyisamがデッドロックしない理由です.
4、同時挿入
myisamテーブルの読み取りと書き込みはシリアルですが、これは全体的に、myisamテーブルもクエリと挿入操作の同時実行をサポートします.
myisamストレージエンジンにはシステム変数concurrent_があります.Insertは、同時挿入の動作を制御するために使用され、その値はそれぞれ0、1、2であることができます.
値が0の場合、同時挿入は許可されません.
値が1の場合、myisamテーブルに空洞がない(すなわち、テーブルの真ん中に削除されたローがない)場合、myisamはあるプロセスでテーブルを読むと同時に、別のプロセスがテーブルの最後からレコードを挿入することを許可します.これもmysqlのデフォルト設定です.
値が2の場合、myisamテーブルに空洞があるかどうかにかかわらず、テーブルの最後にレコードを同時に挿入できます.
5,myisamのロックのスケジューリング
myisamストレージエンジンのリード・ロックとライト・ロックは反発し、リード・ライト・オペレーションはシリアルです.では、あるプロセスはmyisamテーブルのリードロックを要求し、別のプロセスも同じテーブルのライトロックを要求します.mysqlはどのように処理しますか.
答えは、書き込みプロセスがロックを取得することです.それだけでなく、読み取り要求がロック待ちキューに先に到着しても、書き込み要求が到着すると、書き込みロックは読み取りロックの前に挿入されます.これはmysqlが,書き込み要求が読み取り要求よりも一般的に重要であると考えているからである.しかし、myisamのスケジューリング動作をいくつかの設定で調整することができます.
(1)起動パラメータlow-priority-updatesを指定することによりmyisamエンジンにデフォルトでリードリクエストを優先させる権利
(2)コマンドset low_を実行するpriority_updates=1で、接続からの更新要求の優先度を低下させる
(3)insert,update,delete文を指定するlow_priorityプロパティを使用して、文の優先度を下げます.
三、まとめ
では、ロックの問題をまとめます.主にmysqlのmyisamテーブルレベルのロックの実現特徴を紹介しました.
myisamのテーブルロックには、主にいくつかの点があります.
(1)共有ロック(s)間は互換性があるが、共有ロック(s)と排他ロック(x)間、および排他ロック書き込みロック(x)間は反発的であり、すなわち読み書きはシリアルである.
(2)一定の条件下でmyisqmはクエリーと挿入を同時に実行することを可能にし,これを利用してアプリケーションにおける同じテーブルに対するクエリーと挿入のロック競合問題を解決することができる.
(3)myisamのデフォルトのロックスケジューリングメカニズムは書き込み優先であり、これは必ずしもすべてのアプリケーションに適しているとは限らず、ユーザーはlow_を設定することができる.priority_updatesパラメータ、またはinsert、update、delete文でlow_を指定します.prorityオプションは、読み書きロックの競合を調整します.
(4)テーブルロックのロック粒度がヒットし,読み書き間がシリアルであるため,更新操作が多い場合myisqmテーブルに深刻なロック待ちが生じる可能性があり,innodbテーブルを用いて競合を低減することが考えられる.
他のデータベースに比べてmysqlのロックメカニズムは比較的簡単で、その最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることである.例えば、myisamとmemoryストレージエンジンは表レベルロックを採用し、bdbはページロックを採用しているが、表レベルロックもサポートされており、innodbストレージエンジンは行レベルロックも表レベルロックをサポートしているが、デフォルトでは行レベルロックである.
3つのロックの特性は、以下のようにまとめられています.
(1)表級ロック:オーバーヘッドが小さく、ロックが速い;デッドロックは発生しません.ロック粒度が大きく,衝突が発生する確率が高く,同時度が低い.
(2)行レベルロック:オーバーヘッドが大きく、ロックが遅い;ロック死が発生します.ロック粒度が最も小さく,ロック衝突が発生する確率が最も低く,同時度も高い.
(3)ページロック:オーバーヘッドとロック時間が表ロックと行ロックの間にある;デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
したがって、テーブル・レベル・ロックは、クエリーを主とし、インデックス条件でデータを更新するアプリケーションが少ない場合に適しています.また、レベル・ロックは、インデックス条件に従って少量の異なるデータを同時に更新し、同時に同時クエリーを実行するアプリケーションに適しています.
innodbロックに関する質問は、次のとおりです.http://blog.csdn.net/fuzhongyu2/article/details/52743680
二、myisamテーブルロック
1,ステータス変数を表示することでシステム上のテーブルロック競合を解析する
mysql> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 101 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)以上の2つのデータ、table_locks_waitedの値が高い場合は、深刻なテーブル・レベルのロック競合があることを示します.
2,テーブルレベルロックのロックモード
mysqlのテーブルレベルロックには、テーブル共有リードロック(table read lock)とテーブル独占ライトロック(table write lock)の2つのモードがあります.ロックモードの互換性は次のとおりです.
mysqlテーブルロック互換性
none
リードロック
書き込みロック
リードロック
はい
はい
いいえ
書き込みロック
はい
いいえ
いいえ
myisamテーブルの読み取り操作は、他のユーザの同じテーブルに対する読み取り操作をブロックすることはないが、同じテーブルに対する書き込み操作をブロックすることがわかる.
myisamテーブルの書き込み操作は、同じテーブルに対する他のユーザーの読み取りと書き込み操作をブロックします.
myisamテーブルの読み取り操作と書き込み操作の間、および書き込み操作の間はシリアルです.
次に例を示します.
まず、テーブルを作成し、いくつかのデータを挿入し、テーブルの構造を見てみましょう.
mysql> create table t18(id int auto_increment primary key,name varchar(63),age int(3));
Query OK, 0 rows affected (0.33 sec)
mysql> insert into t18(name,age)values('fzy1',1),('fzy2',2),('fzy3',3);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 2 |
| 3 | fzy3 | 3 |
+----+------+------+
3 rows in set (0.00 sec)
書き込みブロックの読み取り例
session_1
session_2
表t 18のwriteロック
mysql> lock table t18 write;
Query OK, 0 rows affected (0.00 sec)
現在のセッションは、ロックされたテーブルのクエリー、更新、挿入操作に対して
mysql> insert into t18(name,age)values('fzy4',4);
Query OK, 1 row affected (0.04 sec)
mysql> update t18 set age=5 where id=2;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
ロックされた他のセッションのテーブル操作がブロックされている
mysql> select * from t18;
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
セッション2はロックを取得し、クエリー結果
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (1 min 21.56 sec)
リードブロック書込み例
session_1
session_2
t 18のreadロック
mysql> lock table t18 read;
Query OK, 0 rows affected (0.00 sec)
現在のセッションでは、テーブルレコード
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
session_2このテーブルレコード
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
現在のセッションでは、ロックされていないテーブルはクエリーできませんが、ロックされたテーブル(このセッションでロックされているテーブル)
mysql> select * from t17;
ERROR 1100 (HY000): Table 't17' was not locked with LOCK TABLES
他のセッションでは、ロックされていないテーブル
mysql> select * from t17;
+----+------+------+--------+
| id | name | age | intro |
+----+------+------+--------+
| 1 | fzy1 | 1 | intro1 |
| 2 | fzy2 | 2 | intro2 |
| 3 | fzy3 | 3 | intro3 |
| 4 | hsq | 4 | intro4 |
+----+------+------+--------+
4 rows in set (0.00 sec)
現在のセッションでデータを挿入または更新すると、
mysql> insert into t18(name,age)values('fzy5',5);
ERROR 1100 (HY000): Table 't18' was not locked with LOCK TABLES
他のセッションがロックを更新するテーブルは、ロック
mysql> insert into t18(name,age)values('fzy6',6);
リリースロック
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
取得ロック実行
Query OK, 1 row affected (2 min 33.25 sec)
例:
mysql> lock table t18 read;
Query OK, 0 rows affected (0.00 sec)
mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES
テーブル自体の接続を行い、クエリーが間違っています.正しい方法は次のとおりです.
##
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
##
mysql> lock table t18 a read,t18 b read;
Query OK, 0 rows affected (0.00 sec)
mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
+------+------+
| name | name |
+------+------+
| fzy1 | fzy1 |
| fzy2 | fzy2 |
| fzy3 | fzy3 |
| fzy4 | fzy4 |
| fzy6 | fzy6 |
| fzy6 | fzy6 |
+------+------+
6 rows in set (0.01 sec)
3、時計のロック方法
myisamはクエリー文(select)を実行する前に、関連するすべてのテーブルに自動的にリードロックを追加し、更新操作(update,delete,insert)を実行する前に、関連するテーブルに自動的にライトロックを追加します.このプロセスにはユーザーの介入は必要ありません.そのため、ユーザーは通常、lock tableコマンドのmyisamテーブルを直接ロックする必要はありません.ここでロックをかけるのは、トランザクション操作をシミュレートし、ある時点で複数のテーブルの一貫性読み取りを実現するためです.
2つのテーブルを同時にロックする必要がある場合:
mysql> lock tables t17 read local,t18 read local;
Query OK, 0 rows affected (0.00 sec)
#
...
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(1)上記の例lock tablesにlocalを加えた場合,myisamテーブルの同時挿入条件を満たす場合に,他のユーザがテーブルの最後にレコードを同時挿入できるようにする役割を果たす.
(2)lock tablesでテーブルに明示的にロックをかける場合は、テーブルに関連するすべてのロックを同時に取得する必要があり、mysqlはロックアップグレードをサポートしていない.すなわち、lock tablesを実行した後に明示的にロックをかけたテーブルにのみアクセスでき、ロックをかけていないテーブルにアクセスできない.自動ロックの場合、myisamは常にsql文を一度に読み込むために必要なすべてのロックであり、これもmyisamがデッドロックしない理由です.
4、同時挿入
myisamテーブルの読み取りと書き込みはシリアルですが、これは全体的に、myisamテーブルもクエリと挿入操作の同時実行をサポートします.
myisamストレージエンジンにはシステム変数concurrent_があります.Insertは、同時挿入の動作を制御するために使用され、その値はそれぞれ0、1、2であることができます.
値が0の場合、同時挿入は許可されません.
値が1の場合、myisamテーブルに空洞がない(すなわち、テーブルの真ん中に削除されたローがない)場合、myisamはあるプロセスでテーブルを読むと同時に、別のプロセスがテーブルの最後からレコードを挿入することを許可します.これもmysqlのデフォルト設定です.
値が2の場合、myisamテーブルに空洞があるかどうかにかかわらず、テーブルの最後にレコードを同時に挿入できます.
5,myisamのロックのスケジューリング
myisamストレージエンジンのリード・ロックとライト・ロックは反発し、リード・ライト・オペレーションはシリアルです.では、あるプロセスはmyisamテーブルのリードロックを要求し、別のプロセスも同じテーブルのライトロックを要求します.mysqlはどのように処理しますか.
答えは、書き込みプロセスがロックを取得することです.それだけでなく、読み取り要求がロック待ちキューに先に到着しても、書き込み要求が到着すると、書き込みロックは読み取りロックの前に挿入されます.これはmysqlが,書き込み要求が読み取り要求よりも一般的に重要であると考えているからである.しかし、myisamのスケジューリング動作をいくつかの設定で調整することができます.
(1)起動パラメータlow-priority-updatesを指定することによりmyisamエンジンにデフォルトでリードリクエストを優先させる権利
(2)コマンドset low_を実行するpriority_updates=1で、接続からの更新要求の優先度を低下させる
(3)insert,update,delete文を指定するlow_priorityプロパティを使用して、文の優先度を下げます.
三、まとめ
では、ロックの問題をまとめます.主にmysqlのmyisamテーブルレベルのロックの実現特徴を紹介しました.
myisamのテーブルロックには、主にいくつかの点があります.
(1)共有ロック(s)間は互換性があるが、共有ロック(s)と排他ロック(x)間、および排他ロック書き込みロック(x)間は反発的であり、すなわち読み書きはシリアルである.
(2)一定の条件下でmyisqmはクエリーと挿入を同時に実行することを可能にし,これを利用してアプリケーションにおける同じテーブルに対するクエリーと挿入のロック競合問題を解決することができる.
(3)myisamのデフォルトのロックスケジューリングメカニズムは書き込み優先であり、これは必ずしもすべてのアプリケーションに適しているとは限らず、ユーザーはlow_を設定することができる.priority_updatesパラメータ、またはinsert、update、delete文でlow_を指定します.prorityオプションは、読み書きロックの競合を調整します.
(4)テーブルロックのロック粒度がヒットし,読み書き間がシリアルであるため,更新操作が多い場合myisqmテーブルに深刻なロック待ちが生じる可能性があり,innodbテーブルを用いて競合を低減することが考えられる.