mercredi 5 août 2015

Is it possible to isolate db table from read operations in transaction?


I'm writing Java app(Spring Data/Hibernate/JPA), which should work with MySQL DB. I have a simple task, which should be atomic: 1. Retrieve first item from database 2. Bind this item to current user(UPDATE item SET user_id=current_user_id WHERE id=id)

I've put 500ms pause between these two steps to test it against concurrency and run it in transaction. Transaction exists, at least it rollbacks if I throw exception in this code. But atomicity doesn't work as I want it to, with this 500ms pause I get the following situation:

  1. user1 starts transaction
  2. user1 retrieves first item - item1
  3. user2 starts transaction
  4. user2 retrieves first item - item1
  5. user1 updates item1
  6. user1 commits transaction
  7. user2 updates item1
  8. user2 commits transaction

Is there a way to isolate table from user2's read/write queries while user1 is in transaction? It should be DB level transaction to allow 2 instances of the app on different servers.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire