BizStationブログ

ビズステーション株式会社の公式ブログです。

Transactd Plugin PHP Ruby C# C++のチュートリアル

MySQL用NoSQLプラグイン Transactdのチュートリアルで使用するプログラミング言語の種類を拡充しました。
この中に慣れた言語がありましたら、是非試してみてください。SQLに比べてとても高速に処理ができるようになります。

Transactd チュートリアル


今まではJScriptしかありませんでしたが、ようやくPHPRubyC#C++の4つの言語を追加することができました。
また、内容も細かく見直しています。

内容は、このような構成です。

  • プラグインとクライアントの準備
  • プログラム言語ごとの準備
  • データベースとテーブルの操作
  • SQLライクな読取と結果セットの操作
  • データベースとテーブルの作成・削除
  • サンプルコードダウンロード

簡単なハウツー本のような内容になっています。
ダウンロード用のサンプルコードは、エラー処理も含め実用に耐えるコードにしています。

できれば、このチュートリアルを使って、ハンズオンセミナーなどもやりたいなと考えています。関連するセミナーなどで「うちでやって」とか「この時間空いてるよ」などご意見ありましたら是非お寄せください。

MySQL 5.7 + Transactd スループット117万QPSを記録


ようやく24コアのマシンでTransactdのベンチマークを取ることができました。
Xeon E5-2697 V2 2.7GHz 24コア48スレッドの物理マシンです。32または36コアでできれば良かったのですが、お借りできたこのマシン*1ベンチマークを行いました。

結果はタイトルの通りで、パフォーマンスの改善されたMySQL 5.7.7にて驚きの117万QPSを記録しました。
以下はその結果グラフです。横軸がクライアント数、縦軸が1秒間に処理したクエリー(読み取りレコード)数です。

f:id:bizstation:20150430111654p:plain
memcached-pluginなどと基本的な考え方は同じですので、100万QPSを出すには48コア位は必要かと思っていましたが、24コアでこの値には少し驚いています。

MySQL 5.6.20でも87万QPSを出しました。5.7の結果が良いので5.6が少なく見えますが、24コアで87万QPSは好結果です。
48コアマシンだったら、MySQL 5.7とTransactd 2.4で200万QPSも可能ではないかと思います。

このテストのポイントについていくつか説明しておきます。

テスト環境

MachineFujitsu RX300 S8
CPUXeon E5-2697 V2 2.7GHz 24Core
Hyper-ThreadingON
memory24GB
OSWindows Server 2012 R2
Transactd Version2.4(Not yet release)
NetworkLocal pipe (shared memory)

テスト方法

ベンチマークプログラム

ベンチマークプログラムは、シングルプロセスで2~48個のスレッドにてランダムなidのプライマリーキー値でサーバーにアクセスします。それぞれのスレッドは無限ループでアクセスを開始し、5秒間の計測シグナルがONの間にアクセスできた数を返すようになっています。
OSに制御を返す時間がほとんどない連続アクセスのため、論理コア数以下でスループットの劣化が始まります。
(ときどき、数百クライアントまでスケールする結果があったりしますが、それはサーバーの処理スレッドがOSに制御を返す空き時間が多い処理(通信)方法によるもので、空き時間がなければ論理コア以上にスケールすることはありません。)

ネットワーク

他のMySQL用のNoSQLプラグインでもベンチマークはローカルマシン内で行っています。これはネットワークレイテンシと負荷を軽減するためです。Transactdのパケットは数十バイト程度の小さなものが多く、1Gbpsのネットワークでも1Gbpsのスループットを出すことはできません。ネットワークのレイテンシによって、10~30万パケット程度で頭打ち*2になってしまいます。また、ネットワーク処理の負荷も問題になるため、今回のテストも他と同様にサーバーとクライアントは同一マシンで行っています。*3

OSはWindowsで行いました。TransactdのWindows版は共有メモリとイベント送受信を使ったプロセス間通信を行うことができます。これで通信のレイテンシと負荷を最小限にすることができます。

f:id:bizstation:20150430110038p:plain

この方法はTCPのレイヤーを一切介さず、非常に高速に通信します。

TCPでの接続においても、ネットワーク処理を除いたデータアクセスに24コアが割り当てできれば、Linux/Windowsともにほぼ同様な結果がでると思います。それには、低負荷で高速なネットワークカードが必要です。逆に言えば今回の結果はそれを示していると言えます。

Transactdの高速性を真に発揮するには高速なネットワークカードも併せて利用していただけたらと思います。

MySQL 5.7

MySQL 5.7はスループットの改善が顕著でした。Transactdのコードは5.6と5.7のインタ-ーフェースの差異を吸収するだけの違いしかなく、基本的に同じものです。それにもかかわらず大きな性能差が出ています。特に、クライアント数が物理コア数を超えたあたりからの差が大きく、Hyper -Threadingによる論理コアをうまく使えているようです。InnoDBの改善が進んでいるのがわかります。
これだけの差があると、スループットをすぐにでも上げたい場合5.7はお勧めです。

5.7は既にRC版で、間もなくGA版になるかと思います。早期の導入には不安もありますが、TransactdはMySQLのhandlerインターフェース以下の層しか使わないので、潜在的なバグもSQLでのアクセスより少なくなります。また、TransactdのAPIは十分にテストされますのでチェックも2重に働いています。

まとめ

Transactd APIにて、性能劣化を起こすことなくInnoDBのパフォーマンスを引き出せていることが数字で確認できました。

今回のテストはidによる読み取りなのでkey-valueアクセスです。このようなkey-valueアクセス用途はもちろんですが、Transactdはフル機能のAPIを備えています。複雑な集計やトランザクションもすべて高速に行えます。
みなさんも、Transactdで最高パフォーマンスのWebアプリケーションを作ってみてください。今回のテストは開発中のVersion2.4で行いましたが、パフォーマンスに関しては2.3も同様です。

共有メモリを使用したWindowsローカルでの処理は非常に高速です。BizStationでは、大きなデータで時間のかかるデータのマイグレーションなどの際に、RAMディスクと併用して極端に短時間で処理するなどに利用しています。

また、MySQL 5.7の読み取りスループットの改善は本当に大きく、リリースされたらBizStationでも積極的に使っていこうと思います。
MySQL 5.7 + Transactdのこのスピードはちょっとわくわくします。

*1:ダイワボウ様、富士通様ありがとうございます。

*2:100バイト ☓ 30万パケット ≒ 240Mbps

*3:最近の10Gbpsネットワークカードのテストなどを見ると小さなパケットでも100万PPSを超えるようです。これであれば、ネットワークがボトルネックになることを回避できるかと思います。

TransactdでInnoDBロックを自在に操る

前回の MySQL/MariaDBとTransactdのInnoDBロック制御詳細 その1 - BizStationブログ では、InnoDBのロックの詳細について説明しました。
今回は、TransactdのトランザクションにおいてInnoDBのロックをどう扱うかを説明します。Transactdは、InnoDBロックを自在に操って同時実行性を高めることができます。

