Hi Lukas, Thanks for the reply. I have a little more info.
I mentioned we are using Hikari for the connection pool. Hikari has a AutoCommit property that defaults to TRUE. We are using that default value. It is my understanding that this flushes data to disk/DB after each SQL statement. So this is in effect like transactions for a single call at least as far as data flush is concerned. I also investigated using JOOQ's default transaction management and found that the AutoCommit property must be set to false for that to work, which makes sense as you wouldn't want both transaction/flushes operational at the same time. Regarding vertx, no we are not using any of vertx's async support in this section of code, this is 100% simple single threaded synchronous method calls. So I'm convinced there is a bug in here someplace, here is our runtime stack. JOOQ 3.10.8 HikariCP 3.1.0 postgresql driver 42.2.2 PostgreSql DB 9.6.10 And here is our code generation stack. jooq-codegen-maven 3.8.9 with jooq-meta, jooq-codegen 3.10.8 & vertx-jooq-generate 2.4.1 We use io.github.jklingsporn.vertx.jooq.generate.future.FutureVertxGenerator However as stated above the code that is causing the DB error is NOT using any Vertx related methods, the method that fails is DAOImpl#insert(Collection<P> objects) which is in JOOQ 3.10.8. I would love to create a MCVE however I cannot reliably reproduce the issue even with our multiple deployments. We have the stack trace from one server, some devs have seen the issue on their local VM but I have never been able to reproduce on my VM. So the bottom line is we are 100% sure there is a bug someplace because we have it recorded but its very hard to reproduce so the MCVE would come back as can't reproduce, yet there is a bug someplace. Anything anyone can think of is much appreciated, we are starting to deploy our app now and I fear this bug will show up in production at the worse possible time. -Dave On Tue, Apr 30, 2019 at 6:38 AM Lukas Eder <[email protected]> wrote: > Hi David, > > Thanks for your additional information. Before we digress on transactional > issues, I think the ideal thing here is to try to provide an MCVE: > https://github.com/jOOQ/jOOQ-mcve. It is really difficult to assess from > an email description alone what could have gone wrong. > > My assumption about transactions was just that: An assumption. Another > assumption is that you're doing something wrong in terms of what vertx is > doing for you (so your calls are not synchronous / sequential after all). > Another assumption might be there's a bug in jOOQ, the driver, or the > database version you're using. > > Ideally, if you could reproduce it in an MCVE, we'd get to a point where > we can decide what's going on much more quickly. > > I hope this helps, > Lukas > > On Mon, Apr 29, 2019 at 4:26 PM David Hoffer <[email protected]> wrote: > >> Hi Lukas, >> >> Thanks for your reply. Let me provide a bit more background then see my >> replies inline below. >> >> Let me start by saying I'm not a DBA. >> >> Our application is a port of an application that used a variety of DB >> access approaches, part Spring, part Hibernate, part home grown. We have >> converted the app to be standardized on Vertx & JOOQ for DB access. >> Regarding DB access we use a combination of auto generated DAOs and custom >> queries using DSLContext. >> >> We use HikariDataSource as our connection pool. >> >> We do *not* use transactions (yet) as my understanding is JOOQ does not >> directly provide transaction management so we have not investigated what it >> would take to add this. We have assume we might need transactions at some >> point but that would be to provide rollback support if a group of related >> JOOQ/SQL calls should fail. We did not assume we would need this to make >> single JOOQ/SQL calls synchronous. >> >> It is my/our understanding that if a single thread makes a JOOQ/SQL call >> it will complete before the next method starts. See below for more details >> on my replies. >> >> Thanks, >> -Dave >> >> On Mon, Apr 29, 2019 at 2:39 AM Lukas Eder <[email protected]> wrote: >> >>> Hi Dave >>> >>> Thanks for your message. I will comment inline >>> >>> On Thu, Apr 25, 2019 at 8:36 PM David Hoffer <[email protected]> wrote: >>> >>>> We are using JOOQ 3.10.8 using the auto generated DAOs via >>>> jooq-codegen-maven >>>> >>>> We are using PostgreSql with postgresql-42.2.2.jar with >>>> SQLDialect.POSTGRES_9_4 >>>> >>>> In our JOOQ Settings we have ReturnRecordToPojo=false >>>> >>>> We have a situation were occasionally we get a >>>> org.jooq.exception.DataAccessException when we do a batch insert of 100 >>>> records where each record has a FK to another record that was previously >>>> just inserted as well. Our code is like this: >>>> >>>> elsetDao.insert(elsets); // First 100 records created. >>>> >>>> episodeDao.update(episode); // Single record update. >>>> >>>> episodePointDao.insert(points); // Second 100 records created. Each of >>>> these points to one of the elset records inserted previously. >>>> >>>> We are assuming that each of these lines are atomic and synchronous >>>> (e.g. fully committed to the DB before each method returns. However what >>>> is happening is that occasionally the 3rd line will fail with >>>> DataAccessException saying that the associated elset record does not exist >>>> in the DB. >>>> >>> >>> What makes you think so? jOOQ makes no such guarantees, it simply >>> executes SQL on a JDBC driver. Are you running these statements in >>> individual transactions? >>> >> >> [DH] No. As stated above we are not using transactions. We are assuming >> that the SQL/JDBC call will flush all data to DB before the next method >> starts. If that were not so one could not depend on the behavior of the >> prior SQL/JDBC call. Are you saying that each DAO method has to be wrapped >> in a transaction to get the desired behavior? How would we do that? I've >> always assumed transactions are to group related SQL/JDBC calls so they all >> succeed or all fail as an atomic unit...not to make sure that a single call >> is flushed to DB. Can you elaborate on how one can make sure each call is >> flushed and if that requires transactions a simple way to add that to the >> DAOs/DSLs? >> >>> >>> >>>> Here is an example stack trace. >>>> >>>> INFO processReachableVolumeResult(): number of elsets built from cloud >>>> results: 100 >>>> INFO processReachableVolumeResult(): number of elsets after equals >>>> comparator eval: 100 >>>> INFO Episode: uuid=11BA946DCA9340E3A0B2269FE6D11E07; name=SR_17APR_0910 >>>> INFO updateEpisodeWithCloudInfo(): Persisted elsets, duration=191(ms), >>>> count=100 for episode uuid: 11BA946DCA9340E3A0B2269FE6D11E07 >>>> INFO updateEpisodeWithCloudInfo(): Updated episode for uuid: >>>> 11BA946DCA9340E3A0B2269FE6D11E07 >>>> ERROR org.jooq.exception.DataAccessException: SQL [insert into >>>> "odin_owner"."episode_point" ("episode_point_uuid", "episode_uuid", >>>> "vector_uuid", "elset_uuid", "priority") values (?, ?, ?, ?, ?)]; Batch >>>> entry 93 insert into "odin_owner"."episode_point" ("episode_point_uuid", >>>> "episode_uuid", "vector_uuid", "elset_uuid", "priority") values >>>> ('283C198BD074443DAAE9CFAC92F4752E', '11BA946DCA9340E3A0B2269FE6D11E07', >>>> NULL, '283C198BD074443DAAE9CFAC92F4752E', 2.0) was aborted: ERROR: insert >>>> or update on table "episode_point" violates foreign key constraint >>>> "episode_point_elset_uuid_fkey" >>>> Detail: Key (elset_uuid)=(283C198BD074443DAAE9CFAC92F4752E) is not >>>> present in table "elset". Call getNextException to see other errors in the >>>> batch. >>>> >>>> When we inspect the DB after this occurs it does in fact contain the >>>> elset record that the above stacktrace says is missing. >>>> >>> >>> But since you're assuming that there are transactions, which are atomic, >>> are you *sure* that first transaction is committed when the third >>> transaction starts? Otherwise, it might not see your data. >>> >> >> [DH] As stated above we are not using transactions as we didn't know this >> was required for the first call to be committed before the third call >> starts. Can you point me to how I would achieve this? Is there a way to >> force each JOOQ/SQL call to flush/commit or do we need transactions to do >> this? >> >>> >>> >>>> What is going on here? Is it not true that JOOQ always persists all >>>> records to the DB before returning from each DAO method? The above code >>>> works 99% of the time but its the 1% we are trying to resolve. >>>> >>> >>> At first sight, I don't see anything that jOOQ could do wrong here. The >>> listed methods are simply "convenience" on top of what you'd do manually >>> with JDBC. I suspect there's something wrong in the way you handle your >>> transactions. >>> >> >> [DH] Please advise how we can achieve fully flushed JOOQ/SQL method calls >> with or without adding transaction support. >> >>> -- >>> 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. >> > -- > 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.
