Don't use a transaction for Stored Procedure

Question

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?

Answer 1

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit. All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated). You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together. A stored procedure is an example of a batch. In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it. If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.

Answer 2

All of the transactions won't execute in a single transaction. Take a look at this example:

use TestDB;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTranTable1'))
    drop table dbo.TestTranTable1;
create table dbo.TestTranTable1
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTranTable1
default values;
go 4

select *
from dbo.TestTranTable1;

if exists (select 1 from sys.sql_modules where object_id = object_id('dbo.ChangeValues'))
begin
    drop proc dbo.ChangeValues;
end
go
create proc dbo.ChangeValues
as
    update dbo.TestTranTable1
    set some_int = 11
    where id = 1;

    update dbo.TestTranTable1
    set some_int = 12
    where id = 2;

    update dbo.TestTranTable1
    set some_int = 13
    where id = 3;

    -- this will error out (arithmetic overflow)
    update dbo.TestTranTable1
    set some_int = 2147483648
    where id = 4;
go

exec dbo.ChangeValues;

select *
from dbo.TestTranTable1;

Here is the output:

By creating an Extended Events session to monitor the sql_transaction event, here is the output from executing dbo.ChangeValues:

As you can see in this above screenshot, there are separate transactions for each of the four statements. The first 3 commit, and the last one rolls back because of the error.

Answer 3

Everything in sql server is contained in a transaction.

When you explicitly specify begin transaction and end transaction then it is called Explicit Transaction. When you dont, then it is Implicit transaction.

To switch which mode you're in, you'd use

set implicit_transactions on

or

set implicit_transactions off

select @@OPTIONS & 2

if above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

A transaction is ALL or nothing to keep database in a consistent state .. remember ACID properties.

CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [varchar](25) NULL,
    [DatabaseName] [sysname] NOT NULL,
 CONSTRAINT [pk_Product_ID_ServerName] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- insert some data 
INSERT INTO [dbo].[Products]([ProductID], [ProductName], [DatabaseName])
SELECT 1, N'repl1_product1', N'repl1' UNION ALL
SELECT 1, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 1, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 2, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 2, N'repl2_product1', N'repl2' UNION ALL
SELECT 2, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 3, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 3, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 3, N'repl3_product1', N'repl3' UNION ALL
SELECT 4, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 4, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 5, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 5, N'repl2_product1_02', N'repl2'

-- create SP now -- note that the first 3 will succeed and 4th will fail due to string truncation...

IF OBJECT_ID ('usp_UpdateProducts', 'P') IS NOT NULL
    DROP PROCEDURE usp_UpdateProducts;
GO
create procedure usp_UpdateProducts
as 
begin try
update Products 
set ProductName = 'repl1_product1'
where DatabaseName = 'repl1'and ProductID = 1;
update Products
set ProductName = 'repl2_product1'
where DatabaseName = 'repl2' and ProductID = 2;
update Products
set ProductName = 'repl3_product1'
where DatabaseName = 'repl3' and ProductID = 3;
update Products
set ProductName = 'repl3_product1_03&&&&&&&&&&39399338492w9924389234923482' -- this will fail ...
where DatabaseName = 'repl3' and ProductID = 4;
SELECT 1/0;
end try
begin catch
SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
end catch
go

Refer to : Is it a bad practice to always create a transaction?

Answer 4

This is how stored procedures work by default. The stored procedure isn't wrapped within a transaction automatically.

If you want the stored procedure to stop when it hits the first error you'll want to put some TRY/CATCH login in there to return in the event of a problem with command 2 for example.

Answer 5

You will need individual transactions for each command. You can also accomplish this with saved transactions:

See SAVE TRANSACTION (Transact-SQL) in the product documentation.

I want to qualify that individual transactions it the default behavior for stored procedures, because all statements are wrapped in implicit transactions; however, no one should rely on implicit transactions to control the destiny of their code. It is a much better practice to explicitly control the way transactions are handled in production code.

Answer 6

separate each of the parts with a BEGIN TRAN and to a check if the transaction was successful. if it was commit it, otherwise do a rollback, since they are all executing from the same level you will be able to commit each section separately without having to rollback all if one fails.

For more info you can take a look at: http://msdn.microsoft.com/en-us/library/ms188929.aspx