CODEWITHSUNDEEP
postgresqljdbcclojure
Michiel Borkent
Michiel Borkent

Reputation: 34820

Nested transactions in clojure.java.jdbc

We have a table m1 with millions of records. We would like to produce a table m2 with calculations on every record in m1. We're currently running it as follows:

(jdbc/with-db-transaction [tx connection]
  (jdbc/query tx
    [(jdbc/prepare-statement (:connection tx)
                              "select * from m1"
                              {:fetch-size 1000})]
     {:result-set-fn (process! [tx result-set] ...)}))

where process! is

(defn process! [tx result-set]
  (jdbc/with-db-transaction [tx tx]
    (jdbc/insert-multi! tx :m2 [:m2_column]
      (mapv (fn [r] [(calculate r)]) 
        result-set))))

The select query is using cursors and is consumed lazily. See: clojure.java.jdbc lazy query. That's the reason it is wrapped inside the outer transaction.

Questions:

Upvotes: 6

Views: 1641

Answers (1)

OlegTheCat
OlegTheCat

Reputation: 4513

nested transactions are absorbed by the outer one

This is totally true. You may check out the sources: here's branch that is executed when we are already in transaction; as you can see, func (function that represents body of with-transaction macro) is just called with no additional setup.

is the solution to use a different connection to the database?

With clojure.java.jdbc this seems to be the only option. Just pass your pool instead of tx to process! function. The issue is that those transactions are not nested anymore, so if inner transaction commits and outer fails due to some reason, the inner transaction won't rollback. You may achieve "nested transactions behaviour" with raw JDBC and savepoints.

Upvotes: 6

Related Questions