开发者

Select for Update sql is a read and write mode?

I have simultaneous request to a particular row in a table and PL/SQL statement is used to update the table by reading the data from master row in 开发者_开发知识库the same table and update the current range row and master row it read. Algorithm is like this:-

Declare

variable declaration

BEGIN

Select (Values) into (values1) from table where <condition1> for update;

select count(*) into tempval from table where <condition2>;

if (tempval == 0) then

insert into table values(values);

else

select (values) into (values2) from table where <condition2> for update;

update  table set (values1) where <condition2>;

end if

update table set (values1+incrval) where <condition1>

END;

Unfortunately the master row is updated properly with the correct sequence but the current range picks up the old value of the master range. It does the dirty read. Even though the transaction isolation level for the table is serialized. Please could some tell me what is happening here?


This is working as designed. Oracle default, and only, read isolation lets the session see all of their own updates. If you perform:

INSERT INTO TABLE1 (col1) values (1);
COMMIT;
UPDATE TABLE1 SET col1 = 2 where col1 = 1;
SELECT col1 FROM TABLE1; 

you will see 2 returned from the last query. Please read the Merge Explanation for how to use a MERGE statement to perform the insert or update based upon a single criteria.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