MySQL でクライアントが突然死したらロックが残ったままになった

MySQL Linux

珍しい現象に遭遇しました。冷静に考えると当たり前のことだし、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

※ これらのパラメータをこんなに短くして良いのかどうかは知りません