Home » SQL & PL/SQL » SQL & PL/SQL » Update an unprocessed record exclusively
Update an unprocessed record exclusively [message #422719] Fri, 18 September 2009 08:53 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
Update a single unprocessed record with maximum PRIORITY and minimum INSERT_DATE exclusively.

CREATE TABLE TEST
(
TID NUMBER PRIMARY KEY,
STATUS VARCHAR2(40),
PRIORITY NUMBER,
INSERT_DATE  DATE
);


UPDATE TEST SET STATUS='PROCESSED'
WHERE TID=
(SELECT TID FROM
(Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc) 
WHERE rownum=1)
--RETURNING .. INTO
;
do_something;
COMMIT/ROLLBACK;


I tried this in two seperate session but both updates the same record?
Any suggestion,Thanks in advance

[Updated on: Tue, 22 September 2009 01:59] by Moderator

Report message to a moderator

Re: Update an unprocessed record exclusively [message #422728 is a reply to message #422719] Fri, 18 September 2009 09:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want to lock the record first:
CURSOR c_lock IS SELECT <columns>
FROM  <table>
WHERE ...
FOR UPDATE NOWAIT;

BEGIN
  OPEN c_lock;
  FETCH c_lock INTO <variables>;

<do processing>

UPDATE <table>
SET    STATUS='PROCESSED'
WHERE CURRENT OF c_lock;


This guarantees that only one session will have access to this row at a time. Any other session trying to run at the same time will get a message that the record is locked.
Re: Update an unprocessed record exclusively [message #422741 is a reply to message #422719] Fri, 18 September 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I tried this in two seperate session but both updates the same record?

I don't trust you. Can you prove it?


Regards
Michel
Re: Update an unprocessed record exclusively [message #423075 is a reply to message #422741] Tue, 22 September 2009 00:59 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Session 1
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       282

SQL> set timing on
SQL>  select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 18-SEP-09
         2 22                                                2 18-SEP-09

Elapsed: 00:00:00.01
SQL>  UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
  2  (SELECT TID FROM
(Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc) 
WHERE rownum=1);

1 row updated.

Elapsed: 00:00:00.01
SQL> select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 18-SEP-09
         2 PROCESSED                                         2 18-SEP-09

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>


Session 2
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       732

Elapsed: 00:00:00.01
SQL>  select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 18-SEP-09
         2 22                                                2 18-SEP-09

Elapsed: 00:00:00.00

SQL> UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
  2  (SELECT TID FROM
(Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc) 
WHERE rownum=1);

1 row updated.

Elapsed: 00:00:06.35
SQL>  select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 18-SEP-09
         2 PROCESSED                                         2 18-SEP-09

Elapsed: 00:00:00.00
SQL>


Second session's update was executed between first session's update and commit.

[Updated on: Tue, 22 September 2009 01:58] by Moderator

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423082 is a reply to message #423075] Tue, 22 September 2009 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I still don't trust you. What you posted does not prove anything.
Read what you posted, how can you say if the update is from session 1 or 2?

Regards
Michel
Re: Update an unprocessed record exclusively [message #423086 is a reply to message #423082] Tue, 22 September 2009 01:20 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Sorry,What makes you to believe me and how do you want me to prove?
Re: Update an unprocessed record exclusively [message #423093 is a reply to message #423086] Tue, 22 September 2009 01:41 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Try in SESSION 1:
DROP TABLE test_239;

CREATE TABLE test_239
  (id  VARCHAR2(20),
   sid NUMBER,
   tme DATE,
   str VARCHAR2(20)
  );
  
INSERT INTO test_239 VALUES ('A', 0,sysdate,'');

COMMIT;

UPDATE test_239 SET 
  sid = sys_context('USERENV', 'SESSIONID'),
  tme = sysdate,
  str = 'Session 1'
  WHERE id='A';

SELECT id, sid, to_char(tme,'HH24:MI:SS') FROM test_239;

In SESSION 2:
DECLARE
   v_lock test_239%ROWTYPE;

   CURSOR c_lock
   IS
      SELECT * FROM test_239
           WHERE id='A'
      FOR UPDATE NOWAIT;

BEGIN
   OPEN c_lock;

   FETCH c_lock INTO v_lock;

   UPDATE test_239 SET 
     sid= SYS_CONTEXT('USERENV', 'SESSIONID'),
     tme = sysdate,
     str = 'Session 2'
    WHERE CURRENT OF c_lock;
END;

And You will get the ORA-00054 Message. You can prove the sessionid and the time exactly.

[Updated on: Tue, 22 September 2009 01:44]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423096 is a reply to message #423093] Tue, 22 September 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the point you try to emphasize with this test case?
How does this prove or contradict or answer OP issue?

Regards
Michel

[Updated on: Tue, 22 September 2009 01:58]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423097 is a reply to message #423075] Tue, 22 September 2009 02:03 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
johnbach wrote at Tue, 22 September 2009 11:29
Second session's update was executed between first session's update and commit.


I think you mean to say that session 2's update is blocked till session 1 does a commit or rollback and once session 1 does commit the session 2 updates the record successfully.This is expected behavior in Oracle. You are seeing the effects of Lost Updates

Re: Update an unprocessed record exclusively [message #423098 is a reply to message #423097] Tue, 22 September 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
bonker wrote on Tue, 22 September 2009 09:03
johnbach wrote at Tue, 22 September 2009 11:29
Second session's update was executed between first session's update and commit.


I think you mean to say that session 2's update is blocked till session 1 does a commit or rollback and once session 1 does commit the session 2 updates the record successfully.This is expected behavior in Oracle. You are seeing the effects of Lost Updates

[Edit: sorry answer to wrong person]
This is what I said I didn't trust what OP told and wait he proved what he claimed in his last post as well as in his original one.
Note that what he said in his first post is the opposite of Oracle behaviour.

Regards
Michel

[Updated on: Tue, 22 September 2009 02:19]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423101 is a reply to message #423098] Tue, 22 September 2009 02:24 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
This is a test case to prove, in reply to:
Quote:
Sorry,What makes you to believe me and how do you want me to prove?

Quote:
Read what you posted, how can you say if the update is from session 1 or 2?

[Updated on: Tue, 22 September 2009 02:25]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423106 is a reply to message #423101] Tue, 22 September 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
_jum wrote on Tue, 22 September 2009 09:24
This is a test case to prove, in reply to:
Quote:
Sorry,What makes you to believe me and how do you want me to prove?

Quote:
Read what you posted, how can you say if the update is from session 1 or 2?

So you case failed. You cannot prove something from update with a select for update NOWAIT.
I still don't understand what your test case may show, can you explain? What does getting ORA-00054 prove?

Regards
Michel

Re: Update an unprocessed record exclusively [message #423108 is a reply to message #423106] Tue, 22 September 2009 03:21 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
The OP's question was:
Quote:
I tried this in two seperate session but both updates the same record? Any suggestion,Thanks in advance

You (and me) didn't trust this. So I only tried to give the OP a simple method to prove it by himself as he asked and You asked for.

If he only UPDATES in SESSION_2 this session will block until he COMMIT or ROLLBACK in SESSION_1 - prove fails.
If he locks the record as @JRowbottom suggested, the SESSION_2 won't block but he gets the ORA-00054 - prove fails.
If he can UPDATE in SESSION_2 he can select the time and session and if this is SESSION_2 and the meantime - prove is correct.
May be I misread the OP's question?
Re: Update an unprocessed record exclusively [message #423109 is a reply to message #423108] Tue, 22 September 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
May be I misread the OP's question?

Maybe not, but OP said he updated the row with both sessions.
Or maybe the question is "does the row will be updated by the second session"? Who knows until we have a feedback from OP.

Regards
Michel
Re: Update an unprocessed record exclusively [message #423114 is a reply to message #423109] Tue, 22 September 2009 03:54 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Let me try to explain my requirement.
I have a multithreaded application.
I wanted to pick
a single unprocessed record(column status!='PROCESSED')
exclusively (No other thread should process this record)
based on priority and inserted date
process it(do something),based on processing result mark it as 'PROCESSED' or leave as it is.



Quote:
I think you mean to say that session 2's update is blocked till session 1 does a commit or rollback and once session 1 does commit the session 2 updates the record successfully.


Yes,session 2's update is blocked till session 1's commit.
Quote:
Elapsed: 00:00:06.35
Re: Update an unprocessed record exclusively [message #423116 is a reply to message #423114] Tue, 22 September 2009 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But session 2 DOES NOT update row updated by session 1.

Regards
Michel
Re: Update an unprocessed record exclusively [message #423122 is a reply to message #423116] Tue, 22 September 2009 04:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 22 September 2009 10:58
But session 2 DOES NOT update row updated by session 1.

Regards
Michel

How come? Both sessions show "1 row updated". In the final select (after the update in session 2 returns) it shows 1 row changed.
As far as I can tell, this means that both sessions updated the same row.
Re: Update an unprocessed record exclusively [message #423124 is a reply to message #423116] Tue, 22 September 2009 04:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
But session 2 DOES NOT update row updated by session 1.


Session 1
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       158
SQL> select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 22-SEP-09
         2 22                                                2 22-SEP-09

SQL>  UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
  2   (SELECT TID FROM
  3   (Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc)
  4   WHERE rownum=1)
  5  /

1 row updated.

SQL> select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 22-SEP-09
         2 PROCESSED                                         2 22-SEP-09

SQL> commit;

Commit complete.

Session 2
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       150
SQL>  select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 22-SEP-09
         2 22                                                2 22-SEP-09
               
       
SQL> ed
Wrote file afiedt.buf

  1   UPDATE TEST SET STATUS='PROCESSEDby2' WHERE TID=
  2   (SELECT TID FROM
  3   (Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc)
  4*  WHERE rownum=1)
SQL> /
----waits for commit in other session
1 row updated.

SQL> select * from testl
  2  .
SQL> select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
         1 11                                                1 22-SEP-09
         2 PROCESSEDby2                                      2 22-SEP-09    
Re: Update an unprocessed record exclusively [message #423126 is a reply to message #423122] Tue, 22 September 2009 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes I admit something strange happened here and I think there is a bug as Oracle seems to not realize the conditions changed due to first update.
I explain, this is what happened.
Session 1
11:42:17 SQL> CREATE TABLE TEST
11:42:17   2  (
11:42:17   3  TID NUMBER PRIMARY KEY,
11:42:17   4  STATUS VARCHAR2(40),
11:42:17   5  PRIORITY NUMBER,
11:42:17   6  INSERT_DATE  DATE,
11:42:17   7  UPDATE_DATE  DATE
11:42:17   8  );

Table created.

11:42:17 SQL> insert into test values(1, '11', 1, sysdate, null);

1 row created.

11:42:17 SQL> insert into test values(2, '22', 2, sysdate, null);

1 row created.

11:42:17 SQL> commit;

Commit complete.

11:42:17 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 11                                                1 22/09/2009 11:42:17
         2 22                                                2 22/09/2009 11:42:17

2 rows selected.

11:42:18 SQL> UPDATE TEST SET STATUS='PROCESSED', UPDATE_DATE=SYSDATE
11:42:25   2  WHERE TID=
11:42:25   3    (SELECT TID FROM
11:42:25   4      (Select TID FROM TEST WHERE STATUS!='PROCESSED' 
11:42:25   5       ORDER BY PRIORITY desc,INSERT_DATE asc) 
11:42:25   6     WHERE rownum=1);

1 row updated.

11:42:25 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 11                                                1 22/09/2009 11:42:17
         2 PROCESSED                                         2 22/09/2009 11:42:17 22/09/2009 11:42:25

2 rows selected.

11:42:25 SQL> commit;

Commit complete.

Session 2
11:42:27 SQL> UPDATE TEST SET STATUS='PROCESSED', UPDATE_DATE=SYSDATE
11:42:29   2  WHERE TID=
11:42:29   3    (SELECT TID FROM
11:42:29   4      (Select TID FROM TEST WHERE STATUS!='PROCESSED' 
11:42:29   5       ORDER BY PRIORITY desc,INSERT_DATE asc) 
11:42:29   6     WHERE rownum=1);

1 row updated.

11:42:37 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 11                                                1 22/09/2009 11:42:17
         2 PROCESSED                                         2 22/09/2009 11:42:17 22/09/2009 11:42:29

2 rows selected.

11:42:38 SQL> commit;

Commit complete.

Session 2 updated the same row.
What should happen is the following:
Session 1
11:44:19 SQL> CREATE TABLE TEST
11:44:19   2  (
11:44:19   3  TID NUMBER PRIMARY KEY,
11:44:19   4  STATUS VARCHAR2(40),
11:44:19   5  PRIORITY NUMBER,
11:44:19   6  INSERT_DATE  DATE,
11:44:19   7  UPDATE_DATE  DATE
11:44:19   8  );

Table created.

11:44:19 SQL> insert into test values(1, '11', 1, sysdate, null);

1 row created.

11:44:19 SQL> commit;

Commit complete.

11:44:19 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 11                                                1 22/09/2009 11:44:19

1 row selected.

11:44:20 SQL> UPDATE TEST SET STATUS='PROCESSED', UPDATE_DATE=SYSDATE
11:44:24   2  WHERE STATUS!='PROCESSED';

1 row updated.

11:44:24 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 PROCESSED                                         1 22/09/2009 11:44:19 22/09/2009 11:44:24

1 row selected.

11:44:25 SQL> commit;

Commit complete.

Session 2
11:44:31 SQL> UPDATE TEST SET STATUS='PROCESSED', UPDATE_DATE=SYSDATE
11:44:31   2  WHERE STATUS!='PROCESSED';

0 rows updated.

11:44:39 SQL> select * from test;
       TID STATUS                                     PRIORITY INSERT_DATE         UPDATE_DATE
---------- ---------------------------------------- ---------- ------------------- -------------------
         1 PROCESSED                                         1 22/09/2009 11:44:19 22/09/2009 11:44:24

1 row selected.

The second does update the row and it is no more part of the selection.
I think Oracle does not see that STATUS is part of the selection because it is in a not-correlated subquery (whereas actually it is).

Regards
Michel


[Updated on: Tue, 22 September 2009 04:51]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423127 is a reply to message #423126] Tue, 22 September 2009 04:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi Michel

Lost Updates
A lost update is a classic database problem. Simply put, a lost update occurs when the
following events occur, in the order presented:
1. User 1 retrieves (queries) a row of data.
2. User 2 retrieves that same row.
3. User 1 modifies that row, updates the database and commits.
4. User 2 modifies that row, updates the database and commits.

[Source Expert One on One Oracle]
Re: Update an unprocessed record exclusively [message #423128 is a reply to message #423114] Tue, 22 September 2009 04:55 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
That is what I mentioned you are seeing effect of Lost updates.

You need to use pessimistic locking as mentioned by JRowbottom or you can add additional condition in your update as below, so that blocked session will do a restart of update once the blocker session commits.

UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
 (SELECT TID FROM
(Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSERT_DATE asc) 
WHERE rownum=1)
and status != 'PROCESSED' 


If you use above update against your test case you will see that session 2 blockwill be blocked until session 1 commits or rollbacks, and if session 1 commits, you will find that session 2 will now update the status as "Processed" for record with TID=1.

You can read more about the restartable DML's in the below link

Write Consistency Part I

Write Consistency Part II

Write Consistency Part III




Re: Update an unprocessed record exclusively [message #423129 is a reply to message #423127] Tue, 22 September 2009 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ayush_anand wrote on Tue, 22 September 2009 11:53
Hi Michel

Lost Updates
A lost update is a classic database problem. Simply put, a lost update occurs when the
following events occur, in the order presented:
1. User 1 retrieves (queries) a row of data.
2. User 2 retrieves that same row.
3. User 1 modifies that row, updates the database and commits.
4. User 2 modifies that row, updates the database and commits.

[Source Expert One on One Oracle]

Isn't what I posted?
But in this case, it does not happen... so seems a bug.

Regards
Michel

Re: Update an unprocessed record exclusively [message #423130 is a reply to message #423128] Tue, 22 September 2009 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
and if session 1 commits, you will find that session 2 will now update the status as "Processed" for record with TID=1.

This contradicts your previous post.
Should session 2 modify the row or not?
Why the 2 examples lead to different behaviour?

Regards
Michel
Re: Update an unprocessed record exclusively [message #423131 is a reply to message #423126] Tue, 22 September 2009 04:59 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Yes Michel, I feel Oracle is just comparing the current version and consistent version of TID as only TID is part of where condition in the update and since the current and consistent version of TID were same, it went ahead updated the row one more time without doing the restart.

If you add addition condition of and status != 'PROCESSED' as part of update condition alongwith TID you will see the restart of update.
Re: Update an unprocessed record exclusively [message #423136 is a reply to message #423130] Tue, 22 September 2009 05:07 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Ok here is the worked example of what I am trying to say.

Session 1


XE@SQL> select * from test;

       TID STATUS                 PRIORITY INSERT_DATE
---------- -------------------- ---------- --------------------
         1 11                            1 22-Sep-2009 00:00:00
         2 22                            2 22-Sep-2009 00:00:00

2 rows selected.

XE@SQL> set time on

15:33:32 XE@SQL> ed
Wrote file afiedt.buf

  1  UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
  2   (SELECT TID FROM
  3  (Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY desc,INSE
RT_DATE asc)
  4  WHERE rownum=1)
  5* and status != 'PROCESSED'
15:33:53 XE@SQL> /

1 row updated.

15:34:51 XE@SQL> commit;

Commit complete.

15:34:56 XE@SQL> select * from test;

       TID STATUS                 PRIORITY INSERT_DATE
---------- -------------------- ---------- --------------------
         1 11                            1 22-Sep-2009 00:00:00
         2 PROCESSED                     2 22-Sep-2009 00:00:00

2 rows selected.






Session 2


15:34:27 XE@SQL> select * from test;

       TID STATUS                 PRIORITY INSERT_DATE
---------- -------------------- ---------- --------------------
         1 11                            1 22-Sep-2009 00:00:00
         2 22                            2 22-Sep-2009 00:00:00

2 rows selected.

15:34:28 XE@SQL> UPDATE TEST SET STATUS='PROCESSED' WHERE TID=
15:34:49   2   (SELECT TID FROM
15:34:49   3  (Select TID FROM TEST WHERE STATUS!='PROCESSED' ORDER BY PRIORITY
desc,INSERT_DATE asc)
15:34:49   4  WHERE rownum=1)
15:34:49   5  and status != 'PROCESSED'
15:34:50   6  /

1 row updated.

15:34:53 XE@SQL> select  * from test;

       TID STATUS                 PRIORITY INSERT_DATE
---------- -------------------- ---------- --------------------
         1 PROCESSED                     1 22-Sep-2009 00:00:00
         2 PROCESSED                     2 22-Sep-2009 00:00:00

2 rows selected.

15:35:00 XE@SQL>






[Updated on: Tue, 22 September 2009 05:15]

Report message to a moderator

Re: Update an unprocessed record exclusively [message #423138 is a reply to message #423130] Tue, 22 September 2009 05:13 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
As explained in Expert one on one Oracle

Optimistic Locking
The second method, referred to as optimistic locking, is to keep the old and new values in
the application and upon updating the data use an update like this:
Update table
Set column1 = :new_column1, column2 = :new_column2, ....
Where column1 = :old_column1
And column2 = :old_column2
...

Here, we are optimistically hoping that the data doesn't get changed. In this case, if our
update updates one row - we got lucky, the data didn't change between the time we read
it out and the time we got around to submitting the update. If we update zero rows, we
lose - someone else changed the data and now we must figure out what we want to do in
order to avoid the lost update.
Re: Update an unprocessed record exclusively [message #423139 is a reply to message #423136] Tue, 22 September 2009 05:15 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Quote:
If you add addition condition of and status != 'PROCESSED' as part of update condition alongwith TID you will see the restart of update.

Thanks,This is more simple.
I thought the second session will not find any rows to update(i mean reevaluate the subquery)
Re: Update an unprocessed record exclusively [message #423140 is a reply to message #423131] Tue, 22 September 2009 05:19 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
bonker wrote on Tue, 22 September 2009 11:59
Yes Michel, I feel Oracle is just comparing the current version and consistent version of TID as only TID is part of where condition in the update and since the current and consistent version of TID were same, it went ahead updated the row one more time without doing the restart.

If you add addition condition of and status != 'PROCESSED' as part of update condition alongwith TID you will see the restart of update.

Obviously!

Regards
Michel

Previous Topic: pad number when no decimals
Next Topic: Database script for Insert statements
Goto Forum:
  


Current Time: Mon Feb 17 18:58:58 CST 2025