Skip to Main Content
  • Questions
  • pessimistic locking vs optimistic locking

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, antoine.

Asked: October 05, 2002 - 7:09 pm UTC

Last updated: January 18, 2010 - 3:59 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to have your point of view on that, pessimistic vs optimistic locking.

I read this article
</code> http://www.orafaq.com/papers/locking.pdf <code>
on optimistic locking.

What the guy is saying is better to use optimistic locking with trigger to ensure concurrency.

So what is point of view?

BTW: I never had your response back did I do something?


thanks anyway.

Antoine


and Tom said...

I have this email in my list of "bad" emails: xxxxx@symapatico.ca (i x'ed out your email name, you typed in the domain wrong)

Perhaps, you used that one, the email bounced and I put it in my reject list for future questions. Watch your typing, the only way I can get back to you is to have you input a valid email address in the first place.


If you have my book "Expert one on one Oracle", I cover this topic in detail. In a stateful environment (like client server, java servlets, things that maintain a session for you) I prefer pessimistic locking, whereby you select for update the data. So called "Optismistic locking" is for database managment systems whereby write locks prevent readers from reading data (such as sqlserver, db2, informix, et. al.)


I am a huge fan of so called pessimistic locking. The user has very clearly announced their intent to UPDATE THE DATA. The lockouts they refer to are easily handled with session timeouts (trivial) and the deadlock is so rare and would definitely be an application bug (in both Oracle and RDB). And to say that RDB only offers pessimisitic locking is false. Optimistic locking is something you have to implement in your code yourself -- regardless of product. It involves adding time stamp type columns to every table and having the update statement verify the timestamp.

How annoying would it be for you to fill out a form, spend all of the time doing the work -- only to be told at the end "sorry, the data you are trying to update was updated -- PLEASE START ALL OVER AGAIN FROM THE VERY BEGINNING"

Thats optimistic locking for you.

I just disagree with this paragraph:

...
More challenging than the technology is overcoming resistance from seasoned development professionals who have been using the trusted SELECT& FOR UPDATE for all of their Oracle careers. These individuals may need to be convinced of the benefits of using optimistic and on large development projects their support will be crucial.
......


I haven't been convinced personally.

Optimistic locking is useful in stateless environments (such as mod_plsql and the like). Not only useful but critical.

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pessimistic locking vs optimistic locking

Antoine Bocti, October 06, 2002 - 12:47 pm UTC

Thanks again Tom, for your point of view it was excellent!

Sorry again for the email bounced! I am very sorry.

Is it possible Tom to have the response for the email bounced? My question was about

Beginning Oracle Programming
and
Professional Oracle 8i Application Programming with Java, PL/SQL and XML.

What was you're contribution to each book?

It's because I want to buy those books but I want to be sure of your contribution especially Professional Oracle 8i Application Programming with Java, PL/SQL and XML.

Thanks Again.

Tom Kyte
October 06, 2002 - 1:37 pm UTC

I wrote one chapter in the Pro Oracle 8i -- not a very large contribution.

As for Beg Oracle Programming -- I wrote 3 chapters for that, reviewed the entire thing and work real close with the other major authors (the guys on the front cover, they report to me actually ;) So, I can vouch for that book.

pessimistic locking vs optimistic locking

Antoine Bocti, October 06, 2002 - 2:22 pm UTC

thanks again Tom!
I will buy the begining oracle programing!

My last question, if I want to be better with Oracle is it better to read a lot and experiment a lot, and be the only one DBA in a entreprise.

OR

Work with a lot of DBA in a entreprise and share the knowledge and read and experiment a bit?

thanks.

Tom Kyte
October 06, 2002 - 4:17 pm UTC

I learned by doing with others. Learning from and with others to me seems to be the better way.

thanks again!

Antoine Bocti, October 06, 2002 - 4:58 pm UTC

That's what I thought!

Pessimistic locking in Java etc ?

A reader, July 29, 2003 - 3:03 am UTC

Hi Tom,
You mentioned using pessimistic locking in java, but that does not really seem to be the way ahead specially if one is using connection pooling,As the session can get used by any other user without the original user doing any change.
For E.g
User 1 selects the record for update,Does nothing and just closes the browser window
User 2 login in an tries to select a record to update, unfortunatly gets the same session as user 1, the application will display the user that record is locked whereas it is not actually locked.

We had developed a client server application where we had used selected for update and it worked beautifully , i did want to implement the same in the webbased application which uses connection pooling, but could not as the developers can't write codes in window close event for browsers events as they can with client server technology.

The only way may be to rollback the connection right after opening,am i on the right track.









Tom Kyte
July 29, 2003 - 7:07 am UTC

of course you cannot use it when the database state is not held across requests.



auto generation of no. / selection of record - how to use LOCK

Raaghid, May 31, 2004 - 1:29 pm UTC

The following is the requirement:

1. In a Mini ERP-inventory module, each stock qty is stored as row in inventory table
With autogenerated serial no. (manual auto generation - no sequence, since skipping not allowed)
2. Generated serial no is the basis for identifying stocks, so NO duplicate is allowed.
3. while doing receipts, to generate a next no. the maximum no. to be locked. (in the multi user system).
4. And while doing issue, the issued record should be locked and while one user is issuing,
the other person, should not use that record.


Bascially, While receipt, new no. should be generated. - hence locking required to avoid duplicate
While issuing, locking is required, to avoid 2 or more user to choose the same record. (I think rowlevel
locking is ok)

Please guide me how to use lock in this above 2 circumstance.

Thanks in advance.

Tom Kyte
May 31, 2004 - 2:16 pm UTC

this truly will be a mini-erp since it'll never scale beyond mom and pop doing work together in the store.


lock table stock in exclusive mode;
select nvl(max(stock_id+1),1) into whatever from stock_table;
perform rest of transaction;
commit;

wonder how you are going to deal with miskeyed data, you certainly cannot delete.

would be one approach. another would be to build a special one row, one column table:

create table stock_number as select 1 stock# from dual;


and then:


update stock_number set stock# = stock#+1 returning stock# into l_your_variable;
perform rest of transaction;
commit;


that would permit concurrent updates (which don't need a new number) and only serialize inserts.

auto generated seiral no. locking - followup question

Raaghid, June 02, 2004 - 3:53 am UTC




Thanks a lot.

Your answer is fine for my top half of the query. (point 1 to 3)

ie., Regarding new no. generation, I think it is better to use approach-2 (stock_number), so that
rest of the users will not have any problem in accessing stock_table.


But while issuing the stock- (each quantity is one row / one serial no. per qty), to avoid duplicate
issue, what kind of lock can be used in the stock table.
(e.g.,, while making INVOICE, stock should be issued out in FIFO basis, the stock allocated for
one invoice, ie.,allocated serial no on FIFO basis, should not be used for other invoice in
the multi user environment)
{Point no 4. in the last posting::: And while doing issue, the issued record should be locked and while one user
is issuing, the other person, should not use that record.}


Please suggest me, how to go about.



Tom Kyte
June 02, 2004 - 8:30 am UTC

you don't need a lock on the stock table.

this:

update stock_number set stock# = stock#+1 returning stock# into l_your_variable;
perform rest of transaction;
commit;

will do the serialization for you already. everyone will jam up on that single row.

Done it

Raaghid, June 14, 2004 - 7:42 am UTC

I have revamped the locking finally and it improved performance immensely.

I used
1. update - returning for new no. generation
2. row level locking (select for update) for issuing the stock

Thanks for the gr8 help.



Budhi, November 18, 2004 - 10:02 am UTC

Hi Tom,

I have doubt here . WHile using MERGE statement, is there any way we can use optimistic or pessimistic locking, if so can u just show me an example.

Thanks a lot



Tom Kyte
November 18, 2004 - 10:53 am UTC

in order to do either -- you need "select" to be involved.

merge -- updates information

optimistic locking -- you read data out and only update it if it did not change.
pessimistic locking -- you lock the data as you read it out AND THEN modify it.


both imply "select" was used.

Budhi, November 18, 2004 - 4:17 pm UTC

Hi Tom,

I am sorry I didnot understand, could you please explain with an example how we can do optimistic and pessimistic locking while doing Merge statements.



Tom Kyte
November 19, 2004 - 10:01 am UTC

do you know how to do it with update? then you know how to do it with merge.

merge isn't any different. you need to start with SELECT and then modify data (merge)

no different than update.


but, with merge, it probably doesn't make sense -- you are doing big batch update, you are syncronizing "target table" with "source data". lost update probably isn't even coming into play here. think about it.

no need for select for update here ?

pasko, November 19, 2004 - 6:10 am UTC

Hi Tom,
thanks for your comments above.
In your Response to the above user, you said, Quote:
In the statement:
"update stock_number set stock# = stock#+1 returning stock# into l_your_variable;"
will do the serialization for you already. everyone will jam up on that single
row.

does that mean i do not need to select the row out for update first ?

That means if i have 3 concurrent users performing that same update statement at the same time , Oracle will make sure that there are no lost updates ?

Is the locking mechanism different if we omit the Returning Clause?


Thanks in advance,


Tom Kyte
November 19, 2004 - 11:18 am UTC

<quote>
another would be to build a special one row, one column
table:
</quote>


not in that case, there is but one row -- you want to serially increment the number and retrieve it. we don't need to read it and lock it and then update it, we can update/lock/read simultaneously.

you have no lost update here -- there is one row, one column and everyones goal is "increment it"

Bulk locking

Vipin, November 23, 2004 - 10:05 am UTC

Hi Tom,

I have a requirement in which I will be getting have to lock a set of rows before doing my dml operation. The scenario is

something like below.

-- Create table
create table TEST2
(
A NUMBER not null,
B VARCHAR2(4),
UPDATED_BY DATE,
PK_COL NUMBER not null
);

alter table TEST2
add constraint PK_TEST2 primary key (PK_COL,A);


insert into TEST2 (A, B, UPDATED_BY, PK_COL)
values (1, 'See', to_date('19-11-2004 16:20:05', 'dd-mm-yyyy hh24:mi:ss'), 1);
insert into TEST2 (A, B, UPDATED_BY, PK_COL)
values (2, 'Che', to_date('19-11-2004 16:20:05', 'dd-mm-yyyy hh24:mi:ss'), 1);
insert into TEST2 (A, B, UPDATED_BY, PK_COL)
values (3, 'Gan', to_date('19-11-2004 16:20:05', 'dd-mm-yyyy hh24:mi:ss'), 1);
insert into TEST2 (A, B, UPDATED_BY, PK_COL)
values (4, 'Bill', to_date('19-11-2004 16:20:05', 'dd-mm-yyyy hh24:mi:ss'), 1);

Now the scenario is I will get a set of rows from front end using an XML, i will bulk collect this into my PL/SQL tables and

then I want to do a bulk update on my table.

Following the procedure which I use to bulk update into my table using Optimistic locking:-

CREATE OR REPLACE PROCEDURE test_lck
(
i_pk_col dbms_sql.number_table,
i_a dbms_sql.number_table,
i_b dbms_sql.varchar2s,
i_updated_by dbms_sql.date_table
)
AS
v_last_date_tb dbms_sql.date_table;
v_error_count NUMBER;
e_bulk_dml_errors EXCEPTION;
e_resource_busy_nowait EXCEPTION;
PRAGMA EXCEPTION_INIT (e_bulk_dml_errors, -24381);
PRAGMA EXCEPTION_INIT (e_resource_busy_nowait, -54);
BEGIN
BEGIN
FORALL i IN 1..i_updated_by.COUNT save exceptions
UPDATE test2 c
SET c.b = i_b(i),
c.updated_by = SYSDATE
WHERE c.pk_col = i_pk_col(i)
AND c.a = i_a(i)
AND c.updated_by = i_updated_by(i);
--
FOR i IN 1..i_updated_by.COUNT
LOOP
IF SQL%BULK_ROWCOUNT(i) = 0 THEN
RAISE_APPLICATION_ERROR (-20107, 'Data has been updated since last read - requery data.');
END IF;
END LOOP;
EXCEPTION
WHEN e_bulk_dml_errors THEN
IF SQL%bulk_exceptions.COUNT > 0 THEN
RAISE_APPLICATION_ERROR (-20105, 'Errors occured while bulk updating.');
END IF;
END;
END;

Following package is used to simulate a condition in which the above procedure will be called:-

create or replace procedure test
AS
v_a dbms_sql.number_table;
v_b dbms_sql.varchar2s;
v_updated_by dbms_sql.date_table;
v_exceptions dbms_sql.varchar2s;
v_pk_col dbms_sql.number_table;
begin
v_a(1) := 1;
v_a(2) := 2;
v_a(3) := 3;
v_a(4) := 4;
v_b(1) := 'ABC';
v_b(2) := 'Kewl';
v_b(3) := 'See';
v_b(4) := 'This';
v_pk_col(1) := 1;
v_pk_col(2) := 1;
v_pk_col(3) := 1;
v_pk_col(4) := 1;
v_updated_by(1) := To_date('11/19/2004 4:20:05 PM','mm/dd/yyyy hh:mi:ss PM');
v_updated_by(2) := To_date('11/19/2004 4:20:05 PM','mm/dd/yyyy hh:mi:ss PM');
v_updated_by(3) := To_date('11/19/2004 4:20:05 PM','mm/dd/yyyy hh:mi:ss PM');
v_updated_by(4) := To_date('11/19/2004 4:20:05 PM','mm/dd/yyyy hh:mi:ss PM');
--
test_lck
(
i_pk_col => v_pk_col,
i_a => v_a,
i_b => v_b,
i_updated_by => v_updated_by
);
end test;

Tom, Now I just wanted to confirm that this is the (optimistic locking) only way when I am dealing with this kind of

conditions. Or would I be able to use pessimistic locking in this case, if so could you please show me how, please observe

that I am doing it for a set of rows selected using a set of pk_cols.


Thanks as always

Vipin


Tom Kyte
November 23, 2004 - 10:32 am UTC

well, whether or not you can use pessimistic locking depends entirely the CLIENT


I fail to see how you could have a trigger in this case that would detect "lost update" -- where does the 20107 get thrown from? who is doing that and more importantly *how* could they do it (seems to me you have insufficient data to detect a lost update here)



If the client is "stateful", you would:


select * from t where ... FOR UPDATE; <<<<== locks rows

and then just

forall i update them






Vipin, November 23, 2004 - 3:10 pm UTC

Hi Tom,

Here the columns test2.pk_col and test.a will never be updated, these columns along with the updated_by (i am very sorry the column name should have been updated_date) will be passed to the front end while they are selecting data for editing and when they are actually updating this, they will pass this back to the back end. In this scenario would this be fine.

Regarding pessimistic locking i have an issue here:- if you observe the queries above, i am updating all rows for which =>pk_col in i_pk_col PL/SQL table
=>a in i_a PL/SQL table.

How will issue a SELECT ..BULK COLLECT INTO..WHERE pk_col = i_pk_col(i) AND a = i_a(i) FOR UPDATE NOWAIT...may be we should have a FORALL for select also ;)

Please comment


Tom Kyte
November 23, 2004 - 7:18 pm UTC

UPDATE test2 c
SET c.b = i_b(i),
c.updated_by = SYSDATE
WHERE c.pk_col = i_pk_col(i)
AND c.a = i_a(i)
AND c.updated_by = i_updated_by(i);

that shows that pk_col and a will not be.


but I'm failing to understand where the user defined -20000 range error comes in or HOW it could even work...


i don't understand your last paragraph. BULK COLLECT (with or without limit) *is* a forall for selects.

Vipin, November 24, 2004 - 11:29 am UTC

The PL/SQL table i_pk_col and i_a will inlcude the list of records which the frond end is interested in modifying. For doing this they also send us the i_updated_by PL/SQL table which will have the list of the update timestamp as of when they retreived the records. Now

=>after they retreived these records for view,
=>and just before this UPDATE is fired for them

any updates by any other user happens then the SQL%ROWCOUNT will be 0 for the UPDATE of these changed records (since the i_updated_by is also there in the WHERE clause.) Now in this case the user defined exception kicks in.

Regarding my last para what I mean is that the following statement is valid :-

SELECT ANY_COLUMN
BULK
COLLECT
INTO ANY_PL_SQL_TABLE
FROM TEST2
WHERE PK_COL = I_PK_COL(3) --WHERE PK_COL(3) = 1
FOR UPDATE NOWAIT;

But the following statement is not valid and I think i need something similar to this for the SELECT FOR UPDATE to work:-

FORALL I IN 1..I_PK_COL.COUNT
SELECT ANY_COLUMN
BULK
COLLECT
INTO ANY_PL_SQL_TABLE
FROM TEST2
WHERE PK_COL = I_PK_COL(I)
AND A = I_A(I)

Hope I made myself clear this time



Tom Kyte
November 24, 2004 - 12:25 pm UTC

umm, if you have the timestamp, you don't need to select them out and lock them - just try to update them.

rowcount = 0, means you lose. someone else updated it.

Vipin, November 29, 2004 - 9:49 am UTC

So Tom,

If we have the updated timestamp then, we need not do the locking. But this means that I can'r use MERGE for when I want to employe some kind of locking right? Because the only kind of locking I can employ here is the optimistic one which I don't know how it will work with the MERGE statement, any comments on this one

Tom Kyte
November 29, 2004 - 3:10 pm UTC

if you wanted to lock before merge, that would be a select for update.

Pretend MERGE was UPDATE.

Why treat it any different?


But mergeing is really for syncing up two data sets. Meaning -- the lost update thingy really isn't happening. Lost update happens when you

a) read out data
b) interact with data
c) update data

