java.jdbc

Add support for read-only transactions

Details

  • Type: Enhancement Enhancement
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None
  • Environment:
    [org.clojure/java.jdbc "0.3.3"]

Description

Currently it is possible to set the transaction isolation only (db-transaction*). In our case we use SERIALIZABLE isolation, so it makes sense to mark read-only transactions using Connection/setReadOnly.

Thanks!

Activity

Hide
Sean Corfield added a comment -

Can you explain a bit more about how you'd expect this to work? Perhaps show me the sort of code / API you'd want to write / use for this?

Show
Sean Corfield added a comment - Can you explain a bit more about how you'd expect this to work? Perhaps show me the sort of code / API you'd want to write / use for this?
Hide
Gerrit Hentschel added a comment -

Sure, actually I created a patch that describes how I would like to use it, but when trying to attach the file I get a 500-Internal Server Error.

I hope it is okay to just paste it here:

From 4a4ea3d478f7ddb717764499b7d7c3e5efb5caee Mon Sep 17 00:00:00 2001
From: Gerrit Hentschel <gerrit@doo.net>
Date: Thu, 27 Mar 2014 08:57:54 +0100
Subject: [PATCH] add support for read-only transactions

---
 src/main/clojure/clojure/java/jdbc.clj | 21 ++++++++++++++-------
 1 file changed, 14 insertions(+), 7 deletions(-)

diff --git a/src/main/clojure/clojure/java/jdbc.clj b/src/main/clojure/clojure/java/jdbc.clj
index 01c4f21..47a6d74 100644
--- a/src/main/clojure/clojure/java/jdbc.clj
+++ b/src/main/clojure/clojure/java/jdbc.clj
@@ -559,16 +559,20 @@ compatibility but it will be removed before a 1.0.0 release." }
   The isolation option may be :none, :read-committed, :read-uncommitted,
   :repeatable-read, or :serializable. Note that not all databases support
   all of those isolation levels, and may either throw an exception or
