22

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?

0

6 Answers 6

19

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.

1
  • I did not find any article that says, "A store procedure is an example of a batch". I believe Stored Procedure is very similar to batch but it is not a Batch. The main difference is: SP is guaranteed to be compiled ahead and ready for execution multiple times unlike Batches. The similarities are: - They both executes each command at a time. - If one command failed then all previous commands are committed (unless it was running in a transaction) - if one command has failed then all next commands are not executed.
    – Ashi
    Aug 15, 2017 at 6:18
17

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:

enter image description here

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

enter image description here

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.

0
9

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?

3

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.

0
2

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.

0
-2

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

4
  • 1
    Will that create sub transactions within my Stored Procedure? Ideally I'd like to avoid that if possible Jul 12, 2013 at 16:28
  • 1
    If the SP is called from within a transaction, then saved transactions above are the answer. If the sp isn't called with, then @mrdenny is correct. Sql server doesn't support nested transaction. Jul 12, 2013 at 16:40
  • @StrayCatDBA just to clarify.. there are Nested Transactions in SQL Server, but they are evil .. SQL Server allows you to start transactions inside other transactions – called nested transactions. Refer to sqlskills.com/blogs/paul/… , msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx and sqlblog.com/blogs/kalen_delaney/archive/2007/08/13/…
    – Kin Shah
    Jul 12, 2013 at 17:02
  • 2
    To be clear (and for the lazy who don't want to click the links), you aren't actually starting another transaction. Aka the title on Paul's post: "Myth: Nested Transactions are real." They aren't real transactions. COMMIT in a nested transaction does nothing except decrement @@TRANCOUNT. It's true that you don't get an error if you nest BEGIN TRAN/COMMIT, but that is different from having real nested transctions. Jul 12, 2013 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.