and between a and c something else changed the data. You overwrite their update without ever looking at it.

If you just

a) update data

there is no need to "lock it first", you are not a) reading it out, b) working with it and c) putting it back -- you are just UPDATING it blindly. the lost update doesn't really come into play.

A reader, November 29, 2004 - 5:08 pm UTC

Tom,

There can be situations where in I am merging after READING out and INERACTING with data,:-

I have a situation in which I am returning a data set to the front end (dataset includes updated_date column also). Front end is modifying the dataset by updating and adding new rows. Now the frond end passes me an XML (which includes the updated_date column which I had passed earlier), in which I have the dataset that has to be added/updated to my table. I will be using a Merge and here I want to update only if the data has not been modified. how would I be able to use Merge here? Or should I go for SELECT FOR UPDATE just before the MERGE so that I serialize the operation just before MERGING.

Tom Kyte
November 29, 2004 - 7:07 pm UTC

great, as I keep saying "pretend this merge were to be an update, do the same thing you would do with an update"

you will have to select for update from the table with a "where (primary key, last_updated) in (the set of primary keys/last updated they send to you in XML)"

You will have to make sure you get back all N rows (you need to remember N in your protocol somewhere)

If count = N, you are good to go.

Banking Appliction - Pessimistic vs Optimistic

Totu, February 13, 2006 - 3:57 am UTC