ロック制御は、マルチスレッドプログラミングとmutex lockなどの制御とよく似ています。これらを扱ったことがあるようでしたら、Transactdのロック制御はとてもやり易く感じると思います。

書き込みにおけるパフォーマンスの確保には、ロックを最小限にし同時実行性を高めることが不可欠です。ミッションクリティカルなアプリケーションもパフォーマンスの良いものにしましょう。

Transactdのロック制御

TransactdのAPIは行単位のナビゲーションアクセスが基本です。そのため、InnoDBの行ロックの制御とはとても相性が良く、行単位でロックを解放したり、種類を変えたりといったことができます。ミッションクリティカルなアプリケーションも最小限のロックで安全に処理することが可能です。

トランザクションの種類

Transactdのトランザクションには以下の3つの種類があります。

それぞれ順に説明します。

自動トランザクション

自動トランザクションとは、Transactd内部で自動的に開始されるトランザクションです。その他の2つが明示的に開始を指定するのに対して、自動トランザクションは暗黙のうちに開始されます。
自動トランザクションは、ユーザートランザクションまたはスナップショットが開始されていないときにテーブルに対するアクセスを行うと自動で開始され、1つのオペレーションが終わると終了します。すなわち、テーブルアクセスの通信1回ごとに開始され終了します。

読み取りオペレーション

自動トランザクションの読み取りオペレーションは、常に nonlocking readsでありロックを取得しません。オペレーションごとにトランザクションが開始されるため、その都度最新のスナップショットが使用されます。

更新と行削除

更新nstable::update()と行削除nstable::del()は、直前のオペレーションで読み取られたカレントレコードをサーバー側で再度読み直し、排他ロック(X)を取得してから、更新または削除を行います。
直前のオペレーションで読み取られた値と最新の値が異なる場合は、それを検出してnstable::stat()にSTATUS_CHANGE_CONFLICTエラーが返ります。そのため、このシナリオではロストアップデートは起こりません。また、事前にロックを取得してから更新する方法もあります。それらについては、以降で詳しく説明します。

ユーザートランザクション

ユーザートランザクションは読み書き可能なトランザクションです。 nsdatabase::beginTrn(bias=SINGLELOCK_READ_COMMITED+NOWAIT_WRITE)メソッドで開始し、 nsdatabase::endTrn() または nsdatabase::abortTrn()で終了します。
ユーザートランザクションは開始メソッドのbias値によって複数のロックタイプとロック粒度を選択できます。ロック粒度には、シングルレコードロックとマルチレコードロックの2種類があります。

  • シングルレコードロックは、テーブルごとに最後にアクセスしたレコードのみロックを保持します。
  • マルチレコードロックは、トランザクション中にアクセスしたすべてのレコードのロックを保持します。

以下はbias値に対するロック粒度とロックタイプの一覧表です。

beginTranのbias値 ロック粒度 InnoDB分離レベル 行ロックタイプ
SINGLELOCK_NOGAP
(デフォルト)
シングルレコードロック READ_COMMITED row lock(X)
MULTILOCK_NOGAP マルチレコードロック READ_COMMITED row lock(X)
MULTILOCK_GAP マルチレコードロック REPEATABLE_READ next key lock(X)

SINGLELOCK_NOGAPはbiasのデフォルト値です。SINGLELOCK_NOGAPは、最後に読み取ったレコードの排他ロック(X)のみ保持します。更新や削除を行う際にはその行を確定するために読み取りを必要とします。読み取った値に基づいて更新を行うことでロストアップデートのない処理を行うことができます。また、ロックの範囲が非常に狭いため最も同時実行性の高い処理が行えます
MULTILOCK_NOGAPは、アクセスしたすべてのレコードを排他ロック(X)を取得します。ただし、row lockなので読み取り範囲内への挿入はブロックできません。ロックの範囲は広くなりますので、同時実行性は悪くなります
MULTILOCK_GAPは、アクセスしたすべてのレコードを排他ロック(X)+GAPロックします。これによりアクセスした範囲への挿入もブロックします。InnoDB分離レベルは、REPEATABLE_READ を使用していますが、機能的な分離レベルはSERIALIZABLEになります。同時実行性は最も悪くなりますが 完全な読み取り一貫性を確保した処理が行えます。(以降に説明するunlock()を使用しなかった場合です。)

SINGLELOCK_NOGAPとMULTILOCK_NOGAPの場合は、nstable::unlock() で直前の読み取り行のロックを解放することができます。必要な行のみロックを得たい場合に細かな制御を可能にします。なお、 nstable::find()など1回のオペレーションで複数の行を取得する場合は、ロックの解放はできません。細かくロック・アンロックの制御を行いたい場合は、seek系オペレーションを使用してください。

// ユーザートランザクション unlock()の例
db->beginTrn(MULTILOCK_NOGAP);
tb->setFVN("id", 1);
tb->seek();
if (tb->stat() == 0)
   tb->unlock();
...
db->endTrn();

マルチレコードロックの場合は、読み取りオペレーションの lockBias値にROW_LOCK_Sを指定することで、排他ロック(X)に替えて共有ロック(S)を使用することができます。更新を行わない行の読み取りにこれを使うことで、不要な排他ロック(X)を防止し、必要な行のみ排他ロック(X)にすることができます。lockBias値が指定できる読み取りオペレーションは、nastable::seek系とnastable::step系のオペレーションです。

// ユーザートランザクション 共有ロック(S)を指定する例
db->beginTrn(MULTILOCK_GAP);
tb->setFVN("id", 1);
tb->seek(ROW_LOCK_S); //<-- 共有ロック(S)にする
if (tb->stat() == 0)
...
db->endTrn();

スナップショット

スナップショットは読み取り専用トランザクションです。nsdatabase::beginSnapshot(bias=CONSISTENT_READ)メソッドで開始し、nsdatabase::endSnapshot()で終了します。 スナップショットは開始メソッドのbias値によって複数のロックタイプを選択できます。
以下はbias値に対するロック粒度とロックタイプの一覧表です。

beginSnapshotのbias値 ロック粒度 InnoDB分離レベル 行ロックタイプ
CONSISTENT_READ
(デフォルト)
対象外 REPEATABLE_READ ロックなし (consistent nonlocking reads)
MULTILOCK_NOGAP_SHARE マルチレコードロック READ_COMMITED row lock(S)
MULTILOCK_GAP_SHARE マルチレコードロック REPEATABLE_READ next key lock(S)

CONSISTENT_READはbiasのデフォルト値です。CONSISTENT_READは、REPEATABLE_READによる一貫性のある読み取りが行えます。集計処理などはもちろん、複数の読み取りオペレーションを実行する際にこれを使うと、自動トランザクションに比べて内部処理のオーバーヘッドが減ってより高速に処理できます。
MULTILOCK_NOGAP_SHAREの場合は、nstable::unlock() にて直前の読み取り行のロックを解放することができます。 必要な行のみロックを得たい場合に細かな制御を可能にします。なお、 nstable::find()など1回のオペレーションで複数の行を取得する場合は、ロックの解放はできません。細かくロック・アンロックの制御を行いたい場合は、seek系オペレーションを使用してください。

