EJB pattern to lock a database row while some user is editing it


EJB design: EJB pattern to lock a database row while some user is editing it

  1. Hi! I have found a problem that I think it may be solved with some ejb design pattern. I need that While somebody is editing data of a row or entity (through a graphical interface), anybody can read but no write this row or entity. I have though a possible solution. When an user is editing a row, this entity is marked as locked (using a aditional field in the database) and when the user finish, the row is marked as not locked. Then, if other user try to access to this row while the firts one is editing it, it's possible to show a message as 'This row is now beeing modified by another user' and no wait until first user ends. Simply, not to permit any write action and stop second user action. If I use transactions, they are serialized to execute just after the first transactions and the two modifications are execute sequentially What do you think about this solution? I'm working with EJB3 in JBoss and I dont found any other better solution. In this web page, I have found a old thread about this topic with EJB2 with this solution but I think it's possible that EJB3 be different. Thank you very much. Santi

    Threaded Messages (8)

  2. or use a timestamp[ Go to top ]

    Many people would just use a timestamp on the row to detect a data collision, then display an error or whatever to the user.
  3. Re: or use a timestamp[ Go to top ]

    A timestamp versioning mechanism would not prevent the second user updating the row, it would only allow the first user to find out that the data has gone stale while having it in his gui. You need to consider at what level you want to lock the data. If locking it in the db, what happens if the user that locked the data does not unlock it (looses his connection, shuts down the computer, etc). If you only have one application updating the db, one way may be to lock the data "in memory" at the application level, allowing for data release at session timeouts, etc. This is a more failsafe approach, but it does not work if updates to the db are made from different directions. If the lock mechanism must be in the db, you will need a "locked flag", either on the individual data rows or on some sort of data group id. /Niklas
  4. Re: Niklas[ Go to top ]

    I thinks TIMESTAMP not solve my problem. A second user could write the same row as the first one, I agree with you. Now, I'm locking the row in the session bean with 'a flag' in the row that indicates that this row is blocked to the other users that try to access. I would prefer to do that in Database tier but I don't know how lock a row while an user is reading it (a row, an entity bean). Moreover, the timeout of the transactions in Database is a little time, and an user can spend ten minutes to modify this row. In addition, since the user click firts in the button 'Modify' and, next, in the button 'Save Changes', it's necessary to begin a transaction in one method and to commit this transactions in the second method. I think it's very important to obtain a good design to do that, and better in the database tier.
  5. Re: Niklas[ Go to top ]

    Hi Santiago, You do not need (and must not have) transactions spanning over user "thinking time". At "Modify", you update the flag in the db through a short transaction and commit. Any other requests will then see (you need to check this programatically/by sql of course) the commited lock flag and leave the row alone. When the user clicks "Save Changes", you update the row and reset the flag to a unlocked state in a new isolated transaction. The drawback with this design is (as I wrote in my previous post) that nothing forces the user to save changes (releasing the row) after locking it in the first place, making it locked forever unless releasing it some other way. I'm not saying that this is a show stopper, it is just a reason to consider alternative solutions. This might however very well be the correct solutions in your case. /Niklas
  6. Re:Niklas[ Go to top ]

    On one side, I think it's a good idea to do two short transactions instead a long transaction (as long as 'thinking time' as you said), to lock and unlock the row. Thank you. On the other side, my client application doesn't allow an user to exit if they have some row locked (when an user clicks 'Modify', I mark a boolean with true, and when this user clicks 'Save Changes' I mark the boolean with false). In this manner, I can ensure that an user doesn't leave the application with a row locked. However, the application could be crashed and, then, the row will be locked until the database administrator (or someone) fix the problem. Now, I'm searching a solution for this problem. Do you know alternatives to this lock/unlock ('Modify', 'Save Changes') row 'pattern'? Thank you Santi
  7. Extract locking logic from entity[ Go to top ]

    Since I assume (from your description) that you require pessimistic locking of an entity, you might consider removing locking logic from the entity and create a Lock entity that will be a collaborator of the entity you wish to lock. A lock entity would be backed by it's own database table that could include the key of the object to lock (and optionally it's type) and the user who has the lock along with perhaps a timestamp. When selecting an entity for exclusive write-lock, just before preparing the locked entity for presentation to the user, add an entry to the lock table (the entry is already there? it must be locked from another user). After the entity to be locked has been persisted to the DB, you would then delete the lock from the lock table. There are a couple of considerations when using this pattern: what happens if the user was editing a record and all of a sudden got up and left the building? There should be some mechanism for lock timeouts (i.e. the lock has a lifespan, and when that lifespan expires, the lock is removed). Depending on the requirements, an optimistic lock pattern may be easier to maintain.
  8. I think the idea is not bad. Actually I work on a SAP Netweaver project which involves usage of Entity Beans. They provide a lock table called the enque where in once an entity is recieved in a transaction it is put into that table.(This is not a DB table but an in memory table and hopefully replicated on clusters).So if the record is in the enque with a write lock attribute it cant be accessed by othe threads and the system throws a unchecked exception which can be handled adequately by either trying the transaction again. Regards Shashank
  9. There are somethings that just cant be done (in a clean and elegant fashion). How about using a stateful session EJB. It supports transactions spanning methods. (this is a really bad solution, but then, you've got an equally bad....)