Hi, I am coding an integration test for a query which uses the SKIP LOCKED <https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/> 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 Le mercredi 29 juin 2011 10:29:35 UTC+2, Lukas Eder a écrit : > > 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 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