-  substitute another isolation level."
-  [db func & {:keys [isolation]}]
+  substitute another isolation level.
+  The read-only? option puts the transaction in read-only mode."
+  [db func & {:keys [isolation read-only?]}]
   (if (zero? (get-level db))
     (if-let [^java.sql.Connection con (db-find-connection db)]
       (let [nested-db (inc-level db)
             auto-commit (.getAutoCommit con)
-            old-isolation (.getTransactionIsolation con)]
+            old-isolation (.getTransactionIsolation con)
+            old-read-only? (.isReadOnly con)]
         (io!
          (when isolation
            (.setTransactionIsolation con (isolation isolation-levels)))
+         (when read-only?
+           (.setReadOnly con true))
          (.setAutoCommit con false)
          (try
            (let [result (func nested-db)]
@@ -583,9 +587,11 @@ compatibility but it will be removed before a 1.0.0 release." }
              (db-unset-rollback-only! nested-db)
              (.setAutoCommit con auto-commit)
              (when isolation
-               (.setTransactionIsolation con old-isolation))))))
+               (.setTransactionIsolation con old-isolation))
+             (when read-only?
+               (.setReadOnly con old-read-only?))))))
       (with-open [^java.sql.Connection con (get-connection db)]
-        (db-transaction* (add-connection db con) func :isolation isolation)))
¯+        (db-transaction* (add-connection db con) func :isolation isolation :read-only? read-only?)))
     (try
       (func (inc-level db))
       (catch Exception e
@@ -595,8 +601,9 @@ compatibility but it will be removed before a 1.0.0 release." }
   "Evaluates body in the context of a transaction on the specified database connection.
   The binding provides the database connection for the transaction and the name to which
   that is bound for evaluation of the body. The binding may also specify the isolation
-  level for the transaction, via the :isolation option.
-  (with-db-transaction [t-con db-spec :isolation level]
+  level for the transaction, via the :isolation option and/or set the transaction to
+  read-only, via the :read-only? option.
+  (with-db-transaction [t-con db-spec :isolation level :read-only? true]
     ... t-con ...)
   See db-transaction* for more details."
   [binding & body]
-- 
1.8.3.4 (Apple Git-47)
Show
Gerrit Hentschel added a comment - Sure, actually I created a patch that describes how I would like to use it, but when trying to attach the file I get a 500-Internal Server Error. I hope it is okay to just paste it here:
From 4a4ea3d478f7ddb717764499b7d7c3e5efb5caee Mon Sep 17 00:00:00 2001
From: Gerrit Hentschel <gerrit@doo.net>
Date: Thu, 27 Mar 2014 08:57:54 +0100
Subject: [PATCH] add support for read-only transactions

---
 src/main/clojure/clojure/java/jdbc.clj | 21 ++++++++++++++-------
 1 file changed, 14 insertions(+), 7 deletions(-)

diff --git a/src/main/clojure/clojure/java/jdbc.clj b/src/main/clojure/clojure/java/jdbc.clj
index 01c4f21..47a6d74 100644
--- a/src/main/clojure/clojure/java/jdbc.clj
+++ b/src/main/clojure/clojure/java/jdbc.clj
@@ -559,16 +559,20 @@ compatibility but it will be removed before a 1.0.0 release." }
   The isolation option may be :none, :read-committed, :read-uncommitted,
   :repeatable-read, or :serializable. Note that not all databases support
   all of those isolation levels, and may either throw an exception or
-  substitute another isolation level."
-  [db func & {:keys [isolation]}]
+  substitute another isolation level.
+  The read-only? option puts the transaction in read-only mode."
+  [db func & {:keys [isolation read-only?]}]
   (if (zero? (get-level db))
     (if-let [^java.sql.Connection con (db-find-connection db)]
       (let [nested-db (inc-level db)
             auto-commit (.getAutoCommit con)
-            old-isolation (.getTransactionIsolation con)]
+            old-isolation (.getTransactionIsolation con)
+            old-read-only? (.isReadOnly con)]
         (io!
          (when isolation
            (.setTransactionIsolation con (isolation isolation-levels)))
+         (when read-only?
+           (.setReadOnly con true))
          (.setAutoCommit con false)
          (try
            (let [result (func nested-db)]
@@ -583,9 +587,11 @@ compatibility but it will be removed before a 1.0.0 release." }
              (db-unset-rollback-only! nested-db)
              (.setAutoCommit con auto-commit)
              (when isolation
-               (.setTransactionIsolation con old-isolation))))))
+               (.setTransactionIsolation con old-isolation))
+             (when read-only?
+               (.setReadOnly con old-read-only?))))))
       (with-open [^java.sql.Connection con (get-connection db)]
-        (db-transaction* (add-connection db con) func :isolation isolation)))
¯+        (db-transaction* (add-connection db con) func :isolation isolation :read-only? read-only?)))
     (try
       (func (inc-level db))
       (catch Exception e
@@ -595,8 +601,9 @@ compatibility but it will be removed before a 1.0.0 release." }
   "Evaluates body in the context of a transaction on the specified database connection.
   The binding provides the database connection for the transaction and the name to which
   that is bound for evaluation of the body. The binding may also specify the isolation
-  level for the transaction, via the :isolation option.
-  (with-db-transaction [t-con db-spec :isolation level]
+  level for the transaction, via the :isolation option and/or set the transaction to
+  read-only, via the :read-only? option.
+  (with-db-transaction [t-con db-spec :isolation level :read-only? true]
     ... t-con ...)
   See db-transaction* for more details."
   [binding & body]
-- 
1.8.3.4 (Apple Git-47)
Hide
Sean Corfield added a comment -

Thanks Gerrit. That seems nice and simple. I can't take the patch exactly as-is, because you do not appear to be a formal contributor yet - http://clojure.org/contributing - but this definitely points me in the right direction to create tests and implement something that should provide the functionality you're after.

Show
Sean Corfield added a comment - Thanks Gerrit. That seems nice and simple. I can't take the patch exactly as-is, because you do not appear to be a formal contributor yet - http://clojure.org/contributing - but this definitely points me in the right direction to create tests and implement something that should provide the functionality you're after.
Hide
Gerrit Hentschel added a comment -

Great! Glad it helped.
I will take care of becoming a formal contributor, but feel free to use that patch as you see fit.
Thanks Sean.

Show
Gerrit Hentschel added a comment - Great! Glad it helped. I will take care of becoming a formal contributor, but feel free to use that patch as you see fit. Thanks Sean.
Hide
Sean Corfield added a comment -

Will be in 0.3.5

Show
Sean Corfield added a comment - Will be in 0.3.5

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: