Clojurians Log v2

For those of you that use stored procedures, I would dearly love some simple examples that generate multiple results and also accept both in, inout, and out parameters. I would like to add these to the test suite so I can verify that all DBs that support SP at all can have them invoked.

This is a prelude to a) supporting multiple result sets (not possible today) and b) supporting stored procedure calling in a simpler manner somehow (but I don't know enough about SPs to be able to design this myself).

You can add notes to https://github.com/seancorfield/next-jdbc/issues/116 or post here or DM me

hi! we are migrating an app from jdbc. to next.jdbc and so far so good, only problem now is durings tests. we used to have a “wrapping txns” on a fixture that woould do something like

(defn with-rollback
  [f]
  (jdbc/with-db-transaction [tx db/*db*]
    (jdbc/db-set-rollback-only! tx)
       (binding [db/*db* tx]
          (f))))

(defn with-rollback
  [f]
  (jdbc/with-transaction [tx db/*db* {:rollback-only true}]
       (binding [db/*db* tx]
          (f))))

however tests are now not rolled back.

we do have inside our code other txns, that use the binded var, we were expeting the rollbacak flag would move down on those txns.

are we missing something obvious?

debugging next.jdbc.transaction/transact* i noticed the opts are lost when inside the nested txns

@jmayaalv What is db/*db*? A db-spec? A datasource? A connection?

on prod is a datasource (a hikari pool), but the idea with the biding on the test fixture was to use it as the connection already marked as rollback.

You can't run multiple transactions on a single connection -- transaction is destructive on a connection. Transactions do not nest (in general) so you can't rely on wrapping code that uses transactions inside another transaction and expect the inner transactions to rollback (since they have their own commit phase).

i understand this. we don’t want to run multiple txns

on prod we have a pool, then we create a txn that runs some business logic.

on test what we were doing with the bind, was to replace the pool with a rollbacked transaction, so the inner macro would continued with an already rollback txn.

we have (jdbc/with-transaction) on our service layers and we are running some integrations tests, dont’ think can’t move all the transaction limits to a layer above.

so i believe next.jdbc creates a new transaction on every jdbc/with-transaction and clojure.jdbc was not creating a new one but using the outer transaction.

thank you @seancorfield we will see how to find a way around it 🙂

@jmayaalv clojure.java.jdbc had a very complicated piece of machinery for maintaining a "stack" of transaction contexts and effectively merging nested contexts into the outer context if needed. That was necessary partly because every SQL operation in c.j.j was wrapped in a transaction and you had to explicitly opt-out of that. next.jdbc instead relies on auto-commit on connections and so all transactions used are explicit and under user control. That means it's a lot easier to use savepoints with next.jdbc than with c.j.j and that you could safely wrap "regular" (non-explicitly-transacting) in an outer transaction and directly control the behavior from the outside. In addition, I used to get bug reports against c.j.j because of its transaction-handling from users of databases that actually did allow nested transactions and there was no way to workaround it -- you had to switch to interop and it got messy. next.jdbc avoids that completely, because a transaction does exactly what you expect for your database.

As with everything else, there are trade offs with both approaches, but I'm more comfortable with how next.jdbc deals with these trade offs overall.

totally agree, we are happy to pay for the trade offs, so far experience for us has been very possitive :)

we were expecting some issues when upgrading anyway. i am sure app will be better after 😉

A recent change has been to make transactions executed directly on connections lock on the connection object to improve reliability with multithreading (which I think should be avoided: connections are mutable and are not safe to be operated on in multiple threads in the first place!). That would prevent double-nesting transactions (it would deadlock) while still allowing single-nesting -- although with the behavior you noted above: nested commits affect the outer context. Your comment about nested transactions "losing" their options makes me wonder whether I could improve how (single) nested transactions work via a dynamic (thread local) var...

Because next.jdbc traffics in Java objects directly, there's nothing to attach options to -- so options literally cannot cascade into nested operations.

But having the ability to ignore nested transactions so your testing rollback fixture would work... that has value... and also being able to use the same machinery to detect and disallow nested transactions would also probably add value.

(at the cost of making transactions dependent on a global dynamic var)

That would be awesome. i can imagine is a common use case.

one of the things we wanted to try was to add some meta-data on the with-transaction macro, so that we could reuse the metadata from the prev object, not sure if it will work. 😄

oh right, it can’t be done because they are java objects

Yeah, that's why I'm looking at some sort of middleware/wrapper functionality. There's an issue open about that and some code and tests (in the test tree, not src).

a middleware would make a lot of sense. will check it out! thanks a lot