// スナップショット unlock()の例
db->beginSnapshot(MULTILOCK_NOGAP_SHARE);
tb->setFVN("id", 1);
tb->seek();
if (tb->stat() == 0)
   tb->unlock();
...
db->endSnapshot();

自動トランザクション時の行ロック

自動トランザクションの読み取りはデフォルトで nonlocking readsですが、lockBias値にROW_LOCK_Xを指定することで、排他ロック(X)を取得することができます。単純に1つのレコードを更新したい場合に使用します。

// 行ロックの例
tb->setFV("id", 1);
tb->seek(ROW_LOCK_X);// <-- 排他ロック(X)を取得
if (tb->stat() == 0)
{
    tb->setFV("name", "ABC");
    tb->update();
    if (tb->stat() == 0)
        ;// success!
}
...

自動トランザクションは、通常1つのオペレーションで終了してしまいますが、このロックオペレーションを行った場合に限り、次に続くオペレーションを行ってから終了します。これによりロストアップデートの無い更新・削除が行えます。
また、このロックは、テーブルごとに最後にアクセスしたレコードのみ有効で、次になんらかのオペレーションを行うとそのロックは解放されます。

ロックWait

ロックが競合した場合、InnoDBは自動的に一定時間内リトライを繰り返します。その間にロックが解放されればそのまま処理が進みます。解放されなかった場合は、Transactdが nstable::stat()にSTATUS_LOCK_ERRORを返します。リトライ時間は、サーバー側の my.cnf の、mysqldセクション transactd_lock_wait_timeout に秒で指定します。エントリが無い場合はデフォルト値 1(秒) が使用されます。my.cnfでの設定の詳細はTransactd 運用マニュアルを参照してください。
また、 nsdatabase::lockWaitCount()nsdatabase::lockWaitTime() によってクライアント側でリトライすることもできます。これらは、Transactdへのアクセスにおいてはデフォルトで共にゼロが設定され無効になっています。多くの場合は、InnoDBによるリトライの方が、通信のオーバーヘッドがなく効率的です。

更新処理のレコードアクセスアルゴリズム統一の重要性

同時実行性の向上は、不正な更新が起きるかも知れないであろうシナリオの推測と、更新処理のレコードアクセスアルゴリズムにかかっています。特にアルゴリズムは、処理ごとに統一されることがとても重要です。それができれば無限にある同時実行シナリオを限定的なものにできます。
処理ごとに更新アルゴリズムが1つであれば、ネクストキーロックをせずとも挿入ができなくなることは多々あります。また、マルチレコードロックでなくともシングルレコードロックで済むこともしかりです。
Transactdを使用した場合、レコードアクセスのインデックス・オペレーション・順序などはすべてプログラムコードで記述され、それが変わることはありません。しかしSQLの場合はそれらの多くをオプティマイザが決定しています。このためレコード数の増加やそのほかの条件によって変化する可能性があります。これもまた、ロックを多くしなければならない要因になります。

まとめ

Transactdでミッションクリティカルかつ同時実行性の高いアプリケーションを書く上で大切なことをまとめます。

  • アトミックな操作は、トランザクションで(beginTrn() ... endTrn())で行う。
  • 読み取りで一貫性が必要ならスナップショット(beginSnapshot(CONSISTENT_READ) ... endSnapshot())で行う。また、複数の読み取りオペレーションがある場合に使うと全体の実行速度も向上する。
  • 1行だけの更新・削除は、その読み取りオペレーションのbiasにROW_LOCK_Xを指定しロックして行う。
  • ファントムリードが問題になるトランザクションは beginTrn(MULTILOCK_GAP)で開始する。更新しないレコードの読み取り時は、その読み取りオペレーションのbiasにROW_LOCK_Sを指定して、同時実効性を良くする。
  • ファントムリードが問題にならないトランザクションは beginTrn(MULTILOCK_NOGAP)で開始する。
  • トランザクションの同時実効性を追求するときは、beginTrn(MULTILOCK_NOGAP)で不要なロックをunlock()をするか、beginTrn(SINGLELOCK_NOGAP)を検討する。
  • 更新処理ごとにレコードアクセスアルゴリズムを可能な限り統一する。
  • テーブルを占有したいときは、openTable()のmodeパラメータに、TD_OPEN_EXCLUSIVEかTD_OPEN_READONLY_EXCLUSIVEを指定する。ただし、テーブルロックはテーブルを占有してしまうので、マルチユーザー環境では特別な状況以外使用してはいけない。

いかがだったでしょうか?
Transactdはミッションクリティカルなアプリケーションを書けるのはもちろん、SQLに比べてトランザクションの同時実行性能を向上させることが可能です。
新しいプロダクトには、Transactdを使ってみませんか?

次回は、「実践ミッションクリティカル MySQL/Transactd」です。ロックのことはわかったけれども、実際どういうときにどうすればいいのかわからないという方のために、
いえ、自分のためにまとめてみたいと思います。

Transactd 2.3 リリース!

Transactd 2.3 をリリースしました。
このリリースでは大きな機能追加はありませんが、アプリケーションの機能の実現や運用のために重要な細かな変更がされています。

Transactd 2.3 の新機能を紹介します。

readMore 検索

Transactdの検索クエリーは以下のいずれかの条件で終了します。

  • クエリーの条件から判断してこれ以上レコードがない。
  • 指定したレコード数に達した。
  • マッチしないレコード数が指定した数に達した。

2番目もしくは3番目の条件で終了したときは、まだそれに続くレコードを検索したい場合があります。従来は、最後に読み取ったレコードの値から再度クエリー条件を作成して検索を行う必要がありました。
readMoreを使うと、クエリーを再指定することなく同じ条件で続きから検索できます。たとえば、スマートフォンのアプリケーションなどで「もっと読む」といったリンクがありますが、これを簡単に実現することができます。
この「続き」は真に続きで、直前の検索で最後に読み取ったレコードの次のレコードから検索を行います。対して、SQLのLIMIT句とOFFSETを使用した場合では、もう一度最初から検索しOFFSET数を読み飛ばしてようやく目的の検索開始位置にたどり着きます。
readMoreは無駄な再読み取りがなく、OFFSETの使用に比べて高パフォーマンスです。

あわせて、クエリーが1~3番目のどの理由で終了したかを調べるstatReasonOfFind()メソッドも追加されました。

新しいグルーピング関数 firstとlast

recordsetのグルーピング関数に、firstlast関数が追加されました。これらの関数は、各グループの最初または最後のレコードのフィールド値をそのまま返します。
これを使用すると、非正規化されたテーブルでidなどに対する説明のフィールド(nameなど)の値をそのまま利用し、説明フィールドのためのJOINを不要にできます。
グルーピングでは、グループ化のキーフィールドと計算結果フィールドの列しかレコードセットに残りません。そこで、firstlast関数を使用して説明フィールドを残すことができます。

