Linuxなどのメモ書き

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が完了する。
WRITEロックを取った場合は、3のレコードの取得もブロックするようになる。


表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      |
     :

3.2 スレッド情報

SHOW PROCESSLISTで表示するスレッド一覧で、Stateフィールドが"Locked"になっているものは、ロックの解放待ちでブロックしていることを示す。通常ロックの解放待ちの時間は短いはずなので、SHOW PROCESSLISTの結果で"Locked"のスレッドが多いということは、テーブルロックを長時間に渡って持ったままになる処理があるなどの問題があることが考えられる。


最終更新 2006/08/08 11:34:37 - kztomita
(2006/06/02 12:08:07 作成)


リンク

その他のWiki
Linuxメモ
Xnuメモ

会社
(有)ビットハイブ
受託開発やってます。

よくやる仕事

・Webシステム開発(LAMP環境)
・Linuxサーバー設定関連
サーバー移転作業代行

開発事例にデジタルカタログ/マンガビューワーを追加しました。

draggable.jsのスマホ対応版デモページを追加しました。説明はこちら

検索

Adsense
最近のコメント