CODEWITHSUNDEEP
mysqlpostgresqljpaconcurrencypessimistic-locking
mika
mika

Reputation: 2585

Physical lock on DB rows obtained from a JPA PESSIMISTIC lock

According to the JPA 2.1 specification...

The lock modes PESSIMISTIC_READ, PESSIMISTIC_WRITE, and PESSIMISTIC_FORCE_INCREMENT are used to immediately obtain long-term database locks.

I assume a pessimistic lock will always trigger a SELECT ... FOR UPDATE SQL on the database, no matter what lock-mode is used. Now three questions on that:

  1. Is the assumption correct or are there exceptions from this rule, if correct?
  2. Given a SELECT ... FOR UPDATE locked the rows. Locked rows cannot be updated by any other transaction except the transaction which locked it?
  3. The lock can be released by performing a commit or rollback on the transaction. What happens with the lock if the application (and the transaction which locked the rows) suddenly terminates without doing a commit or a rollback on the transaction?

Upvotes: 1

Views: 2199

Answers (1)

Ondro Mihályi
Ondro Mihályi

Reputation: 7710

For the question 1 and 2, your assumptions are correct:

  1. Yes - pessimistic lock generally uses SELECT ... FOR UPDATE, as most databases and JPA implementations only support this type of lock. In this case there is no difference between READ and WRITE block, and JPA specification allows it as long as both behave as WRITE locks.

  2. Yes - locked rows cannot be modified by any other transaction. In case of WRITE lock (and most time also for READ lock - se answer for 1), locked rows cannot be read also until the lock is released. Note that other unlocked rows in the same table are free to be read and modified.

To answer also question 3:

  1. Yes - locks are release in case of commit or rollback. However, rollback also happens automatically when an error happens, or connection is dropped, or transaction takes too long. So, when the application dies, rollback is triggered immediately. If not, it is rolled back after some timeout (usually 5 minutes).

Upvotes: 6

Related Questions