Home » SQL & PL/SQL » SQL & PL/SQL » lost updates
lost updates [message #223663] Sat, 10 March 2007 03:31 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

PLEASE EXPLAIN LOST UPDATE WITH A GOOD PRACTICAL EXAMPLE
Re: lost updates [message #223665 is a reply to message #223663] Sat, 10 March 2007 03:36 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


and can you please explain to us what is a lost update? is it related to oracle .what exactly is it?sorry for my ignorance,but i have never come across such a thing.



regards,
Re: lost updates [message #223687 is a reply to message #223665] Sat, 10 March 2007 06:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Lost updates are a result of a bad transaction and locking strategy. It means that when you have two sessions, the following can happen:

Session 1                                 Session 2
Read row x                                   
Busy changing things in form              Read row x
Issue an update                           Busy changing things in form
Commit
                                          Issue an update
                                          Commit.

Note how the update from session 2 is based on the situation PRIOR to session 1's update. Session 1's update is called a lost update.

The solution to this is either to lock the row upon reading (bad for scalability), called pessimistic locking, or check prior to your update whether the row in the database is still the same, and lock that row in the database at the same time, which is called optimistic locking.
The downside of pessimistic locking is that you run the chance of locking rows without the need (the viewer did not intend to change anything), thus prohibiting anybody else access to that row.
The downside of optimistic locking is that you run the risk that you have to make your changes all over, because in the time between fetching your row and the update, somebody else made a change to that same row.

[Edit: added locking text]

[Updated on: Sat, 10 March 2007 06:58]

Report message to a moderator

Re: lost updates [message #223769 is a reply to message #223663] Sun, 11 March 2007 04:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Usually you do following:
Session 1                                 Session 2
SELECT * FROM EMP WHERE ID = 19           
Busy changing things in form              SELECT * FROM EMP 
                                          WHERE ID = 19
UPDATE EMP SET ename = <new_value>        Busy changing things
WHERE id = 19 AND ename = <old_value>     in form
IF SQL%ROWCOUNT = 1 THEN
  COMMIT;
ELSE
  ROLLBACK; -- Not actually needed
  Issue warning "The row was updated
   by another user. Refresh the form"
END IF;
                                          UPDATE EMP
                                          SET ename = <new_value>
                                          WHERE id = 19 AND
                                             ename = <old_value>
                                          ... 
Re: lost updates [message #223785 is a reply to message #223769] Sun, 11 March 2007 07:46 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Just wondering :
can't you use a "select...... for update"

I think that this maybe solves the problem also.
Re: lost updates [message #223786 is a reply to message #223785] Sun, 11 March 2007 07:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you have a web-application, you definitely don't want to use select for update. Chances are too big that the lock never gets released..
It all depends on your type of application.
Re: lost updates [message #223843 is a reply to message #223786] Mon, 12 March 2007 01:19 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
ah, I understand.

The below is somewhat off-topic, and not based on a real-world issue (Maybe I should create a new thread?)
And what will happen if you put all of this in a stored procedure?
1) start (web)session.
2) somewhere in the session execute the procudure
3) exit the session before the procedure finishes
4) I think the procedure will finish and the lock will get released

Is my assumption rigth?
Re: lost updates [message #223862 is a reply to message #223843] Mon, 12 March 2007 02:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, but that would mean you already did a select without for update prior to calling the procedure.
What else would you do in your web-session? (Assuming you have a data-entry webform)
Re: lost updates [message #224274 is a reply to message #223663] Tue, 13 March 2007 12:32 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Stored proc will release the lock ONLY if it issues COMMIT.

2. There is always a possibility that somebody will retrieve the same data and call a SP with a DIFFERENT parameters in the same time (as Frank already mentioned).


Michael
Previous Topic: PL/SQL and Java Servlets
Next Topic: aggregating row data
Goto Forum:
  


Current Time: Sun Dec 04 12:43:38 CST 2016

Total time taken to generate the page: 0.09488 seconds