This discussion is archived
14 Replies Latest reply: Dec 13, 2012 9:45 PM by krzysztof.lorencki RSS

Locking strategy

krzysztof.lorencki Explorer
Currently Being Moderated
Hi all,
I want to ask you about best practice regarding row locking strategy. I need to write web application and want to know what are the best practices to avoid DML operations on same records from two or more users at the same time. I would like to start discussion about generic (or most popular) solutions for web applications What do you propose?
Best regards
Kikolus
  • 1. Re: Locking strategy
    Paul Horth Expert
    Currently Being Moderated
    kikolus wrote:
    Hi all,
    I want to ask you about best practice regarding row locking strategy. I need to write web application and want to know what are the best practices to avoid DML operations on same records from two or more users at the same time. I would like to start discussion about generic (or most popular) solutions for web applications What do you propose?
    Best regards
    Kikolus
    I would recommend optimistic locking.

    Plenty of stuff if you google it.

    See http://www.dbasupport.com/forums/showthread.php?7282-What-is-Optimistic-Locking-vs.-Pessimistic-Locking
    or http://www.orafaq.com/papers/locking.pdf
  • 2. Re: Locking strategy
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle is designed to be a multi-user rdbms.
    Oracle automatically locks records that are updated/inserted until a commit is issued.
    Also, writers don't block readers and readers don't block writers.


    What is the issue you are expecting?

    I suggest you read the Oracle Concepts manual, especially the following chapters...

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/part_txn.htm#CHDJIGBH
  • 3. Re: Locking strategy
    905562 Explorer
    Currently Being Moderated
    @BluShadow:

    Lost updates. If the app is badly designed, you can 'lose' updates in a concurrent environment. It's not a DB fault, but requires DB config (via either locktokens/hashes or explicit row locking) to avoid.


    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30562552526857

    @OP:
    It depends, optimistic is good if the collision risk is low, if it is high, then pessimistic locking is probably better.

    Edited by: mrk on Dec 13, 2012 11:25 AM
  • 4. Re: Locking strategy
    krzysztof.lorencki Explorer
    Currently Being Moderated
    Hi,
    I also prefer optimistic locking as general and my application will user such locking for 95% of cases. Some part of application must use pessimistic locking. I'm looking for best implementation techniques used with locking for web applications. Link you give me are great for locking on row level, but suppose that I have a row with two columns A and B. Session 1 updates column A and session 2 column B. I want to allow such operation (I know that this is not recommended) and raise an exception only when session 2 try to update already changed column A. Usually update from session 2 can look like (pseudo code):
     
    .. 
    update table set A = 'something' 
    where  id = anyId and A = 'prev. value'; 
    if sql%rowcount = 0 then 
    raise_application_error(-20000,'Record has been changed by another user'); 
    end if; 
    .. 
    But such clause for each column in a table forces me to create dynamic updates every time and I want to avoid this.
  • 5. Re: Locking strategy
    Stew Ashton Expert
    Currently Being Moderated
    kikolus wrote:
    I also prefer optimistic locking as general and my application will user such locking for 95% of cases. Some part of application must use pessimistic locking. I'm looking for best implementation techniques used with locking for web applications.
    In web applications, you have no choice: you must use optimistic locking. Pessimistic locking only works when the same session does SELECT FOR UPDATE and then UPDATE. You have no guarantee of this in a web application.
    But such clause for each column in a table forces me to create dynamic updates every time and I want to avoid this.
    What language is the application written in? Languages like Java and PHP generally do dynamic SQL anyway...
  • 6. Re: Locking strategy
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    what you have stated

    "but suppose that I have a row with two columns A and B. Session 1 updates column A and session 2 column B. I want to allow such operation (I know that this is not recommended) and raise an exception only when session 2 try to update already changed column A. Usually update from session 2 can look like (pseudo code): "

    Locks are at row level and not at column level, if the session 1 updates a row or holds lock on a row then session 2 will have to wait for the commit / rollback even if session 2 wants to update another column. They can't simultaneously update same row.
  • 7. Re: Locking strategy
    905562 Explorer
    Currently Being Moderated
    Oracle locks at a row level (for what you're talking about). You cannot have two (genuinely) concurrent updates to the same row at the same time on different columns. They will be serialized by the DB and applied in order. This is precisely why you need a locking strategy.

    This explains it quite well: http://www.orafaq.com/papers/locking.pdf

    Edit: It is an older link, but the broad principles are not bad.

    How you implement your particular strategy is down to you, your environment, your hardware and application.

    Edited by: mrk on Dec 13, 2012 12:16 PM
  • 8. Re: Locking strategy
    krzysztof.lorencki Explorer
    Currently Being Moderated
    Hi,
    Maybe I didn't write clear, but I'm looking for best implementation practises regarding locking within web applcations.
    regards
    kikolus
  • 9. Re: Locking strategy
    krzysztof.lorencki Explorer
    Currently Being Moderated
    Hi,
    You're all right. Locking is on row level, my fault :(
    But back to my example: I was thinking about lost updates you mentioned, my example prevent lost updates if session 1 commit changes before session 2, but I'm not use that this is best practice here.
    For now I'm interested about your general concepts: implementation language, requirements are not so important right now. I wonder what will be better: dynamic statements, stored procedures etc. It's general question and I expect general answers :)
    kikolus
  • 10. Re: Locking strategy
    Stew Ashton Expert
    Currently Being Moderated
    kikolus wrote:
    It's general question and I expect general answers
    Is your question really general? You say:
    suppose that I have a row with two columns A and B. Session 1 updates column A and session 2 column B. I want to allow such operation
    which is a specific requirement. Then you add:
    such clause for each column in a table forces me to create dynamic updates every time and I want to avoid this.
    So your requirement is not "general", it is specifically dynamic, yet you want to avoid using a dynamic technique. Why?
  • 11. Re: Locking strategy
    krzysztof.lorencki Explorer
    Currently Being Moderated
    Yes, my question is still general. I just provide an example with example solution to expand the debate. Please don't focus on this particular example. I'm stiil looking for best concepts nothing all
  • 12. Re: Locking strategy
    905562 Explorer
    Currently Being Moderated
    What's better is up to you.

    We can't tell you that, not really. You need to research the methods and decide which suits your site best.
  • 13. Re: Locking strategy
    Stew Ashton Expert
    Currently Being Moderated
    Tom Kyte mentions three methods:
    1) Compare old and new values to make sure someone else hasn't changed them.
    2) Comparing old and new "version number", adding one to the version number with each update
    3) Comparing old and new checksums (or hashes)

    Methods 2) and 3) operate on the entire row, not on individual columns.

    Method 1) is the only one that applies to the scenario you describe.

    Method 1) can be applied using dynamic SQL or static SQL, but the static SQL will be a bit more complicated.

    I would suggest dynamic SQL, checking and updating only those values that have actually changed.
  • 14. Re: Locking strategy
    krzysztof.lorencki Explorer
    Currently Being Moderated
    Hi guys,
    Thanks a lot for your reply. It was really constructive discussion for me.
    kikolus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points