Dear Tom.
As from this discussion I noticed that you prefer Pessimistic mode.

Lets say there is Table1 in DB.

2 users at the same time view it at client side using
C#.net form (f.e: data grid--> this only select * from table1).

Now user1 wants to update active record inside data grid. I do as below:
1. Get primary key value from local table;
2. do another sql as "select * from table1 where keyid = .. for update".
3. Do changes and commit work.

Of course, 2nd user's application will freeze, if he want to update the same data. This is not desired, hoc can I detect that that row was locked? Doing it I can can tell the user that this row was locked bu another and etc...

Is it OK for true banking application (2 tier: OracleDb and WindowsForm)?

Thanks in advance.




Tom Kyte
February 13, 2006 - 8:32 am UTC

... you prefer Pessimistic mode. ...

IF and ONLY IF your environment can support it. Client Server - stated connections - use "for update" locking and pessimistic concurrency control. It is "easier", end users lose less often.

Stateless environment, you cannot use pessimistic, you have to use optimistic.

DataSet Locking

JHT, September 21, 2006 - 12:17 pm UTC

Tom,

I'm trying to implement pessimistic locking in for a stateless application and to prevent lost updates. The issue is that I want to lock a "data set", not just one row.

Say I have a dept and emp table and a GUI that shows a particular dept and allows the user to put emp's into and out of that dept. If 2 different users are working on the same dept, then only whoever hit's save first will get his change (new emp's and/or remove emp's)in.

The scenarios I want to are:

Starting point: both users open up the page for dept A and see the same data

1) User1 adds emp10 to deptA and user2 adds emp20 to deptA and both hits save. Whoever hits save first gets his emp addition in. The other user will be prevented because "data set" was updated underneath.

2) For deletes, it's not a problem, since send delete will hang and then return 0 rows deleted if the row was deleted by another user between the viewing and commiting of the delete.

The main issue I have is with inserts, because you cannot lock or prevent the insert of rows that have not been inserted yet. The primary key on the mapping table is on both deptid and empid.

Tom Kyte
September 22, 2006 - 2:16 am UTC

why not serialize then at the level of the DEPT record.

That is, make part of the transaction be a modification also to the parent record to update a "last modified" column.

DataSet Locking

JHT, September 21, 2006 - 2:31 pm UTC

Sorry, wrong term... Meant to say optimistic lock in a stateless environment.

SELECT FOR UPDATE

Lise, October 04, 2006 - 10:06 am UTC

Hi Tom,

Imagine this scenario:

Term1: select max(account_nbr)
into l_account_nbr
from table1
for update NOWAIT;

Term2: select max(account_nbr)
into l_account_nbr
from table1
for update NOWAIT;
== Term 2 has to wait

Term1: insert into table1 (account_nbr)
values(l_account_nbr + 1);
commit;

