1

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?
mika
  • 2,495
  • 22
  • 30

1 Answers1

6

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).
OndroMih
  • 7,280
  • 1
  • 26
  • 44
  • Thx! Regarding the 3rd answer, the rollback of the lock is then a feature of the underlying dbms I guess? Can we rely on it for all dbms (the big one's, mysql, postgres, orcale, db2..)? – mika Jan 06 '16 at 10:04
  • Yes, rollback is triggered by the dbms when connection is dropped. I think this is pretty standard error-handling behavior, as otherwise the transaction would be left open forever. I would rely on that, although I would do some simple tests to prove it for myself, as there may be an issue/bug in particular dbms. – OndroMih Jan 06 '16 at 14:01
  • I want to know that after getting response also I did not want to release the lock for next 10 minutes (Some business requirement) Will it be possible by any way using this perssimistic locking? – Neha Gour Feb 02 '20 at 10:40
  • Pessimistic locking exists to prevent conflicting DB access. Once a transaction commits, the lock is released. JPA doesn't provide anything beyond it. Maybe your database supports an SQL that will create a lock which is released after 10 minutes but that's not supported in JPA. – OndroMih Feb 07 '20 at 14:38
  • @OndroMih I am not able to add another row to db while having pessimistic_write lock on another row. Is this expected behaviour or something needs to be done there? – Manish Bansal Mar 02 '22 at 12:22
  • 1
    Hi @ManishBansal, this isn't expected in general. But some databases also lock the whole table for inserts, for example MySQL in some configuration, as this article explains: https://vladmihalcea.com/how-does-database-pessimistic-locking-interact-with-insert-update-and-delete-sql-statements/. So yes, it's possible that locking a single row will also lock all insert statements into the table and new rows can be inserted only after the lock is released. – OndroMih Mar 03 '22 at 17:41