悲観ロックの実装方法

Question

前提

cronで定期的に実行するバッチプログラムを作成しています。

Hogeテーブルに処理のキューを登録しておき、
バッチが起動するとHogeテーブルからステータス”待ち状態”のレコードを1件取得して特定の処理を実行します。
その処理の完了後、Hogeテーブルのステータスを”実行中”に更新します。

このバッチは多重起動されるものなので、ステータスを”待ち状態”から”実行中”に更新する間、
対象のレコードを他のプロセスから参照（select）させたくありません。
そのため、select ~ for updateを利用してロックをかけようと考え下記の実装で動作を試してみました。

問題

Eclipseのデバッグモードで起動し、ブレークポイント（下記参照）で止めた状態で
別途SQLクライアントを立ち上げそちらで"select * from hoge_queue;"を実行してみました。

予想ではロックされているため、selectが待ち状態になるはずでしたが、
実際はupdate文が実行される前のレコードが取得できてしまいました。
（プログラムをステップ実行し、commit後に再度selectすると、update後のレコードが正しく取得できました）

下記の実装はどこが間違っているのでしょうか？
解決方法をご存知の方がいらっしゃいましたら、ご指摘、アドバイスを頂きたいです。
どうぞよろしくお願い致します。

開発環境

Java 1.8.0_172
MyBatis 3.4.6
MariaDB 10.2.14（トランザクション分離レベルはデフォルトの"REPEATABLE-READ"）

実装

ロジック

private static final String WAITING = "1";
private static final String PROCESSING = "2";

// ～～省略～～

try (SqlSession session = sessionFactory.openSession()) {
    HogeMapper hogeMapper = session.getMapper(HogeMapper.class);
    // 処理するレコードを取得
    HogeQueue record = hogeMapper.findByStatusForUpdate(WAITING);

    // (特定の処理)
    
    // ステータスの更新
    rec.setStatus(PROCESSING);//←★ここにブレークポイントを置いて確認★
    int updateCount = hogeMapper.updateStatus(record);
    session.commit();
}

HogeMapper.java

@Mapper
public interface HogeMapper{

    HogeQueue findByStatusForUpdate(char status); 

    int updateStatus(HogeQueue queue);
}

HogeMapper.xml

<select id="findByStatusForUpdate" resultType="HogeQueue">
    select
      id
     ,input_file_name
     ,status
     ,create_user_id
     ,create_date
     ,update_user_id
     ,update_date
    from hoge_queue
    where status = #{status}
    order by id
    limit 0, 1
    for update
</select>
<update id="updateStatus">
    update hoge_queue
    set status = #{status}
       ,update_user_id = 'batch'
       ,update_date = now()
    where id = #{id}
</update>

Answer 1

これ「ホントにロックかからないのだろうか？」と思い、すでに解決済みになっているようですが、検証してみました。

検証環境：Windows10+Eclipse4.7.2+SpringBoot+Mybatis+MariaDB

検証対象：質問と同様の単一テーブルに対し、Spring+Mybatisの組み合わせて、MybatisからSELECT (複数カラム指定) FOR UPDATE を実施するSQLを1つ実行したところでブレークポイント設置

1. ブレーク中は別スレッド（ブラウザから別タブで同URLを実行）を起動し、同じSELECTを投げてロックされるかを確認
2. ブレーク中にHeidiSQLから同一のロック対象のテーブルに対してSELECTを実行。このとき、SELECTには以下の２種類を検証。

• SELECT * FROM ～～～ FOR UPDATE
• SELECT (いくつかのカラム指定） FOR UPDATE

検証結果：1を実行した結果、先行したスレッドが完了するまで後発のスレッドが待機していることから、MybatisからSELECT ～　FOR UPDATEには行ロックがかかっていることは明らかだった。

検証結果：2を実行した結果、HeidiSQLからSELECT * FROM は無条件で取得されたが、HeidiSQLからSELECT (カラム複数指定) FOR UPDATE はロック待機待ち状態になった。

つまり、MariaDBでは、SELECT * は行ロックを突き抜けて検索される。

Comments

[flaumig]

わざわざお時間を割いて検証して頂きまして、本当にありがとうございます。
“select * 〜”だけがロックをすり抜けるという挙動をするのですね。
こちらの確認不足でした。

Answer 2

select for updateは他のトランザクションから更新や削除をさせないためのロックなので、
他のトランザクションからselectできるのはRepeatabl Read以下の分離レベルにおいては普通かと思います。
正確には、Repeatable Readなのでコミット**"前"**の状態が見えるという状況は仕様通りの動きです。

やりたいことを実現されたいのであれば、
分離レベルをSeriarizableに上げることも選択肢ですが、並行処理の意味が無くなりパフォーマンスが悪くなりますし、
ましてやバッチ処理でその分離レベルは少なくとも私は見たことがありません。
(Seriarizableでも許容時間内に完了するのであれば、問題は無いかと思います)

並行で、パフォーマンスもある程度保ちたいのであれば

• 親プロセスで一旦処理対象のレコードをすべて読み取り、レコード分の子プロセスを起動して一つずつ処理を移譲する
• もしくはcronによるバッチプロセス起動は1回のみで、Javaの中で親スレッドのようなもので処理対象のレコードをすべて読み取り、子スレッドを起動して一つずつ処理を移譲するようなマルチスレッドプログラミングを実装する

といった選択肢しか思いつきませんでした。
（やりたいことの理解が間違っていたらすみません）
後者はやったことがありますが、並行処理を自前で作りこむのは複雑でバグを生みやすいので、
昨今ではこういった仕組み的なところはSpring Batch等のフレームワークに任せたほうが良いかもしれません。

Comments

[flaumig]

ご回答ありがとうございます。
select出来るのは仕様通りなのですね。私が挙動を勘違いしておりました。
仰る通り、処理そのものを一旦見直そうと思います。

Answer 3

別途SQLクライアントを立ち上げそちらで"select * from hoge_queue;"を実行してみました。

予想ではロックされているため、selectが待ち状態になるはずでしたが、
実際はupdate文が実行される前のレコードが取得できてしまいました。

トランザクション内で行わないとロックされませんよ。
MySQLでのselect for updateを実施したがロックされない
また、別のセッションでSELECT しているほう(SQLクライアント側)でも FOR UPDATEしないと駄目じゃなかったかな？

Comments

[flaumig]

すみません。間違って確認前にベストアンサーを押してしまいました…。
まだご覧になっていたら再度、ご教示いただきたいのですがMyBatisを利用していますのでopenSession()を呼んだ時点でトランザクションが開始されている認識です。
そうではなく明示的にトランザクションを開始する、というメソッドや実装方法があるのでしたら、そちらを教えて頂きたいのですが可能でしょうか？
自分で探してみましたがMyBatisで”begin”を実行する方法が見つけられませんでした。

[euledge]

トランザクションが無いとという記述はSQLクライアント側での手動実行の時にという意図です。