Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Locking the record in DB and showing it on the browser page.

 
Arun Prasath
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

I am using EJB/JPA (Hibernate) in a project. I need to lock the record and show it to the user on (UI) page. I should not realase the lock until the user updates the record or cancel the action, such a way that same record should not be updated by other user ( probably by throwing optimistic locking exception when such attempts made). ..


Could you please give some efficient solution.?
 
Arun Prasath
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could anyone give some solution for the question posted above?
 
Justin Chi
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. add a column named "current_user" to table that is going to be updated
2. use "select for update" statement to lock the records whose curernt_user is empty and is going to be updated by user (101), then update those records' current_user colun with user id (101) and release the lock
3. another user (102) comes and see that there is a value in current_user column , this means someone is changing this records , so edit function is disable.
4. after user (101) modifying the records , empty current_user value
5. then user (102) come again, repeat step 2.

If 101 and 102 try to get lock of a record in the same time , one will be failed.
By doing this, only updating of current_user requires a lock in DB, hope it's clear enough.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That will only work on databases that support select ...for update. There is also a flaw in your logic: how do you recover when the process that set the current_user field dies before it set it back to null?

Instead, since this is JPA, you should set the lock mode to be pessimistic.
 
Justin Chi
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:That will only work on databases that support select ...for update. There is also a flaw in your logic: how do you recover when the process that set the current_user field dies before it set it back to null?

Instead, since this is JPA, you should set the lock mode to be pessimistic.


Hi Paul,

Thanks for your comments.

1. If lock applied at JPA level how transaction will be controlled ? It will start from when pages are loaded and after user confirms the changes ?
2. How another user will know some records already be locked by JPA ? By trying to update and get a returned error?
3. "set the current_user field dies" , if there is any fatal error application should catch it and reset the flag otherwise they need production support team.
4. I understand that pessimistic locking ensures that transactions do not update the same entity at the same time, which can simplify application code, but it limits concurrent access to the data which can cause bad scalability and may cause deadlocks.

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

1. If lock applied at JPA level how transaction will be controlled ? It will start from when pages are loaded and after user confirms the changes ?

Not sure I follow - there will be no difference in transaction handling for the two approaches. Was there something specific you were worried about?


How another user will know some records already be locked by JPA ? By trying to update and get a returned error?

For pessimistic locking they don't need to know. The database will block till the lock is released, or the transaction times out.


"set the current_user field dies" , if there is any fatal error application should catch it and reset the flag otherwise they need production support team.

How do you do this when the process dies in a way that does not allow you to do this? If a program exits properly you can use shut down hooks, but what happens if the power goes? There is no way another process can know that this flag is invalid and reset it. To use the sort of locking strategy you suggest you would need to identify all processes that could be locking in this way and make your application aware if there is one missing - possibly by using a unique thread id as your flag and implementing a mechanism for other threads to query if that specific thread is live. Difficult and not required given how pessimistic locking is implemented by databases.


I understand that pessimistic locking ensures that transactions do not update the same entity at the same time, which can simplify application code, but it limits concurrent access to the data which can cause bad scalability and may cause deadlocks.

Absolutely. Pessimistic locking in databases should only be used in exceptional circumstances.
 
Ran Pleasant
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Arun Prasath wrote:Hello All,

I am using EJB/JPA (Hibernate) in a project. I need to lock the record and show it to the user on (UI) page. I should not realase the lock until the user updates the record or cancel the action, such a way that same record should not be updated by other user ( probably by throwing optimistic locking exception when such attempts made). ..


Could you please give some efficient solution.?


In the book "JE2EE Design Patterns" by William Crawford (O'Reilly) they have the "Lockable Design Pattern" for locking a object and ensuring that it is release after a given period of inactivity. I have never used the pattern myself and cannot offer any information on how well it works but it may be worth looking into.

Ran
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic