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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Java

Javaは、1995年にサン・マイクロシステムズが開発したプログラミング言語です。表記法はC言語に似ていますが、既存のプログラミング言語の短所を踏まえていちから設計されており、最初からオブジェクト指向性を備えてデザインされています。セキュリティ面が強力であることや、ネットワーク環境での利用に向いていることが特徴です。Javaで作られたソフトウェアは基本的にいかなるプラットフォームでも作動します。

iBATIS

iBATISとは、O/Rマッピングフレームワークです。簡単にデータベースとJavaオブジェクトをマッピングできるO/Rマッピング機能と、DAOパターン用フレームワークで構成されています。

Q&A

解決済

2回答

2932閲覧

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

tomy0732

総合スコア7

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Java

Javaは、1995年にサン・マイクロシステムズが開発したプログラミング言語です。表記法はC言語に似ていますが、既存のプログラミング言語の短所を踏まえていちから設計されており、最初からオブジェクト指向性を備えてデザインされています。セキュリティ面が強力であることや、ネットワーク環境での利用に向いていることが特徴です。Javaで作られたソフトウェアは基本的にいかなるプラットフォームでも作動します。

iBATIS

iBATISとは、O/Rマッピングフレームワークです。簡単にデータベースとJavaオブジェクトをマッピングできるO/Rマッピング機能と、DAOパターン用フレームワークで構成されています。

0グッド

1クリップ

投稿2018/02/14 11:02

編集2018/02/15 09:44

前提・実現したいこと

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

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

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

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

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

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

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

該当のソースコード

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

Java

