[Checkins] SVN: relstorage/trunk/ When deleting transactions, do so in batches of 1000 and release the lock in-between.

[Martijn Pieters]

Log message for revision 121783:
  When deleting transactions, do so in batches of 1000 and release the lock in-between.
  
  This way we don't hold on to the commit lock for any longer than we have to and do not block normal operations of the Zope RelStorage cluster.

Changed:
  U   relstorage/trunk/CHANGES.txt
  U   relstorage/trunk/relstorage/adapters/packundo.py

-=-
Modified: relstorage/trunk/CHANGES.txt
===================================================================
--- relstorage/trunk/CHANGES.txt	2011-05-23 13:37:32 UTC (rev 121782)
+++ relstorage/trunk/CHANGES.txt	2011-05-23 19:50:03 UTC (rev 121783)
@@ -4,6 +4,10 @@
 - Fixed another minor compatibility issue with PostgreSQL 9.0.  Packing
   raised an error when the client used old an version of libpq.
 
+- Delete empty transactions in batches of 1000 rows instead of all in one
+  go, to prevent holding the transaction lock for longer than absolutely
+  necessary.
+
 1.5.0b2 (2011-03-02)
 --------------------
 

Modified: relstorage/trunk/relstorage/adapters/packundo.py
===================================================================
--- relstorage/trunk/relstorage/adapters/packundo.py	2011-05-23 13:37:32 UTC (rev 121782)
+++ relstorage/trunk/relstorage/adapters/packundo.py	2011-05-23 19:50:03 UTC (rev 121783)
@@ -223,6 +223,18 @@
             )
         """
 
+    # See http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
+    # for = any(array(...)) rationale.
+    _script_delete_empty_transactions_batch = """
+        DELETE FROM transaction
+        WHERE tid = any(array(
+            SELECT tid FROM transaction
+            WHERE packed = %(TRUE)s
+              AND empty = %(TRUE)s
+            LIMIT 1000
+        ))
+        """
+
     def verify_undoable(self, cursor, undo_tid):
         """Raise UndoError if it is not safe to undo the specified txn."""
         stmt = """
@@ -690,7 +702,7 @@
                         packed_func(oid, tid)
                 packed_list = None
 
-                self._pack_cleanup(conn, cursor)
+                self._pack_cleanup(conn, cursor, sleep)
 
             except:
                 log.exception("pack: failed")
@@ -760,7 +772,7 @@
             tid, state, removed_objects, removed_states)
 
 
-    def _pack_cleanup(self, conn, cursor):
+    def _pack_cleanup(self, conn, cursor, sleep=None):
         """Remove unneeded table rows after packing"""
         # commit the work done so far
         conn.commit()
@@ -774,19 +786,20 @@
         self.runner.run_script(cursor, stmt)
 
         # We need a commit lock when touching the transaction table though.
-        self.locker.hold_commit_lock(cursor)
+        # We'll do it in batches of 1000 rows.
         log.debug("pack: removing empty packed transactions")
-        stmt = """
-        DELETE FROM transaction
-        WHERE packed = %(TRUE)s
-            AND empty = %(TRUE)s
-        """
-        self.runner.run_script_stmt(cursor, stmt)
+        while True:
+            self._pause_pack_until_lock(cursor, sleep)
+            stmt = self._script_delete_empty_transactions_batch
+            self.runner.run_script_stmt(cursor, stmt)
+            deleted = cursor.rowcount
+            conn.commit()
+            self.locker.release_commit_lock(cursor)
+            if deleted < 1000:
+                # Last set of deletions complete
+                break
 
         # perform cleanup that does not require the commit lock
-        conn.commit()
-        self.locker.release_commit_lock(cursor)
-
         log.debug("pack: clearing temporary pack state")
         for _table in ('pack_object', 'pack_state', 'pack_state_tid'):
             stmt = '%(TRUNCATE)s ' + _table
@@ -842,7 +855,14 @@
         CREATE UNIQUE INDEX temp_undo_zoid ON temp_undo (zoid)
         """
 
+    _script_delete_empty_transactions_batch = """
+        DELETE FROM transaction
+        WHERE packed = %(TRUE)s
+          AND empty = %(TRUE)s
+        LIMIT 1000
+        """
 
+
 class OracleHistoryPreservingPackUndo(HistoryPreservingPackUndo):
 
     _script_choose_pack_transaction = """
@@ -868,7 +888,14 @@
         WHERE tid = %(tid)s
         """
 
+    _script_delete_empty_transactions_batch = """
+        DELETE FROM transaction
+        WHERE packed = %(TRUE)s
+          AND empty = %(TRUE)s
+          AND rownum <= 1000
+        """
 
+
 class HistoryFreePackUndo(PackUndo):
     implements(IPackUndo)