Transaction not rolling back with PK violation

user_9910164 0
Viewed 399

As per my knowledge if we start transaction (begin tran/commit tran), it will be completely done or nothing. But when I am executing below TSQL code the first insert statement works while the 2nd doesn't.

Background: table A has two columns (ID primary key, Name varchar), and it already had 3 rows of data (ID of 1,2,3). 

begin tran
    insert into A values (4, 'Tim') -- this works 
    insert into A values (2, 'Tom') -- this doesn't work because it violates the PK constraint
commit tran

select * from A

Here is my question: since the 2nd insert statement violates the PK constraint and couldn't be committed, I was thinking that everything inside this transaction should all be rolled back because the transaction should be succeed or fail as one unit. But in fact, 'Tim' is added into A while 'Tom' didn't. Does this violate the automicity of transaction?

transactions sql-server sql
2 Answers

It depends on how you handle errors in your transaction. If you catch them, or if you ignore them (it seems you are ignoring them), then the transaction will continue and will commit.

Any decent "transaction manager" of a programming language/framework:

  • Will stop the execution of the code and will roll the transaction back, or
  • Will doom the transaction, so a commit will never be carried out. It will be replaced by a roll back instead.

If you run these commands at the SQL prompt, you are probably not using any transaction manager, and that's why you may be ignoring the error purposedly and carrying on as if everything was good.

user_64361910

That's not how transactions work in SQL Server. If you have a "statement-terminating" error, SQL Server just continues to the next statement. If you have a "batch-terminating" error, the transaction is aborted and rolled back.

Now I don't want that behaviour ever.

So the first line I write in every stored procedure is:

SET XACT_ABORT ON;

That tells SQL Server that "statement-terminating" errors should be automatically promoted to "batch-terminating" errors. Add that statement to the beginning of your script and you'll see that it now works as expected.

user_120691780
Related