[Checkins] SVN: relstorage/trunk/ Apologies to Oracle users: no more read only mode. It seems to be buggy.

[Shane Hathaway]

Log message for revision 116481:
  Apologies to Oracle users: no more read only mode.  It seems to be buggy.
  

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

-=-
Modified: relstorage/trunk/CHANGES.txt
===================================================================
--- relstorage/trunk/CHANGES.txt	2010-09-17 07:42:23 UTC (rev 116480)
+++ relstorage/trunk/CHANGES.txt	2010-09-17 08:32:20 UTC (rev 116481)
@@ -2,6 +2,12 @@
 Next Release
 ------------
 
+- Worked around an Oracle RAC bug: apparently, in a RAC environment,
+  the read-only transaction mode does not isolate transactions in the
+  manner specified by the documentation, so Oracle users now have to
+  use serializable isolation like everyone else. It's slower but more
+  reliable.
+
 - Use the client time instead of the database server time as a factor
   in the transaction ID.  RelStorage was using the database server time
   to reduce the need for synchronized clocks, but in practice, that

Modified: relstorage/trunk/relstorage/adapters/oracle.py
===================================================================
--- relstorage/trunk/relstorage/adapters/oracle.py	2010-09-17 07:42:23 UTC (rev 116480)
+++ relstorage/trunk/relstorage/adapters/oracle.py	2010-09-17 08:32:20 UTC (rev 116481)
@@ -235,8 +235,36 @@
 class CXOracleConnectionManager(AbstractConnectionManager):
 
     isolation_read_committed = "ISOLATION LEVEL READ COMMITTED"
-    isolation_read_only = "READ ONLY"
 
+    # Note: the READ ONLY mode should be sufficient according to the
+    # Oracle documentation, which says: "All subsequent queries in that
+    # transaction see only changes that were committed before the
+    # transaction began."
+    #
+    # See: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200
+    #   /statements_10005.htm
+    #
+    # This would be great for performance if we could rely on it.
+    # It's like serializable isolation but with less locking.
+    #
+    # However, in testing an Oracle 10g RAC environment with
+    # RelStorage, Oracle frequently leaked subsequently committed
+    # transactions into a read only transaction, suggesting that read
+    # only in RAC actually has read committed isolation rather than
+    # serializable isolation. Switching to serializable mode solved the
+    # problem. Using a DSN that specifies a particular RAC node did
+    # *not* solve the problem. It's likely that this is a bug in RAC,
+    # but let's be on the safe side and have all Oracle users apply
+    # serializable mode instead of read only mode, since serializable
+    # is more explicit.
+    #
+    # If anyone wants to try read only mode anyway, change the
+    # class variable below.
+    #
+    #isolation_read_only = "READ ONLY"
+
+    isolation_read_only = "ISOLATION LEVEL SERIALIZABLE"
+
     disconnected_exceptions = disconnected_exceptions
     close_exceptions = close_exceptions
 
@@ -285,7 +313,7 @@
         """Reinitialize a connection for loading objects."""
         self.check_replica(conn, cursor)
         conn.rollback()
-        cursor.execute("SET TRANSACTION READ ONLY")
+        cursor.execute("SET TRANSACTION %s" % self.isolation_read_only)
 
     def check_replica(self, conn, cursor):
         """Raise an exception if the connection belongs to an old replica"""