Re: a question about transactions and point-of-time

Hi, Yoram.

To serialize your operation you don't need to introduce a new table. Look at DBMS_LOCK package. I think it's exactly what you need.

Michael.

Yoram Ayalon wrote:
> 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 - 15:08:13 CDT