Table exclusive lock with JPA

Question

Is it possible to lock exclusively entire table with JPA in database agnostic way (without using native queries for instance)? So far I saw only EntityManager.lock(*) but this locks only given record.

Answer 1

I don't think this is supported by JPA natively - you always lock single entity or every entity which is a result from a query. Inserts are always allowed as they are new entities. You may either use native query to trigger a lock using SQL supported by your DB server, or you need to lock your insert programmatically.

By programmatically I mean that you need to acquire a lock before every insert. You may do it by creating a separate lock entity class, each entity for a table you want to lock, and then before each insert, you lock particular entity with a read lock like this:

em.createQuery("select l from TableLock l where l.entity = :entityName", TableLock.class)
   .setParameter("entityName", MyEntity.class.getSimpleName())
   .setLockMode(LockModeType.PESSIMISTIC_READ)
   .getResultList();
em.persist(new MyEntity());

Of course, you should also check if the lock entity exists in database, and if not, create it first, with entity field being the name of your entity.