5

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+

serv-inc
  • 35,772
  • 9
  • 166
  • 188
logger
  • 1,983
  • 5
  • 31
  • 57
  • What have you found when looking at how to do this in the `h2` documentation? – sstan Jul 13 '15 at 18:05
  • 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. http://www.h2database.com/html/advanced.html – logger Jul 13 '15 at 20:00
  • 2
    Another good example why you shouldn't be using a different DBMS for testing than you use in production. –  Jul 13 '15 at 21:07
  • 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. – sehe Jul 23 '15 at 23:28
  • 2
    Also relevant: http://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_ – sehe Jul 23 '15 at 23:51

1 Answers1

0

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

serv-inc
  • 35,772
  • 9
  • 166
  • 188