READ_COMMITEDでの共有ロック

トランザクション中のseekstepメソッドについて、2.2では、トランザクション分離レベルがREPEATABLE_READの場合、レコードごとに共有ロック(S)と排他ロック(X)の指定が可能でした。しかし、READ_COMMITEDでは排他ロック(X)のみで、共有ロック(S)の指定は行えませんでした。
2.3からはREAD_COMMITEDにおいてもその指定ができるようになりました。これで主要な2つのトランザクション分離レベルで、共有ロック(S)、排他ロック(X)、unlockRow()を使って、自由自在にロックをコントロールできます。
ロック競合は、書き込みパフォーマンスを悪化させる大きな要因です。細かなロック制御によってよりパフォーマンスの高いアプリケーションを作成できます。

コネクションの再接続

ネットワーク接続の再接続コマンドdatabase::reconnect()が追加されました。不意にサーバープロセスが再起動された場合でも、このメソッドによって再接続が可能になりました。再接続とともに、以下の処理も同時に行われます。

  • 開いていたテーブルの再オープン
  • カーソル位置の復元
  • レコードロックの復元

トランザクション中の切断でなければ、アプリケーションは何事も無かったかのように処理を継続できます。
ただし、現在のバージョンでは、ネットワーク接続の切断時に実行中だったトランザクションは復元されません。再度実行する必要があります。また、再接続先の変更も行えません。
将来的には、MySQLレプリケーションを使ってHA運用を行うために、接続先の自動変更、処理中トランザクションの自動再実行をサポートする予定です。

ネットワークタイムアウト

クライアントの接続タイムアウトと、送受信タイムアウトが別々に設定可能になりました。設定はクライアント側のtransactd.cnf(ini)にて行います。
デフォルトは以下の値(秒)です。

  connectTimeout = 20
  netTimeout = 180

この変更も、コネクションの再接続と併せてHAための準備になっています。Transactdは、サーバーの死活監視をクライアントライブラリが直接行う予定です。ネットワークのタイムアウト時間は、その判定においてとても重要な要素です。

まとめ

紹介した新機能に加えて、既知のバグについてはすべて修正し、より完成度を高めています。
前バージョンをご利用の方は、2.3にアップグレードすることを強くお奨めします。
詳しくはリリースノート(GitHub)をご覧ください。

MySQL/MariaDB UPDATEとDELETEの内部ロジック

前回のブログ「MySQL/MariaDBとTransactdのInnoDBロック制御詳細」で、「ロックなし読み取りと更新の混在に注意」と書きましたが、「更新前の値を条件で指定したUPDATEやDELETEは問題ないと思うが心配だ」という質問をいただきました。そこで今回は、UPDATEとDELETE文のInnoDBロックについて説明したいと思います。とは言っても、ロックの内容については既に説明済なので、更新処理の内部ロジックについて説明します。それが今回の質問の答えでもあります。

更新前の値をWHERE句で特定した更新

更新前の値をWHERE句で特定した更新の例としては、statusを1から2に変更して状態遷移させるような更新があります。たとえば以下のようなSQL文です。
UPDATE user SET status = 2 WHERE id = 1 and status = 1;
ポイントは、WHEREの条件に status = 1を指定している点です。レコードにバージョン番号フィールドを設けてそれを指定する場合も同様です。*1

UPDATE文内のWHERE id = 1 and status = 1の読み取りは、ロックされたものでしょうか?それとも、Consistent nonlocking readなのでしょうか?

UPDATEとDELETEの内部ロジック

UPDATEとDELETE文では、SELECT句によって更新する対象のレコード(SELECT句が無い場合はすべてのレコード)をフィルタリングします。
例えばすべてのレコードを対象にid >= 5 and id <= 10といった条件でフィルタリングをしたとき、MySQL内部のHandlerインタフェースの疑似コードは以下のようになります。(このサンプルはわかりやすくするため、先頭から最後まで検索しますが、実際はインデックスを使って必要なレコードを可能な限り絞って読み取ります。)

#define FIELD_INDEX_ID 0
#define KEY_ID         0

//テーブルオープン
TABLE_LIST tables;
tables.init_one_table("databaseName",strlen("databaseName"),
                      "tableName",strlen("tableName"), TL_READ);
open_table(thd, tables);
TABLE* table = tables.table;
Handler* file = table->file;
//インテンショロック IXの明示
table.reginfo.lock_type = TL_WRITE; // <-- IX
thd->lock = mysql_lock_tables(thd, &table, 1, 0);

//レコード処理
int keyNum = KEY_ID;
Field* field = *(table.field + FIELD_INDEX_ID);
file->ha_index_init(keyNum , true);
int stat = file->ha_index_first(); // <-- LOCK(X)
while (stat == STATUS_SUCCESS)
{
   if ((field->val_int() >= 5) && (field->val_int() <= 10))
   {   
      // UPDATE record[0]に新しいフィールド値をセットする
      file->ha_update_row(table->record[1],table->record[0]);
   }else
     file->unlock_row();
   file->ha_index_next();  //<-- LOCK(X)
}

//コミットとロック解放
trans_commit_stmt(thd);    
mysql_unlock_tables(thd, thd->lock);

//テーブルクローズ
close_thread_tables(thd);

エラー処理などはだいぶ省いていますがおおよそこのような感じになります。
file->ha_index_first()file->ha_index_next()の部分がレコードを読み取っている部分です。
このコードのロックに関するポイントをまとめます。

  • 更新処理のインテンションロックは無条件に最初にIXが指定される。すなわち、その後に行われる対象レコードを探す読み取りはロックが取得される。
  • (InnoDBの場合)インデックス順に、順次ロック読み取りをしてそれが対象レコードなら、ha_update_row()によって更新が行われる。(削除ならha_delete_row())
  • ロックのタイプは、分離レベルに応じたものになる。
  • 更新対象外のレコードはアンロックが試みられる。(分離レベルによってInnoDBは無視する)

もう、お解かりだと思います。指定した条件の読み取りはロック付き読み取りで、更新時にも間違いなくその値です。更新前の値をWHERE句で特定した更新は、事前にロックもトランザクションの開始も不要です。

自動トランザクション複数行の更新を行った際に他のクライアントとの競合によってロックできなかった場合などは、この文全体が失敗します。部分的に成功してしまう心配も無用です。
ただ、自動トランザクションの場合、その直後に他のクライアントによって変更される可能性があります。続けて何かをする場合はトランザクション内で行いましょう。

Transactdの更新コード

上記の処理内容をTransactdのコードで表現してみました。手続きの流れは上記のHandlerとそっくりです。SQLでしかデータベースを扱ったことがない方も多いと思いますが、実はISAMデータベースのアクセスメソッドはどれもこのようなものです。

#define FIELD_INDEX_ID 0
#define KEY_ID         0