==Lock is released for Term2 and it returns the account_nbr as it was before Term1 added a new record to it.
Term2 will then fail when inserting the l_account_nbr + 1 since it is a unique key.

Does this mean that I need two SELECT FOR UPDATES one after the other so that I reselect the new MAX account_nbr?




Tom Kyte
October 04, 2006 - 5:13 pm UTC

you cannot use max with select for update.

and select for updates are just like updates - they are subject to restarts when the underlying data is modified (or 8177, cannot serialize access in a serializable transaction)

Without MAX

Lise, October 09, 2006 - 9:16 am UTC

Hope this is not a silly question, just need to understand it.

If I changed the query to:
SELECT name
INTO ln_name
FROM table1
WHERE a_number = (SELECT MAX(a_number) FROM table1)
FOR UPDATE;

Say the max a_number is currently 10.

When two sessions are running at the same time, the second session will get locked.
The first session will add a new record to table1 with a_number set 11. When session one has committed, the second session will continue, but the value returned is 10 and not 11.

I can resolve this by having the SELECT FOR UPDATE statement executed twice one after the other. There must be a better way though?



Tom Kyte
October 09, 2006 - 9:28 am UTC

I'd need to better understand the entire flow of the logic here to answer with a better response.

I don't understand the logic that says 'lock the record with the largest a_number', how that would play in real life?

