On Tue, Dec 9, 2014 at 4:25 AM, Garret Wilson <[email protected]> wrote: > On 12/8/2014 11:05 PM, Witold Szczerba wrote: >> >> ... >>> >>> * How does TxManager interact with DSLContext? If TxManager makes a >>> Connection.commit(), how do I know that the DSLContext is using the same >>> connection? How do I know that the DSLContext hasn't pulled another >>> connection out of the data pool? >> >> As I said above, when you use DataSource managed by ULTM to produce >> the DSLContext, ULTM will track connections used by DSLContext. jOOQ >> will be under control of ULTM which means the TxManager will be able >> to commit/rollback on connections the jOOQ were using. The only thing >> to remember is that in managed environments like this (same applies to >> EJB and Spring), you cannot jump from one thread to another while >> preforming the commit-able "unit of work". However, using different >> threads does not make sense when talking to databases through JDBC. > > > So are you saying that each thread only gets a single JDBC connection? So no > matter how many times jOOQ asks for a connection, it gets the same one? But > that doesn't make sense... > > So if I use ULTM to create this "managed data source", then it will ensure > that jOOQ gets the same connection every time it asks for one? But doesn't > that defeat the purpose of connection pooling? I'm afraid I'm completely > lost. > > Let me take this step-by-step: > > 1. I create a data source: > > HikariDataSource datasource = new HikariDataSource(); > > 2. I create ULTM: > > ULTM ultm = new ULTM(pgDataSource); > > 3. I create a managed data source: > > DataSource ultmDataSource = ultm.getManagedDataSource(); > > 4. I create a transaction manager: > > TxManager txManager = ultm.getTxManager(); > > 5. I create a jOOQ configuration: > > org.jooq.Configuration jooqConfiguration = new DefaultConfiguration() > .set(ultmDataSource).set(SQLDialect.POSTGRES).set(new > Settings().withExecuteLogging(true)); > > 6. I create a DSLContext: > > DSLContext jooq=DSL.using(jooqConfiguration); > > 7. OK, now let's do something. Let's say I have a method > CarManager.storeCar(Car car). At the start of the method I want to start a > transaction. Let's assume I get the TxManager from the DI container somehow: > > txManager.begin(); > > I assume that this set connection.setAutoCommit(false) somewhere? But on > what connection? Or does it wait for jOOQ to get a connection?
Yes, it does wait for someone to actually get a connection and checks if autocommit is disabled. > > 8. So let's start storing the car. First I want to delete whatever car that > is already there, which (which cascading deletes will delete all car > properties, all wheels, etc.): > > int deleteCount = > dslTransaction.delete(CAR).where(CAR.ID.equal(car.getID()).execute(); > > Wait, so jOOQ just went and asked for a connection... right? Did it get the > same connection that txManager used above to turn of autocommit? Or did > TxManager wait until just now to turn off autocommit on the new connection > that jOOQ asked for from the pool? Yes, the connection gets pulled from your original data source now. > > So now that this SQL statement is finished and executed, jOOQ will "close" > the connection, which normally would return it to the pool. Does TxManager > step in and keep the connection from going back into the pool? Yes, it does. Connection does not go back to pool yet. > > 9. So now let's start storing the car: > > CarRecord carRecord = dslTransaction.newRecord(CAR); > ... > carRecord.store(); > > Again, where does jOOQ get its connection? Does the managed data source > ensure that it gets the same connection as before? After the transaction is > over, does the manage data source simply pass the connection requests (that > are not part of any transaction) on to the underlying data source? If this > is how it all works, then I suppose it's starting to make sense. As I said in previous posts, jOOQ will get the same connection each time within TxManager transaction. It won't close, commit, rollback or give it back to the pool until TxManager say so. > > 10. What if I were to create a new DSLContext in the middle of the > transaction: > > DSLContext jooq2=DSL.using(jooqConfiguration) > > Does this DSLContext, because it is based on the same ULTM-managed data > source, wind up using the same JDBC connection? Yes. > > 11. Now I want to store the car wheels. I call WheelManager.storeWheel(Wheel > wheel) four times. The wheel manager has no knowledge of transactions or > ULTM or whatever. It just creates yet another DSLContext and starts storing > wheels. If the new DSLContext is based on a jOOQConfiguration that is based > on a ULTM-managed data source, then all the statements use the same JDBC > connection. Do I have this right now? Yes. > > 12. Let's say that inside WhellManager.storeWheel(Wheel wheel), after > storing all the wheels, I use the new DSLContext to check the number of > wheels in the table; we expect four of them. If there are not four of them, > I want to roll back the transaction. You can, but that would be so strange. You do not want to mess with transactions like this, I guess. I mean, I wouldn't, but as I said previously, I do not know the details of your system, so maybe you are right and you should just rollback there. > How do I do that? Can I just do that > straight from jOOQ? Will ULTM know about it, or think it's still in a > transaction? How would I reach out to ULTM? Would I need access to the > TxManager? The only way to rollback is to call txManager.rollback() or to throw an exception within "unit of work". > > I'll stop there for now, because I've made several assumptions on what the > answers are, and I want to see if I'm correct before going further. > > Thanks for bearing with me! > > Garret You are welcome. Also, I would strongly advise reading the source code. This is one of the primary reason of creating the ULTM. It is so small and easy to reason about. Before writing ULTM I was hoping I could use the Spring Transaction module. I have spent like 30 minutes trying to go through that (I was asking myself questions more or less like the you just did) and I gave up. It was _horrible_ experience. Regards, Witold Szczerba > > > >>> * In fact I'm still unsure how DSLContext gets connections. One page >>> >>> (http://www.jooq.org/doc/2.6/manual/sql-building/factory/connection-vs-datasource/) >>> seems to indicate that DSLContext will use a different connection for >>> each >>> query. (Yes, I realize this documentation is really old, but Lukas, the >>> link >>> there for 3.5 is broken. I finally found it; >>> >>> http://www.jooq.org/doc/3.5/manual/sql-building/dsl-context/connection-vs-datasource/ >>> seems to say the same thing.) >>> >> DSLContext asks for connections from some kind of jOOQ's connection >> provider. It's part of API, but there is no need to implement it, >> because there is the DataSource adapter built-in. So, when you create >> DSLContext with DataSource, that DataSource becomes indirectly the >> connection provider. Since connections are tracked by ULTM - TxManager >> can do it's job. You can experiment like this: create TxManager and >> managed DataSource, create DSLContext using that datasource. Modify >> the data using jOOQ then manually fetch the JDBC connection from that >> DataSource and you will get exactly the same one used by jOOQ. You can >> use that connection to modify something else, then txManager rollback >> will revert both changes of what you have done with jOOQ and >> connection itself. >> >>> I realize I have asked some of these questions before, but the answers >>> never >>> seem to tie together and we've been trying to release a new product and >>> I've >>> been doing construction in a new apartment in another country (not in my >>> native language) and my mind is about to become frazzled... >>> >>> Garret >> >> I hope it's a little bit clearer right now. You asked good questions >> here, maybe I should create some kind of wiki based on those... I wish >> I were better organized... >> >> Regards, >> Witold Szczerba >> >>> On 12/8/2014 3:06 PM, Witold Szczerba wrote: >>>> >>>> Hi, >>>> you should have one ULTM (TxManager + managed DataSource) per >>>> application (assuming you have only one database). >>>> >>>> What seems strange to me is why do you use >>>> "DSL.using(getConfiguration())" everywhere instead of creating >>>> DSLContext once and use that everywhere? There is no need to create it >>>> all the time, one instance should be enough. >>>> >>>> Do you use DI? It's brilliant design pattern. The basic idea is to >>>> split object creation code from business logic, so I create TxManager >>>> and DSLContext once and inject it everywhere I need (using Guice or if >>>> you do not want it, by manually creating and configuring objects when >>>> bootstrapping application). It works flawlessly. >>>> >>>> There is no need to pass TxManager everywhere. I would use it on some >>>> external layer, like some gateway, to begin and commit or rollback, >>>> but your case can be a little bit different, so I cannot tell. >>>> Referring to your example with CarManager and WheelManager, it would >>>> look odd to operate with TxManager in one and not use it in another >>>> (but you can do this). However, I would think about getting rid of >>>> transaction management from those manager, because if you start >>>> transaction yet elsewhere, in some ThirdManager, then you cannot call >>>> CarManager (because it handles transactions by itself) but you can >>>> call WheelManager (because it doesn't). >>>> >>>> In my app, there is only one "point of entry", but there might be many >>>> (like JAX-RS endpoints). >>>> >>>> There is no mailing list for ULTM, but fell free to ask question on >>>> issue tracker, because I am not sure if people here are interested :) >>>> >>>> Regards, >>>> Witold Szczerba >>> >>> -- >>> 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 [email protected]. >>> For more options, visithttps://groups.google.com/d/optout. > > > -- > 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]. > For more options, visit https://groups.google.com/d/optout. -- 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]. For more options, visit https://groups.google.com/d/optout.
