kikolus wrote:I would recommend optimistic locking.
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?
But such clause for each column in a table forces me to create dynamic updates every time and I want to avoid this.
.. 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; ..
kikolus wrote: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.
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.
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...
kikolus wrote:Is your question really general? You say:
It's general question and I expect general answers
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 operationwhich 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?