質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.93%

[PostgreSQL]カーソルがトランザクションを超えて使用できない

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 2,541

tomy0732

score 7

 前提・実現したいこと

Javaでwebサービスを作っています。
DBはPostgreSQL9.6を使用しています。
少し古めのサービスでMyBatisではなく、iBATISにて動的なSQLを作成をしています。

・実現したいこと
PostgreSQLのカーソルを使用して、大量にあるレコードを少しずつ取得しながらUPDATEをしたい。
既存実装から、カーソルを宣言するトランザクションと、実際にUPDATE処理を行うトランザクションとで分けて実装する必要があった。

そこでここに書いてあるとおりにカーソルを宣言する際にCURSOR WITH HOLD FOR SELECTとし、宣言したトランザクション外でもカーソルが使えるようにしました。

しかし、別のトランザクションでカーソルを使おうとしても、宣言したカーソルは存在しないとエラーが発生し、UPDATE処理ができなかった。

どうすればカーソルをほかのトランザクションでも使えるようになるのか、また、他に何か気をつけなればならないことがあるのであればご教授お願いいたします。

 発生している問題・エラーメッセージ

下記のソースコードにてトランザクションを開始するか終了するかの宣言で、カーソルが存在しなくなる。
コミットする分にはカーソルは消えなかった。
ちなみにカーソルが存在するかの確認はSELECT * FROM pg_cursors;を使い確認していた。

 該当のソースコード

簡略化してるので抜けがあるかもしれません。
複数のトランザクションで処理

public class RecordEdit() {
    // DBにアクセスするためのもの
    final SqlMapClient sqlMapClient = baseDao.getSqlMapClient();
    // フェッチしてくる数を設定(1000件)
    final int RECS_AT_ONCE = 1000;

    // レコードIDを格納するリスト
    Linkedlist<Long> recordIdList;
    // カーソル名(本来は動的に決める)
    String cursorName;

    // コンストラクタ
    public RecordEdit() {
        recordIdList = new LinkedList<>();
        cursorName = "cursorTest";
    }

    /** 宣言されたカーソルを使いフェッチしてくる **/
    private Linkedlist<Long> fetchRecordIds() {
        try {
            // トランザクションの開始
            sqlMapClient.startTransaction();

            String query = "FORWARD " + RECS_AT_ONCE " FROM " + cursorName;
            List<Map<String, String>> recordList = null;
            // レコード情報の取得(FETCH)
            recordList = sqlMapClient.queryForList("Record.fetch", query);
            List<Long> resIdList = /** 取得したレコード情報からIDだけ抜き取る処理 **/

            // 処理が成功したならコミット
            sqlMapClient.commitTransaction();
            return resIdList; 
        } finally {
            // 成功/失敗に関わらず、トランザクションの終了
            sqlMapClient.endTransaction();
        }
    }

    /** カーソルを宣言する **/
    private void declareCursor() {
        try {
            // トランザクションの開始
            sqlMapClient.startTransaction();

            String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
            // カーソルの宣言(DECLARE)
            sqlMapClient.queryForList("Record.declareCursor", query);

            sqlMapClient.commitTransaction();
        } finally {
            // ↓ここで宣言したカーソルが消える
            sqlMapClient.endTransaction();
        }
    }

    /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/
    private Long getNextId() {
        // リストが空ならフェッチしてくる
        if (recordIdList.isEmpty()) {
            recordIdList = fetchRecordIds(cursorName);
        }
        // nullかidをを返す
        return recordIdList.isEmpty() ? null : recordIdList.removeFirst();
    }

    /** UPDATE処理を行う **/
    private void update(Long id) {
        try {
            // トランザクションの開始
            sqlMapClient.startTransaction();

            /** 指定されたIDに対してUPDATE処理 **/

            sqlMapClient.commitTransaction();
        } finally {
            sqlMapClient.endTransaction();
        }
    }

    /** カーソルのクローズ処理 **/
    private void closeCursor() {
        // カーソルを閉じる(CLOSE)
        sqlMapClient.queryForList("Record.closeCursor", cursorName);
    }

    /** メイン処理 **/
    public void execute() {
        // カーソルを宣言
        declareCursor();
        while (true) {
            // idを取得
            long recordId = getNextId();
            // 次のレコードが無いならループを抜ける
            if (recordId == null) {
                break;
            }
            // UPDATE処理を行う
            update(recordId);
        }
        // 無事ループを抜けたならカーソルを閉じる
        closeCursor();
    }
}

追記
1つのトランザクションで処理

public class RecordEdit() {
    // DBにアクセスするためのもの
    final SqlMapClient sqlMapClient = baseDao.getSqlMapClient();
    // フェッチしてくる数を設定(1000件)
    final int RECS_AT_ONCE = 1000;

    // レコードIDを格納するリスト
    Linkedlist<Long> recordIdList;
    // カーソル名(本来は動的に決める)
    String cursorName;

    // コンストラクタ
    public RecordEdit() {
        recordIdList = new LinkedList<>();
        cursorName = "cursorTest";
    }

