Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> a question about transactions and point-of-time

a question about transactions and point-of-time

From: Yoram Ayalon <yoram.ayalon_at_structuredweb.com>
Date: 16 May 2006 08:25:03 -0700
Message-ID: <1147793103.136945.253590@j55g2000cwa.googlegroups.com>


We have an operation which we want to serialize, since it can be called concurrently by same user from different web servers which would cause duplicates

We have a Table 'Search'

   UserID
   <other fields>

the Search table is updated in stored proc (INUserID, INSearchString) where INSearchString is a complete SELECT statement that can encompass many tables. Currently this is what the proc does:

DELETE FROM Search WHERE UserID = INUserID;

EXECUTE IMMEDIATE 'INSERT INTO Search SELECT INUserID,RowNum FROM (' ||

   INSearchString || ')';

I want to add a new table SearchLock (UserID)

and add a SELECT * FROM SearchLock WHERE UserID = INUserID FOR UPDATE to this stored proc

my question is, where exactly should I put the transaction BEGIN and COMMIT to ensure that a second call, after waiting if needed, will see the contents of Search AFTER the first call has commited? would the following sequence work every time?

BEGIN TRANSACTION ...

SELECT ... FOR UPDATE
DELETE...
EXECUTE ...

COMMIT (obviously will add en EXCEPTION handling)

my worry, and what i'm trying to prevent, is that the second call, after waiting, will see the contents of Search BEFORE the first call has commited it. Received on Tue May 16 2006 - 10:25:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US