Savepoint & Rollback

shell100 · Posted: Thu May 07, 2009 3:43 pm

Need help to understand the significance of putting save point & rollback as below in the case described below

----------------------------------------------------------
EXEC SQL
SAVEPOINT A ON ROLLBACK RETAIN CURSORS
END-EXEC.
-----------------------------------------------------------
IF ERROR-FOUND

EXEC SQL
ROLLBACK TO SAVEPOINT A
END-EXEC
-----------------------------------------------------------

Lets take the scenario -
User1 invoked the SP to update Row1 which will take Save point - A
User2 also invoked the same SP to update Row2 which will again take Save point. Lets assume this transaction get completed.
Now user1 got some issue due to which rollback happened. Would it Rollback included updation done by User2 or it would only rollback user1 transaction.

Thanks in advance

dick scherrer · Posted: Thu May 07, 2009 8:14 pm

Hello,

User1 will not rollback work done by user2.

shell100 · Posted: Tue May 12, 2009 2:42 pm

Thanks Dick!!

Just to understand - would both the savepoints be different for both Stored proc transactions?

dick scherrer · Posted: Tue May 12, 2009 7:40 pm

Hello,

Yes. Each logical unit of work is managed separately.