Especially with concurrent users doing this - why would the first user that got blocked here want to process record 11 all of a sudden?

If this is a queue, I might suggest - well - a queue. but this would be a strange queue that by dequeueing and processing a record, we create yet another record to process in the same queue?

This is how it is...

Lise, October 09, 2006 - 11:18 am UTC

This particular field is not maintained by a sequence (I wish it was).
When I want to add a new record, I therefore need to go and select the current max value and add 1.
The application is running on-line with multiple users, so if two or more sessions decide to add a new record at the same time, they will end up with the same max value. The first session to commit will be ok. However the next one etc. will fail due to unique key constraints (the max value is a unique key on this table).

I thought I could prevent this by doing a SELECT FOR UPDATE of the current row with the max value. It does this ok, but when unlocked, the WHERE clause is not reselecting.

Tom Kyte
October 09, 2006 - 11:32 am UTC

the logic should therefore be (sigh...)


dbms_lock to allocate a constant named lock, to serialize us
your logic


you need to entirely serialize. You could also just:


lock table T in exclusive mode;
your logic


in hindsight, that is what you should do - to serialize operations on this table entirely. dbms_lock would cause just the inserts to serialize (permitting others to udpate/delete) the lock table would just be "obvious"

Thank you (again)

Lise, October 09, 2006 - 5:58 pm UTC


A reader, October 10, 2006 - 8:13 am UTC

Tom,

In a 'Select * from tableA where primary_key_value = 1 for update of colA nowait' statement, is it the same as 'Select * from tableA where primary_key_value = 1 for update of colA,colB nowait'. That is for update of any one column is enough really?


Similarly if I had more than one table in the Join and I want to lock all the joined rows, then is it correct that I need to represent a minimum of one column for each of the table?

Thanks

Ravi

Tom Kyte
October 10, 2006 - 12:00 pm UTC

it'll lock the entire row, the "for update OF" is useful in a multi-table join to identify the table that should have the row locked, yes.

pessimistic locking vs optimistic locking

Himanshu, September 26, 2008 - 12:53 pm UTC

Hi Tom,

I have a question, about how to convince that Pessimistic is bad for scabality as we are getting a deadlock.

My situation is we have a systm from a vendor which instead of using sequence to generate a serial number is storing the table name and the max number in a table table. Ever time if a record is inserted in a table it updates the table by incrementing the number.

The problem started when some developer wrote a Oracle function ( instead of using a function which was selecting and incrementing the value by 1 and updating the transaction ) ,in which they use a FOR UPDATE statement , lock the row, increment the number and commit the transaction.

