Clojurians Log v2

Hello. I ran into a situation where my tests were failing because rollback was not happening. Digging into the code, I realized I was nesting transactions which, according to my readings, is not a great idea. The code roughly looks like this:

;; the application code
(defn create-product!
  [db ,,,]
  (jdbc/with-transaction [tx dbs]
   (do-thing-1 tx)
   (do-thing-2 tx)
   (do-thing-3 tx)))

;; the test
(deftest test-product! 
  (testing "create product"
    (jdbc/with-transaction [tx testdbs {:rollback-only true}]
      ;; generate mock data 
      (create-product! tx))))
      ;; assertions

There's a dynamic var you can bind to either disallow or ignore nested TX. In your case you want it to ignore nested TX.

In general, what is the advise on where to use these transactions? I added mine at what I would call the “application” layer, but maybe the transactions are better in general in the handler? Perhaps this is an architecture question though. :thinking_face:

@tkjone I almost never use transactions. I use them only if I have a specific sequence of mutations that I need to either all succeed or all fail. In practice, that is pretty rare.

hmmmm. Okay, I will take note of this. In my use case, it’s being used after a successful sale where a order is generated, cart cleared etc. I wouldn’t want one to succeed while the others fail. would you say this is a valid use case? (apologies for the limited information)

Yeah, e-commerce is sometimes a good case for a transaction. I tend to point folks to https://ieeexplore.ieee.org/document/1407829

Transactions work well "in the small" but they don't scale well (and can't be nested in JDBC)

clojure.java.jdbc slightly ignored nested TX (so your test code would likely have worked with c.j.j) but it also meant that accidental nesting were not detectable.

next.jdbc takes the "do what I say" approach and that's why it specifically documents use named and unnamed save points on connections instead of simply relying on transactions.

And it's also why you can tell it to throw an exception if a nested TX is attempted or to simply ignore nested TX, just like c.j.j silently did.

In general, even in e-commerce, I tend to explicitly control mutations, with try/`catch` and manually undo things if part of a "transaction" fails -- that way I can control all the logging (as in, to the database) separate from the actual e-commerce actions.

It's more work, but it gives you more control, and you can see a "paper trail" in the (logging tables) of what was attempted and what failed.

After all, what happens if you charge the customer and then fail to insert the records in your local DB? Or you've performed some remote actions and then others fail? Or your e-commerce process is a mixture of several remote interactions and several local database ones?

None of those scenarios can be handled just be JDBC transactions.

It's why, at work, we have processes that reconcile remote e-commerce logs (PayPal, Braintree, etc) with what ends up in our DB, so we can detect "orphan" transactions (in the financial sense) caused by partial failures during the overall process.

Just want to say, I used c.j.jdbc for a long time, but started switching to next.jdbc in recent projects, and absolutely loving it. Reducing over plan is super powerful. I haven't dived into the nav/datafy parts yet but very excited about it

(So far the one thing that I haven't liked is the way connection/component works. Having to call the resulting component as a function to get the datasource is a bit awkward, as it means callers need to be aware of the difference between using that or using something that has value semantics. I worked around this with my own version:

(defn new-hikari-connection-pool
  "Returns new hikari connection pool component from db spec."
  [db-spec]
  (let [;; Hikari wants :username key rather than :user
        db-spec (set/rename-keys db-spec {:user :username})]
    (with-meta {}
      {`component/start
       (fn [_]
         (let [pool (connection/->pool HikariDataSource db-spec)]
           (reify
             component/Lifecycle
             (start [this] this)
             (stop [this]
               (.close pool)
               (new-hikari-connection-pool db-spec))

             p/Sourceable
             (get-datasource [_] pool))))})))

At the same time probably this reflects even better on the core design - the fact that central next.jdbc protocols made this so easy

@michael.gaare Glad you're liking it. I would note that a connection pool is a DataSource already so perhaps implementing p/Connectable would be more natural here? The function call idiom is something we've been using extensively at work where Component-compatible things are callable to return the thing they wrap and we really like that.

Having a component that implements p/Sourceable works well in my case because I inject into other service-level components. Those components then use it directly (jdbc/plan datasource ...) or (jdbc/execute-one! datasource ...) or whatever. For that pattern it's nice to be able to just inject in a db-spec map as a datasource for dev/testing, and have that work exactly the same as the connection pool

Fair enough. We use connection pools in dev so the same code works everywhere for us.

I can see the advantages of the unwrap-with-function pattern for components more broadly though. Not everything has a nice set of protocols to hook into

I just announced com.github.seancorfield/next.jdbc {:mvn/version "1.1.643"} (in #announcements) and I wanted to call out the GraalVM compatibility change here in particular: I've copied next.jdbc.prepare/execute-batch! to next.jdbc/execute-batch! and would encourage anyone using execute-batch! to switch over to the next.jdbc version. The original next.jdbc.prepare version ended up depending on next.jdbc.result-set (which already depended on next.jdbc.prepare) and to break that circular dependency, I used requiring-resolve at runtime. Clearly, execute-batch! should never have been added to next.jdbc.prepare in the first place -- a bad decision on my part -- but in order to avoid breaking changes, I have modified next.jdbc.prepare/execute-batch! to rely on a volatile! Var that is set up in next.jdbc.result-set -- this is a workaround suggested by @borkdude as a way to get GraalVM compatibility when you would otherwise have a dynamic runtime dependency. It shouldn't be a breaking change, since you would normally require next.jdbc to call prepare to get a PreparedStatement object which is what execute-batch! requires, and next.jdbc requires next.jdbc.result-set so the new Var should always be set up. However, any code that somehow managed to not cause next.jdbc.result-set to be required and yet was requiring next.jdbc.prepare and calling execute-batch! may now fail. Like I say, I don't think any real world code could call execute-batch! without somehow causing next.jdbc.result-set to be required but I wanted to post this caution, just in case you update to 1.1.643 and your code breaks.

Thanks, Sean! That was a great response and I see the approach you take makes more sense in many ways :man-bowing:

In addition, I'm double-publishing all my libraries to both the existing seancorfield group and the new com.github.seancorfield group so that I can switch them all over to verified group names per the recently-announced Clojars policy. depstar and clj-new already started doing that last week (and the depstar README now explains how I do it). I plan to keep the double-publishing going until download stats on Clojars indicate dropping the non-verified group will cause very little inconvenience -- but, obviously, I'd like folks to switch over to using the new group whenever they next update their dependencies. Any new libraries I publish will be under com.github.seancorfield.

It's tricky because whenever we learn about SQL, RDBMS, and JDBC, it is always emphasized how important transactions are to data integrity -- but the real world has grown a lot more complicated and a simple, in-database transaction just isn't sufficient a lot of the time these days (which in turn also makes our lives so much more complicated too!).

You mentioned “logging tables”. How do you mean?

And in your model, if something fails, and you have the logs, at what point do you course correct?