Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> a question about transactions and point-of-time
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 ...
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