Hi all,

I have a code which acquires a Pesimistic write lock for a given criteria present on the source code below. I have multiple instances (Actually 4 instances) running that code, accessing the database concurrently.

The documentation indicates the lock is acquired on all entities scanned by the Criteria, guaranteeing mutual exclusion to update fields of those entities, releasing the lock when the transaction is committed. The problem is we are experiencing concurrent read and writes on those instances, which lead to problems on our application. Does anyone has experienced such behaviour? We are using hibernate-5.0.9-Final.

Although you haven't specified the underlying database, I assume it's either Oracle, PostgreSQL or MySQL since the behavior is the expected one.

With any DB that uses MVCC instead of two-phase locking (e.g. the default concurrency control mechanism in SQL Server), readers do not block writers and writer don not lock readers. For this reason, even if you acquire a PESSIMISTIC_WRITE lock, the exclusive lock will not prevent other transactions from reading the underlying locked rows.

For MVCC DBs, the row-level lock which is acquired either by a FOR UPDATE clause or by an UPDATE, prevents the dirty write phenomena. This way, other transactions are not allowed to write the same record, and they will simply block until the first transaction ends. Depending on the current isolation level, after the first transaction commits, the second transaction update statement that was blocked can either succeed (READ_COMMITTED) or fail (REPEATABLE_READ or SERIALIZABLE) because otherwise a lost update phenomena will occur (this can happen with MySQL even on REPEATABLE_READ).

Hi mihalcea_vlad,

thanks for your reply. Could you point me a developer documentation concerning the Criteria Hibernate API? I would like to learn more about the behaviour of the setLockMode method, which I am using to lock the registers for the query performed in the example I have sent in this post. I am using an Oracle database, and I expected to have the write lock on all rows affected by the given criteria.

If you don't know any further documentation, I will take a look into the hibernate source code.

Cheers,
jefecomp.

You can check the official documentation.