Hi Lukas, Thanks for the response.
On Tue, Sep 5, 2017 at 12:54 PM, Lukas Eder <[email protected]> wrote: > Hi Kamal, > > Thank you very much for posting that detailed example. Indeed, optimistic > locking in jOOQ applies only to Record.store(), Record.update(), and > Record.delete() operations, in case of which the semantics is clearly > defined. It does not apply to concrete SQL statements, where semantics are > unclear. *In your particular example, what client version or timestamp > value would you like jOOQ to compare the database version or timestamp > value with?* > > I didn't quite get that. However I made the code to work as expected (throwing exception on concurrent modification): DSLContext optimistic = DSL.using(conn, SQLDialect.POSTGRES, new Settings().withExecuteWithOptimisticLocking(true)); Result<LayoutRecord> a = optimistic.fetch(Layout.LAYOUT,Layout.LAYOUT.ID.eq(1L)); Result<LayoutRecord> a1 = optimistic.fetch(Layout.LAYOUT,Layout.LAYOUT.ID.eq(1L)); a.get(0).setHeight(15); a.get(0).store(); a1.get(0).setHeight(67); a1.get(0).store(); It throws the org.jooq.exception.DataChangedException: Database record has been changed exception. This is what I was looking for. There are couple of things I can see: 1. I had a table with the following schema: create table userteams( userid BIGINT REFERENCES USERDATA(ID), teamid BIGINT REFERENCES teams(ID), CONSTRAINT user_teams_pk PRIMARY KEY (userid,teamid) ); code generation does ends up with a compilation error on keys.java file. I guess the code generation also creates a key called user_teams_pk, which already I have named it as my constraint. Looks like a bug to me. 2. Is there any special performance cost by making all the DSLContext to be withExecuteWithOptimisticLocking as true? 3. By looking at the documentation about OL over here : https://www.jooq.org/doc/3.7/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/ the code sample seems to be little wrong to me. Because the DSLContext's fetch does return Result<Record> not Record (if I'm not wrong). Guess you need to update that in the documentation as well. Thanks a lot for making up this library! Its just brilliant. I hope this helps, > Lukas > > 2017-09-04 18:48 GMT+02:00 Kamal raj <[email protected]>: > >> Hi Luke, >> >> Thanks for your reply. I tried to opt for OL as you have mentioned , in >> the following code: >> >> public void runQueries2() { >> String userName = "bidev"; >> String password = "biapp"; >> String url = "jdbc:postgresql://localhost:5432/postgres"; >> >> try { >> Connection conn = DriverManager.getConnection(url, userName, >> password) >> Configuration configuration = new DefaultConfiguration() >> .set(conn) >> .set(SQLDialect.POSTGRES); >> >> configuration.settings().withExecuteWithOptimisticLocking( >> true); >> >> conn.setAutoCommit(false); >> Connection conn1 = DriverManager.getConnection(url, >> userName, password); >> conn1.setAutoCommit(false); >> Configuration configuration1 = new DefaultConfiguration() >> .set(conn1) >> .set(SQLDialect.POSTGRES); >> >> configuration1.settings().withExecuteWithOptimisticLocking( >> true); >> DSLContext context = DSL.using(configuration); >> >> DSLContext context1 = DSL.using(configuration1); >> context1.update(Layout.LAYOUT) >> .set(Layout.LAYOUT.HEIGHT,200) >> .where(Layout.LAYOUT.ID.eq(1L)) >> .returning(Layout.LAYOUT.ID) >> .fetchOne(); >> context.update(Layout.LAYOUT) >> .set(Layout.LAYOUT.HEIGHT,10) >> .where(Layout.LAYOUT.ID.eq(1L)) >> .returning(Layout.LAYOUT.ID) >> .fetchOne(); >> >> conn.commit(); >> conn1.commit(); >> } >> >> //TODO: should be throwing it back.. >> catch (Exception e) { >> e.printStackTrace(); >> } >> } >> >> But the code does end up in a deadlock. Note that here I'm just mimicking >> the scenario. Layout schema looks like the following: >> >> CREATE TABLE LAYOUT( >> ID BIGSERIAL PRIMARY KEY NOT NULL, >> DID SERIAL REFERENCES DATASOURCE (ID), >> VERSION_ID INTEGER, >> TYPE VARCHAR(30) NOT NULL, >> QUERY VARCHAR(500) NOT NULL, >> WIDTH INTEGER, >> HEIGHT INTEGER, >> POSIITION INTEGER, >> LAYOUT JSONB NULL >> ); >> >> and the in the code generation I have used the following: >> >> <recordVersionFields>VERSION_ID</recordVersionFields> >> >> Not sure, where I'm making the mistake. >> >> On Mon, Sep 4, 2017 at 3:19 PM, Lukas Eder <[email protected]> wrote: >> >>> Hi Kamal, >>> >>> Thanks for your message. I'll comment inline >>> >>> 2017-09-03 16:38 GMT+02:00 Kamal raj <[email protected]>: >>> >>>> 1. Need to enable the OL in Jooq using executeWithOptimisticLocking >>>> settings >>>> >>> >>> Yes >>> >>> >>>> 2. Since OL in Jooq automatically throws / fails when see the >>>> concurrent modification, I believe its totally fine to run them in any >>>> transaction mode (note that I'm using Postgres here). My understanding here >>>> is that OL themselves hides the complexity of transaction. May be I'm wrong >>>> here. Please correct me. >>>> >>> >>> Well, to get a completely accurate picture, the usual transaction >>> anomalies can still happen under non-serializable transaction isolation - >>> although, I cannot think of such an edge case right now. >>> >>> >>>> 3. How does Jooq knows which column it needs to watch while versioning >>>> under OL? The documentation from here : https://www.jooq.org/doc/2.5 >>>> /manual/sql-execution/crud-with-updatablerecords/optimistic-locking/ >>>> says that, we can create a column called MODIFIED as a TIMESTAMP. But the >>>> question here is, how does the column mapping works w.r.t to OL? >>>> >>> >>> Follow the link to the manual sections about the code generator >>> configuration. You can specify the columns to be used as version / >>> timestamp columns here: >>> https://www.jooq.org/doc/latest/manual/code-generation/codeg >>> en-advanced/codegen-config-record-version-timestamp-fields >>> >>> I hope this helps, >>> Lukas >>> >>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "jOOQ User Group" group. >>> To unsubscribe from this topic, visit https://groups.google.com/d/to >>> pic/jooq-user/VLWO1rFJ4AM/unsubscribe. >>> To unsubscribe from this group and all its topics, 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 a topic in the > Google Groups "jOOQ User Group" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/jooq-user/VLWO1rFJ4AM/unsubscribe. > To unsubscribe from this group and all its topics, 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.
