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.
