FOR UPDATE SKIP LOCKED functionality

[Lukas Eder]

I don't have an immediate use case for this with H2, but I had been
digging into this topic lately and I quite like Oracle's FOR UPDATE
clause extensions as documented here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

I had previously raised an issue about the [ OF { table-list | column-
list } ] subclause in H2, which allows for locking only a subset of
the tables/columns involved in the query. Another very nice feature
are the WAIT/NOWAIT and especially the SKIP LOCKED clause. SKIP LOCKED
is used internally by Oracle to implement Oracle's Advanced Queue
functionality. Those queues are basically just tables with some
additional support to hook multiple producers/consumers into them.
When executing something like

SELECT * FROM messages WHERE ROWNUM = 1 FOR UPDATE SKIP LOCKED

A consumer can actually select the first "non-locked" record from
messages, and then immediately lock it for consumption. Producers
would just append to the messages table, without any locking.

Had you ever given this any thought before? Would that be a nice
feature for H2? I could imagine so, especially when your users run H2
in embedded mode for smaller applications, that might be a very nice
way to persistently implement producer/consumer queues

[Thomas Mueller]

Hi,

> Had you ever given this any thought before?

No, this isn't something I have considered. It's nice to know about
this feature however.

Regards,
Thomas

[Rémi Bantos]

Hi,

I am coding an integration test for a query which uses the SKIP LOCKED Postgresql feature with H2.
When I run my query, I get a syntax error from H2. I know that this feature is not part of ANSI SQL.
However, I was hopping that this would work with Postgresql compatibility Mode. (at least not throwing a syntax error even if i does not behave exactly like in Postgres)
Should I raise an issue / change request for that, or did I miss something?

Thanks,
Rémi