トップに関する質問 [PostgreSQL]カーソルがトランザクションを超えて使用できない

編集履歴

質問編集履歴

ソースの追記

2018/02/15 09:44

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -44,6 +44,8 @@
 簡略化してるので抜けがあるかもしれません。
+複数のトランザクションで処理
 ```Java
 public class RecordEdit() {
@@ -128,6 +130,8 @@
         try {
+            // トランザクションの開始
             sqlMapClient.startTransaction();
@@ -180,6 +184,8 @@
         try {
+            // トランザクションの開始
             sqlMapClient.startTransaction();
@@ -252,6 +258,180 @@
+**追記**
+1つのトランザクションで処理
+```Java
+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();
+        }
+    }
+}
+```
 ### 試したこと
 トランザクションを別々にせずに、同じトランザクション内で処理を行えば、カーソルでの処理はできました。

ソースコードの修正

2018/02/15 09:44

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -50,7 +50,13 @@
     // DBにアクセスするためのもの
-    final SqlMapClient sqlMapClient;
+    final SqlMapClient sqlMapClient = baseDao.getSqlMapClient();
+    // フェッチしてくる数を設定(1000件)
+    final int RECS_AT_ONCE = 1000;
     // レコードIDを格納するリスト
@@ -66,8 +72,6 @@
     public RecordEdit() {
-        sqlMapClient = baseDao.getSqlMapClient();
         recordIdList = new LinkedList<>();
         cursorName = "cursorTest";
@@ -76,7 +80,7 @@
-    /** 宣言されたカーソルを使って1000件ずつフェッチしてくる **/
+    /** 宣言されたカーソルを使いフェッチしてくる **/
     private Linkedlist<Long> fetchRecordIds() {
@@ -88,9 +92,7 @@
-            // 1000件ずつ取得
-            String query = "FORWARD 1000 FROM " + cursorName;
+            String query = "FORWARD " + RECS_AT_ONCE " FROM " + cursorName;
             List<Map<String, String>> recordList = null;
@@ -122,7 +124,7 @@
     /** カーソルを宣言する **/
-    private String declareCursor() {
+    private void declareCursor() {
         try {
@@ -142,7 +144,7 @@
         } finally {
-            // ここで宣言したカーソルが消える
+            // ↓ここで宣言したカーソルが消える
             sqlMapClient.endTransaction();
@@ -154,9 +156,9 @@
     /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/
-    private Long next() {
+    private Long getNextId() {
-        // リストが空なら再びフェッチしてくる
+        // リストが空ならフェッチしてくる
         if (recordIdList.isEmpty()) {
@@ -222,7 +224,7 @@
             // idを取得
-            long recordId = next();
+            long recordId = getNextId();
             // 次のレコードが無いならループを抜ける

修正

2018/02/15 02:20

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -50,7 +50,7 @@
     // DBにアクセスするためのもの
-    final SqlMapClient sqlMapClientent;
+    final SqlMapClient sqlMapClient;
     // レコードIDを格納するリスト
@@ -66,7 +66,7 @@
     public RecordEdit() {
-        sqlMapClientent = baseDao.getSqlMapClient();
+        sqlMapClient = baseDao.getSqlMapClient();
         recordIdList = new LinkedList<>();

修正

2018/02/15 02:03

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -200,7 +200,7 @@
     /** カーソルのクローズ処理 **/
-    private void cursorClose() {
+    private void closeCursor() {
         // カーソルを閉じる(CLOSE)
@@ -240,7 +240,7 @@
         // 無事ループを抜けたならカーソルを閉じる
-        cursorClose();
+        closeCursor();
     }

修正

2018/02/15 02:02

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -120,7 +120,7 @@
-    /** カーソルを宣言し、カーソル名を返す **/
+    /** カーソルを宣言する **/
     private String declareCursor() {

ソースコードの修正

2018/02/15 02:00

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -70,13 +70,15 @@
         recordIdList = new LinkedList<>();
+        cursorName = "cursorTest";
     }
     /** 宣言されたカーソルを使って1000件ずつフェッチしてくる **/
-    private Linkedlist<Long> fetchRecordIds(String cursorName) {
+    private Linkedlist<Long> fetchRecordIds() {
         try {
@@ -120,7 +122,7 @@
     /** カーソルを宣言し、カーソル名を返す **/
-    private String getCursorName() {
+    private String declareCursor() {
         try {
@@ -128,8 +130,6 @@
-            cursorName = "cursorTest";
             String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
             // カーソルの宣言(DECLARE)
@@ -140,8 +140,6 @@
             sqlMapClient.commitTransaction();
-            return cursorName;
         } finally {
             // ここで宣言したカーソルが消える
@@ -218,7 +216,7 @@
         // カーソルを宣言
-        cursorName = getCursorName();
+        declareCursor();
         while (true) {

いろいろ修正

2018/02/15 01:59

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -70,8 +70,6 @@
         recordIdList = new LinkedList<>();
-        cursorName = "cursorTest";
     }
@@ -90,7 +88,7 @@
             // 1000件ずつ取得
-            String newSqlStr = "FORWARD 1000 FROM " + cursorName;
+            String query = "FORWARD 1000 FROM " + cursorName;
             List<Map<String, String>> recordList = null;
@@ -130,6 +128,8 @@
+            cursorName = "cursorTest";
             String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
             // カーソルの宣言(DECLARE)
@@ -254,9 +254,11 @@
 ### 試したこと
-同じトランザクション内で処理を行えば、うまくできました。
+トランザクションを別々にせずに、同じトランザクション内で処理を行えば、カーソルでの処理はできました。
-しかし、件数が多すぎると、UPDATE処理にすごく時間がかかり、あまりよろしくありません。(テーブルの列が10個で1レコード40㎳ぐらい、1万レコードだと400sかかる)
+しかし、件数が多すぎるとUPDATE処理にすごく時間がかかり、あまりよろしくありません(テーブルの列が10個で1レコード40㎳ぐらい、1万レコードだと400sかかる)。
+UPDATE処理は別のトランザクションで処理した方が速いというのはこれまでの試験でわかっており(1レコード15msくらい)、どうしても別々のトランザクションで処理を行いたいと考えています。

ソースコードの更新

2018/02/15 01:53

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -82,6 +82,8 @@
         try {
+            // トランザクションの開始
             sqlMapClient.startTransaction();
@@ -100,12 +102,16 @@
+            // 処理が成功したならコミット
             sqlMapClient.commitTransaction();
             return resIdList;
         } finally {
+            // 成功/失敗に関わらず、トランザクションの終了
             sqlMapClient.endTransaction();
         }
@@ -174,8 +180,6 @@
         try {
-            // トランザクションの開始
             sqlMapClient.startTransaction();
@@ -184,14 +188,10 @@
-            // 処理が成功したならコミット
             sqlMapClient.commitTransaction();
         } finally {
-            // 成功/失敗に関わらず、トランザクションの終了
             sqlMapClient.endTransaction();
         }

ソースコードの更新

2018/02/15 00:59

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -46,146 +46,208 @@
 ```Java
