MySQL テーブルのロック

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"のスレッドが多いということは、テーブルロックを長時間に渡って持ったままになる処理があるなどの問題があることが考えられる。

4. その他

テーブルアクセスを排他制御する以外にも、テーブルに対して大量のSQLステートメントを実行する場合はテーブルをロックした状態で行った方が高速に実行できるというメリットがある。これはキャッシュのフラッシュ処理がテーブルをUNLOCKするまで行われないため。テーブルの更新処理を大量に行う場合は、ロックを取ってから行うとよいかもしれない。ただし、当り前だがその間他のスレッドからはテーブルアクセスはできないのでどちらが良いかは状況しだい。

どの程度速くなるのか以下のスクリプトで試してみた。

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $UseLock = 0;
my $dsn = "DBI:mysql:test";

my $dbh = DBI->connect($dsn, "xxxx", "xxxx");
if ($UseLock) {
  $dbh->prepare("LOCK TABLES friends WRITE")->execute;
}
my $sth = $dbh->prepare('INSERT INTO friends (name, address, birthday)
     VALUES("tomita",  "Kamata xxxx", "2006-05-13 12:34:56")');
for (my $i = 0 ; $i < 10000 ; $i++) {
  $sth->execute;
}
if ($UseLock) {
  $dbh->prepare("UNLOCK TABLES")->execute;
}
$dbh->disconnect;

このスクリプトは10000件のレコードを挿入する。timeで時間を計ると経過時間(real)ロックを取った方がわずかではあるが速くなっている。(CPUはPentium(R) 4 3.00GHz)

ロックを取らない($UseLock = 0)場合
[tomita@tank tmp]$ time perl db.pl

real    0m1.525s
user    0m0.328s
sys     0m0.230s

ロックを取る($UseLock = 1)場合
[tomita@tank tmp]$ time perl db.pl

real    0m1.340s
user    0m0.307s
sys     0m0.231s