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

Question

I have a table from which I'm calculating the current number of rows between two dates and using the resultant value in my 'to be inserted' rows in that same table.

I'm having issue when two concurrent requests come, say A1 and A2, and want to store new rows at the same time (after the calculation above), both have the same resultant, say 10 rows. Even though A1 should have 10, while A2 should get 11.

Both transactions are conflicting. So I need to lock the table. I know the lock function

aRepository.lock(Object);

but the issue is that this will lock only a row, and I want to lock the whole table(entity) as I'm calculating the total number of rows.

Answer 1

As far as i know you cannot lock the whole table with JPA. But of course you can use native queries to do that. Why don't you use a sequence like this in you entity:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

You would be able to work parallel instead of running one after the other.

If you really insist on making in one after the other you can also have a little table with one column and one row. There you can use

aRepository.lock(Object);

But I would not advise this.

Answer 2

Use entityManager.find(yourClass.class , your-primary-key, LOCK_MODE) method . This would lock the whole table.

Answer 3

Try the following:

   private void lock()
   {
      final Query lockQuery = entityManager.createNativeQuery("LOCK TABLE MY_TABLE_NAME IN EXCLUSIVE MODE NOWAIT");
      entityManager.joinTransaction();
      lockQuery.executeUpdate();
   }

You need to wrap this in a transaction, e.g. with @Transactional, as the lock is valid until the commit:

A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table.

If this does not work for you (e.g. it failed for h2), you can try a native query, e.g. in the entity

@NamedNativeQuery(name = "lock", query = "SELECT * FROM MY_TABLE_NAME FOR UPDATE")

and in the repo

@Query(nativeQuery = true, name = "lock") void lockTable();