CODEWITHSUNDEEP
jpatimestamplockingoptimistic
alessandro feliziani
alessandro feliziani

Reputation: 11

JPA optimistic lock with SQLServer TIMESTAMP

I am trying to setup JPA optimistic lock on SQLServer. For that purpose I use a TIMESTAMP column (SQLServer TIMESTAMP is an incrementing number and does not preserve a date or a time).

Being an auto-incrementing number, on my Java entity I need to set insertable/updatable to false otherwise I would get an exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot update a timestamp column

Here is my Java entity mapping:

@Version
@Column(name = "TSROWVERSION", insertable = false, updatable = false)
private byte[] version;

When I update a record this SQL is executed:

(1) *SELECT ... FROM cmd_e_entities WHERE uidentity=?*

(2) *UPDATE cmd_e_entities SET... WHERE uidentity=?*

but I was expecting to get something like:

(3) *UPDATE cmd_e_entities SET... WHERE uidentity=? AND tsrowversion=?*

Hibernate executes first a SELECT to check if the record has changed (1), and then it updates (2). If the record was changed it throws an exception:

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction

So it works fine, but I was expecting the UPDATE to take into account the version (3). Actually if a change the sql type from TIMESTAMP to NUMBER and remove insertable/updateble it works as expected.

If the UPDATE does not consider the version (... AND tsrowversion=?) how can optimistic lock be guaranteed? How can I get my expected behaviour?

Upvotes: 1

Views: 1017

Answers (1)

gtosto
gtosto

Reputation: 1341

Hibernate checks that the version of the in memory entity is the same as the persisted one. Entity's id doesn't change, besides Hibernate keep only one version of an entity so there is no need to "filter" on version column. Maybe this JPA and optimistic locking modes could help

Upvotes: 0

Related Questions