14

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.

Andremoniy
  • 34,031
  • 20
  • 135
  • 241
  • It is not a duplicate of https://stackoverflow.com/questions/33062635/difference-between-lockmodetype-jpa, because I have concrete question: does it lock the **WHOLE** table – Andremoniy Mar 07 '18 at 08:26
  • It **COULD** lock the whole table, it's implementation specific. But at least in Postgres' situation it's not very likely since it can easily be done with `SELECT...FOR UPDATE` instead of a full table lock. – Kayaman Mar 07 '18 at 08:27
  • Some certain answer about how does it work for PostgreSQL will be sufficient for me – Andremoniy Mar 07 '18 at 08:28
  • 1
    to make it more fun `LockModeType.PESSIMISTIC_WRITE` in MSSQL will translate to `with (updlock, rowlock)` where rowlock could be escalated to table or page entirely, as this is just a hint. Even more fun, doing queries to find some entries that are not part of a *covering non-clustered* index (or clustered) will still block each other... You really have to look and understand what the database will do EXACTLY. – Eugene Mar 07 '18 at 11:09
  • Oracle on the other hand can and will do a rowlock if you request it and AFAIK hibernate will generate queries like this, so you have to look at the exact queries that are generated and read the documentation of what they mean – Eugene Mar 07 '18 at 11:13
  • @Eugene but isn't that stupid to block the entire table for `PESSIMISTIC_WRITE`? I would not expect this from the "normal" RDBMS to be honest. But still can't find any meaningful documentation about that. – Andremoniy Mar 07 '18 at 11:49
  • @Andremoniy the documentation is "there", I've read numerous blogs about it, official docs from microsoft and even had tests for this, I did a rundown about it here : https://smarterpeoplethenme.wordpress.com/2016/10/11/hibernate-jpa-ms-sql-locks/ I don't blog any more btw as I never thought I did a good job anyway... – Eugene Mar 07 '18 at 11:52
  • @Eugene yep, that's a nice research. Why not continue doing that? But anyway, I am currently interested in the PostgeSQL behaviour... – Andremoniy Mar 07 '18 at 11:53
  • 1
    I know that - exactly my point here (I hope I was able to transmit it to you) - look at the generated queries and try to understand those and don't trust hibernate blindly (or any other tool that is) – Eugene Mar 07 '18 at 11:56
  • 1
    @Eugene, yep I get your point. – Andremoniy Mar 07 '18 at 11:56
  • 2
    See, told ya ;) – Kayaman Mar 07 '18 at 12:44

1 Answers1

9

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).

Andremoniy
  • 34,031
  • 20
  • 135
  • 241
Anton Malyshev
  • 8,686
  • 2
  • 27
  • 45
  • so you get the proper row guarantees? If you select a single row that is, what if there is a range of touched rows? – Eugene Mar 07 '18 at 12:50