Firebird and pessimistic lock

113 views
Skip to first unread message

sdne...@gmail.com

unread,
Apr 11, 2014, 6:04:16 AM4/11/14
to jooq...@googlegroups.com
Hi,

in firebird the select .. for update statement it is very different than in other database 


particularly this passage : 

The Select For Update syntax has been present in InterBase for a long time but it has nothing to do with locking. During execution of a normal Select, records are sent to the client as packets. Although records are fetched by the application one by one, the client program (gds32, fbclient, libgds, etc.) gets from the server a packet of records of a requested size and buffers it. During execution of Select For Update the packets are formed of exactly one record. The next packet will be formed and fetched by the client only after the application requests it. Select For Update With Lock combines the functionality of Select For Update with a dummy update. In other words a new version of the record is created at the moment of fetch. It is the same thing that occurs when an update is posted, except that the triggers do not fire. So this statement can be used in all the above-mentioned cases instead of dummy update and you can forget about work of triggers. When using With Lock option remember that locks are released at the moment of the transaction end, not after the query is closed.

So in JOOQ with a firebird db we don't obtain a lock when i use the forUpdate method

ex : 

getDsl().select().from(MATABLE).forUpdate().fetch() => select * from matable for update

i think for firebird it must generate => select * from matable for update with lock no ?


Lukas Eder

unread,
Apr 14, 2014, 1:39:20 PM4/14/14
to jooq...@googlegroups.com
Hello,

Thanks for reporting this. I wasn't aware of Firebird going against the standard here. I have registered an issue for this:

In the mean time, if you're sure that the jOOQ-Firebird integration should render FOR UPDATE WITH LOCK, then you could attempt to replace your generated SQL using an ExecuteListener. Upon the listener.renderEnd() event, you could replace the SQL string as such:

ctx.sql(ctx.sql().replaceAll("for update$", "for update with lock"));

See:

Hope this helps and thanks again for reporting,
Lukas


--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

unread,
Apr 14, 2014, 1:39:41 PM4/14/14
to jooq...@googlegroups.com
... which reminds me, I should check why this slipped through the integration tests...

sdne...@gmail.com

unread,
Apr 29, 2014, 4:35:14 AM4/29/14
to jooq...@googlegroups.com
Thanks for the tip with listener.

have you seen why the test don't detect this issue ?

Lukas Eder

unread,
Apr 29, 2014, 9:43:45 AM4/29/14
to jooq...@googlegroups.com
Hello,

No, I haven't been able to figure it out after a quick glance. The integration tests run normally, but I must admit, they're not too sophisticated:

Maybe you have an idea of how to improve things?

Cheers
Lukas

Lukas Eder

unread,
Apr 29, 2014, 9:49:21 AM4/29/14
to jooq...@googlegroups.com
In the meantime, I have proceeded with a fix for #3186

jOOQ now renders FOR UPDATE [ OF <column-list> ] WITH LOCK

Cheers
Lukas
Reply all
Reply to author
Forward
0 new messages