question

SQLJim1 avatar image
SQLJim1 asked

Transactions Across Sub Procedures

Question about transactions in SQL Server. We are using 2008 R2. I have inherited a system that implements transactions around sub stored procedures. BEGIN TRY BEGIN TRANSACTION Numerous SQL Selects and Updates EXEC dbo.LongRunningStoredProcedure_A EXEC dbo.LongRunningStoredProcedure_B EXEC dbo.LongRunningStoredProcedure_C More SQL Selects and Updates COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH We have been having some problems with saving data consistently, and I wanted to change the transaction isolation level from READ COMMITTED to SNAPSHOT. But when I do that, the data that is saved by the sub-stored procedures is all zeros rather than the desired values. Does anyone have an recommendations on how I can get the sub stored procedures to attain the same transaction isolation level as I have in my parent procedure? Also, any thoughts about spanning a transaction across multiple sub procedures?
stored-procedurestransactionisolation-level
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
I'm guessing the sub procs have TRY/CATCH blocks. In each of the sub procs make sure you re-throw the local error back to the calling proc. E.g. ALTER PROC [dbo].[LongRunningStoredProcedure_A] AS SET NOCOUNT ON BEGIN TRY /* proc code here */ END TRY BEGIN CATCH DECLARE @error int, @message varchar(4000), @procname varchar(4000); SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @procname = ERROR_PROCEDURE(); RAISERROR ('%s: #%d - %s', 16, 1, @procname, @error, @message); END CATCH
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@SQLJim1 I would like to add a small touch up about the last part in the script in the question. Currently, it reads this:
begin catch
    rollback tran;
end catch;
This is probably fine in this specific script, but because the healthy infusion of paranoia does not hurt, I would replace the line reading **rollback tran;** with something like this: if @@trancount > 0 rollback tran; This is just for sanity check, to ensure that the attempt to rollback transaction when there is nothing to rollback will never happen.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.