Is it not happening outside the database then?
Sanjay Jha, December 21, 2004 - 2:57 pm UTC
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?
December 21, 2004 - 3:17 pm UTC
yup
my question on this is
Somesh, January 15, 2005 - 5:21 pm UTC
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.
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 ?
A reader, January 16, 2005 - 9:57 am UTC
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.
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
Somesh, January 17, 2005 - 1:32 pm UTC
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
miquel, May 29, 2005 - 2:54 pm UTC
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 ...
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
miquel, May 29, 2005 - 4:24 pm UTC
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.
Helena Marková, May 30, 2005 - 8:22 am UTC
Good Job tom
satyanand, July 25, 2005 - 3:12 am UTC
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
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
satyanand, July 25, 2005 - 9:45 pm UTC
Many thanks for an early reply.now i understand the Lost update clearly. You are doing a great job.
Thanks again.
Confirmation needed
satyanand, August 21, 2005 - 11:01 pm UTC
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)
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
satyanand, August 21, 2005 - 11:07 pm UTC
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).
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
satyanand, August 22, 2005 - 9:32 am UTC
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.
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
ian galllacher, September 01, 2005 - 6:38 am UTC
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
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
A reader, September 01, 2005 - 8:04 am UTC
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
September 01, 2005 - 9:17 am UTC
it checks, it checks column by column. (forgot the nowait part)
A reader, September 01, 2005 - 8:09 am UTC
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
Yuan, November 16, 2005 - 3:21 pm UTC
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?
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?
Yuan, November 17, 2005 - 9:10 am UTC
Don't need to now. More of a theoretical question at the moment.
November 18, 2005 - 7:08 am UTC
then see b)
but especially a) and c) ;)
2 identical updates at the same time
A reader, July 16, 2007 - 9:52 am UTC
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
Mircea, February 12, 2019 - 11:41 am UTC
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
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.