Edited at

updateが行ロックするとは限らない(MySQL InnoDB)

More than 5 years have passed since last update.

MySQLで、スレッドが排他的にテーブルを操作する方法がねえかと考えた。

updateなりdeleteなりなら行ロックでも別にいいのだが、テーブルに対するinsertもロックする方法がわからん。

LOCK TABLES構文というのを見つけて「名前的にこれやろ」と思ったが全然違って、しかも運用中のサービスに安易にコードを付け加えテストもせずにリリースしてしまったおかげで、4人しか入れない部屋に10人ぐらい参加してゲームが始まらないという恐怖の状態に陥ってしまった。当たり前である。(LOCK TABLES は、現在のスレッドに対してベース テーブル (ビュー以外) をロックします

最初は、LOCK TABLESが「現在のスレッドに対しロック」して、「もしテーブルが1つでも他のスレッドによってロックされていたら、それは全てのロックを入手するまでブロック」するというのなら、必要なデータの読み書きとしては使わないロック専用テーブルを用意しといて、排他的読み書きが必要な処理の先頭で、ロック専用テーブルにLOCK TABLESやりゃいいんじゃねーのと思った。

でも友人に「LOCKってそういう風にはあんま使わないんじゃないですかね……」と言われたので、ちょっと考え直した。

その辺の相場がよくわからんが、そうだというならそうかもしれんので、他の方法を考えた。

次に考えたのは、MySQLによる行ロックをそのまま利用する方法。

エンジンがInnoDBだったので、行ロック(同一スレッドで更新したりの操作をしたレコードに対して、コミットするかスレッドが終わるまでは書き込みロックをする、という解釈でいいのかな)がかかり、排他的処理をする一番最初で、適当な「ロック専用レコード」をupdateしとけば、他のスレッドは待つだろうと考えた。


ロックのためのSQL

UPDATE config SET v = '1'

WHERE k = 'lock';

configは、「k」「v」という2つのフィールド(varchar(100))を持つだけのテーブル。

「k = 'lock', v = '1'」というレコードを用意しといて、これを適当に更新しとけばええやろ、と思っていた。

実験として、以下のPHPコードを実行してみた。


実験用

<?php

$db = new PDO('mysql:host=localhost;dbname=xxx', 'root', 'xxxx');
$db->query("SET NAMES utf8;");

$db->beginTransaction();

$sql = <<<EOM
UPDATE config SET v = '1'
WHERE k = 'lock';
EOM;

$sth = $db->prepare($sql);
$sth->execute();

// コミットする前に凍らせる
sleep(100);

$db->commit();
?>


この状態で、ブラウザからphpMyAdminを使って、


ロックのためのSQL

UPDATE config SET v = '1'

WHERE k = 'lock';

して、updateできなければよしと思っていた。

しかし、updateはできてしまった。

どうやら更新前と更新後で同じ結果になるupdateは、updateをおこなわない(行ロックもしない)らしい。

じゃあ


ロックのためのSQL2

UPDATE config SET v = (1+v)

WHERE k = 'lock';

とかやろうと思ったが、未来永劫運用していけば、いつかオーバーフローする運命なのだと思うと、涙が滂沱とあふれてきそうだったのでやめた。

なので、


ロックのためのSQL3

UPDATE config SET v=(!v)

WHERE k = 'lock';

としてやった。

これでうまいこと1と0を往復してくれるし、必ずロックがかかって、上記PHPコードでsleepしている間は、phpMyAdminで該当のレコードを更新してやろうとしても、待たされているのが確認できた。

なのでこれを運用中のサービスに埋め込み、排他的ロックをかけたい処理の最初に記述した。

これでうまくいくのかは、しばらく様子を見てみるつもり。

===追記===

うまくいってなかった。

なのでもうこうした。


ロックのためのSQL4

select * from config

WHERE k = 'lock';
for update

しばらく様子を見てみるつもり。

===さらに追記===

複数のスレッドが同時にトランザクションを張って、select等した時に、どういうデータを持ってくるのかという論点で、結果的に排他ロックできてなかったことが判明。

つまり、for updateなどでロックすると、確かにそのスレッドが排他的に読み書きできる状態になるのだが、他のスレッドが待った後にselect等をしても、トランザクションを「張った時点」のDBの状態(排他ロックしたスレッドのcommit等を反映していないデータ)を持ってくることがある。

それを避けるには、トランザクションを張る前に


sqlロックのためのSQL5

set session transaction isolation level READ COMMITTED


としてやりゃよい。

for updateと併せてPDOインスタンス生成直後にこれをやって、数週間様子を見たが、うまくいったように思う。