Full exposure to SQL Server exceptions and orphaned transactions!

Server|sqlserver


first, starting from the error in SQL Server, there are some strange error-handling errors at the same level as 16 but the results are different.





SELECT * from an absent table


if @ @error <>0


print ' This has no output '


Go





RAISERROR (', 16, 3)


if @ @error <>0


print ' This output '


Go





EXEC (' select * from an Absent table ')


if @ @error <>0


print ' This output '


Go





exec sp_executesql N ' select * from an absent table '


if @ @error <>0


print ' This output '





so you can find that you execute suspicious SQL through exec or sp_executesql, so that you can catch an abnormally terminated error later.








two, leads to isolated affairs:


1, the generation of isolated affairs





SELECT @ @trancount number of active transactions for the current connection-the number of active transactions for the current connection is 0





BEGIN Tran





SELECT * from an absent table


if @ @error <>0


begin


print ' did not perform to come here! '


if @ @trancount <>0 rollback Tran


End





Commit Tran





SELECT @ @trancount the number of active transactions for the current connection-after execution you look at the active transaction number of the current connection is 1, and repeat execution will accumulate each time, which is very resource-intensive.




The
should be rollback and not be rolled back at all.





2, using existing means to resolve isolated affairs








Print @ @trancount print ' Number of active transactions for current connection '--the number of active transactions for the current connection is 0





if @ @trancount <>0 rollback Tran-write here to allow orphaned transactions to remain only until the next time your process is invoked


BEGIN Tran





SELECT * from an absent table


if @ @error <>0


begin


print ' did not perform to come here! '


if @ @trancount <>0 rollback Tran


End





Commit Tran





---Execute you look at the active transaction number of the current connection is 1, but repeat execution does not accumulate


Print @ @trancount print ' The number of active transactions currently connected '





third, use set Xact_abort to control the execution of partial violations of constraints





CREATE TABLE Table1 (a int check (a>100))


Go





set XACT_ABORT on


BEGIN Tran


Insert table1 VALUES (10)


print ' is not being executed here '


Commit Tran


Go


 


print ' print ' ============================================== ' print '


 


set Xact_abort off


BEGIN Tran


Insert table1 VALUES (10)


print ' is executed here '


Commit Tran





Go


drop TABLE table1





but set Xact_abort does not work for errors that are generated by compilation, and also generates orphaned transactions





set XACT_ABORT on


BEGIN Tran


insert an absent table values (10)


print ' is not being executed here '


Commit Tran


Go





print ' print ' ============================================== ' print '





set Xact_abort off


BEGIN Tran


insert an absent table values (10)


print ' is not being executed here '


Commit Tran


Go





SELECT @ @trancount the number of active transactions for the current connection---there are two orphaned transactions


if @ @trancount <>0 rollback Tran








for the various errors in SQL, and orphaned transactions in T-SQL programming must pay attention to the trap of isolated transactions, to avoid wasting or isolate resources, Microsoft publicly announced Sqlserve the next version of Yukon will have built-in exception handling syntax. At that time, you can have better control over unpredictable errors with code.