All geek questions in one place

Proper transactions in stored documents

Suppose I have a stored procedure that manages its own transaction

CREATE PROCEDURE theProc AS BEGIN BEGIN TRANSACTION -- do some stuff IF @ThereIsAProblem ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END

If I call this proc from an existing transaction, proc can ROLLBACK execute an external transaction.

 BEGIN TRANSACTION EXEC theProc COMMIT TRANSACTION

How can I use a transaction correctly in a stored procedure so that the stored procedure does not roll back external transactions?

+4
source share
2 answers

The syntax for this is probably database dependent. But in Transact-SQL, what you are doing is checking @@ TRANCOUNT to see if you have a transaction. If you want to create a savepoint, and in the end you can simply go through the end of the function (assuming that the commit or rollback occurs later) or rollback to your savepoint.

For more information, see the Microsoft documentation for savepoints .

Support for savepoints is fairly common, but I think that the mechanism (provided that it exists) to find out what you are currently in a transaction will be slightly different.

+2
source

use @@ trancount to find out if you are in a transaction as you type

+1
source

Source: https://habr.com/ru/post/1276901/

More articles:


All Articles