Does PESSIMISTIC_WRITE lock the whole table?

Question

Just to be sure that I correctly understand how things work.

If I do em.lock(employee, LockModeType.PESSIMISTIC_WRITE); - will it block only this entity (employee) or the whole table Employees?

If it matters, I am talking about PostgreSQL.

Answer 1

It should block only the entity.

PostgreSQL hibernate dialect adds for update in case of write locks: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/dialect/PostgreSQL81Dialect.java#L549 (newer versions just use the same implementation)

for update is treated row-wise by PostgreSQL: https://www.postgresql.org/docs/9.5/static/explicit-locking.html

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted).