Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sanjay.

Asked: December 21, 2004 - 2:10 pm UTC

Answered by: Tom Kyte - Last updated: February 12, 2019 - 5:25 pm UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,
I was reading your book expert one-on-one and in Chapter 3: Locking and Concurrency, you have mentioned a scenario of lost update.

I was trying to simulate that and I did not succeed in doing the test. I tried to update emp table in session A, it was completed. However without committing I opened another session B and tried to update the same table, it did not get a lock and instead was hanging until I released the lock on table by rollback or commit in session A.

So Oracle would not allow the "lost update" to take place by itself, is that correct?

Session A
----------
scott@TEST> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ------------------------------------------------
HOST_NAME
----------------------------------------------------------------------------------------------------
VERSION STARTUP_TIME STATUS PARALLEL
--------------------------------------------------- --------------- --------------------- ---------
THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS
---------- --------------------- --------------------------------- ------------------------------
SHUTDOWN_ DATABASE_STATUS
--------- ---------------------------------------------------
INSTANCE_ROLE
------------------------------------------------------
1 test
mrpp
8.1.7.3.0 17-DEC-04 OPEN NO
1 STARTED ALLOWED
NO ACTIVE
PRIMARY_INSTANCE


scott@TEST> update emp set sal=sal+20;

14 rows updated.


Session B
---------
scott@TEST> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ------------------------------------------------
HOST_NAME
----------------------------------------------------------------------------------------------------
VERSION STARTUP_TIME STATUS PARALLEL
--------------------------------------------------- --------------- --------------------- ---------
THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS
---------- --------------------- --------------------------------- ------------------------------
SHUTDOWN_ DATABASE_STATUS
--------- ---------------------------------------------------
INSTANCE_ROLE
------------------------------------------------------
1 test
mrpp
8.1.7.3.0 17-DEC-04 OPEN NO
1 STARTED ALLOWED
NO ACTIVE
PRIMARY_INSTANCE


scott@TEST> update emp set sal=sal+30;


After this, I go back to Session A, and issue rollback or commit:

Session A:
----------
scott@TEST> rollback;

Rollback complete.

That releases the lock from the table and I get following in Session B:
scott@TEST> update emp set sal=sal+30;

14 rows updated.

Now I go back to Session A and issue:

scott@TEST> select * from emp
2 for update nowait;
select * from emp
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Again, Oracle prevented any such lost update scenario by itself.Can you demonstrate this scenario?

and we said...



You missed the point of the lost update I think. What you are seeing is normal "two people cannot lock the same piece of information at the same time", that happens normally.

A lost update happens when:

session 1: read out Tom's Employee record

session 2: read out Tom's Employee record

session 1: update Tom's employee record

session 2: update Tom's employee record


Session 2 will OVER WRITE session 1's changes without ever seeing them -- resulting in a lost update.

Consider the web application that allows people to update their addresses and their phone numbers. the update statement used is always:

update emp set address = :a, phone = :b where empno = :x;


Ok,

YOU pull up my record on the web.

I pull up my record on the web.

YOU update my phone number (you were given this task, update these peoples phone number)

I update my address (i was given this task, I just moved)


Whomever updates last -- wins and WIPES OUT the others update -- putting either the phone number or the address back the way it was. We "lost that update"



That is what we are trying to protect against.



and you rated our response

  (19 ratings)

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

Reviews

Is it not happening outside the database then?

December 21, 2004 - 2:57 pm UTC

Reviewer: Sanjay Jha from Toronto

Tom,

If I have understood correctly the data is read and taken out from the database and is in the application's memory area and then we are coming back and issuing the update statements with implicit commits in two different sessions.

The scenario is that first session read the data before second session and before it will issue its own update, second session has issued the update and committed. When first session issues its own update (after the second session is over), it erases the changes made in the second session.

However, if anyone had read the data between the completed second session and before first session had completed its transaction, would see the data as committed by second session.

Oracle did not loose any data but the application "made it appear so" and that is why need for pessimistic lock to prevent any such incident.

Did I understand the "lost update" correctly now?

Tom Kyte

Followup  

December 21, 2004 - 3:17 pm UTC

yup

my question on this is

January 15, 2005 - 5:21 pm UTC

Reviewer: Somesh from San Francisco , CA

I dont think the phone/address example you mentioned would cause a lost update.

If both of you tried to update only the phone ( for eg.)
, only the second chnage would be recorded
in the Database.
But if one is updating only the address, and the second is
updating only the phone. Both changes should be saved correctly.

And why should this happen?
The two sessions do not get their own copy of the
disk data in memory. They both share the same memory
copy of that data. They modify the same memory copy of
the data. And as long as they do not overwrite each others
changes in memory, things should be fine.
This is what makes computers *RELIABLE*.

Anybody who has studied Operating Systems and
knows about memory management , will tell you this
fundamental issue.


Tom Kyte

Followup  

January 15, 2005 - 6:00 pm UTC

please reread the answer.

<quote>
consider the web application that allows people to update their addresses and
their phone numbers. the update statement used is always:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

update emp set address = :a, phone = :b where empno = :x;
</quote>



Anyone who has written a database program for money would recognize this for what it is -- the CLASSIC example of a lost update, that happens in thousands of programs, thousands of times -- because the people writing the database programs don't really understand transaction processing.

This is what anyone who was studied database and transactional processing will tell YOU about this fundemental simple, common database issue.

Maybe you just read it too fast and didn't get it.

what if i don't read the Data out ?

January 16, 2005 - 9:57 am UTC

Reviewer: A reader

Hi Tom,

thanks for Great follow-up to the previous user:

My Question is that, if we do not need to Read/Inspect the Data first, then is it ok if we issue Updates without locking the Data ?

What kind of Locking mechanism would be used if someone is updating a Database Record from a Web Service?

Thanks in advance.



Tom Kyte

Followup  

January 16, 2005 - 11:44 am UTC

if you do not need to read/inspect the data first -- it is OK to just update. This occurs in BATCH all of the time.

In a web facing application though, the cases where you would not need to "look and lock" before updating are rare.

You read a record out 5 minutes ago and put it on a web page.

Unbeknownst to you -- 50 other people did too.

You do not want to overwrite their changes.


people on the web use things like:

o maintain a timestamp field on the record, read it out when you paint the page for the enduser to use. read and lock the record and verify it is the SAME when you go back to update . If not, someone else updated the record.

o compute a checksum of the fields you read out. read and lock the record and verify the checksum is the same when you go back to update (bonus here is that if I work on fields A, B and you work on C, D -- we can both work on the same record some I compute the checksum on A,b and you on C,D -- we won't bump into eachother)

We agree

January 17, 2005 - 1:32 pm UTC

Reviewer: Somesh

I think we both agree on the issue.
The only issue was of misreading.
I did'nt read the SQL very well, and went
by the description,the two of
which do *differ* in the post.
On a second look at the query
yes updates will be lost.

multiple selection

May 29, 2005 - 2:54 pm UTC

Reviewer: miquel from Catalunya, Spain

I have read that chapter on Expert one-on-one too, and i must say it make me understand the concept of lost-update more clearly than other manual or book anywhere (as usual).
The scenario i'm working now is the same , but with multiple selection of records to update:
- the forms screen shows a tree component
- the user selects multiple records to update hitting one button
Given this, for pessimistic locking:
- i open and close a cursor 'for update nowait' with the selection in the where clause to lock the rows (if i can)
-But i am not sure about the best way to look if someone has already changed any of the rows selected, in the time between the query and the selection.
I think i can count the records selected, and count the rows on the database table with the 'old values'. If they differ , the data on the screen is stale, and the user must requery.
What dou you think of that? There is a better way?
Thanks Tom for your great job ...


Tom Kyte

Followup  

May 29, 2005 - 3:08 pm UTC

you would need to lock the records probably one by one in this case


for each record selected
loop
begin
select * from t where pk = :bv and c1 = :c1 and .... cN = :cn
for update nowait;
exception
when resource busy (ora 54) then Sorry, record(s) locked by someone else
you lose
raise
when no data found then Sorry, someone modified the data already, you must
requery the information
raise
end
end loop



Unless you have some timestamp field that you can use instead of a column by column interrogation.

thanks

May 29, 2005 - 4:24 pm UTC

Reviewer: miquel from catalunya, spain

Given that only two columns can be modified , i'll take that logic,it's much better.
Thanks again , i'm working now on the module at home and your response is faster than my compiler.

May 30, 2005 - 8:22 am UTC

Reviewer: Helena Marková from Bratislava, Slovakia


Good Job tom

July 25, 2005 - 3:12 am UTC

Reviewer: satyanand from tokyo, japan

thanks for making the lost update problem easy to understand.This is the most simplest and comprehensive explanation of the lost update. I have one more thing to ask regarding the lost update if we are using a timeStamp Field to select for update then why use a select for update at all, we could say

update tableName set (col1 = 'XXX',col2 = 'yyyy' ......)where timeStamp = 'previousTimestamp' in which case it would be impossible to miss out the changes made by other users and this would eliminate locking the rows. It would prevent users from missing out data upadated by other users since they would never be able to overwrite data themselves(because of the timestamp clause in the update)which would not be equal if other users had updated the data unknowing to the current user.

what do you say tom am i missing out something

Tom Kyte

Followup  

July 25, 2005 - 7:43 am UTC

select for update permits a "nowait" or "wait (n)" -- update doesn't

if you don't care about getting blocked for a potentially indefinite amount of time, you don't need to select for update it in an optimistic locking scheme.

Thanks very much

July 25, 2005 - 9:45 pm UTC

Reviewer: satyanand from tokyo , japan

Many thanks for an early reply.now i understand the Lost update clearly. You are doing a great job.

Thanks again.

Confirmation needed

August 21, 2005 - 11:01 pm UTC

Reviewer: satyanand from tokyo japan

Tom i have a continuation to ask regarding the lost update

say i have a emp table with eName and salary

eName salary
-------- --------
john 200
smith 200
adam 300

USER A would like to update the salary of employees whose salary is equal to 200

so i proceed as follows
select * from emp where sal = '200' for Update no wait;

this would lock the rows
eName salary
-------- --------
john 200
smith 200

and then USER A would issue a command to update the rows like

update emp set sal = '400' where sal = '200';
commit ;


this would effectively update the two rows locked by the previous select for update right?

but consider the situation when after USER A obtains the lock on

eName salary
----- -------
john 200
smith 200

USER B would insert a new row into the emp table with
eName = 'king'
salary ='200'

and then user A issues the command to update the rows with salary ='200'

all three rows are updated.

isnt this a kind of lost update(in the reverse) since user A without seeing the data
eName salary
------ --------
king 200

has updated the data for this row also along with other data which he has locked also.
how can you make sure that user A only updates the rows which he has not locked and not other rows which also meet his criteria but which he has not locked.(talking in the context of a web application)


Tom Kyte

Followup  

August 22, 2005 - 7:27 am UTC

serializable isolation would prevent the phantoms from being read.


However, it seems unlikely a web application would operate in this mode as they are normally in a stateless environment (eg: they cannot use pessimistic locking with FOR UPDATE at all since the select and the update are generally done on different 'pages' and hence different transactions)

In that case, the web application would read out the "sal=200" rows and remember their primary key.

upon the user ok'ing the update, they would update by primary key AND sal=200 to ensure the salary was not modified since they read it out.

sorry a typing mistake

August 21, 2005 - 11:07 pm UTC

Reviewer: satyanand from tokyo japan

to the above question i meant to say

how can you make sure that user A only updates the rows which he has locked and not other rows which also meet his criteria but which he has not locked.(talking in the context of a web application).


instead of

how can you make sure that user A only updates the rows which he has not locked and not other rows which also meet his criteria but which he has not locked.(talking in the context of a web application).




Tom Kyte

Followup  

August 22, 2005 - 7:28 am UTC

serializable in a single transaction.

but in a web application, probably not applicable as there is a "commit" generally between the read and display of the data and the actual modification.

Is my understanding correct

August 22, 2005 - 9:32 am UTC

Reviewer: satyanand from tokyo japan

Hi, Tom thanks for the reply.I would like to summarize the dicussion ,just let me know wether i am right.

Time T1
user A selects two employees for updating their salaries which are displayed on his screen.(which was done through a simple select statement)
Time T2
meanwhile user B inserts another employee who would be eligile to be processed by User A.(If the logic to update was based only on salary)

Time T3
User A OK's the update of the two employees displayed on his screen

The point is, to remember the primary key of the two employees which user A is updating and update their records based not only on salary but, based on primary key and salary.
So we proceed as follows.
1.
We obtain a select for update lock based on primary key of the two employees.
2.
Do an update of the two employees records based on primary key and salary and

3.Commit releasing all locks.

Is my understanding correct?

thanks for the answer and time in advance.



Tom Kyte

Followup  

August 23, 2005 - 3:54 am UTC

no, you are missing the OPTIMISTIC part of the locking here.

time t1: application selects out primary key AND SALARY

time t2: whatever....

time t3: application wants to update the data.


a) select for update the data:
where primary_key = :PK AND SALARY = :SALARY

if you get zero rows - then someone else changed the salary or fired the guy.

if you get the row - then you can update it.

if you get blocked, you decide whether you want to wait.




lost update

September 01, 2005 - 6:38 am UTC

