Write-lock entity in Spring from database

Question

I need write-lock a parent entity while i am working with his child entities, in order of not allow to modify (or earse) the parent. I need to do this using Spring and performing the lock directly from the database (to avoid problems if the app is executed in a cluster).

Answer 1

In order to implement the strategy you are looking for, you will need to fire a SELECT FOR UPDATE SQL query on the parent row (for example, SELECT * FROM parent WHERE id = ? FOR UPDATE. This obtains a lock on the rows fetched by the SELECT query.

General strategy

1. Start a transaction.
2. Load the parent row with a SELECT FOR UPDATE.
3. Update children.
4. Save children.
5. Commit the transaction. This will save the children and also release the lock on the parent row.

You can use Spring Transactions to enforce transaction boundaries. Something like the following will work:

class SomeService {
  @Transactional
  public ... someMethod(...) {
    // Load the parent row using SELECT FOR UPDATE.

    // Save children.
  }
}

@Transactional will apply transaction semantics around calls to someMethod. Note that the method must be public for @Transactional to work.

---

Executing a SELECT FOR UPDATE depends on how exactly you are accessing the database - Spring JDBC, Spring ORM, Spring Data JPA, etc. Here is how you will achieve this with each of these libraries:

Spring JDBC

You can simply execute a SELECT FOR UPDATE query using the JdbcTemplate class. jdbcTemplate.execute("SELECT * FROM parent WHERE row = ? FOR UPDATE") should work.

Spring ORM

You will have to use the ORM-specific template class to enforce the lock mode. For example, with Hibernate4 HibernateTemplate you can use hibernateTemplate.get(Class<T> entityType, Serializable id, LockMode lock).

Spring Data JPA

You can annotate a repository method with @Lock(LockModeType.PESSIMISTIC_WRITE) to enforce pessimistic locking when the query is executed. For example

interface ParentRepository extends CrudRepository<Parent, Long> {
  @Lock(LockModeType.PESSIMISTIC_WRITE)
  Parent findOne(Long id);
}

will do the job.

---

One thing you should be careful about is that if this operation is called too many times concurrently, you will experience time outs and may be deadlocks as well because rows are being exclusively locked. You will benefit from keeping the following in mind:

1. Keep the locks for only a very short period of time, probably towards the very end of the processing, making sure that any validations, etc. are performed upfront. This will ensure that the locks are freed quickly.
2. Test with concurrent users and develop a sense of how frequently time outs and deadlocks can occur. If you see time outs and deadlocks and do not want the users to retry, you can use functionality provided by the Spring Retry project to retry methods.