At 10:43 AM 15/01/2012, Lars wrote:

>Hello
>
>I have a stored procedure that updates a record based on a specific condition (see below)
>
> NEWTRAN = GEN_ID(SEQ_TRAN,1);
>
> UPDATE INVENTORY
> SET QUANTITY = :NEWQUANT , TRANID = :NEWTRAN
> WHERE RECORDID = :RECORDID AND TRANID = :OLDTRANID;
>
>
> SELECT TRANSID,QUANTITY
> FROM INVENTORY
> WHERE RECORDID = :RECORDID
> into :NEWTRAN2, :NEWQUANT2 <-- return values
>
> if (NEWTRAN <> NEWTRAN2) THEN
> BEGIN
> NEWTRAN2 = 0;
> NEWQUANT2 = 0;
> END
> SUSPEND;
>
>If 2 users tried updating same record with this SP, would they collide or would one be successful and one fail?

The latter, i.e., just a regular update conflict. It makes no difference whether it is being done through a SP or via DSQL.

>Would the result of the Update statement be visible to another transaction

No. But the other transaction would be aware that an update was pending on the record.

> or would the SP need to complete and return its values in order for the updates to be seen in another users transaction?

Assuming you mean "the first SP", certainly the SP would have to complete...but the work would also have to be committed in order for it to become visible to other transactions.

>I am assuming that the update is visible

No. It will be visible only to the transaction that performed the work, until the work is committed.
Other transactions that started after this one but before the work is committed won't see the effects, either (although, depending on isolation level and lock resolution policy, they may get a "fresh view" after the commit (if Read Committed). A WAIT transaction may be able to complete its update after the commit in some conditions; but in most cases will except once the WAIT is over.

./heLen