if (db->open(uri))
{
  table* tb = db->openTable(_T("user"));
  db->beginTrn();
  tb->setKeyNum(KEY_ID);
  tb->seekFirst();
  while(tb->stat() ==0)
  {
    if ((tb->getFVint(FIELD_INDEX_ID) >= 5) && 
     (tb->getFVint(FIELD_INDEX_ID) <= 10))
    {
      tb->setFV("status", 2);
      tb->update();
    }
    tb->seekNext();
  }
  db->endTrn();
  tb->release();
  db->close();
}

まとめ

「更新前の値をWHERE句で特定した更新」は条件の読み取りに排他ロックXが使われ、事前にトランザクションを開始しなくても安全に更新できます。ただ、続けて何かをする場合は、トランザクション内で行いましょう。

フィールドにバージョン番号をつけてそれを指定して更新する手法は「楽観的ロック」と呼ばれたりしますが、これもロックが無いわけではなく、InnoDBはレコードをロックしてから更新しています。SELECT ... FOR UPDATEとの違いを大雑把に言うと、1文で処理するか2文で処理するかの違いです。2文で処理するにはBEGINでトランザクションを開始する必要があるので少し手間がかかります。ですが、バージョンフィールドのような特別なフィールドは不要です。

*1:余談ですが、もし事前にトランザクション内で SELECT * from user WHERE id = 1 and status = 1 FOR UPDATE として読み取っていてidがユニークなら、UPDATE文に status = 1 が不要なのは以前の説明で理解していただけると思います。

MySQL/MariaDB 知っておきたい REPEATABLE-READ の不思議

昨日のブログ記事のアクセスが想像以上に多く、とても驚いています。

この中で、REPEATABLE-READのトランザクションで、「ロックなし読み取りした値を元に更新してはいけない」と書いたのですが、具体的なサンプルを入れていなかったので、ここで紹介したいと思います。

REPEATABLE-READにならないトランザクションを試す

更新 (update)よりも、Insertの方がより驚きがありますので、Insertで示します。

まずは前準備です。MySQLコマンドプロンプトを開いて、以下のSQLでデータベースとテーブルを用意します。

CREATE DATABASE test1;
USE test1;
CREATE TABLE `test1`.`test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test1`.`test1` (`field2`) VALUES (11);
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
SELECT @@tx_isolation;

準備ができたら、トランザクションを開始BEGIN;して、SELECT * FROM test1;でINSERTの確認をします。

BEGIN; /* <--- Trx id = N */
SELECT * FROM test1; /* <-- Consistent nonlocking reads, */
+----+--------+      /*     reads version N */
| id | field2 |
+----+--------+
|  1 |     11 |
+----+--------+

BEGIN;でこのトランザクションのidが決まります。ここではNとします。SELECT * FROM test1;はロックなしリードなのでConsistent nonlocking readsです。ここで読み取ったバージョンはNです。


ここで、別のクライアントからINSERT INTO `test1`(`field2`) VALUES (12);を実行します。

/* execute following command FROM ANOTHER CONNECTION. */
USE test1;
INSERT INTO `test1`(`field2`) VALUES (12); /* <--- Trx id = N+1      */
                                           /* (auto transaction N+1) */

ここで実行したINSERTは別のトランザクション id=N+1 (自動トランザクション) で処理されます。


次に、元のクライアントに戻って、再びSELECT * FROM test1;を見てみます。

SELECT * FROM test1; /* <-- Consistent nonlocking reads, */
+----+--------+      /*     reads version N */
| id | field2 |
+----+--------+
|  1 |     11 |
+----+--------+
1 row in set (0.02 sec)

ちゃんとさっきと同じ結果が表示されます。REPEATABLE-READですね。

では次に、LOCK IN SHARE MODEを加えて、SELECT * FROM test1 LOCK IN SHARE MODE;を実行します。

SELECT * FROM test1 LOCK IN SHARE MODE; /* <-- Locking reads,       */
+----+--------+                         /*     reads latest version */
| id | field2 |
+----+--------+
|  1 |     11 |
|  2 |     12 |
+----+--------+

あれれ!!! 別のクライアントでINSERTしたものまで見えてしまっています。

ロックを要求すると、スナップショットはロックできないので、最新バージョンを読み取ってロックします。言い換えると、ロック要求は「最新をロックしてね」と言っているのと同義です。(SELECT * FROM test1 LOCK IN LATEST VERSION SHARE MODEってコマンドだったら判りやすいかも。ロックなしは、SELECT * FROM test1 READ OWN TRX VERSIONですね。)
更新処理(UPDATE,DELETE)は、MySQLが自動でロック付読み取りをします。なので「ロックなし読み取りした値を元に更新してはいけない」です。


面白いので、ここで、元のクライアントからINSERTしてみます。

INSERT INTO `test1`(`field2`) VALUES (13); /* <--- Trx id = N */

そしてやはりロックなしとロック付で読み取ってみます。

SELECT * FROM test1; /* <-- Consistent nonlocking reads, */
+----+--------+      /*     reads version N */
| id | field2 |
+----+--------+
|  1 |     11 |
|  3 |     13 |
+----+--------+
SELECT * FROM test1 LOCK IN SHARE MODE; /* <-- Locking reads, */
+----+--------+                         /*     reads latest version */
| id | field2 |
+----+--------+
|  1 |     11 |
|  2 |     12 |
|  3 |     13 |
+----+--------+

予想通りというか要求どおり、ロックなし読み取りはバージョンNのスナップショットを返し、ロック付は最新バージョンを返します。
(ロックなしはOWN TRX VERSIONを要求し、ロック付はLATEST VERSIONを要求)

COMMIT;
SELECT * FROM test1; /* <--- Consistent nonlocking reads,  */
+----+--------+      /*      reads latest version          */
| id | field2 |      /*      auto transaction trx id > N+1 */
+----+--------+
|  1 |     11 |
|  2 |     12 |
|  3 |     13 |
+----+--------+

コミットしてからロックなし読み取りをすると、この読み取りにも(自動)トランザクションのidが割り振られて、N+1よりも大きい値になります。読み取りにはそのバージョン(N+1+x)のスナップショットが使われ、それはほぼ最新バージョンです。

まとめ

InnoDBのMVCCとロックを理解すると、一見すると不思議に思えるこれらの動作も予想通りです。また、この動作(振る舞い)は仕方がないものかと思います。(判りづらいのは確かで、改善の余地はあるかとは思いますが。)

InnoDBのMVCCとロックの詳細は以下の記事で。

MySQL/MariaDBとTransactdのInnoDBロック制御詳細 その1

今回から数回にわたり、TransactdのオペレーションとInnoDBにおけるロックについて解説します。
ロックについてはあまり良くわからなくてもとりあえずそれなりに動くアプリケーションは作れてしまいます。ですが、マルチユーザー環境でミッションクリティカルなアプリケーションを書くには、ロックの理解が不可欠です。ロックをうまく使って、矛盾や間違いのない読み書きをしつつ同時実行性も高いアプリケーションにしましょう。
その1では、Transactdを実装する上でMySQLのソースやドキュメントから得た知見を基に、InnoDBのロックの種類と分離レベルに応じてそれをどのように使うかをまとめてみます。

