Java Developers

Java Developers

261,909 members
  • Join

    When you join a group, other members will be able to see your profile and message you. The group logo will be visible on your profile unless you change that setting.

  • Information and settings

Have something to say? Join LinkedIn for free to participate in the conversation. When you join, you can comment and post your own discussions.

Dani

Can someone explain to me what OPTIMISTIC LOCKING is? I have done some research myself but wanted to speak with someone who has experience with it. Thanks so much!

Resource Manager at Signature Consultants

  • Comment (3)
  • July 1, 2012
  • Close viewer

Comments

  • Peter J.

    Peter

    Peter J.

    IT-Architect at Brå

    So what have you found out so far?

  • Charles

    Charles W.

    Principle Information Systems Trainer at DST Systems

    Hi Dani,

    I am using optimistic locking in my current project, so I'll try to explain the rationale and the technique I'm using.

    First, optimistic locking should only be employed when there is the extremely low likelihood that the same record will be updated by two different users. This could be the case with large national-level data bases where certain tables are only updated on rare occasions...and yet, there is still some small probability of a simultaneous record update.

    Second, it can be used when traditional record locking could cause inordinate delays which might be deemed unacceptable. For example, I'm in the middle of an update and I get a phone call. Meanwhile, the record stays locked and unavailable for updates by others until I finish my phone call and my update operation.

    In our case, we have multiple tables with a foreign key that points back to a parent table which is holding an accumulated value. When a child table record is inserted or updated, that parent table field has to be updated as well. Obviously, this is done in a transaction context. The problem is, that when a customer is involved, it might take a while to insert/update all of the child tables. If I lock the parent record at the beginning of the process, it could be unavailable for quite some time. (You know how indecisive those customers can be.) On the other hand, what are the odds that someone else wants to update that same customer record while I'm helping them? Unlikely...but it could happen. (Oops...this guy called me first and bought something, but I had an emergency lunch date I had to go to before I could complete the transaction on my end...but I'm back now.)

    Rather than lock the parent record, we include a separate field, say an optimistic_lock_sequence, in the parent table record, which is incremented with every update. I begin my transaction by inserting/updating the child table records and when I'm ready to update the parent table with the accumulated value, I first obtain the current optimistic_lock_sequence value. I then increment it, but use the ORIGINAL value in my where clause. The incremented value is used to update the optimistic_lock_sequence field.

    Now...suppose two of us are updating the same customer records at the same time. We both read the same optimistic_lock_sequence number and the race is on! One of us will get there first and successfully update the parent record. The other will then try to update it, but the record will not be found because the optimistic_lock_sequence number will have changed before they could get access to the DB server to do their update. So...the app throws an exception which Spring sees and initiates a rollback. Bummer! But at least the customer data is kept valid...and that's the important thing. Plus, no one physically locked the parent record and kept someone else from working with the same customer. The actual downside? Well, someone gets a message and then has to click the Retry button. I can deal with that.

    I'm working with a couple of parameterized SQL statements, something like the following:

    select optimistic_lock_sequence from parent_table where customer_id = :customerId

    update parent_table set optimistic_lock_sequence = :optimisticLockSequence, total_buying_events = :totalBuyerEvents where customerId = :customerId and optimistic_lock_sequence = :optimisticLockSequence -1

    Note that the DBMS has to find the record using the original optimistic_lock_sequence number that I obtained from the select statement. If someone has updated the parent record between the time I obtained the optimistic_lock_sequence number, incremented it, and issued the update command, the update will fail...that is, the record will not be found and the method call will return a 0 rows changed value. The app tests for that and throws an exception when it occurs...which triggers a rollback...and presents the retry page to the "loser".

    Does that make sense?

  • Edgar D.

    Edgar

    Edgar D.

    Technical Consultant at Guidewire

    In my experience, avoiding locks is most preferable, then optimistic locks, then pessimistic locks. Pessimistic locking should be avoided whenever possible. If you do use pessimistic locking, do so inside a single transaction to avoid deadlock.

    Pessimistic locks do a better job of data integrity but the costs are very high in terms of risks of resource locks and poor throughput. Also row level locks are not always implemented correctly. If you have to implement the lock in Java you will hit other issues, i.e. multiple JVM's alternate db access, etc.

    With databases that guarantee unique timestamps, i.e. DB2 you can also do optimistic locks with just the last update timestamp.

    update <table> set value=<newvalue> where id=<updateid> and last_update_ts=<read timestamp>.

    check the return record update count to see if the record was updated. This methodology is supported by hibernate as well.

Your group posting status

Your posts across groups are being moderated temporarily because one of your recent contributions was marked as spam or flagged for not being relevant. Learn more.

Feedback