English 中文(简体)
SQL Server - transactions roll back on error?
原标题:

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.

最佳回答

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.

问题回答

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

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

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.

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.





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...

热门标签