JDBCによる悲観ロックの落とし穴

やばいJDBCの悲観ロックのコード

あるあるですが、JDBCを使って悲観ロックでレコードを更新する場合、たまにこんなコードを見かけることがあります。

更新するテーブルの定義

CREATE TABLE ACCOUNT
(
   NAME varchar(12) PRIMARY KEY NOT NULL,
   BALANCE int NOT NULL
)

更新するコード

Statement s = ・・・

rsA = s.executeQuery("select name, balance from account where name='A' for update");
if (rsA.next()) {
    balanceA = rsA.getInt("balance");
    logger.debug("balanceA = " + balanceA);
} else {
    throw new Exception("行がありません");
}
s.executeUpdate(String.format("update account set balance=%d where name='A'", balanceA - 100));
logger.debug("update A");

conn.commit();

実は Isolation level が READ COMMITED では、これは悲観ロックになっていないのです。

このコードがまずいわけ

上のコードでは、

更新モードでカーソルを取得
カーソルの行の値を取得し更新ロックをかける。
行の値を更新し排他ロックをかける。

となっていて問題なさそうに見えますが、問題点は更新ロックの寿命です。

多くのDBでは、更新ロックは、Isolation Level が READ COMMITED の場合、カーソルの指す行のみがロックされ、カーソルが別の行へ移ると元の行の更新ロックは解除されます。カーソルがクローズされれば当然更新ロックは残りません。

ところが、 Statement オブジェクトは executeXXX を実行すると、そのStatementを使って作られたカーソルをクローズするため、上記のコードでは executeUpdateメソッドでレコードを排他ロックするまでのわずかな隙間時間に、レコードのロックがない期間が存在するのです。 これではうまく動きません。

対処方法

対処方法は４つ程あります。

Isolation Level を REPEATABLE READ 以上にする

最も簡単ですが、同時性が少し悪くなります。 REPEATABLE READ を使うと更新ロックはトランザクションが終わるまで存続します。

カーソルを取得せず update account set balance=balance-100 where ... の一行でレコードを更新する

更新の計算を Java でなくても SQL で表現できるならこれを使うべきでしょう。

select と uodate で異なる Statement オブジェクトを使う

Statement sr = ・・・
Statement su = ・・・

rsA = sr.executeQuery("select name, balance from account where name='A' for update");
if (rsA.next()) {
    balanceA = rsA.getInt("balance");
    logger.debug("balanceA = " + balanceA);
} else {
    logger.error("balanceA の行がありません");
}

su.executeUpdate(String.format("update account set balance=%d where name='A'", balanceA - 100));
logger.debug("update A");

この方法は複数の行を同時にロックしたい時などでも便利です。

カーソルを使って行を更新する。

rsA = s.executeQuery("select name, balance from account where name='A' for update");
if (rsA.next()) {
    balanceA = rsA.getInt("balance");
    logger.debug("balanceA = " + balanceA);
    rsA.updateInt("balance", balanceA - 100);
    rsA.updateRow();
    logger.debug("update A");
} else {
   throw new Exception("行がありません");
}
conn.commit();

JDBCを長く使っておられる方でも、いろいろ制約はあるものの、カーソルで更新ができることを知らない人が結構います。 Updatable Cursor を活用しましょう。