珍しい現象に遭遇しました。冷静に考えると当たり前のことだし、MySQL に限ったことでは無いと思いますが。
MySQL のサーバとクライアントが別のホストになっているものとします(仮に DB サーバ と AP サーバ)。
- 192.0.2.1 => DB サーバ
- 192.0.2.2 => AP サーバ
まず AP サーバから DB サーバに接続します。
$ mysql -h 192.0.2.1 test
適当にテーブルを作ります。
mysql> create table t ( id int not null primary key ); Query OK, 0 rows affected (0.05 sec) mysql> insert into t values (1); Query OK, 1 row affected (0.00 sec)
トランザクションを開始して行ロックします。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1 for update; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
おもむろに AP サーバを突然死させます、正常な shutdown ではなく突然死です。
方法は幾つか考えられますが・・・
- 仮想環境なら destroy とか poweroff のような方法で強制終了させる
- 実環境なら LAN ケーブルを引っこ抜く
- 死んではいないけど DB サーバから見たら死んでいるようなもの
DB サーバで processlist すると死んだはずの AP サーバの接続が残っています。
$ mysqladmin processlist +----+------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 3 | root | 192.0.2.2:50730 | test | Sleep | 7 | | | | 4 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------------+------+---------+------+-------+------------------+
この状態で同じ行をロックすると、競合してタイムアウトになります。
$ mysql test ... mysql> select * from t where id = 1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
その行をロックしている AP サーバは既に死んでいますが、DB サーバはそのことを知らないのでロックが残ったままになっています。
こうなると kill で供養するしかなさそうです。
mysql> kill 3; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1 for update; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
AP サーバの最後のクエリから wait_timeout
秒経過すれば接続が切れてロックも解放されます。
が、wait_timeout
はデフォルトだと 8 時間とかだったと思うので、AP サーバで接続時に wait_timeout
を短めに設定してみます。
$ mysql -h 192.0.2.1 test ... mysql> set @@session.wait_timeout = 60; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1 for update; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
これなら AP サーバが突然死しても 60 秒アイドルが続けば DB サーバから接続が消えるのでロックも解放されます。
ただしクエリとクエリの間で 60 秒かかると接続が切れてしまうので諸刃の剣です。
MySQL は TCP KeepAlive が有効らしいです。CentOS6 のデフォルトだと 2 時間だったので・・・
# sysctl -a | grep keep net.ipv4.tcp_keepalive_time = 7200 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_intvl = 75
適当に短い値を設定すれば TCP KeepAlive で対向ホストの死亡を検出して接続も解放されます。
# sysctl -w net.ipv4.tcp_keepalive_time=60 # sysctl -w net.ipv4.tcp_keepalive_probes=3 # sysctl -w net.ipv4.tcp_keepalive_intvl=15
※ これらのパラメータをこんなに短くして良いのかどうかは知りません