Lock Table with exclusive in H2 database

Question

I am having trouble to convert Oracle syntax to H2 syntax:

For example (Oracle):

    LOCK TABLE CAR_CHIP_ID_LOCK IN EXCLUSIVE MODE

In H2 it results in a (syntax error)

How do I change the oracle grammar to h2? I have tried Lock_mode but it doesnt work and MVCC is true by default v1.4+

Rodriguez

What have you found when looking at how to do this in the h2 documentation? – Litton

I dont see any equivalent. In h2 it is using 'set lock_mode' / 'MVCC=TRUE' but I dont want to set or change configs in h2 since I am only using it to test. h2database.com/html/advanced.html – Rodriguez

Another good example why you shouldn't be using a different DBMS for testing than you use in production. – Rapport

Porting database backends is not usually just about transcribing syntax. In this case you'll need to know what the lock was there for. So you can asses how to achieve the goal in the new database. IME explicit table locks are rare in an application using isolated transactions. I'd not be surprsed if the lock is redundant with proper Tx isolation. – Edo

Also relevant: meta.stackexchange.com/a/243965/159703. X/Y problems arise here because you are trying to achieve goal X in h2, using the solution Y that was appropriate for Oracle. Don't get hung up on the Y – Edo

Answer 1

What seems to work both in oracle and h2 was

select * from CAR_CHIP_ID_LOCK for update;

See also How to lock whole entity (table) in JPA entity manager

Tova

select for update isn't really the same thing as lock table is it? – Carbonaceous

no, but the effect should be the same considering only updates, no inserts – Tova

The problem is that select...for update doesn't do any good if the table is empty. – Carbonaceous