Correct use of transactions in SQL Server

Question

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly.

What's the problem with the following script?

BEGIN TRANSACTION [Tran1]

INSERT INTO [Test].[dbo].[T1]
    ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]
  SET [Title] = N'az2' ,[AVG] = 1
  WHERE [dbo].[T1].[Title] = N'az'

COMMIT TRANSACTION [Tran1]
GO

The INSERT command is executed, but the UPDATE command has a problem.

How can I implement this to rollback both commands if any of them have an error in execution?

score 625 · Accepted Answer · edited Aug 22 '18 at 14:29

625

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH

edited Aug 22 '18 at 14:29

Aaron Bertrand

272,866
37
466
490

answered Apr 14 '12 at 12:41

Darren

68,902
24
138
144

1

Shouldn't `BEGIN TRANSACTION [Tran1]` be placed inside `TRY`? Anyway - very simple and elegant piece of code. – Piotr Nawrot Aug 14 '15 at 11:01
20

@PiotrNawrot No, if the transaction creation failed there is no need to rollback it in the catch. – Monsignor Nov 30 '15 at 15:04
8

If you want to see the error, then include this in the catch: `SELECT ERROR_MESSAGE() AS ErrorMessage;` – Kevin LeStarge Mar 22 '21 at 15:19
2

@KevinLeStarge or simply `THROW;` if you're using SQL Server >= 2012 as mentioned [here](https://dba.stackexchange.com/a/144814/184793) – Leponzo Sep 09 '21 at 18:14
If one wants a "visually nested syntax" so to say, i.e. such that `begin transaction` is nested under `begin try`, then a condition should be added before `rollback transaction`, which is: `if @@trancount > 0`. – OfirD Jun 05 '23 at 12:25

score 130 · Answer 2 · edited Apr 18 '22 at 20:45

130

At the beginning of stored procedure one should put SET XACT_ABORT ON to instruct Sql Server to automatically rollback transaction in case of error. If ommited or set to OFF one needs to test @@ERROR after each statement or use TRY ... CATCH rollback block.

edited Apr 18 '22 at 20:45

TylerH

20,799
66
75
101

answered Apr 14 '12 at 12:43

Nikola Markovinović

18,963
5
46
51

3

In other words, your transaction is not atomic unless you SET XACT_ABORT ON first. – 4AM Apr 22 '16 at 16:56
3

Note - For XACT_ABORT: OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger – J. Rockwood Feb 03 '22 at 15:04
Just a clarification please, ¿ SET XACT_ABORT ON applies only for the current procedure? – U. Busto May 03 '22 at 06:37
1

@U.Busto Current transaction, yes. – Nikola Markovinović May 03 '22 at 07:30

Bohdan · Answer 3 · 2015-10-17T02:14:27.897

45

Easy approach:

CREATE TABLE T
(
    C [nvarchar](100) NOT NULL UNIQUE,
);

SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
BEGIN TRAN
    INSERT INTO T VALUES ('A');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('C');
COMMIT TRAN
SELECT * FROM T; -- Check after.
DELETE T;

edited Oct 17 '15 at 02:14

answered Oct 17 '15 at 02:07

Bohdan

16,531
16
74
68

Correct use of transactions in SQL Server

3 Answers3

Linked

Related