Index

MySQLトランザクション関連用語

説明に入る前に、事前にMySQLトランザクション関連用語の理解が必要です。下記の用語についてあまりよくわからない場合は、大まかで良いので理解しておいてください。

MySQLのREPEATABLE-READ

MySQLトランザクション分離レベルはREPEATABLE READが標準です。通常 REPEATABLE READではファントムリードを回避できませんが、MySQLのREPEATABLE READはネクストキーロックという仕組みによってファントムリードが起きないようになっています。そのため、さらに分離レベルの高い SERIALIZABLE とほぼ同じものです。

トランザクション分離レベルは4つありますが、READ_UNCOMMITEDはほとんど使用されないので、ここでは省いて説明します。

InnoDBのロック

はじめにInnoDBの基本的なロックについて説明します。InnoDBはMVCCとこれらのロックを使って、トランザクション分離レベルを実装しています。

行ロック (row-level locking)

InnoDBは行ロックをサポートしています。行ロック(row lock)にはShared lock(共有ロックS)とExclusive lock(排他ロックX)の2種類があります。
トランザクションT1によって共有ロック(S)が取得されたレコードRは、別のトランザクションT2から共有ロック(S)はできますが、排他ロック(X)はできません。また、トランザクションT1によって排他ロック(X)が取得されたレコードRは、別のトランザクションT2から共有ロック(S)も排他ロック(X)もできません。

2つのトランザクションが同じレコードのロックを取得しようとしたときのロックの可否を表したのがInnoDBソースコードの以下の部分です。「TRUE」の組み合わせでは互いに競合せず双方とも取得できます。「FALSE」の組み合わせでは、後から取得しようとした方は、先に取得されたロックが解放されるまでロックを取得することができません。まだ説明していないIXやISというロックがありますが、ここではXとSの組み合わせのみ見てください。

//mysql-5.6.20/storage/innobase/lock/lock0lock.cc : 323

static const byte lock_compatibility_matrix[5][5] = {
 /**         IS     IX       S     X       AI */
 /* IS */ {  TRUE,  TRUE,  TRUE,  FALSE,  TRUE},
 /* IX */ {  TRUE,  TRUE,  FALSE, FALSE,  TRUE},
 /* S  */ {  TRUE,  FALSE, TRUE,  FALSE,  FALSE},
 /* X  */ {  FALSE, FALSE, FALSE, FALSE,  FALSE},
 /* AI */ {  TRUE,  TRUE,  FALSE, FALSE,  FALSE}
};

共有ロックと排他ロックそれぞれの使い道は以下の通りです。

共有ロック 読取
排他ロック 読取、更新、削除

GAPロック

GAPロックは、レコードとレコードの間の空間をロックします。これでInsertをブロックし、行ロックで更新をブロックすることで、ファントムリードを防止します。具体的には、後ろ側のレコードにGAPロックを行います。GAPロックは単体で使用する場合と、行ロックと組み合わせて使用する場合があります。行ロックと組み合わせたものをネクストキーロック(Next key lock)と呼んでいます。

GAPロック単体、ネクストキーロック、GAPロックを含まない通常の行ロックの3種類をinnodb_monitorで見ると、下記のように表示されます。*1

GAPロック単体

.`test` trx id 134515465 lock mode S locks gap before rec

ネクストキーロック

.`test` trx id 134515465 lock mode S locks

GAPロックを含まない通常の行ロック(row lock)

.`test` trx id 134515465 lock mode S locks rec but not gap

読み取ったレコードすべてにネクストキーロックを行い、最後に読み取ったレコードにGAPロックかネクストキーロック*2を行うことで、検索範囲内の更新と挿入ブロックを実現します。
たとえば、ユニークなインデックスフィールド id の1から15までレコードが挿入されたテーブルがあるとします。そこにREPEATABLE-READで select id from table where id <= 10 LOCK IN SHARE MODEとすると、まず、id 1から10まで10個のレコードは確実にネクストキーロックが行われます。11は、ユニークキーで <= としているので読まなくても検索終了を判断できそうなのですが、11もネクストキーロックされます。(11のロックについては、インデックスや検索条件の書き方によって変わります。MySQLでは、ほぼロックされると考えてください。)

GAPロック単体でロックされたレコードは、前の空間がロックされているだけでレコードそのものはロックされていません。以下の例では、同じレコード(0: 1:で示されたフィールドの内容が同じ)に対して異なる2つのトランザクションがGAPロック(X)と共有ロック(S)を取得しています。

//トランザクション
.`test` trx id 134515525 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 4; hex 80000003; asc ;;

//トランザクション2

.`test` trx id 134515526 lock mode S
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 4; hex 80000003; asc ;;

なお、検索がEND OF RECORDSまで到達した場合は、GAPロックを明示するレコードが存在しません。その場合は、最後のレコードのさらに後ろにある終端を示すレコード(supremum)がロックされます。

.`test` trx id 134515465 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

MySQLのhandlerインターフェースには、ネクストキーロックを明示的に指示する方法は存在しませんが、分離レベルにREPEATABLE READかSERIALIZABLEが指定されていると、行ロックに替えてネクストキーロックが使用されます。

余談ですが、SQLの悩みに「ロックされるレコードの範囲はどこになるのかがわかりにくい」といったものがあります。読み取り方法や範囲をオプティマイザが決めるためです。Transactdの場合はそのような悩みはありません。それらはコードを書く人が決めるからです。

インテンションロック (Intention Locks)

インテンションロックは、multiple granularity locking (複数粒度ロック)とMySQLのドキュメントに記されていますが、実際にこのようなロックがあるわけではありません。テーブルにこのロックを明示すると、以降の行操作に対して、行の共有ロック(S)または排他ロック(X)の取得が行われる仕組みのことです。
行ロックに合わせて、インテンションロックにも共有ロック(IS)と排他ロック(IX)の2種類があります。ともにテーブルに対してロックを明示します。

  • 共有ロック(IS)は、トランザクションT内でテーブルtの複数の行に対して共有ロック(S)の取得を試みる。
  • 排他ロック(IX)は、トランザクションT内でテーブルtの複数の行に対して排他ロック(X)の取得を試みる。

インテンションロックの使われ方を簡単に説明すると、これから更新や削除を行う際には排他ロック(IX)を設定し、更新や削除をされたくない読み取りを行う際には共有ロック(IS)を設定します。(SQL文で明示的にインテンションロックを操作する方法はありません。MySQLSQL文からテーブルロックタイプを決め、そのタイプからInnoDBインテンションロックタイプを決定します)

Multi-Versioning

InnoDBの各レコードには、最後に追加または更新・削除したトランザクションのIDが記録されます。更新・削除によって古くなったバージョン(旧バージョン)のレコードは、テーブルスペースのロールバックセグメントに保持されます。

  • スナップショットは、自分のトランザクションIDと同じか、より前のトランザクションIDで最後にコミットされたレコードの集合。
  • スナップショットの読み取りはロックは不要。また、ロックの取得や変更はできない。