Reviewer: ian galllacher from Bonnie Scotland

very interesting but would comment that I do not use Sql extensively to maintain my database but use Oracle Forms
which keeps me away from all the nasty issues !

In Forms dont have worry about lost update ( I hope !) eg

User 1 accesses record 1 ok
user 2 accesses record 1 ok
user 1 amends a field on record 1 ( doesnt commit )
user 2 tries to amend any field on record 1 - gets message saying in user, please re-query
user 1 commits record
user 2 re-queries and picks up amended record

Have found asktom very useful for picking up new techniques
and browse through posts each day now

Look forward to any comments

Ian



Tom Kyte

Followup  

September 01, 2005 - 6:57 am UTC

forms does automatic pessimistic concurrency control for lost update prevention - it is very very nice that way.


when you start to modify a record (before you actually change it) forms does this:


for i in 1 .. 3
loop
select * from t
where rowid = :block.rowid and c1 = :block.c1 and <all columns in block>
FOR UPDATE NOWAIT;

if got the row -- you win, return SUCCESS.
if ora-54, resource busy -- try again.
if no data found - you lose, someone has modified it already
end loop
return FAILURE


September 01, 2005 - 8:04 am UTC

Reviewer: A reader

Tom,
Doing a Trace it looks like the Form does a query with "for update no wait" with the where clause having the ROWID but not any of the Block values.

So if one of the column values has changed (another session modifying and committing), how does Forms discover data that it is trying to Lock has been modified.

Thanks



Tom Kyte

Followup  

September 01, 2005 - 9:17 am UTC

it checks, it checks column by column. (forgot the nowait part)

September 01, 2005 - 8:09 am UTC

Reviewer: A reader

Sorry Tom, I think I know the answer to the previous Post, doing a little more digging:

1)Forms actually does a "Select xx....for update of xx nowait"
if that does NOT fetch a row, the row has been deleted and Forms returns an error

if it returns a value the value of xx is compared with value of :block.xx and if they are NOT similar Forms tells data modified by another user.

Connection Pooled Web App Transactions

November 16, 2005 - 3:21 pm UTC

Reviewer: Yuan from Newark, NJ USA

How would one implement a transaction in a web app that spans more than one http request? Does XA handle this? If so, is there a .NET equivalent?

Tom Kyte

Followup  

November 16, 2005 - 6:17 pm UTC

don't know about .nyet but

a) i wouldn't do it
b) if forced to do it, it'd be j2ee and I'd be joining back to a stateful session in the middle tier that keeps a connection to the database for itself
c) but i still wouldn't do it.


why do you believe you need to?

November 17, 2005 - 9:10 am UTC

Reviewer: Yuan from Newark, NJ USA

Don't need to now. More of a theoretical question at the moment.

Tom Kyte

Followup  

November 18, 2005 - 7:08 am UTC

then see b)

but especially a) and c) ;)

2 identical updates at the same time

July 16, 2007 - 9:52 am UTC

Reviewer: A reader

hi tom,

just for my understanding. given 2 identical updates:
ddl1) "update t set y = 0 where y between 1 and 1000;"
ddl2) "update t set y = 0 where y between 1 and 1000;"

i guess if these 2 statements are executed slighly one after another the second one is blocked and RESTARTED by oracle automagically TO AVOID LOST UPDATES after the first one is finished?

==>>
10:00:00 exec ddl1
10:00:01 exec ddl2; blocked by ddl1
10:00:05 ddl1 commits; ddl2 is restarted by oracle at scn/time 10:00:05

is this correct? case yes how is this magic called?

Lost update or Last commit wins problem

February 12, 2019 - 11:41 am UTC

Reviewer: Mircea from Moldova

Tom,

I am just reading "Java Persistence with Hibernate Second edition", and the problem you described as "Lost update" in the book is named "Last commit wins".

The problem of lost update they describe as having two transactions that update the same column on the same row. Afterwards one commits, another one rollback causing both changes to be lost.

Thanks
Chris Saxon

Followup  

February 12, 2019 - 5:25 pm UTC

Tom is talking about two sessions updating the same rows and columns. Both users execute actions that run this SQL:

update emp set address = :a, phone = :b where empno = :x;


Whoever commits last "wins", keeping their changes and overwriting the other person's. So I guess you could call it "last commit wins" too.

Afterwards one commits, another one rollback causing both changes to be lost.

I don't understand how that leads to a lost update. If one session rolls back, it only undoes its work. You can't undo work committed by another session by issuing a rollback.