Can a constraint violation outside of a TRY block behave like an exception (and stop execution)?

user_533120 0
Viewed 90

Summary

I just discovered a surprising behavior of SQL Server: if a statement is executed outside of a TRY block, and it violates a constraint1, the execution will continue to the next statement.

I would have expected the execution to stop, like for an exception, but apparently constraint violations do not (always) behave like exceptions. However, if a statement is executed inside a TRY block, a constraint violation behaves like exception and stops the flow of execution before it reaches the next statement.

Test

Here is my test setup...

CREATE TABLE T (
    T_ID int,
    CONSTRAINT T_PK PRIMARY KEY (T_ID)
);

INSERT INTO T VALUES (1);

Below, the INSERT has violated the primary key, but the next statement is executed nonetheless ('next reached' is printed):

INSERT INTO T VALUES(1);
PRINT 'next reached';

Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'T_PK'. Cannot insert duplicate key in object 'dbo.T'. The duplicate key value is (1).
The statement has been terminated.
next reached

This is different from the "normal" exception ('next reached' is not printed):

THROW 50000, 'Some exception.', 0;
PRINT 'next reached';

Msg 50000, Level 16, State 0, Line 15
Some exception.

Curiously, if I put the INSERT into a TRY block, the constraint violation starts behaving like an exception (note how neither 'next reached' nor 'end reached' are printed):

BEGIN TRY
    INSERT INTO T VALUES(1);
    PRINT 'next reached';
END TRY
BEGIN CATCH
    THROW;
END CATCH
PRINT 'end reached'

(0 rows affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'T_PK'. Cannot insert duplicate key in object 'dbo.T'. The duplicate key value is (1).

Question

Is there a way to make the constraint violations behave like exceptions even when not enclosed within a TRY block?

Motivation: The above behavior is different from all programming languages supporting exceptions that I know of. When implementing complex business logic in stored procedures, using TRY everywhere would make the code less readable. More importantly, it would make it fragile: forgetting it somewhere, or relying on a caller to have it, and then somebody independently removing it from the caller, could easily make the stored procedure continue execution unexpectedly and potentially even corrupt the data.

1 I have tested PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints, and they all seem to behave the same.

--- EDIT ---

For what it's worth, I have created a suggestion for Microsoft, but I'm not holding my breath that it will be implemented soon (or ever):

Consistent flow of execution with arithmetic errors and constraint violations

tsql sql-server
1 Answers

Addressing your "Motivation" part, I strongly disagree. There are many versions of what is called try-transaction block, but sooner or later one realizes it should be included in almost every procedure. It's not more than 20 lines of code, so not really that pain to read. Here's what I use:

set xact_abort on -- rollsback when try-catch doesn't work, eg on table not found errors
begin try
    begin transaction    -- start the transaction
    ...........................
    commit
end try
begin catch
    if @@trancount > 0 rollback
    declare @rethrow nvarchar(4000)='Error >> num '+convert(nvarchar(max),error_number())+', line '+convert(nvarchar(max),error_line())+', message: '+error_message()
    raiserror(@rethrow, 16, 0)
end catch

As always, I recommend the corresponding Erland Sommarskog's article, inside of which you will find a table detailing the exact behaviour for the various combinations of error types, xact_abort on/off, and using or not of try/catch.

You also mention you want to let your clients choose whether to "rollback or retry". I am unfamiliar with this, but it has an ominous feeling. What is "retry"? If you plan to do some manual change and then retry, don't forget your procedure will still be probably holding locks if its transaction is still open, leading to bad situations. Best way would be to make a loop, where the transaction will be rolled back, and retry means to just fire it up from the beginning.

user_58259630
Related