前述の共有ロック(S)と排他ロック(X)のいずれでもないLOCK_NONE(ロックなし)の読み取りは、スナップショットが使われます。

Consistent Reads

トランザクションT1において、他のトランザクションによって変更された値に影響されることなく、T1内で原始性が保証された一貫性のある読み取りができることをConsistent reads(一貫性読み取り)と言います。
たとえば、売上伝票のヘッダーと明細はトランザクションによって一式でアトミックな書き込みが行われるとします。読み取りにおいて一貫性がないと、ヘッダーと明細で異なるバージョンを読んでしまうことが起こります。T1がヘッダーを読み取り、次に明細を読み取る前に、T2によって変更された伝票がコミットされた場合などです。Consistent readsはアトミックな特性と併せて、このような問題を解決するためにとても重要な機能です。
InnoDBのConsistent readsには、2つの方法があります。

  • Consistent Nonlocking Reads (ロックなし一貫性読み取り)
  • Consistent locking Reads (ロッキング一貫性読み取り)

Consistent Nonlocking Reads

Consistent Nonlocking Readsはマルチバージョン機能を使って、トランザクションを開始した時点のスナップショットにアクセスすることで実現します。他のトランザクションによってコミット済みの挿入・更新されたレコードがあっても、スナップショットにはそれはないので、Consistent readsを実現します。また、ロック不要なので Consistent Nonlocking Readsです。
InnoDBでConsistent Nonlocking Readsを行うには、ロックを要求しない読み取りを行うことで実現します。(SQLでは、SELECT ... FOR UPDATE か SELECT ... LOCK IN SHARE MODEを使わない読み取り)ただし、分離レベルがSERIALIZABLEの場合は、トランザクション内のすべての読み取り時に共有ロック(S)を要求してしまうので、Consistent Nonlocking Readsは行えません。
以下は、SERIALIZABLEがLOCK_Sを強要する部分のInnoDBのコードの抜粋です。(mysql-5.6.20)

