SQL Server's geek: exceptions and orphaned transactions

Source: Internet
Author: User
Tags commit end exception handling execution insert sql reference rollback
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



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.