locking issue with select for update, sql advice requested

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Wed, 12 Oct 2005 08:34:23 -0700 (PDT)
Message-ID: <20051012153424.54709.qmail@web35007.mail.mud.yahoo.com>

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

Received on Wed Oct 12 2005 - 10:36:37 CDT