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.
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.
The following is a reference fragment:
The following are the referenced contents: 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 ' |
This allows you to detect the execution of suspicious SQL through exec or sp_executesql, so that you can catch an abnormally terminated error later.
Second, the extraction of isolated affairs:
1, the emergence of isolated affairs
The following is a reference fragment:
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 ' didn't 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 can see that the active transaction number of the current connection is 1, and repeat execution will accumulate each time, which is very resource-intensive.
The rollback should not be rolled back at all.
2. Using existing means to resolve isolated affairs
The following is a reference fragment:
The following are the referenced contents: Print @ @trancount print ' Active transaction count for current connection '--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 called
BEGIN Tran
SELECT * From an absent table
If @ @error <>0
Begin
print ' didn't perform to come here! '
If @ @trancount <>0 rollback Tran
End
Commit Tran
After---execution, you look at the active transaction number of the current connection is 1, but repeat execution does not accumulate
Print @ @trancount print ' Active transaction number of current connection ' |
Third, the use of setxact_abort to control the partial violation of constraints of the implementation of the error
|
CreateTableTable1 (AintCheck (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
However, set Xact_abort does not work for errors generated by compilation, and also creates 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
The SELECT @ @trancount the number of active transactions for the current connection---has two orphaned transactions
If @ @trancount <>0 rollback Tran
|
