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? Would the result of the Update statement be visible to another transaction or would the SP need to complete and return its values in order for the updates to be seen in another users transaction?

I am assuming that the update is visible which is why I will return 0 values indicating update was not done, otherwise caller gets to see new TranID and new value which matches.

Thanks for reading this.


- Lou