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

What db version?? I probably missed that in op.

Raj

On 10/12/05, laura pena <lizzpenaorclgrp_at_yahoo.com> wrote:
>
>
> 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
>

--
------------------------------
select standard_disclaimer from company_requirements where category =
'MANDATORY';

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