After the function FOR UPDATE was used we started getting deadlock errors once a week. My problem is how do is convince other's that FOR UPDATE is the reason for the deadlock as they want the code to be changed at other place where we are UPDATING with out using FOR UPDATE to start using FOR UPDATE.
There is not much we can do as there are different programs from the vendor which are not using FOR UPDATE and we cannot change it.

thanks
Tom Kyte
September 26, 2008 - 1:22 pm UTC

... about how to convince that Pessimistic is bad for scabality
as we are getting a deadlock.
...

you would be incorrect.


... My situation is we have a systm from a vendor which instead of using sequence
to generate a serial number is storing the table name and the max number in a
table table. Ever time if a record is inserted in a table it updates the table
by incrementing the number. ...

that is just a bad idea - it forces serialization, that doesn't have to do with pessimistic vs optimistic inasmuch as the vendor has explicitly and consciously chosen to implement an algorithm that obviously serializes.

the for update has nothing to do with this, the for update - given the logic you prescribe - is actually necessary for correctness.

the code before was broken and would lead to duplicates. The code now is correct with the for update.

Actually, if they HAVE to do this, the code should just be:

  update t set val = val + 1 where name = :X return val into :Y;
  commit;


lock, update, retrieve new value IN ONE STATEMENT. Otherwise you do have to

a) lock row (for update)
b) retrieve value
c) update it

or

a) update it
b) retrieve it


if you

a) retrieve value
b) update it

two people can do A - get the same value, serialize on the update but then have duplicates. Only by serializing the read of the row can you do this correctly.


Optimistic Locking

A reader, June 01, 2009 - 1:40 am UTC

Tom,
I have a question for you reg. optimistic locking and how best to implement it in our case. We have a front end java-based GUI that allows users to query/update data. So you could have a user query up some (related set of) information from the database, have a look at it and then decide to update it.
If we have the case where user1 is updating a record, and user2 tries to update the same record, we want to display an error to user2 saying the record is currently being worked on. However they do not want to use FOR UPDATE b'cos of problems with connection pooling and sessions being lost for whatever reason etc. They also do not want user2's session to hang whilst waiting on the lock.
What they want to do is allow user1 a max of say 10 mint to finish the update. If it is within the time they we allow the update. After the 10 mint, user2 can then query/update this record.
Have you any suggestions as to how we could do this? We are currently looking at putting in a timestamp column but I'm unsure exactly how this would work.

(p.s:- if we use a FOR UPDATE and the user closes the browser or the connection dies for whatever reason, will the row(s) still be locked? And will the next person using the same session from the connection pool be then the 'owner' of this lock. I don't understand how it would work with a connection pool to be honest).

Thank you for your input
Tom Kyte
June 01, 2009 - 8:03 pm UTC

... If we have the case where user1 is updating a record, and user2 tries to update
the same record, we want to display an error to user2 saying the record is
currently being worked on. ...

confusion - that is what is called PESSIMISTIC locking.

you were asking about OPTIMISTIC locking - it would NOT CARE (it is optimistic see, hoping the other guy will get bored and leave) that the other sessions that might also see this row will not modify it.


so, be precise, do you really want a) pessimistic or b) optimistic and do you really know the difference between them?

locking

sam, June 01, 2009 - 9:54 pm UTC

Tom:

The above question is for a web application (not client/server)

I thought due the stateless nature of http you can't use pessimistic locking on the web and it has to be optimistic.

On the WEB every web page gets new oracle session id. Orcle will not know who the person issued the select for update using its own session ids.
Tom Kyte
June 02, 2009 - 7:17 am UTC

the web does not HAVE TO be stateless, there are many implemented models whereby it is stateful (Oracle Forms for example - "web delivered" but stateful).

You describe one method (the one mod_plsql uses for example), there are others however.

more info...

A reader, June 02, 2009 - 7:40 am UTC

Hi Tom,
A bit more information for you. Basically they want the functionality of the FOR UPDATE but only for a short period of time! So the first user to update row(s) would have about 10 mint (say) to finish his/her work. If within those 10 mint another user tries to update the same row(s) they will be shown an error message similar to 'somebody else is updating this stuff now. pls try back after 10 mint'.
They do not want the second user to block on the first user, but have instand feedback saying they can't do the update now.
The reason they do not want to use the FOR UPDATE clause is the connection is stateless. So instead they are going to write a timestamp to the table when the first user starts the update. There will be some sort-of countdown I guess to ensure the user does not go over the 10 mint. And if the user has completed the update in 10 mint, they are going to allow it. Else display an error.
If a second user attempts to update the same row(s), the code will check the table for the timestamp value and if it is within the 10 mint then an error will be displayed to the user. Else a new timestamp entered into the table and the user allowed to update
I have not done soemthing like this before. So I'm not even sure how/if this will work. So any pointers/suggestions would be appreciated. I am told that the probability of two users updating the same record is actually high!

