SELECT FOR UPDATE NOWAIT他のオプションにおける、MySQL、PostgreSQL、Oracleの挙動

MySQL8.0から、SELECT FOR UPDATE NOWAIT/SKIP LOCKEDがサポートされました。

パチパチパチ、といっても、MySQL8.0自体は2018-04-19にGA（8.0.11）していますし、この機能自体は8.0.1（2017-04-10, Development Milestone）のリリースノートですでに登場していますから、ここまでの説明についてはすでに色々なところで解説がなされています。

PostgreSQLにもOracleにもあるこのオプション、やっと来たかというヤツですね。

SKIP LOCKEDなんかは、キューをテーブルで管理して複数のクライアントからキューを拾って処理するような設計・実装をしてしまった場合など、ロック衝突を下げてうまい具合に処理させるのに都合がよい機能ですね。
そもそもテーブルでキュー実装すんな、キュー管理に強いヤツ使え、って話ですが。
（実践ハイパフォーマンスMySQL 第3版に、「キューテーブルは大抵うまく行かない（超略）」って書いてあります。最新の第3版でもMySQL 5.6時代の本なので。）

で、オプションの種類と意味・挙動はこんな感じです。

オプション	挙動
無指定	他のセッションがロックしているなど、SELECT対象のロックがすべて取れない場合、他セッションによるロックの解放を待つ。
NOWAIT	他のセッションがロックしているなど、SELECT対象のロックがすべて取れない場合、ロックの開放を待たずに処理を返す。
SKIP LOCKED	他のセッションがロックしているなど、SELECT対象のロックがすべて取れない場合、ロックが取れる行だけをロックして処理を返す。0件であってもエラーにはならない。

いろいろなところで説明されていますので、エビデンス貼るとかしません。

その代わりに、あまり解説されていない注意点や、DBMS毎の細かい違いについて触れていきます。

MySQLの場合

NOWAIT and SKIP LOCKED only apply to row-level locks.
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html#innodb-locking-reads-nowait-skip-locked

NOWAITとSKIP LOCKEDは「行レベルロックにしか効かないよ」ってことです。

つまり、先にLOCK TABLE文でテーブルロックを掴んでいるところに対して、後から別セッションでNOWAITやSKIP LOCKEDを投げても「効かない」、つまり「待たされる」ってことです。

逃げ道は、現時点では存在していません。テーブルロック取られていたら、待つしか無いのです。

なお、SELECT FOR UPDATEでWHERE句なしでテーブル内を全件掴んだとしても、レコードロックです。念の為。

PostgreSQLの場合

これだけじゃつまらないので、さらに広げていきましょう。

PostgreSQLも、同じオプションが使えます。
前述の表と、挙動は同じです。

MySQLでも出た、テーブルロックとの衝突についても

NOWAITおよびSKIP LOCKEDは行レベルロックにのみに適用される点に注意してください。
https://www.postgresql.jp/document/12/html/sql-select.html

と、はっきり書いてあります。よって、MySQL同様に、テーブルロックに対してSELECT FOR UPDATE NOWAIT/SKIP LOCKEDしても、待たされます。

MySQLとの違いは、逃げ道があるということです。

ドキュメントの同じ箇所に、

もし、テーブルレベルのロックを待機せずに獲得しなければならないのであれば、最初にLOCKのNOWAITオプションを使用してください。

という一文があります。

PostgreSQLでは、LOCK TABLE文にもNOWAITオプションが使えます。SKIP LOCKEDはありません。

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
https://www.postgresql.jp/document/12/html/sql-lock.html

SELECT FOR UPDATEの代わりにLOCK TABLEを使うのが妥当か、という話は置いておいて、とりあえずLOCK TABLE NOWAITで投げると、ロック解放を待たずにエラーで処理が戻ってきます。

Oracleの場合

さらにOracleに話を広げましょう。

Oracleの場合、取れるオプションが1種類多いです。

オプション	挙動
WAIT n	他のセッションがロックしているなど、SELECT対象のロックがすべて取れない場合、他セッションによるロックの解放をn秒待つ。

それだけです。

さて、テーブルロックとの関係ですが、

WAIT句およびSKIP LOCKED句の注意事項
　WAITまたはSKIP LOCKEDを指定したときに排他モードで表がロックされていると、表のロックが解除されるまではSELECT文の結果が戻りません。WAITでは、指定されている待機時間にかかわらず、SELECT FOR UPDATE句がブロックされます。
https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_10002.htm#i2065646

という記述があります。

LOCK TABLE文で指定する「ロックモード」によって、動きが異なる、ということです。
具体的には、

EXCLUSIVE MODE
SHARE ROW EXCLUSIVE MODE
SHARE MODE

でLOCK TABLEを発行した後にSELECT FOR UPDATEを発行すると、

オプション	挙動
無指定	待たされる
NOWAIT	即エラーで返る
SKIP LOCKED	待たされる
WAIT n	待たされる（n秒指定は効かない）

となり、それ以外の場合は、取れるロックを取って正常に処理が返ってきます。

なお、テーブルロック同士もモードによって、後から来てもロックが取れるケース、待たされるケースがありますし、LOCK TABLE文に「NOWAIT」や「WAIT n」オプションが使えますが、これは本論から外れるので省略。
本家のドキュメント読んでください。

さらに蛇足ですが、Oracleの公式ドキュメントには、

SKIP LOCKED機能を直接使用するかわりに、Oracle Streams Advanced Queuing APIを使用することをお薦めします。

と書いてあります。
それくらい、キュー管理にテーブル（RDBMS）使うのは要注意、ってことで。

「エラーで返る」におけるRDBMSの差について

エラー発生時のトランザクションの状態管理が、RDBMSによって微妙に違うんです。
特に他のDB経験者でPostgreSQLを触る人は要注意。

まとめ

MySQL8.0からは、SELECT FOR UPDATEに「NOWAIT/SKIP LOCKED」が使えるようになった。
MySQLとPostgreSQLは、LOCK TABLEで表ロックを取っているところに「SELECT FOR UPDATE NOWAIT/SKIP LOCKED」を投げても、待たされる。
Oracleの場合、表ロックのモードによって、「SELECT FOR UPDATE NOWAIT/SKIP LOCKED/WAIT n」の挙動が異なる。
ロックが取れない場合は「エラー」で返ってくる。この際、ステートメントだけエラーになるか、トランザクション全体がエラーになるかは、RDBMSによって異なる。

というわけで、いつも使っているRDBMSと同じノリで他のRDBMSを触るとハマりそうな「ロック」のお話でした。