    /** 宣言されたカーソルを使いフェッチしてくる **/
    private Linkedlist<Long> fetchRecordIds() {
        String query = "FORWARD " + RECS_AT_ONCE " FROM " + cursorName;
        List<Map<String, String>> recordList = null;
        // レコード情報の取得(FETCH)
        recordList = sqlMapClient.queryForList("Record.fetch", query);
        List<Long> resIdList = /** 取得したレコード情報からIDだけ抜き取る処理 **/
        return resIdList; 
    }

    /** カーソルを宣言する **/
    private void declareCursor() {
        String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
        // カーソルの宣言(DECLARE)
        sqlMapClient.queryForList("Record.declareCursor", query);
    }

    /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/
    private Long getNextId() {
        // リストが空ならフェッチしてくる
        if (recordIdList.isEmpty()) {
            recordIdList = fetchRecordIds(cursorName);
        }
        // nullかidをを返す
        return recordIdList.isEmpty() ? null : recordIdList.removeFirst();
    }

    /** UPDATE処理を行う **/
    private void update(Long id) {
        /** 指定されたIDに対してUPDATE処理 **/
    }

    /** カーソルのクローズ処理 **/
    private void closeCursor() {
        // カーソルを閉じる(CLOSE)
        sqlMapClient.queryForList("Record.closeCursor", cursorName);
    }

    /** メイン処理 **/
    public void execute() {
        try {
            // トランザクションの開始
            sqlMapClient.startTransaction();
            // カーソルを宣言
            declareCursor();
            while (true) {
                // idを取得
                long recordId = getNextId();
                // 次のレコードが無いならループを抜ける
                if (recordId == null) {
                    break;
                }
                // UPDATE処理を行う
                update(recordId);
                // 処理が成功したなら都度コミット
                sqlMapClient.commitTransaction();
            }
        } finally {
            // 成功/失敗に関わらず、トランザクションの終了
            sqlMapClient.endTransaction();
            // 無事ループを抜けたならカーソルを閉じる
            closeCursor();
        }
    }
}

 試したこと

トランザクションを別々にせずに、同じトランザクション内で処理を行えば、カーソルでの処理はできました。
しかし、件数が多すぎるとUPDATE処理にすごく時間がかかり、あまりよろしくありません(テーブルの列が10個で1レコード40㎳ぐらい、1万レコードだと400sかかる)。
UPDATE処理は別のトランザクションで処理した方が速いというのはこれまでの試験でわかっており(1レコード15msくらい)、どうしても別々のトランザクションで処理を行いたいと考えています。

 補足情報(FW/ツールのバージョンなど)

ここにより詳細な情報を記載してください。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

SqlMapClientをもう一つ作ればいいのでは?

final SqlMapClient sqlMapClientUpdate = baseDao.getSqlMapClient();

updateメソッドで使うSqlMapClientを上記のインスタンスにすると
おそらくcursorとは別トランザクションになると思います。
updateメソッドの引数から推測するに、特にcursorが必要ではなさそうなので。

ただトランザクションを分けても処理が早くなる気はしないのですが、、、
たぶんトランザクションの開始終了が多すぎるのが問題のような気がします。
update処理が一行毎に行われるので、それで少なくとも3回DBにアクセスすることになります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/21 23:37

    UPDATE 終了後にCOMMITされているのでしょうか?
    COMMITさえされれば、WALは開放される気がするんですけどね。

    キャンセル

  • 2018/02/22 18:02

    コミットはUPDATE処理後に毎回行っております。
    その辺はどうなんですかね...。

    キャンセル

  • 2018/02/23 09:55

    > updateした内容は都度DBに反映されていたため
    という回答から、都度COMMITされているものと推測されるのですが、、、

    性能的なことだけ考えると、全く別の方法でcursorではなく一時テーブルを使う方法など試されてはいかがでしょうか。
    cursorの部分を一時テーブルにすることで、母数が減るはずので、limit offsetでも十分性能が出ると思います。

    キャンセル

+1

本題とは関係ありませんが、その程度の件数で処理時間が掛かるというのは、wal_buffersとかshared_buffersが小さすぎるのではないですか?

もし、postgresql.confがデフォルトのままでしたら、見直しされることをお勧めします。
PostgreSQLをデフォルト設定のまま使っていませんか?

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/15 20:56

    回答ありがとうございます。

    参考URLの設定は2005年時点のもので、参考にはできませんでしたが、web等で調べつつ、postgresql.confの見直しをしてみました。
    結果、以下の設定を変えて、
    ```
    work_mem = 16MB # default値は4MB
    shared_buffers = 4096MB # default値は128MB
    effective_cache_size = 8GB # default値は4GB
    ```
    再度処理時間を計ってみましたが、結果は変わらずでした...。

    キャンセル

  • 2018/02/15 22:25

    wal_buffersは変更しました?

    キャンセル

  • 2018/02/16 09:02

    wal_buffersの値は-1にしてあるため、shared_buffersの32分の1の値になるそうです。
    よって設定の問題は無いのかなと考えています。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.93%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る