Thank you
Tom Kyte
June 02, 2009 - 7:56 am UTC

it will work if EVERYONE does it consistently - so suggestion:

a) do not do this in the application
b) write a stored procedure that does this - implement it ONCE and everyone that touches this table uses this approach
c) revoke select on this table from everyone
d) grant execute on that procedure to all

then it can be done consistently...

Thank you

A reader, June 02, 2009 - 8:38 am UTC

Thank you for a really quick reply.

But therein lies the problem. They DO NOT want to do it in stored procedures. I get the usual database independence, portability and java is the best argument over and over and...... They are going to be implementing it in Java and all database access/update will be through JPA. Sigh!

Thank you for your input tho.


Pointers, January 12, 2010 - 11:05 pm UTC

Hi Tom,

Thanks for your help all the time.

I have been reading this article to understand how
optimistic and pessimistic locking works.

You have mentioned most of the cases, the term
'stateless connection' and 'stateful connection'
I have very less idea about these terms. I would like to
hear what exactly stateful connection and stateless connection
what kind of connection goes in the catagory of
stateful and stateless conncetion

It will be more understandable if hear from you in your words.

Regards,
Pointers



Tom Kyte
January 18, 2010 - 3:59 pm UTC

in a client server application - you have a stateful connection, you login to the database, you own that connection, you are the only one that uses it. YOu log it out later.

A client server application would have what is known as a stateful connection, the 'state' of the connection is never broken. You own it from start to finish.


In a typical 3 tier environment - you do not own the connection. Someone else connects to the database and holds the connection in a pool. You ask for a connection from this pool - you get one - you use it for a little bit - and you give it back. The next time you need a connection - you'll get one but probably a different one. You are not maintaining a stateful connection to the database in your application - rather you use many connections, a little bit at a time.

That is a stateless environment, your application does not maintain a persistent state, connection, to the database.

Great

A reader, January 19, 2010 - 1:58 am UTC

Great thanks Tom for your words as usual....
thanks for the clarity, it was good understanding...

Regards,
Pointers

A reader, May 14, 2010 - 6:58 am UTC

Hi Tom,

I have been reading your book and got a doubt about the 'Lost update' issue .

Address and telephone numbers are two different columns in a single record - As the developer wrote the code such that it has got 'full' record even though the user wants to update a single column - the 'lost update' issue occurred in your example.

In the below example :
let us say both the users are updating the same column..

EMP table has a column phone number ='987654'

USER 1:
select phone into x from emp where emp_no=123;
Local memory (pga/uga):
===================

x:='987654'

the user1 has realised that it has got the wrong value.


USER 2:
select phone into x from emp where emp_no=123;
Local memory (pga/uga):
===================

x:='987654'

the user2 has realised that it has got the wrong value.

USER1:
---
user1 decided to update with the new phone no (whatever he knows)
now he has 'stored ' the new value in the same variable :
x:='9874' (new value)

update emp set phone=:x where emp_no=123
commit ;

USER2:
---
user2 decided to update with the new phone no (whatever he knows)
now he has 'stored ' the new value in the same variable :

x:='1234' (new value)

update emp set phone =:x where emp_no=123
commit;

===================================================
So, now the final value of the phone='123'
user1 next day realised that he lost his changes ..

In the above case: if they use the update statement as below :
when the users decides to update , they will use some other variable 'Y'-
now Y:='9874' (new value)

update emp set phone=:Y where emp_no=123 AND PHONE=X
commit ;

THEN the second user will get '0' rows updated . of course then again he has to query the database

Can't we 'avoid' the last update issue ?? instead of using 'select for update' tool ?

is my understanding correct actually about the issue ?

Thanks for your time .


A reader, May 14, 2010 - 7:16 am UTC

Sorry , after reading further topics in your book - I have understood that its is all 'OPTIMISTIC LOCKING' that you described in your book - sorry for that .. thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library