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:25:07 -0700 (PDT)
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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
