Re: locking issue with select for update, sql advice requested

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Wed, 12 Oct 2005 09:25:07 -0700 (PDT)
Message-ID: <20051012162507.33027.qmail@web35004.mail.mud.yahoo.com>

Here is the issue with this sql:
1 select *
2 from (

  3      select callid
  4      , to_char(calldate,'MMDDYYYY HH24:MM:SS')
  5      from calldetail_tmp
  6      where calldate between sysdate - 110 and
sysdate
  7      and audiostate = 10
  8      and callflowtypeid in (8,13,17,28)
  9      order by calldate

10 )
11* where rownum <= 4
SQL> / CALLID TO_CHAR(CALLDATE,

---------------- -----------------
5020050630008657 06302005 09:06:24
5020050630008658 06302005 09:06:30

5020050630008659 06302005 10:06:13
5020050630008660 06302005 10:06:58

okay so lets add the for udpate now ....

Add the for update:
1 select *
2 from (

 3      select callid
 4      , to_char(calldate,'MMDDYYYY HH24:MM:SS')
 5      from calldetail_tmp
 6      where calldate between sysdate - 110 and
sysdate
 7      and audiostate = 10
 8      and callflowtypeid in (8,13,17,28)
 9      order by calldate

10 )
11 where rownum <= 4
12* for update

ERROR at line 2:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Issue with locking is the application is multi-threaded , if I break up this up into 2 sql statements two threads could update the same row. I don't want this to happen.

Many Thanks,
-Lizz

Anthony Wilson <amwilson_at_iinet.net.au> wrote:

> For the ordering issue, just move the rownum
> predicate out of the query:
>
> select *
> from (
> select callid
> , to_char(calldate,'MMDDYYYY HH24:MM:SS')
> from calldetail_tmp
> where calldate between sysdate - 110 and sysdate
> and audiostate = 10
> and callflowtypeid (8,13,17,28)
> order by calldate
> )
> where rownum <= 4
> /
>
> As for the locking issue, I'm not sure I understood
> the question...??
>
> cheers,
> Anthony
>
> On Wed, Oct 12, 2005 at 08:34:23AM, laura pena
> wrote:
> > 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
> >
> --
> Anthony Wilson
>

Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 12 2005 - 11:27:21 CDT