How transactions are defined in SQL server

Question

Hi All

Today i was doing some testing and come across a small issue. when i ran insert statements in a batch(begin\end) , eventhough one of statements is failing with error :"Arithmetic overflow error converting "  

But rest of insert statements get inserted in table. I was expecting a rollback for all inserts as per ACID properties  (Atomicity)

Code is below :

create table emp_test (id int , age int)
Go
--> Completed Successfully
begin
insert into emp_test values (1 ,1)
insert into emp_test values (2 ,2)
insert into emp_test values (9 ,999999999000000000000000)
insert into emp_test values (3 ,3)
insert into emp_test values (4 ,4)
insert into emp_test values (5 ,5)
end
Go
/*(1 row(s) affected)

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
*/
--> how come after failure of 3rd insert rest of inserts are sucessfull

--> Output of 
select * from emp_test
/*
id	age
1	1
2	2
3	3
4	4
5	5

*/

 Can anyone please explain this functionality of inserts inside Begin \End

---

Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 1

Unless you specifically define a transaction [begin tran and commit tran] the concept of atomicity/commit/rollback is confined to individual sql. Your exact scenario is described here

http://msdn.microsoft.com/en-us/library/ms187878%28v=sql.105%29.aspx

Answer 2

Begin End does not specify a transaction.  It would delimit a block of work after a conditional.  In the case of your code it does nothing.

IF you want a transaction then you need something like this:

create table emp_test (id int , age int)
Go
--> Completed Successfully
begin tran
begin try
insert into emp_test values (1 ,1)
insert into emp_test values (2 ,2)
insert into emp_test values (9 ,999999999000000000000000)
insert into emp_test values (3 ,3)
insert into emp_test values (4 ,4)
insert into emp_test values (5 ,5)
commit tran
end try
begin catch
  rollback tran
end catch
Go

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 3

Besides BEGIN TRAN.. COMMIT TRAN  you need to add SET XACT_ABORT ON;

http://msdn.microsoft.com/en-us/library/ms188792.aspx

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 4

Besides BEGIN TRAN.. COMMIT TRAN  you need to add SET XACT_ABORT ON;

http://msdn.microsoft.com/en-us/library/ms188792.aspx

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Not if you are using a try/catch block.

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 5

Hi,

The Begin and End of the batch doesn't not specify the Transaction Atomicity for multiple SQL statement/transactions.

You would be correct if there is only 1 rows needs inserting of data. For multiple transaction you need wrap the SQL with Begin Tran and End Tran.

create table emp_test (id int , age int)
Go
--> Completed Successfully
begin Try
Begin Tran
insert into emp_test values (1 ,1)
insert into emp_test values (2 ,2)
insert into emp_test values (9 ,999999999000000000000000)
insert into emp_test values (3 ,3)
insert into emp_test values (4 ,4)
insert into emp_test values (5 ,5)
Commit Tran
End try
Begin Catch
Rollback Tran
End Catch
end

Answer 6

Hi All

Today i was doing some testing and come across a small issue. when i ran insert statements in a batch(begin\end) , eventhough one of statements is failing with error :"Arithmetic overflow error converting "  

But rest of insert statements get inserted in table. I was expecting a rollback for all inserts as per ACID properties  (Atomicity)

Code is below :

create table emp_test (id int , age int)
Go
--> Completed Successfully
begin
insert into emp_test values (1 ,1)
insert into emp_test values (2 ,2)
insert into emp_test values (9 ,999999999000000000000000)
insert into emp_test values (3 ,3)
insert into emp_test values (4 ,4)
insert into emp_test values (5 ,5)
end
Go
/*(1 row(s) affected)

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
*/
--> how come after failure of 3rd insert rest of inserts are sucessfull

--> Output of 
select * from emp_test
/*
id	age
1	1
2	2
3	3
4	4
5	5

*/

 Can anyone please explain this functionality of inserts inside Begin \End

---

Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

Let me shed some light on this ...

In SQL Server, there are mainly 2 types of transactions ... a). Implicit and b). Explicit.

Implicit --> Default behavior of SQL Server when you dont specify "begin tran" and "end tran".

Explicit --> You explicitly define the transactions using begin and end tran.

Now to explain the differences :

Begin and End : This specifically deals with code blocks which is more or less similiar to { } (curly braces) in many programing languages.

Begin Transaction and End Transaction: This begins and ends a transaction and do not specify a new code block i.e. they mark the transaction boundaries.

---

HTH, \\K [Kindly mark the reply as answer if it helps - Support SQLServer Family :-)]

Answer 7

Thanks All

for explanation, below must be solution for my case

SET XACT_ABORT OFF;
begin tran
insert into emp_test values (1 ,1)
insert into emp_test values (2 ,2)
insert into emp_test values (9 ,999999999000000000000000)
insert into emp_test values (3 ,3)
insert into emp_test values (4 ,4)
insert into emp_test values (5 ,5)
Commit tran
Go

If one dont want to use try catch.

---

Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem