We have client app that is running some SQL on a SQL Server 2005 such as the following:
BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;
It is sent by one long string command.
If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction? If it does not rollback, do I have to send a second command to roll it back?
I can give specifics about the api and language I'm using, but I would think SQL Server should respond the same for any language.
This question is related to
sql
sql-server
sql-server-2005
transactions
You can put set xact_abort on
before your transaction to make sure sql rolls back automatically in case of error.
If one of the inserts fail, or any part of the command fails, does SQL server roll back the transaction?
No, it does not.
If it does not rollback, do I have to send a second command to roll it back?
Sure, you should issue ROLLBACK
instead of COMMIT
.
If you want to decide whether to commit or rollback the transaction, you should remove the COMMIT
sentence out of the statement, check the results of the inserts and then issue either COMMIT
or ROLLBACK
depending on the results of the check.
Here the code with getting the error message working with MSSQL Server 2016:
BEGIN TRY
BEGIN TRANSACTION
-- Do your stuff that might fail here
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
From MDSN article, Controlling Transactions (Database Engine).
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL).
In your case it will rollback the complete transaction when any of inserts fail.
You are correct in that the entire transaction will be rolled back. You should issue the command to roll it back.
You can wrap this in a TRY CATCH
block as follows
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
Source: Stackoverflow.com