jOOQ did the-right-thingâą here (as always đ). JDBC Connections cannot be shared among threads. JDBC DataSources can be, if they're implemented accordingly by connection pools.
jOOQ wraps the above. Stay within the thread-bound transaction model exposed by e.g. Spring or JavaEE, and then, there isn't any magic added by jOOQ. On Fri, Jun 5, 2020 at 11:44 AM Thorsten Schöning <[email protected]> wrote: > Hi all, > > I would just like to make sure that I'm not understanding things > wrongly, so here's my current setup: > > Some backend exposes web services and has some layer of management > functions providing configs etc., including a pair of a DSLContext > using a JDBC-connection per request. Each request has its own pair of > DSLContext and JDBC-connection, though. Concepts like transactions are > bound to the scope of one entire request by default. > That's great. > > return DSL.using > > ( > > this.jdbc, SQLDialect.valueOf(dialect), > > new JooqSettingsLoader(this.config).load() > > ); > > Am I correct that this approach is at least safe to use with one > thread processing one request? > I don't know. What are these things? > Am I correct that this approach is still safe when the request spawns > additional threads to query the database and all those threads use the > same DSLContext, same JDBC-connection etc.? > What's "this approach"? ... I mean, don't share the JDBC connection among threads, unless you're pooling the connection. But let the connection pool handle this for you (e.g. HikariCP). Or Spring, etc. > Transactions are NOT managed by those individual threads and > statements won't be reused as well. So it reads like from a JDBC point > of view, reusing the connection is safe: > > https://docs.oracle.com/javadb/10.8.3.0/devguide/cdevconcepts89498.html Are you using Derby? I'm not sure about Derby, but yes, you can re-use a pooled connection *sequentially*, but no *in parallel*. In most cases, two threads should never share a JDBC Connection. > Resuing a DSLContext seems to be sage as well: > > >> Can I use the singleton pattern to use it? > > Yes, that's the recommended approach > > https://github.com/jOOQ/jOOQ/issues/7410#issuecomment-380454662 Yes, because the DSLContext just wraps the Configuration, and the Configuration just wraps your various listeners, settings, SQL dialect, and your JDBC DataSource. If it wraps a JDBC Connection, then it inherits the Connection's thread-unsafety. If it wraps a DataSource, then it inherits the DataSource's thread-safety. > The reason I'm somewhat sure to need to share one connection is that I > have queries in the backend providing results using temporary tables > within the scope of one request. This way different queries within the > same request, though basing data on each other, don't necessarily > re-execute the same queries over and over again. But that only works > with temp-tables using a shared connection. Multiple threads are > useful, because not all queries are based on each other if they are, I > implemented some locking to only create temp-tables ones on my own. > > So is there something fundamentally wrong in my assumptions? Thanks! > Yes, it seems to me there's something fishy here. What you seem to want to do is some asynchronous service that caches data in some particular cases. I've seen this being solved using a reactive model where some Flux (from the reactor library) produced the cached / buffered data for other services. This was quite elegant, but perhaps overkill in your case. Maybe, a simple process / thread that can synchronize on buffered query results would be sufficient here in your case. In any case, don't share the Connection itself among threads, it is not designed for that. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5p_BrFq5U0_rXbkQSH_%2B%2B1TXPZ0T7ARQeTH0sJFEqYig%40mail.gmail.com.
