RE: locking issue with select for update, sql advice requested
- From: laura pena <lizzpenaorclgrp@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Oct 2005 09:00:26 -0700 (PDT)
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.
Hope that elaborates the issue.
Many Thanks,
Lizz
--- "Kennedy, Jim" <jim_kennedy@xxxxxxxxxx> wrote:
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> laura pena
> Sent: Wednesday, October 12, 2005 8:34 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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
> --
> //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