1public class RecordEdit() { 2 // DBにアクセスするためのもの 3 final SqlMapClient sqlMapClient = baseDao.getSqlMapClient(); 4 // フェッチしてくる数を設定(1000件) 5 final int RECS_AT_ONCE = 1000; 6 7 // レコードIDを格納するリスト 8 Linkedlist<Long> recordIdList; 9 // カーソル名(本来は動的に決める) 10 String cursorName; 11 12 // コンストラクタ 13 public RecordEdit() { 14 recordIdList = new LinkedList<>(); 15 cursorName = "cursorTest"; 16 } 17 18 /** 宣言されたカーソルを使いフェッチしてくる **/ 19 private Linkedlist<Long> fetchRecordIds() { 20 try { 21 // トランザクションの開始 22 sqlMapClient.startTransaction(); 23 24 String query = "FORWARD " + RECS_AT_ONCE " FROM " + cursorName; 25 List<Map<String, String>> recordList = null; 26 // レコード情報の取得(FETCH) 27 recordList = sqlMapClient.queryForList("Record.fetch", query); 28 List<Long> resIdList = /** 取得したレコード情報からIDだけ抜き取る処理 **/ 29 30 // 処理が成功したならコミット 31 sqlMapClient.commitTransaction(); 32 return resIdList; 33 } finally { 34 // 成功/失敗に関わらず、トランザクションの終了 35 sqlMapClient.endTransaction(); 36 } 37 } 38 39 /** カーソルを宣言する **/ 40 private void declareCursor() { 41 try { 42 // トランザクションの開始 43 sqlMapClient.startTransaction(); 44 45 String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable"; 46 // カーソルの宣言(DECLARE) 47 sqlMapClient.queryForList("Record.declareCursor", query); 48 49 sqlMapClient.commitTransaction(); 50 } finally { 51 // ↓ここで宣言したカーソルが消える 52 sqlMapClient.endTransaction(); 53 } 54 } 55 56 /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/ 57 private Long getNextId() { 58 // リストが空ならフェッチしてくる 59 if (recordIdList.isEmpty()) { 60 recordIdList = fetchRecordIds(cursorName); 61 } 62 // nullかidをを返す 63 return recordIdList.isEmpty() ? null : recordIdList.removeFirst(); 64 } 65 66 /** UPDATE処理を行う **/ 67 private void update(Long id) { 68 try { 69 // トランザクションの開始 70 sqlMapClient.startTransaction(); 71 72 /** 指定されたIDに対してUPDATE処理 **/ 73 74 sqlMapClient.commitTransaction(); 75 } finally { 76 sqlMapClient.endTransaction(); 77 } 78 } 79 80 /** カーソルのクローズ処理 **/ 81 private void closeCursor() { 82 // カーソルを閉じる(CLOSE) 83 sqlMapClient.queryForList("Record.closeCursor", cursorName); 84 } 85 86 /** メイン処理 **/ 87 public void execute() { 88 // カーソルを宣言 89 declareCursor(); 90 while (true) { 91 // idを取得 92 long recordId = getNextId(); 93 // 次のレコードが無いならループを抜ける 94 if (recordId == null) { 95 break; 96 } 97 // UPDATE処理を行う 98 update(recordId); 99 } 100 // 無事ループを抜けたならカーソルを閉じる 101 closeCursor(); 102 } 103}

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

Java

1public class RecordEdit() { 2 // DBにアクセスするためのもの 3 final SqlMapClient sqlMapClient = baseDao.getSqlMapClient(); 4 // フェッチしてくる数を設定(1000件) 5 final int RECS_AT_ONCE = 1000; 6 7 // レコードIDを格納するリスト 8 Linkedlist<Long> recordIdList; 9 // カーソル名(本来は動的に決める) 10 String cursorName; 11 12 // コンストラクタ 13 public RecordEdit() { 14 recordIdList = new LinkedList<>(); 15 cursorName = "cursorTest"; 16 } 17 18 /** 宣言されたカーソルを使いフェッチしてくる **/ 19 private Linkedlist<Long> fetchRecordIds() { 20 String query = "FORWARD " + RECS_AT_ONCE " FROM " + cursorName; 21 List<Map<String, String>> recordList = null; 22 // レコード情報の取得(FETCH) 23 recordList = sqlMapClient.queryForList("Record.fetch", query); 24 List<Long> resIdList = /** 取得したレコード情報からIDだけ抜き取る処理 **/ 25 return resIdList; 26 } 27 28 /** カーソルを宣言する **/ 29 private void declareCursor() { 30 String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable"; 31 // カーソルの宣言(DECLARE) 32 sqlMapClient.queryForList("Record.declareCursor", query); 33 } 34 35 /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/ 36 private Long getNextId() { 37 // リストが空ならフェッチしてくる 38 if (recordIdList.isEmpty()) { 39 recordIdList = fetchRecordIds(cursorName); 40 } 41 // nullかidをを返す 42 return recordIdList.isEmpty() ? null : recordIdList.removeFirst(); 43 } 44 45 /** UPDATE処理を行う **/ 46 private void update(Long id) { 47 /** 指定されたIDに対してUPDATE処理 **/ 48 } 49 50 /** カーソルのクローズ処理 **/ 51 private void closeCursor() { 52 // カーソルを閉じる(CLOSE) 53 sqlMapClient.queryForList("Record.closeCursor", cursorName); 54 } 55 56 /** メイン処理 **/ 57 public void execute() { 58 try { 59 // トランザクションの開始 60 sqlMapClient.startTransaction(); 61 // カーソルを宣言 62 declareCursor(); 63 while (true) { 64 // idを取得 65 long recordId = getNextId(); 66 // 次のレコードが無いならループを抜ける 67 if (recordId == null) { 68 break; 69 } 70 // UPDATE処理を行う 71 update(recordId); 72 // 処理が成功したなら都度コミット 73 sqlMapClient.commitTransaction(); 74 } 75 } finally { 76 // 成功/失敗に関わらず、トランザクションの終了 77 sqlMapClient.endTransaction(); 78 // 無事ループを抜けたならカーソルを閉じる 79 closeCursor(); 80 } 81 } 82}

試したこと

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

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

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

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

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

投稿2018/02/15 09:54

編集2018/02/15 09:59
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tomy0732

2018/02/15 11:56

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

2018/02/15 13:25

wal_buffersは変更しました?
tomy0732

2018/02/16 00:02

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

0

ベストアンサー

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

final SqlMapClient sqlMapClientUpdate = baseDao.getSqlMapClient();

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

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

投稿2018/02/15 07:16

szk.

総合スコア1400

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tomy0732

2018/02/15 08:30 編集

ご回答ありがとうございます。 > SqlMapClientをもう一つ作ればいいのでは? SqlMapClientは基本的に1つしか生成しないとの[記述](https://codezine.jp/article/detail/1289)があり(以下引用)、 ``` 作成したSqlMapConfigを読みこみ、iBATISを利用するためのオブジェクトがSqlMapClientです。このオブジェクトは、長寿命でスレッドセーフであるため、アプリケーションで1つだけ生成すればOKです。SqlMapClientを一度だけ作成し取得するためのクラスを作成します。 ``` これに従って実装されていたため、シングルトンになっています。よってここは関係ない(変えるべきでない)と考えているのですが...。 > おそらくcursorとは別トランザクションになると思います。 カーソル処理のトランザクションと更新処理のトランザクションとで、別々のトランザクションにて処理をしたかったので、これは想定内ですが、なにか認識がずれてますか? > ただトランザクションを分けても処理が早くなる気はしないのですが、、、 > たぶんトランザクションの開始終了が多すぎるのが問題のような気がします。 > update処理が一行毎に行われるので、それで少なくとも3回DBにアクセスすることになります。 今回のカーソルの質問の題目とはずれてしまうのですが、私も同じトランザクション内でコミットだけ細かくやっていれば、トランザクションを何度も開始終了するよりも早いだろうと思っていたのですが、実際に計測した結果はそうはなりませんでした。 上記にも記述しているのですが、 10列、1万レコードあるテーブルに対して1列をUPDATE処理 * 1つのトランザクションで処理(カーソル処理有): 400s * トランザクションを毎回開始終了(カーソル処理無): 150s 処理の経過を見ているとカーソル処理を使いつつ、UPDATE処理をしていると、3000レコード目あたりから、UPDATE処理に時間がかかるようになり、9000レコード目には1レコードの更新に平均で60msかかっていました。 それに比べて、トランザクションを毎回開始終了しているUPDATE処理は、ほぼ同じ処理時間(15ms)で完了していました。 長々と書いてしまい、申し訳ございません。 以上、分かる範囲でなにかありましたら、またのご回答お待ちしております。
szk.

2018/02/15 09:01

ちなみにご提示いただいているソースは cursorとupdateが同じトランザクションのイメージでしょうか。 違ってたらすいませんが、ボクの認識だと同じトランザクションです。 参考にいただいているURL先の > private static SqlMapClient sqlMap; を複数セット作成すればいいのではないかと思います。 あと確認ですが、cursor処理をしているトランザクションでコミットした内容が、 cursor処理中に他のトランザクションから参照できますか? 処理件数の増加と共に、updateに時間がかかるようになるのは、 どこかで更新を溜めているからのような気がします。
tomy0732

2018/02/15 10:40

回答ありがとうございます。 > ボクの認識だと同じトランザクションです 提示していたソースは別トランザクションのつもりで記述していました。 fetchRecordIdsメソッド、declareCursorメソッド、updateメソッドでそれぞれトランザクションの開始終了を行ってトランザクションを分けているつもりでしたが、処理の書き方を間違えているでしょうか? > 複数セット作成すればいいのではないかと思います Java自体の知識がまだまだ無いため、間違えているかも知れませんが、シングルトンを使用しているのは、複数のインスタンスを作られるのを防ぐためだと理解しています。そのため、szk.さんのおっしゃる方法だと、その方式を崩してしまうことになると思うのですが、それはよろしいのでしょうか? > cursor処理中に他のトランザクションから参照できますか? まだ、カーソル処理のトランザクションと他のトランザクションが共存できる術(今回の質問内容)を知らないため、わかりません。 > どこかで更新を溜めているからのような気がします レコードが更新されるごとにコミットをしているはずなので、どこかで溜めていることは無いと思うのですが、リファレンスを確認してみたいと思います。 ご回答、ありがとうございます。
szk.

2018/02/15 12:36

> 提示していたソースは別トランザクションのつもり・・・ 「final SqlMapClient sqlMapClient = baseDao.getSqlMapClient();」 これで1トランザクションが始まると考えていますが、 ボクが間違っているかもしれません。 参考までに(あくまで一例です、複数トランと言っている記事もあります。) http://d.hatena.ne.jp/kuniku/20081108/1226134584 > シングルトンを使用しているのは、複数のインスタンスを作られるのを防ぐためだと理解しています。 はい、一般的な構想としては間違っていません。 ただし業務に支障が出るのであれば、多少崩しても仕方ないと考えています。 あくまで、この処理でしか使わない特異なSqlMapClientを作るということです。 他の処理で使わせるのはNGです。 > カーソル処理のトランザクションと他のトランザクションが共存できる術 処理中にDBサーバに直接繋いで確認することができます。 postgresだと標準のpgAdminやa5m2などのツールで確認できるかと。 サーバに直接ログインしてもいいですし。
sazi

2018/02/15 13:41

>処理中にDBサーバに直接繋いで確認することができます。 >postgresだと標準のpgAdminやa5m2などのツールで確認できるかと。 カーソルの共有は同一セッションでなくとも可能なんですか?
szk.

2018/02/15 14:53

どちらかというと、 "updateした内容を確認できないこと"を確認したい感じになります。 確認できなければ処理しているトランザクションで溜め込んでいるので、 遅くなるのは当然だと判断できます。
sazi

2018/02/15 14:58

そういうことですか。 select してupdateなので、select for updateしているカーソルだったりすると、どうなっちゃうのかなって気がしますけどね。
tomy0732

2018/02/16 03:54

> この処理でしか使わない特異なSqlMapClientを作る 今回の処理用にSqlMapClientを再度作って動作確認しましたが、やはり同じタイミングでカーソルが消えていました...。 > "updateした内容を確認できないこと"を確認したい updateした内容は都度DBに反映されていたため、トランザクションでため込んでいるということはなさそうです。
szk.

2018/02/16 05:30

同じタイミングというのはちょっとおかしいです。 baseDao.getSqlMapClient()の実装って、 参考にいただいた(https://codezine.jp/article/detail/1289)と同じような実装ですか? その場合だと、MyAppSqlConfigのクラス変数(static)から増やす必要がありますが、認識はあっているでしょうか。 あと処理の仕方ですが、 endTransactionをコールするとトランザクションが終わるのでカーソルが消えているのではないかと推測します。 なので、「1つのトランザクションで処理」のupdateメソッドの中だけ特異なsqlMapClientで、 start-commit-endという形を取ることになると思うのですが、 イメージはこんな感じです。 sqlMapClientCursor.start loop-start sqlMapClientUpdate.start sqlMapClientUpdate.commit sqlMapClientUpdate.end loop-end sqlMapClientCursor.end ただカーソル処理中にカーソル内のデータを別トランザクションが更新することの影響が分かりません。 あとはcursorではなくてlimit offsetに代替えは難しいでしょうか。 (性能的なモノが改善されるわけではありませんが、、、)
tomy0732

2018/02/16 12:57

何度も回答ありがとうございます。 > MyAppSqlConfigのクラス変数(static)から増やす必要 はい、おっしゃるような形で作りましたが、変わりありませんでした。 > カーソルが消えているのではないか トランザクションを閉じてもカーソルを使い回せる(WITH HOLD)設定があると質問欄にも書いたのですが、やっぱり使えないのですかね...。 > イメージはこんな感じです ここに質問する前にも試していたのですが、上記のアドバイスを参考に再度やってみても、やはり変わらずカーソルが消えてしまいました。 > limit offsetに代替え このカーソル処理をする前はlimit offsetを使用していました。 レコード件数があがると性能が下がっていくという問題から今回カーソル処理の実装を進めていました。 何度もお返事頂きありがとうございます。
sazi

2018/02/16 15:07 編集

事象から逆に考えると、トランザクションが別セッションで行われているという可能性はないでしょうか? 提示のコードではコネクションを開始している部分の提示はありませんが、問題無いのでしょうか? ただ、その部分のコードを提示されても、javaやiBATISには詳しくないので、回答はできませんが・・・
tomy0732

2018/02/21 08:50

回答が遅れてしまい、申し訳ありません。 セッションは同一だったのですが、tomcatのほうでDBに接続用のトランザクション(コネクション)をプールしており、それが原因なのではないかと。以下のような流れになっていると思っています。 トランザクション1を開始 カーソル宣言する トランザクション1を終了 トランザクションをプールしているため同じトランザクション1で開始 カーソルを使ってレコード情報を取得 トランザクションを終了 トランザクション1を開始 UPDATE処理 トランザクション1を終了 同じトランザクションを使用していることから、宣言したカーソルが使えないのではないかと考えています。また、今回の環境ではトランザクションを明示的に分けることができない為、諦めるしかないという結果に落ち着きました。 szk.様、sazi様 ご回答ありがとうございました。
sazi

2018/02/21 14:37

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

2018/02/22 09:02

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

2018/02/23 00:55

> updateした内容は都度DBに反映されていたため という回答から、都度COMMITされているものと推測されるのですが、、、 性能的なことだけ考えると、全く別の方法でcursorではなく一時テーブルを使う方法など試されてはいかがでしょうか。 cursorの部分を一時テーブルにすることで、母数が減るはずので、limit offsetでも十分性能が出ると思います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問