-// メイン処理
-public void recordEdit() {
-  // カーソルを宣言
-  String cursorName = getCursorName();
-  while (true) {
-    long recordId = next(cursorName);
-    // 次のレコードが無いならループを抜ける
-    if (recordId == null) {
-      break;
-    }
-    // UPDATE処理を行う
-    update(recordId);
-  }
+public class RecordEdit() {
+    // DBにアクセスするためのもの
+    final SqlMapClient sqlMapClientent;
+    // レコードIDを格納するリスト
+    Linkedlist<Long> recordIdList;
+    // カーソル名(本来は動的に決める)
+    String cursorName;
+    // コンストラクタ
+    public RecordEdit() {
+        sqlMapClientent = baseDao.getSqlMapClient();
+        recordIdList = new LinkedList<>();
+        cursorName = "cursorTest";
+    }
+    /** 宣言されたカーソルを使って1000件ずつフェッチしてくる **/
+    private Linkedlist<Long> fetchRecordIds(String cursorName) {
+        try {
+            sqlMapClient.startTransaction();
+            // 1000件ずつ取得
+            String newSqlStr = "FORWARD 1000 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 String getCursorName() {
+        try {
+            sqlMapClient.startTransaction();
+            String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
+            // カーソルの宣言(DECLARE)
+            sqlMapClient.queryForList("Record.declareCursor", query);
+            sqlMapClient.commitTransaction();
+            return cursorName;
+        } finally {
+            // ここで宣言したカーソルが消える
+            sqlMapClient.endTransaction();
+        }
+    }
+    /** カーソルで取得したレコードIDリストからIDを一個ずつとりだす **/
+    private Long next() {
+        // リストが空なら再びフェッチしてくる
+        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 cursorClose() {
+        // カーソルを閉じる(CLOSE)
+        sqlMapClient.queryForList("Record.closeCursor", cursorName);
+    }
+    /** メイン処理 **/
+    public void execute() {
+        // カーソルを宣言
+        cursorName = getCursorName();
+        while (true) {
+            // idを取得
+            long recordId = next();
+            // 次のレコードが無いならループを抜ける
+            if (recordId == null) {
+                break;
+            }
+            // UPDATE処理を行う
+            update(recordId);
+        }
+        // 無事ループを抜けたならカーソルを閉じる
+        cursorClose();
+    }
 }
-// カーソルで取得したレコードIDリストからIDを一個ずつとりだす
-Linkedlist<Long> recordIds = new LinkedList<>();
-private Long next(String cursorName) {
-  if (recordIds.isEmpty()) {
-    recordIds = fetchRecordIds(cursorName);
-  }
-  retrun recordIds.isEmpty() ? null : recordIds.removeFirst();
-}
-// UPDATE処理を行う
-private void update(long id) {
-  try {
-    sqlMapClient.startTransaction(); // トランザクションの開始
-    // 指定されたIDに対してUPDATE処理を行っている //
-    sqlMapClient.commitTransaction(); // 処理が成功したならコミット
-  } finally {
-    sqlMapClient.endTransaction(); // 成功/失敗に関わらず、トランザクションの終了
-  }
-}
-// カーソルを宣言し、カーソル名を返す
-private String getCursorName() {
-  try {
-    sqlMapClient.startTransaction();
-    String cursorName = "cursorTest"; // カーソル名
-    String query = cursorName + " CURSOR WITH HOLD FOR SELECT * FROM testTable";
-    sqlMapClient.queryForList("Record.declareCursor", query); // カーソルの宣言
-    sqlMapClient.commitTransaction();
-    return cursorName;
-  } finally {
-    sqlMapClient.endTransaction();
-  }
-}
-// 宣言されたカーソルを使って1000件ずつフェッチしてくる
-private Linkedlist<Long> fetchRecordIds(String cursorName) {
-  try {
-    sqlMapClient.startTransaction(); // ここでカーソルが消えており処理ができない。
-    String newSqlStr = "FORWARD 1000 FROM " + cursorName; // 1000件ずつ取得
-    List<Map<String, String>> recordList = sqlMapClient.queryForList("Record.fetch", query); // レコード情報の取得
-    List<Long> resIdList = //取得したレコード情報からIDだけ抜き取る処理
-    sqlMapClient.commitTransaction();
-    return resIdList;
-  } finally {
-    sqlMapClient.endTransaction();
-  }
-}
 ```

2018/02/15 00:57

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -10,21 +10,21 @@
 ・実現したいこと
-PostgreSQLのカーソルを使用して、大量にあるレコードを少しずつ取得しながらUPDATEをしようと考えた。
+PostgreSQLのカーソルを使用して、大量にあるレコードを少しずつ取得しながらUPDATEをしたい。
-これまでの実装から、カーソル名を取得するトランザクションと、実際にUPDATE処理を行うトランザクションとで分けて実装する必要があった。
+既存実装から、カーソルを宣言するトランザクションと、実際にUPDATE処理を行うトランザクションとで分けて実装する必要があった。
-そこで[ここ](https://www.postgresql.jp/document/9.6/html/sql-declare.html)に書いてあるとおりにカーソルを宣言する際に`CURSOR WITH HOLD FOR SELECT`とし、宣言したトランザクション外でも使えるようにした。
+そこで[ここ](https://www.postgresql.jp/document/9.6/html/sql-declare.html)に書いてあるとおりにカーソルを宣言する際に`CURSOR WITH HOLD FOR SELECT`とし、宣言したトランザクション外でもカーソルが使えるようにしました。
-別のトランザクション内でカーソルを使おうとしたが、宣言したカーソルは存在しなくなっており、UPDATE処理がうまくできない。
+しかし、別のトランザクションでカーソルを使おうとしても、宣言したカーソルは存在しないとエラーが発生し、UPDATE処理ができなかった。
-どうすればカーソルをほかのトランザクションでも使えるようになるか、他に何か気をつけなればならないことがあるのであればご教授お願いいたします。
+どうすればカーソルをほかのトランザクションでも使えるようになるのか、また、他に何か気をつけなればならないことがあるのであればご教授お願いいたします。
@@ -80,7 +80,7 @@
 Linkedlist<Long> recordIds = new LinkedList<>();
-private Long next(cursorName) {
+private Long next(String cursorName) {
   if (recordIds.isEmpty()) {
@@ -194,7 +194,7 @@
 同じトランザクション内で処理を行えば、うまくできました。
-しかし、件数が多すぎると、UPDATE処理にすごく時間がかかり、あまりよろしくありません。(列が10個のレコードで40㎳ぐらい)
+しかし、件数が多すぎると、UPDATE処理にすごく時間がかかり、あまりよろしくありません。(テーブルの列が10個で1レコード40㎳ぐらい、1万レコードだと400sかかる)

2018/02/14 15:48

投稿

tomy0732

スコア7

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -24,7 +24,7 @@
-他に何か気をつけなればならないことがあるとかそういうことがあればご教授お願いいたします。
+どうすればカーソルをほかのトランザクションでも使えるようになるか、他に何か気をつけなればならないことがあるのであればご教授お願いいたします。
@@ -42,6 +42,8 @@
 ### 該当のソースコード
+簡略化してるので抜けがあるかもしれません。
 ```Java
 // メイン処理