CODEWITHSUNDEEP
javajdbcclojuretransactionsdatabase-connection
Chet
Chet

Reputation: 22065

Clojure JDBC Different DB Connections in One Transaction

I'm using Clojure with JDBC to select and insert records from and into two different databases (specifically MySQL and Vertica) on response to an event. I'd like for all those to take place within a single transaction that could be rolled back if anything went wrong with either command.

(defn handle-request
  [request]
  (jdbc/with-db-transaction [mysql-conn config/mysql-db-spec]
    (jdbc/with-db-transaction [vertica-conn config/vertica-db-spec]
      (let [record (query-some-data mysql-conn request)]
        (update-some-data! mysql-conn record)
        (insert-some-vertica-data! vertica-conn record)))))

I worry that this risks the Vertica transaction succeeding but not the MySQL one. What would be the most idiomatic Clojure way to handle this operation?

Upvotes: 4

Views: 663

Answers (1)

Alan Thompson
Alan Thompson

Reputation: 29958

A transaction is a behavior provided by the database, not by the programming language you use to send/retrieve data from the DB. So, the answer is that you can't have a "real" transaction since there is no way to coordinate the 2 databases.

  1. I think the simplest approach here is to do the mysql tx first, since the update is the only part that looks like it might fail. Then, if successful, do the insert into vertica since inserting new data is (or should be) pretty fail-safe. So it looks like (pseudocode):
(perform mysql tx) 
if (successful) then 
  (do vertica tx)
  1. A more complicated solution is to attempt to do the transaction/rollback behavior yourself, like this:
(do tx in DB 1)
(try
  (do tx is DB 2)
  (catch Exception ex
    (rollback tx in DB 1)))

However, this is sub-optimal because you are now writing a homegrown DB plugin yourself instead of taking advantage of the DB's build-in stability & reliability (i.e. what if there is a problem before your catch clause finishes running?).

  1. If you were use PostgreSQL, for example, you may be able to find a Foreign Data Wrapper (FDW) module that can handle solution #2 for you. For example, postgres is able to treat an Oracle DB as a "foreign" table and provide all of the normal DB guarentees. It is just a little slower than a normal table.

Upvotes: 4

Related Questions