MySQL テーブルのロック
Rev.5を表示中。最新版はこちら。
1.概要
SELECTでレコードを取得してUPDATEするように複数のクエリを発行して、その間に他のスレッドにテーブルの更新を行なわせたくない場合は、テーブルをロックして排他制御を行なう。ロックにはREADロックとWRITEロックがある(表1)。
表1 ロックの種類
|
ロック種別 |
ロックを取ったスレッド |
その他のスレッド |
|---|---|---|
|
READ |
READのみ可能。 WRITEするとエラーになる。 |
READのみ可能。 WRITEするとブロックする。 |
|
WRITE |
READ/WRITE可能 |
READ/WRITE不可。 ブロックされる。 |
READロックだと、ロックを取得したスレッド自身もWRITE不可能になるので注意。
ロックを取得するには以下のようにする。
mysql> LOCK TABLES friends READ; READロックを取得 mysql> LOCK TABLES friends WRITE; WRITEロックを取得 mysql> LOCK TABLES table_a READ, table_b WRITE; 複数のテーブルを同時にロックテーブルのロックを解除する場合は、以下のようにする。
mysql> UNLOCK TABLES;
複数のテーブルをロックする場合にはLOCK TABLESにカンマで区切ってテーブルを並べる。LOCK TABLESを複数回呼び出して複数テーブルをロックすることはできない。(LOCK TABLESを実行するとすでに取っているロックは解除される)
これは、複数のテーブルをロックする場合は、ロックの取得順はMySQLが管理し、どのスレッドも同じ順序でロックを取得するため。このため、複数テーブルのロック取得時でもデッドロックは発生しないようになっている。
2.ロック取得時の挙動
実際にロックを取った場合の挙動を表2にまとめる。- 1ではスレッドA,B共friendsテーブルにアクセスできる。
- 2でスレッドAがテーブルをREADロックする。
- 3でスレッドBがレコードを取得しようとすると、friendsテーブルはREADロックなので正常に取得できる。
- 4でfrindsテーブルのレコードを更新しようとすると、ロックが取られているため、ブロックする。
- 5でスレッドAがテーブルのロックを解除すると、スレッドBでブロックしていたUPDATEが完了する。
表2 READロック時の挙動
|
No. |
スレッドA |
スレッドB |
|---|---|---|
|
1 |
mysql> select id,name from friends; +----+--------+ | id | name | +----+--------+ | 2 | tomita | +----+--------+ 1 row in set (0.00 sec) |
mysql> select id,name from friends; +----+--------+ | id | name | +----+--------+ | 2 | tomita | +----+--------+ 1 row in set (0.00 sec) |
|
2 |
mysql> LOCK TABLES friends READ; Query OK, 0 rows affected (0.00 sec) |
|
|
3 |
|
mysql> select id,name from friends; +----+--------+ | id | name | +----+--------+ | 2 | tomita | +----+--------+ 1 row in set (0.00 sec) |
|
4 |
|
mysql> UPDATE friends SET address="Yokohama" WHERE id=2; |
|
5 |
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) |
Query OK, 1 row affected (51.73 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
3.ロックに関する情報
3.1 統計情報
SHOW STATUSで表示される情報にTable_locks_immediateとTable_locks_waitedがある。
Table_locks_immediateはテーブルのロックを取ろうとしてスレッドがブロックすることなしにロックを取得できた回数を示す。通常のSELECTクエリでレコードを取得する場合なども内部でロックが取られているので、クエリ実行してブロックすることなしに即時に実行できればこのカウンタが増える。
Table_locks_waitedはテーブルのロックを取得しようとして、既にテーブルがロックされていたためスレッドがブロックした回数を示す。SELECT等のクエリを実行してテーブルのロックが取られていたため、待ち状態になった場合はこのカウンタが増える。このカウンタ値が大きいと言うことは、それだけテーブルロックの競合が発生しているということでパフォーマンス上望ましくない。
mysql> SHOW STATUS;
:
| Ssl_version | |
| Table_locks_immediate | 134 |
| Table_locks_waited | 2 |
| Threads_cached | 0 |
: