Update an unprocessed record exclusively [message #422719] |
Fri, 18 September 2009 08:53  |
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   |
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 #423075 is a reply to message #422741] |
Tue, 22 September 2009 00:59   |
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 #423093 is a reply to message #423086] |
Tue, 22 September 2009 01:41   |
_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 #423097 is a reply to message #423075] |
Tue, 22 September 2009 02:03   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
johnbach wrote at Tue, 22 September 2009 11:29Second 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
bonker wrote on Tue, 22 September 2009 09:03johnbach wrote at Tue, 22 September 2009 11:29Second 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 #423108 is a reply to message #423106] |
Tue, 22 September 2009 03:21   |
_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 #423114 is a reply to message #423109] |
Tue, 22 September 2009 03:54   |
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 #423122 is a reply to message #423116] |
Tue, 22 September 2009 04:19   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 22 September 2009 10:58But 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   |
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   |
 |
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   |
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   |
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 #423131 is a reply to message #423126] |
Tue, 22 September 2009 04:59   |
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   |
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   |
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   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
bonker wrote on Tue, 22 September 2009 11:59Yes 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
|
|
|