// /mysql-5.6.20/storage/innobase/handler/ha_innodb.cc : 12191
if (trx->isolation_level == TRX_ISO_SERIALIZABLE
        && prebuilt->select_lock_type == LOCK_NONE
        && thd_test_options(
          thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {

      /* To get serializable execution, we let InnoDB
      conceptually add 'LOCK IN SHARE MODE' to all SELECTs
      which otherwise would have been consistent reads. An
      exception is consistent reads in the AUTOCOMMIT=1 mode:
      we know that they are read-only transactions, and they
      can be serialized also if performed as consistent
      reads. */

      prebuilt->select_lock_type = LOCK_S;
      prebuilt->stored_select_lock_type = LOCK_S;
    }

この、Consistent Nonlocking Readsには2つ注意点があります。

  • READ-COMMITEDの場合は、スナップショットのバージョンがトランザクション内で変わる。
  • スナップショットは更新できない。更新は最新バージョンに対して行うが、スナップショットとは値が異なることがある。

REPEATABLE-READのときは、トランザクションの最初から最後まで同じスナップショットのバージョンです。ところが、READ-COMMITEDのときは1つのSQL文の中でのみ同じものです。トランザクション中であっても、1文目と2文目では異なるスナップショット(のバージョン)が使われる可能性があります。(1文内だけのConsistent Nonlocking Reads)
スナップショットはコミット済の確定した内容なので、その行をロックして変更することはできません。トラザクション内で更新が必要な場合は、スナップショットではなく、最新バージョンをロックする必要があります。もし、最新のバージョンが、自分のトランザクションIDより新しいものであった場合、その内容はスナップショットとは異なるものになります。そのため、トランザクションに更新を含む場合、スナップショットを使ったConsistent Nonlocking Readsとの混合は、REPEATABLE-READを満たすことができません。
下図は、REPEATABLE-READのトランザクションにおいて、スナップショットの様子を図にしたものです。
f:id:bizstation:20141223115353p:plain
トランザクション2がConsistent Nonlocking Reads で読み取りを行ったあと、record 2を更新するには、トランザクション3(trx_id = 3)によって更新された最新のバージョンを読み取って排他ロック(X)を取得しなくてはなりません。最新のバージョンはConsistent Nonlocking Readsで読み取ったバージョンとは異なります。絶対に、ロックなしREADによって得られた値を元に更新してはいけません。

Consistent locking Reads

Consistent locking ReadsというワードはMySQLのドキュメントにはありませんが、Nonlockingと対比するために便宜的にこう呼んでいます。
Consistent locking Readsは、トランザクション内で読み取ったレコードをすべてネクストキーロックして、更新と挿入をブロックすることで、Consistent readを実現する方法です。(この方法は、更新を行いつつ一貫性のある読み取りができますが、ロックが多くなるためトランザクションの同時実行性能は大きく低下してしまいます。)
実はこのConsistent locking Readsが、SERIALIZABLE です。前述のとおり、SERIALIZABLE を指定するとロックを指定していない読み取りにおいても、ネクストキーロック(S)を自動で取得します。
それ以外の分離レベルでは、SELECT ... FOR UPDATE や SELECT ... LOCK IN SHARE MODE を使ってロックを指定しないと読み取りにロックをかけません。Consistent locking Readsにするには明示的にロックを行う必要があります。ちなみに、REPEATABLE-READですべての読み取りにロックを指定をすると、SERIALIZABLEと同じになります。
READ_COMMITEDでは、ロック指定読み取り時にネクストキーロックに替えて通常の行ロックが使用されます。READ_COMMITEDはファントムリードを防止できなくてもよいのでこれでよいのですが、Consistent locking Readsにはなりません。

ロック付読み取り時のロック対象

クエリーにマッチしないレコードのロック保持と開放について説明します。
クエリーの検索対象は、インデックスを使った特定のレコード範囲(または複数の範囲)、または全レコードのどちらかです。その際に、範囲内ではあるけれども条件にマッチしないレコードのロックはどうなるのでしょうか?
それは分離レベルによって以下のようになります。

SERIALIZABLE
REPEATABLE-READ
ロックを保持
(ファントムリードを防止するため)
READ_COMMITED ロックを解放

この保持するか否かの実装は、InnoDBのha_innobase::unlock_row()内にあります。MySQLもTransactdもアンマッチの時は、unlock_row()を呼び出しますが、InnoDBがREPEATABLE-READとSERIALIZABLEのときは無視してロックを解放しないようになっています。以下はそれを実装するInnoDBソースコード抜粋です。

// /mysql-5.6.20/storage/innobase/handler/ha_innodb.cc : 7222
  switch (prebuilt->row_read_type) {
  case ROW_READ_WITH_LOCKS:
    if (!srv_locks_unsafe_for_binlog
        && prebuilt->trx->isolation_level
        > TRX_ISO_READ_COMMITTED) {
      break;
    }
    /* fall through */
  case ROW_READ_TRY_SEMI_CONSISTENT:
    row_unlock_for_mysql(prebuilt, FALSE);
    break;
  case ROW_READ_DID_SEMI_CONSISTENT:
    prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;
    break;
  }

row_read_typeがROW_READ_WITH_LOCKSであるときは、直前の読み取りでロックをしていることを示します。isolation_levelがTRX_ISO_READ_COMMITTEDより大きい場合はbreakして次のアンロック関数 row_unlock_for_mysql()が呼び出されません。

MySQLのロック制御

ここまではInnoDBのロック機能について説明しました。それとは別にMySQLにはテーブルロック機能があります。また、MySQLがどのように、InnoDBのロックを制御するか説明します。

LOCK TABLES statement

LOCK TABLES statementは、InnoDBのロックとは無関係です。LOCK TABLESは、MySQLの管理するテーブルロックマネージャの機能を利用して実現するものです。MySQLのテーブル操作はhandlerインターフェースを通じて行いますが、handlerインターフェースでテーブルにアクセスするには事前に必ずテーブルのロックを取得しなければなりません。
LOCK TABLES ... READは、ロックマネージャから書き込みをブロックするTL_READロック(自身も他者もREAD可 WRITE不可)を取得します。以降このテーブルはREADロックの取得のみ許可されます。
LOCK TABLES ... WRITEはTL_WRITE排他ロック(自身はREAD WRITE可、他者はREAD WRITE不可)を取得して、以降このテーブルのすべてのロック取得をブロックします。すなわち、読み取りも書き込みもできなくなります。
取得したロックは、UNLOCK TABLESで開放されます。

このLOCK TABLESは、行レベルのロックではくテーブル単位のロックなので、書き込みのある複数トランザクションの同時実行はできません。分離レベルの概念もなくトランザクションは占有です。あえて分離レベル風に名前をつけると EXCLUSIVE です。

トランザクションはLOCK TABLES...で自動で開始されています。AUTOCOMMITが有効だと文の終了でCOMMITされ、後でROLLBACKできなくなってしまいます。複数のSQL文を発行するときはLOCK TABLESの前に、SET AUTOCOMMIT=0 で無効にしておきましょう。UNLOCK TABLESの前にCOMMITまたはROLLBACKすることで、アトミックな処理ができます。

Transactdでテーブルをロックする

少しだけTransactdでの処理を説明します。
Transactdでは、テーブルをオープンするときのmodeパラメータに、TD_OPEN_EXCLUSIVE か TD_OPEN_READONLY_EXCLUSIVEを指定することで、WRITEまたはREADのテーブルロックを行うことができます。

MySQLはLOCK TABLES呼び出し時にすべてのテーブルの列挙が必要で、後からテーブルを追加したりできませんが、Transactdは後からいくつでも追加できます。また、通常モードとEXCLUSIVEモードを混在させてオープンすることもできます。さらに、それらのテーブルが混在したトランザクションもそのまま行えます。SQL文にはアトミックな処理に独特の癖がありますが、Transactdにはそのような特別なことは何もありません。

// テーブル1を排他ロックでオープン。 LOCK TABLES ... WRITEと同じ
table* tb1 = db->openTable(name1, TD_OPEN_EXCLUSIVE); 
// テーブル2をREADロックでオープン。 LOCK TABLES ... READと同じ
table* tb2 = db->openTable(name2, TD_OPEN_READONLY_EXCLUSIVE); 
//ロックモードが異なるテーブルが混在したトランザクションもOK!
db->beginTrn();
...
tb2->seek();
...
tb1->update();
...
db->endTrn();

分離レベルとロック制御

分離レベルに応じてInnoDBがどのように読み取り時にロックをするかを表にまとめます。
読み取りには、ロックなしと有り(SELECT...FOR UPDATE(X)または SELECT...LOCK IN SHARE MODE(S)を使った読み取り)がありますので、それぞれをトランザクション中に使うとどのような読み取り処理をするかが整理できます。併せてロック有りの際にアンマッチレコードのロック開放を行うかどうかも加えておきました。

なお、ここでのMySQLトランザクションは、 START TRANSACTIONで開始しCOMMITで終了するトランザクションと考えてください。(1文での自動トランザクションも基本的には同じですが、更新を含まない場合、ロックをしないなどの最適化がMySQL内部で自動的に行われます。)

MySQLの分離レベルと読み取りロックタイプ表
分離レベル ロックなしREAD ロック有READ*3 アンマッチレコードのロック解放
SERIALIZABLE できない Next key lock しない
REPEATABLE-READ Consistent Nonlocking Reads
トランザクション内で同一バージョンのスナップショット
Next key lock しない
READ-COMMITED Consistent Nonlocking Reads
1つのSQL文内で同一バージョンのスナップショット
row lock する

なお、更新や削除を行う場合は、対象レコードの読み取り時に自動で排他ロック(X)の取得がトライされ、取得できれば実行されます。

まとめ

ここまで見てきたInnoDBの仕様やロックの特性から、MySQLSQL文でミッションクリティカルなアプリケーションを書く場合に大切なことをまとめます。

  • 分離レベルのデフォルトは、REPEATABLE-READにする。(以下の前提条件)
  • アトミックな操作は、トランザクションで(START TRANSACTION ... COMMIT)で行う。
  • 複数のSQL文による読み取りで一貫性が必要なら、更新が無くともトランザクション内で(START TRANSACTION ... COMMIT)で読み取る。
  • 行ロックはトランザクション内であっても、SELECT...FOR UPDATE(X)または SELECT...LOCK IN SHARE MODE(S)を使って明示的しなければ行われない。ただし、分離レベルをSERIALIZABLEにすれば、明示しない読み取りも共有ロック(S)が取得できる。
  • (REPEATABLE-READの)トランザクション内において、ロックなし読み取りと更新処理の混在に注意する。この組み合わせは、REPEATABLE-READではなく、異なった値を読むことがある。受け入れられない場合は、すべての読み取りをロック付読み取りにするか、SERIALIZABLEにする。
  • ファントムリードが問題にならないトランザクションであれば、同時実行性を向上するためにREAD-COMMITEDを使う。
  • テーブルを占有したいときは、SET AUTOCOMMIT=0の後で、LOCK TABLES ... を呼び、処理終了後にCOMMITまたはROLLBACKしてから、UNLOCK TABLESを呼び出す。テーブルロックはテーブルを占有してしまうので、マルチユーザー環境では特別な状況以外使用してはいけない。

いかがだったでしょうか?
これらを理解し実践することがミッションクリティカルなアプリケーションの第一歩になると思います。何か参考になればいいなと思います。

次回、その2ではTransactdのInnoDBロック制御について詳しく書きたいと思います。

*1:参考コード /mysql-5.6.20/storage/innobase/lock/lock0lock.cc : 5011 lock_rec_print()

*2:どちらになるかは検索条件によって変わります

*3:SELECT...FOR UPDATE(X)または SELECT...LOCK IN SHARE MODE(S)を使った読み取り