Yes. you are correct. This why we'd like to re-write query to work appropriately.
The java application is multi-threaded. So each thread will be issuing or currently is issuing a select for update.
The concern is if we break this up into two sql statements, two threads will update the same row. So we'd like to prohibit this by running one select for update statement and limiting the number of items returned.
> > > -- --Original Message-- -- > From: oracle-l-bounce@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of > laura pena > Sent: Wednesday, October 12, 2005 8:34 AM > To: oracle-l@(protected) > Subject: locking issue with select for update, sql > advice requested > > I would like to create a lock in my Java application > using select for > update. > > Here is the sql: > > select callid, TO_CHAR(calldate,'MMDDYYYY > HH24:MM:SS') from > calldetail_tmp where calldate between sysdate -110 > and sysdate > and (audiostate = 10 AND callflowtypeid IN > (8,13,17,28)) > and rownum <= 4 > order by calldate for update; > > > Issue hear rownum does not guarantee results will be > in order specified > by the order by clause. Rownum is set before sort is > done by order > clause. > > Most of the time calls are returned in sorted order > ( order of insertion > is what rownum is being returned as). It is in a > backlog condition that > rows are not returned properly. > > > I have looked at locking via a view but can not lock > on a complex view. > Am looking at lock table in share mode now. Any > suggestions would be > greatly appreciated. > > > Many Thanks, > -Lizz > > > > > __ ____ ____ ____ ____ ____ ______ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > -- > http://www.freelists.org/webpage/oracle-l > > Lizz, > Please repeat 10 times. > > "If you want the data in a particular order in your > query you must use > order by. Relying on a particular order without an > order by is shooting > yourself in the foot." > > The order of insertion has nothing to do with the > physical layout of the > rows in the table. Yes, if you insert one and only > one row in the table > it will be the first row.(and the last row). Oracle > keeps a list for > each table of blocks available for inserting data > into. When you insert > data it looks for the first one. If it won't fit in > that block then it > moves on to the next one.(a row being added won't > always fit in the > available room in a block) So if you ponder it for > a bit you can see > that the order of insertion won't equal the physical > order of the rows. > Read Codds rules. > > Post what businesss need you are trying to solve and > I think we can give > you some nice solutions. > Jim > >
__ ____ ____ ____ ____ ____ ______ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l
