0

I am attempting to do a select on a row and update the value. While I do this I need exclusive access to the row. In other words, no other process (inside or outside the VM), should be able to read the row until after I update the row. The current value should not be "selectable". I have tried the following transaction annotation.

@Transactional(isolation = Isolation.SERIALIZABLE, readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class, timeout=960)

This definitely works within the Spring context, but while putting a sleep statement in the middle of the transaction, I'm still able to select the current row value using a database tool.

Is there a way to get a XLOCK/ROWLOCK (whichever is the appropriate) using Spring/Hibernate?

Versions:

  • Spring: 3.0.5.RELEASE
  • Hibernate: 3.6.3.Final
  • JTDS: 1.2.4

If I can't use Spring/Hibernate, a link to a JTDS example would be much appreciated.

Thank you.

JustinKSU
  • 4,875
  • 2
  • 29
  • 51
  • did you try the other way around issuing a select with a "for update" suffix on the database tool and then try running the program from the application ? You may have to check the isolation level in the tool also, before running the above query. – r0ast3d Oct 21 '11 at 15:37
  • I will try that. It could be I'm already acquiring the kind of lock I need without making any changes. – JustinKSU Oct 24 '11 at 20:17
  • I think that should definetly tell you what is happening, please update is you see that it is not locking. – r0ast3d Oct 24 '11 at 22:25
  • I can't do a "FOR UPDATE", but when I used "with (paglock,XLOCK)", I seemed to get the locking I wanted. If you post your answer, I will give you the bounty. – JustinKSU Oct 26 '11 at 23:01

2 Answers2

4

SERIALIZABLE isolation level allows other transactions to read data, but not to modify. So you need to explicitly SELECT ... FOR UPDATE (in Hibernate: Query#setLockMode(LockMode.UPGRADE)).

dma_k
  • 10,431
  • 16
  • 76
  • 128
2

Use explicit locking with the Hibernate. There is more information here.

However, I think you have to think once more time - do you really need the pessimistic lock? In most cases optimistic lock works better, and hibernate supports versioning very well.

kan
  • 28,279
  • 7
  • 71
  • 101
  • This table used as a sequence. I believe I need pessimistic locking to ensure no other process uses the value before I use it and increment it. – JustinKSU Oct 24 '11 at 18:33
  • Do you need gap-less sequence? – kan Oct 24 '11 at 19:37
  • I doubt it is a requirement. Why? – JustinKSU Oct 24 '11 at 20:18
  • You are unlucky... ;) Try to fight it first http://www.gplivna.eu/papers/gapless_sequences.htm . You also should put a create "document" transaction for which you generate the number inside your method. Otherwise, it could have gaps in case if a document creation has been rolled back. – kan Oct 24 '11 at 20:27