Correct use of transactions in SQL Server
Solution 1
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
Solution 2
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.
Solution 3
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;
Related videos on Youtube
Saeid
Updated on April 19, 2022Comments
-
Saeid about 2 years
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 theUPDATE
command has a problem.How can I implement this to rollback both commands if any of them have an error in execution?
-
Piotr Nawrot over 8 yearsShouldn't
BEGIN TRANSACTION [Tran1]
be placed insideTRY
? Anyway - very simple and elegant piece of code. -
Monsignor over 8 years@PiotrNawrot No, if the transaction creation failed there is no need to rollback it in the catch.
-
4AM about 8 yearsIn other words, your transaction is not atomic unless you SET XACT_ABORT ON first.
-
BurnsBA about 4 yearsIt's hard to see with url underlining, but there's an underscore in
XACT_ABORT
-
Kevin LeStarge about 3 yearsIf you want to see the error, then include this in the catch:
SELECT ERROR_MESSAGE() AS ErrorMessage;
-
Leponzo over 2 years@KevinLeStarge or simply
THROW;
if you're using SQL Server >= 2012 as mentioned here -
J. Rockwood about 2 yearsNote - For XACT_ABORT: OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger
-
U. Busto almost 2 yearsJust a clarification please, ¿ SET XACT_ABORT ON applies only for the current procedure?
-
Nikola Markovinović almost 2 years@U.Busto Current transaction, yes.