×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Calgary

ZT...The best article series I've ever seen talking about TSQL transaction---All About Transactions - Part 1

whatwhenwherewhy(一生何求)
本文发表在 rolia.net 枫下论坛Everyone knows that a transaction is defined as a "Unit of work." The transaction completes only if all the individual steps are successful, otherwise it all rolls back as if nothing ever happened, right? That simple definition belies some devilishly complex behavior on the part of SQL Server. Odds are that if you are a DBA or developer SQL Server's transaction handling behavior has left you scratching your head and muttering "What in the world...?" at some point in your career. I was recently confronted by an angry mob of developers (OK, it was only three...and they were more confused than angry) who were baffled at SQL Server's behavior regarding transaction processing and locking. I was able to explain what was happening and fix their problem, but over the years I have come to realize that there is widespread confusion on the subject. I suspect that the details of transaction handling go largely ignored because they CAN be; in most circumstances the default behavior works well--or well enough. My objective is to pull the lid off the transaction "black box" giving you the tools you need to understand what's going on under the covers. When you understand the details you can make SQL Server do your bidding instead of crossing your fingers and hoping for the best.

It has become evident that many of us don't really understand how to put the power of transactions to work. For instance, just placing several SQL commands between a BEGIN TRAN<ACTION> and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction. To get consistent, reliable results you need to understand Transaction Modes, Transaction Isolation Levels, locking, nesting, error handling, Savepoints, XACT_ABORT, bound connections, and just for good measure you need to understand how ANSI_DEFAULTS and your choice of provider affect transaction behavior.

Due to the large number of variables to be considered, I will treat this subject in a series of articles; this will allow me to cover each topic in some depth. I'll state right upfront that the vast majority of this information is available in Books Online. It just tends to be scattered across many different pages, and frankly Books Online, as good a resource as it is, can sometimes be downright misleading. So I'm trying to pull it all together for easier consumption and provide some easy to follow examples. Enough introduction, let's dig in...

Transaction Modes

SQL Server supports three Transaction Modes: Autocommit, Explicit, and Implicit.

Autocommit Mode is the default behavior for SQL Server. In this mode each SQL statement that is issued acts as a single transaction and commits on completion so there is no need--or even an opportunity--to issue a COMMIT or ROLLBACK. Autocommit is the default behavior for ADO, OLE DB, ODBC, or DB-Library connections.
Explicit Mode is entered whenever you issue a BEGIN TRAN command and ends when you COMMIT the top level transaction (see below for a discussion of Nested Transactions) or issue a ROLLBACK. There is no configuration setting that turns Explicit Mode on and off. Once your explicit transaction is committed or rolled back SQL Server immediately returns to the transaction mode it was in before.
Implicit Mode is a bit trickier for most of us to grasp. By default IMPLICIT_TRANSACTIONS is off (in other words Autocommit Mode). If you issue the SET IMPLICIT_TRANSACTIONS ON command a transaction is started for each SQL statement listed in the table below, unless there is already an open transaction, but is not committed on completion. Subsequent commands join the transaction until you issue a COMMIT or ROLLBACK. SET ANSI_DEFAULTS ON puts SQL Server into Implicit Mode, it also turns on several other options that I won't deal with here, but are documented in Books Online.
The following SQL statements start transactions.

ALTER TABLE INSERT
CREATE OPEN*
DELETE REVOKE
DROP SELECT*
FETCH* TRUNCATE TABLE
GRANT UPDATE

*Notice that SELECT, OPEN, and FETCH start transactions but COMMIT and ROLLBACK make only limited sense for them because they don't modify data. Nevertheless the transaction is held open until explicitly ended, depending on your Transaction Isolation Level, or Lock Hints used in the query this can have a major impact on concurrency if you're not careful.

There is a widely circulated myth that CREATE, ALTER, DROP, and TRUNCATE statements can't be rolled back, they can be if you are in Implicit or Explicit Mode. In Autocommit Mode nothing can be rolled back since any SQL statement automatically commits upon completion.

A demonstration seems be in order: (issue each statement in turn from Query Analyzer, if you execute them all as a batch it still works, but won't be as clear.)

SET IMPLICIT_TRANSACTIONS ON

CREATE TABLE TranTest (
Col1 int IDENTITY,
Col2 int)

SELECT @@TRANCOUNT
--Returns 1, indicating that there is one open transaction
--Now let's throw some data into our table with a simple loop
DECLARE @count int
SET @count = 0
WHILE @count <15
Begin
INSERT TranTest (Col2)
VALUES (0)
SET @count = @count + 1
END
SELECT @@TRANCOUNT
/*
This still returns 1 indicating that all our statements so far are part of the same
transaction. Of course it could also mean that each previous statement was its own
transaction and was committed when the next statement was executed and only the last
statement's transaction is now left open, but I will show you that this is not the case.
*/
SELECT * FROM TranTest --We see our data.
COMMIT
--Makes our table and data changes permanent.
-- Notice there is no corresponding BEGIN TRAN since we are in Implicit Mode.
SELECT @@TRANCOUNT
--This should now return 0 to show that there are no open transactions
--Now let's alter the table and add a few more rows to show that the
-- statements are all lumped into a single transaction
ALTER TABLE TRANTEST ADD Col3 int
DECLARE @count int
SET @count = 0
WHILE @count <15
Begin
INSERT TranTest (Col2)
VALUES (0)
SET @count = @count + 1
END
SELECT * FROM TranTest
--Now we see Col3 and the additional data
SELECT @@TRANCOUNT
--Returns 1
ROLLBACK
SELECT * FROM TranTest
/*
Now you see it...Now you don't. Both the ALTER and INSERTs are rolled back
showing that all the statements are grouped into a single transaction until a
COMMIT or ROLLBACK is issued..
*/

Nested Transactions
Transactions can be nested, but some aspects of nested transactions may be a bit surprising for the uninitiated; it does make sense though, so let's take a look. First, unlike stored procedures which are limited to 32 levels, there is apparently no practical limit to the number of nesting levels for transactions. I quit trying to reach the limit when I got to well over 200 million levels--which is way more than I'll ever need! @@TRANCOUNT's return data type is an integer so it would probably run into problems if your nesting level exceeded 2.14 billion or so...

To nest transactions you simply string together two or more BEGIN TRAN statements without a COMMIT or ROLLBACK statement "between" them. It should be obvious that transactions can only be nested when in Explicit Mode. Or to be more precise, upon issuing the required BEGIN TRAN statements SQL enters Explicit Mode.

BEGIN TRAN --We enter Explicit Mode here
INSERT TranTest (Col2)
VALUES (1)
SELECT @@TRANCOUNT
--Returns 1
SELECT * FROM TranTest
BEGIN TRAN
DELETE TranTest
WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
SELECT @@TRANCOUNT
--Returns 2 indicating that there are now two open transactions.
SELECT * FROM TranTest
--We can see that both the INSERT and DELETE appear to have taken effect.
COMMIT
--Decrements @@TRANCOUNT by one but doesn't really commit the inner transaction.
ROLLBACK
--Rolls back both transactions and sets @@TRANCOUNT to 0

Remember that a transaction--to include any nested transactions--must all commit or rollback together. Because of this, the first COMMIT really does nothing more than closes the inner transaction, it can't make the changes permanent since the inner transaction depends on the outcome of any higher level transactions. The COMMIT that is issued when @@TRANCOUNT is 1 is what I call the "Golden COMMIT" and finally makes all the changes permanent. Even though the inner COMMIT doesn't seem to do much, you can't neglect to issue it. The entire transaction is only committed after one COMMIT has been issued for every open transaction. In contrast ROLLBACK will always roll back all open transactions; "if one fails, they all fail" A result of ROLLBACK's behavior is that if the ROLLBACK is issued and then you try to issue a COMMIT or ROLLBACK you will get a 3902 or 3903 error respectively which indicates that there are no open transactions to COMMIT or ROLLBACK. For this, and other reasons I'll get into later, you should include error handling in your SQL code. If you check @@TRANCOUNT before issuing a COMMIT or ROLLBACK you can pretty well eliminate 3902 and 3903 errors.

Just to cement the concept, try the following example:

DECLARE @Count int
SET @Count = 0
WHILE @Count < 100
BEGIN
BEGIN TRAN
--We aren't doing any work here, just nesting transactions...
SET @Count = @Count + 1
END
SELECT @@TRANCOUNT
--Should return 100
COMMIT
SELECT @@TRANCOUNT
--"99 bottles of beer on the wall..."
ROLLBACK
SELECT @@TRANCOUNT
--"Go to Jail, don't pass GO..." We're right back to 0

The idea of nesting transactions applies to triggers and stored procedures too. If you have a complex set of stored procedures that call each other it can get difficult to follow all the possible paths, but any nested transactions will act exactly as I have already described. I should mention that there is always the error caveat that can really throw a monkey wrench into things. I'll deal with errors in some detail later, but will completely ignore errors for now. Below is a simple example of how you can nest a transaction across stored procedures:

CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
INSERT TranTest (Col2)
VALUES (1)
EXEC TranProc2 --Call the other procedure from within the transaction
COMMIT --First level

CREATE PROC TranProc2
AS
BEGIN TRAN --Start the second level transaction
DELETE TranTest
WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
COMMIT --Second level

It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures.
CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
INSERT TranTest (Col2)
VALUES (1)
BEGIN TRAN --Start the second level transaction
EXEC TranProc2
COMMIT --Second level
COMMIT --First level

This stored procedure would do exactly the same thing as the first example, assuming that the second procedure lacked the BEGIN TRAN and COMMIT statements. Actually, even if the second procedure had the BEGIN...COMMIT statements in it, this procedure would work fine and perform the same function, we would just have three levels of transactions, the second of which would do nothing. So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function.

So far we have discussed SQL Server's Transaction Modes and the idea of nested transactions. I have tried to show how these two concepts relate to each other and hinted at how they relate to some of the other concepts listed in the introduction. In the future articles I'll cover these additional topics and try to tie them all together.


By Don Peterson Thursday, September 30, 2004更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#1995933@0)
Last Updated: 2004-11-24
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / ZT...The best article series I've ever seen talking about TSQL transaction---All About Transactions - Part 1 -whatwhenwherewhy(一生何求); 2004-11-24 {11692} (#1995933@0)
    本文发表在 rolia.net 枫下论坛Everyone knows that a transaction is defined as a "Unit of work." The transaction completes only if all the individual steps are successful, otherwise it all rolls back as if nothing ever happened, right? That simple definition belies some devilishly complex behavior on the part of SQL Server. Odds are that if you are a DBA or developer SQL Server's transaction handling behavior has left you scratching your head and muttering "What in the world...?" at some point in your career. I was recently confronted by an angry mob of developers (OK, it was only three...and they were more confused than angry) who were baffled at SQL Server's behavior regarding transaction processing and locking. I was able to explain what was happening and fix their problem, but over the years I have come to realize that there is widespread confusion on the subject. I suspect that the details of transaction handling go largely ignored because they CAN be; in most circumstances the default behavior works well--or well enough. My objective is to pull the lid off the transaction "black box" giving you the tools you need to understand what's going on under the covers. When you understand the details you can make SQL Server do your bidding instead of crossing your fingers and hoping for the best.

    It has become evident that many of us don't really understand how to put the power of transactions to work. For instance, just placing several SQL commands between a BEGIN TRAN<ACTION> and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction. To get consistent, reliable results you need to understand Transaction Modes, Transaction Isolation Levels, locking, nesting, error handling, Savepoints, XACT_ABORT, bound connections, and just for good measure you need to understand how ANSI_DEFAULTS and your choice of provider affect transaction behavior.

    Due to the large number of variables to be considered, I will treat this subject in a series of articles; this will allow me to cover each topic in some depth. I'll state right upfront that the vast majority of this information is available in Books Online. It just tends to be scattered across many different pages, and frankly Books Online, as good a resource as it is, can sometimes be downright misleading. So I'm trying to pull it all together for easier consumption and provide some easy to follow examples. Enough introduction, let's dig in...

    Transaction Modes

    SQL Server supports three Transaction Modes: Autocommit, Explicit, and Implicit.

    Autocommit Mode is the default behavior for SQL Server. In this mode each SQL statement that is issued acts as a single transaction and commits on completion so there is no need--or even an opportunity--to issue a COMMIT or ROLLBACK. Autocommit is the default behavior for ADO, OLE DB, ODBC, or DB-Library connections.
    Explicit Mode is entered whenever you issue a BEGIN TRAN command and ends when you COMMIT the top level transaction (see below for a discussion of Nested Transactions) or issue a ROLLBACK. There is no configuration setting that turns Explicit Mode on and off. Once your explicit transaction is committed or rolled back SQL Server immediately returns to the transaction mode it was in before.
    Implicit Mode is a bit trickier for most of us to grasp. By default IMPLICIT_TRANSACTIONS is off (in other words Autocommit Mode). If you issue the SET IMPLICIT_TRANSACTIONS ON command a transaction is started for each SQL statement listed in the table below, unless there is already an open transaction, but is not committed on completion. Subsequent commands join the transaction until you issue a COMMIT or ROLLBACK. SET ANSI_DEFAULTS ON puts SQL Server into Implicit Mode, it also turns on several other options that I won't deal with here, but are documented in Books Online.
    The following SQL statements start transactions.

    ALTER TABLE INSERT
    CREATE OPEN*
    DELETE REVOKE
    DROP SELECT*
    FETCH* TRUNCATE TABLE
    GRANT UPDATE

    *Notice that SELECT, OPEN, and FETCH start transactions but COMMIT and ROLLBACK make only limited sense for them because they don't modify data. Nevertheless the transaction is held open until explicitly ended, depending on your Transaction Isolation Level, or Lock Hints used in the query this can have a major impact on concurrency if you're not careful.

    There is a widely circulated myth that CREATE, ALTER, DROP, and TRUNCATE statements can't be rolled back, they can be if you are in Implicit or Explicit Mode. In Autocommit Mode nothing can be rolled back since any SQL statement automatically commits upon completion.

    A demonstration seems be in order: (issue each statement in turn from Query Analyzer, if you execute them all as a batch it still works, but won't be as clear.)

    SET IMPLICIT_TRANSACTIONS ON

    CREATE TABLE TranTest (
    Col1 int IDENTITY,
    Col2 int)

    SELECT @@TRANCOUNT
    --Returns 1, indicating that there is one open transaction
    --Now let's throw some data into our table with a simple loop
    DECLARE @count int
    SET @count = 0
    WHILE @count <15
    Begin
    INSERT TranTest (Col2)
    VALUES (0)
    SET @count = @count + 1
    END
    SELECT @@TRANCOUNT
    /*
    This still returns 1 indicating that all our statements so far are part of the same
    transaction. Of course it could also mean that each previous statement was its own
    transaction and was committed when the next statement was executed and only the last
    statement's transaction is now left open, but I will show you that this is not the case.
    */
    SELECT * FROM TranTest --We see our data.
    COMMIT
    --Makes our table and data changes permanent.
    -- Notice there is no corresponding BEGIN TRAN since we are in Implicit Mode.
    SELECT @@TRANCOUNT
    --This should now return 0 to show that there are no open transactions
    --Now let's alter the table and add a few more rows to show that the
    -- statements are all lumped into a single transaction
    ALTER TABLE TRANTEST ADD Col3 int
    DECLARE @count int
    SET @count = 0
    WHILE @count <15
    Begin
    INSERT TranTest (Col2)
    VALUES (0)
    SET @count = @count + 1
    END
    SELECT * FROM TranTest
    --Now we see Col3 and the additional data
    SELECT @@TRANCOUNT
    --Returns 1
    ROLLBACK
    SELECT * FROM TranTest
    /*
    Now you see it...Now you don't. Both the ALTER and INSERTs are rolled back
    showing that all the statements are grouped into a single transaction until a
    COMMIT or ROLLBACK is issued..
    */

    Nested Transactions
    Transactions can be nested, but some aspects of nested transactions may be a bit surprising for the uninitiated; it does make sense though, so let's take a look. First, unlike stored procedures which are limited to 32 levels, there is apparently no practical limit to the number of nesting levels for transactions. I quit trying to reach the limit when I got to well over 200 million levels--which is way more than I'll ever need! @@TRANCOUNT's return data type is an integer so it would probably run into problems if your nesting level exceeded 2.14 billion or so...

    To nest transactions you simply string together two or more BEGIN TRAN statements without a COMMIT or ROLLBACK statement "between" them. It should be obvious that transactions can only be nested when in Explicit Mode. Or to be more precise, upon issuing the required BEGIN TRAN statements SQL enters Explicit Mode.

    BEGIN TRAN --We enter Explicit Mode here
    INSERT TranTest (Col2)
    VALUES (1)
    SELECT @@TRANCOUNT
    --Returns 1
    SELECT * FROM TranTest
    BEGIN TRAN
    DELETE TranTest
    WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
    SELECT @@TRANCOUNT
    --Returns 2 indicating that there are now two open transactions.
    SELECT * FROM TranTest
    --We can see that both the INSERT and DELETE appear to have taken effect.
    COMMIT
    --Decrements @@TRANCOUNT by one but doesn't really commit the inner transaction.
    ROLLBACK
    --Rolls back both transactions and sets @@TRANCOUNT to 0

    Remember that a transaction--to include any nested transactions--must all commit or rollback together. Because of this, the first COMMIT really does nothing more than closes the inner transaction, it can't make the changes permanent since the inner transaction depends on the outcome of any higher level transactions. The COMMIT that is issued when @@TRANCOUNT is 1 is what I call the "Golden COMMIT" and finally makes all the changes permanent. Even though the inner COMMIT doesn't seem to do much, you can't neglect to issue it. The entire transaction is only committed after one COMMIT has been issued for every open transaction. In contrast ROLLBACK will always roll back all open transactions; "if one fails, they all fail" A result of ROLLBACK's behavior is that if the ROLLBACK is issued and then you try to issue a COMMIT or ROLLBACK you will get a 3902 or 3903 error respectively which indicates that there are no open transactions to COMMIT or ROLLBACK. For this, and other reasons I'll get into later, you should include error handling in your SQL code. If you check @@TRANCOUNT before issuing a COMMIT or ROLLBACK you can pretty well eliminate 3902 and 3903 errors.

    Just to cement the concept, try the following example:

    DECLARE @Count int
    SET @Count = 0
    WHILE @Count < 100
    BEGIN
    BEGIN TRAN
    --We aren't doing any work here, just nesting transactions...
    SET @Count = @Count + 1
    END
    SELECT @@TRANCOUNT
    --Should return 100
    COMMIT
    SELECT @@TRANCOUNT
    --"99 bottles of beer on the wall..."
    ROLLBACK
    SELECT @@TRANCOUNT
    --"Go to Jail, don't pass GO..." We're right back to 0

    The idea of nesting transactions applies to triggers and stored procedures too. If you have a complex set of stored procedures that call each other it can get difficult to follow all the possible paths, but any nested transactions will act exactly as I have already described. I should mention that there is always the error caveat that can really throw a monkey wrench into things. I'll deal with errors in some detail later, but will completely ignore errors for now. Below is a simple example of how you can nest a transaction across stored procedures:

    CREATE PROC TranProc1
    AS
    BEGIN TRAN --Start the first level transaction
    INSERT TranTest (Col2)
    VALUES (1)
    EXEC TranProc2 --Call the other procedure from within the transaction
    COMMIT --First level

    CREATE PROC TranProc2
    AS
    BEGIN TRAN --Start the second level transaction
    DELETE TranTest
    WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
    COMMIT --Second level

    It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures.
    CREATE PROC TranProc1
    AS
    BEGIN TRAN --Start the first level transaction
    INSERT TranTest (Col2)
    VALUES (1)
    BEGIN TRAN --Start the second level transaction
    EXEC TranProc2
    COMMIT --Second level
    COMMIT --First level

    This stored procedure would do exactly the same thing as the first example, assuming that the second procedure lacked the BEGIN TRAN and COMMIT statements. Actually, even if the second procedure had the BEGIN...COMMIT statements in it, this procedure would work fine and perform the same function, we would just have three levels of transactions, the second of which would do nothing. So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function.

    So far we have discussed SQL Server's Transaction Modes and the idea of nested transactions. I have tried to show how these two concepts relate to each other and hinted at how they relate to some of the other concepts listed in the introduction. In the future articles I'll cover these additional topics and try to tie them all together.


    By Don Peterson Thursday, September 30, 2004更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • All About Transactions - Part 2 -whatwhenwherewhy(一生何求); 2004-11-26 {11823} (#1999617@0)
      本文发表在 rolia.net 枫下论坛In Part 1 of this series I talked about transactions in general, I explored the three Transaction Modes that SQL Server supports, and nested transactions. In that article I stated "...just placing several SQL commands between a BEGIN TRAN and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction." In this installment we will look into just what I meant by that statement.

      XACT_ABORT
      The lack of error handling is the primary reason that your transactions may not act the way you expect. The following example should illustrate the point:

      CREATE TABLE State (State char(2) PRIMARY KEY)
      CREATE TABLE City (City varchar(30),
      State char(2) REFERENCES State(State))
      --Populate a few states
      INSERT INTO State SELECT 'AK'
      INSERT INTO State SELECT 'CA'
      INSERT INTO State SELECT 'KS'
      INSERT INTO State SELECT 'UT'
      INSERT INTO State SELECT 'NY'
      BEGIN TRAN
      INSERT INTO City SELECT 'Anchorage', 'AK'
      INSERT INTO City SELECT 'Los Angles', 'CA'
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
      INSERT INTO City SELECT 'Topeka', 'KS'
      COMMIT
      SELECT * FROM City --The transaction is partially completed
      SELECT @@TRANCOUNT --Returns 0 to show that the partial transaction is actually committed.

      Even though we explicitly told SQL Server to treat all four INSERT's as a single transaction, it didn't. The reason is the error in the third INSERT. This example would work as a single transaction if the error encountered was fatal, like a deadlock, broken connection, hardware failure, etc... So, somewhat paradoxically, we have to worry more about minor (non-fatal) errors than we do about major errors, at least when it comes to creating robust transactions. SET XACT_ABORT is a command that at first glance fits the bill. Books Online states "When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back." Using the same example let's verify this:

      TRUNCATE TABLE City --Reinitialize the table data
      SET XACT_ABORT ON
      BEGIN TRAN
      INSERT INTO City SELECT 'Anchorage', 'AK'
      INSERT INTO City SELECT 'Los Angles', 'CA'
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
      INSERT INTO City SELECT 'Topeka', 'KS'
      COMMIT
      SELECT * FROM City --Now it works as it should!
      SELECT @@TRANCOUNT --Returns 0 again

      Sure enough, since one statement failed, none of them are committed. But, can we leave the subject of error handling? Not quite! XACT_ABORT (XA for short) works fine for run-time errors in a single transaction, but there is a problem that might not be very obvious. In fact, Books Online is outright misleading in this regard since it talks primarily of transactions; the word "batch" is only mentioned only once in a somewhat offhand way. Despite what Books Online leads you to believe, XA works at the batch level, not the transaction level. It will rollback any open transactions but it also stops processing the batch at the point the error is encountered.

      TRUNCATE TABLE City
      SET XACT_ABORT ON
      INSERT INTO City SELECT 'Anchorage', 'AK'
      INSERT INTO City SELECT 'Los Angles', 'CA'
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
      INSERT INTO City SELECT 'Topeka', 'KS'
      SELECT * FROM City --Anchorage and Los Angles are there
      SELECT @@TRANCOUNT --No open transactions so there is nothing to ROLLBACK or COMMIT

      This example uses Autocommit Mode so each statement is its own transaction. If XA was off, the third INSERT would fail and SQL would return an error, but it would continue processing the fourth statement. With XA on, the current transaction is rolled back, and the batch is aborted so the fourth statement is never executed. This rule applies no matter what Transaction Mode you are in.

      TRUNCATE TABLE City
      SET XACT_ABORT ON
      BEGIN TRAN --Begin first transaction
      INSERT INTO City SELECT 'Anchorage', 'AK'
      INSERT INTO City SELECT 'Los Angles', 'CA'
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table. Nothing below this point is processed.
      INSERT INTO City SELECT 'Topeka', 'KS'
      COMMIT
      BEGIN TRAN --Begin second transaction
      INSERT INTO City SELECT 'New York', 'NY'
      COMMIT
      SELECT * FROM City --0 rows returned

      SQL Server stopped processing the batch at the point of the first error. It should be noted that if the error had been in the second transaction, the first transaction would complete and commit successfully, only the second one would fail. Since the error occurs in the first transaction, neither one is committed, actually, at the risk of being redundant, the second transaction is never even executed, much less committed. If XA truly worked at the transaction level, the first transaction would be rolled back and the second one would be processed and committed. Hopefully it is now obvious that if you turn on XA, you also eliminate your ability to trap and respond to runtime errors. XA can be a useful shortcut, but you should be aware of its limitations.

      Compilation Errors
      A word about compile errors: Books Online states "Compile errors, such as syntax errors, are not affected by SET XACT_ABORT." The reason for this is that SQL Server employs a three step process when executing a batch. First the entire batch is parsed, or checked for syntactical correctness. Then the batch is compiled and an execution plan is created. Finally the compiled code is run. If the batch isn't able to be successfully parsed and compiled, no part of the batch is ever executed. Personally, I think compile errors is a bit of a misnomer here since if you have a syntax error, the batch never makes it to the compilation stage.

      But certain run-time errors are handled in a rather inconvenient way. Unlike most run-time errors, problems with object name resolution are not handled by XA, or any other kind of error checking for that matter. Let's take a look at an example using the same table from Part 1 of this series:

      CREATE TABLE TranTest (
      Col1 int IDENTITY,
      Col2 int)


      --Populate some sample data

      DECLARE @count int
      SET @count = 0
      WHILE @count <15
      Begin
      INSERT TranTest (Col2)
      VALUES (0)
      SET @count = @count + 1
      END
      SET XACT_ABORT ON
      BEGIN TRAN
      UPDATE TranTest
      SET Col2 = 2
      WHERE Col1 = 2
      INSERT INTO NoTable --This table does not exist
      SELECT TOP 1 * FROM TranTest
      COMMIT
      SELECT * FROM TranTest --XA does not have any effect, you can run the same statements with XA off and get the same result
      SELECT @@TRANCOUNT --Retruns 1...?!?!

      Name resolution errors cause SQL Server to stop processing the batch at the point it hits the error, thus the COMMIT is never issued. This means that the transaction can still be rolled back; it also means that it can be committed in its partially completed state. The problem is that if you are not aware of this fact you are likely to do nothing, leaving the transaction open and the locks held. These errors are most likely to occur in Dynamic SQL or stored procedures where deferred name resolution would allow us to create the above query as a stored procedure without any indication of a problem. One interesting thing to note is that if you created a stored procedure like this and then executed it, you would get two errors. Error 208 indicates an invalid object, and error 266 indicates that we are missing a COMMIT or ROLLBACK and that the transaction has been left open. Why error 266 isn't returned if we just run the query ad hoc, I'm not sure...

      Unfortunately name resolution errors aren't easy to deal with because like having XA on, SQL Server stops processing the batch as soon as it hits the error. This means that there is no practical way of dealing with these errors programmatically. The only option is to find and eliminate them in the testing process.

      Error Handling
      OK, on to error checking. While the current state of T-SQL error checking isn't optimal, it isn't all that bad either and besides, you need to do it if you want your SQL to be robust. Essentially it boils down to this: SQL Server has a built in @@ERROR function that returns the error code for the last SQL statement executed, or it returns 0 if the last statement was successful. Since the value of @@ERROR is reset after each statement (regardless of Transaction Mode or anything else) you must check it after every statement in your transactions. When trapping for errors there are many stylistic variations, but generally along two major themes. Which you use is purely a matter of style and familiarity. Let me demonstrate by revisiting our City table again:

      TRUNCATE TABLE City
      BEGIN TRAN
      INSERT INTO City SELECT 'Anchorage', 'AK'
      IF @@ERROR <> 0 GOTO ErrBlock
      INSERT INTO City SELECT 'Los Angles', 'CA'
      IF @@ERROR <> 0 GOTO ErrBlock
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
      IF @@ERROR <> 0 GOTO ErrBlock
      INSERT INTO City SELECT 'Topeka', 'KS'
      IF @@ERROR <> 0 GOTO ErrBlock
      COMMIT --We never get to this point unless all IF statements evaluate to false (the preceding INSERT is successful)
      RETURN --Ensures that SQL Server never executes the ErrBlock unless explicitly told to do so by one of our IF statements
      ErrBlock:
      ROLLBACK
      RETURN /*This is somewhat superfluous--like Krusty's third nipple--since it is the end of the batch anyway. I usually include it for clarity and consistency*/
      SELECT * FROM City --The entire transaction was rolled back as expected
      SELECT @@TRANCOUNT --"nothing up my sleeve"

      Essentially, the addition of this error handling code does the same thing as turning on XA. However, this kind of explicit error checking allows you much greater control over what happens in response to an error. It does tend to clutter things up a bit and makes for additional typing, but I've found that with this particular style of error handling (meaning the GOTO ErrBlock) it is pretty much a matter of cut and paste that takes a minimal amount of time and virtually eliminates typo's.

      One of the alternative styles of error handling is like this example:

      DECLARE @Err1 int,
      @Err2 int,
      @Err3 int,
      @Err4 int
      BEGIN TRAN
      INSERT INTO City SELECT 'Anchorage', 'AK'
      SET @Err1 = @@ERROR
      INSERT INTO City SELECT 'Los Angles', 'CA'
      SET @Err2 = @@ERROR
      INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
      SET @Err3 = @@ERROR
      INSERT INTO City SELECT 'Topeka', 'KS'
      SET @Err4 = @@ERROR
      IF @Err1 = 0 AND @Err2 = 0 AND @Err3 = 0 AND @Err4 = 0
      COMMIT
      ELSE
      ROLLBACK

      These two examples both do the job, but I think the first one is easier. This method doesn't lend itself to cutting and pasting as well as the first, plus you have to declare variables which adds keystrokes and increases the likelihood of a typo. If there is a performance difference between the two I can't seem to detect it. Whichever style you like, and there are a number of different variations, just pick one that works for you and stick with it. Consistency is one of the great keys to good coding.

      While we are all looking forward to SQL Server 2005 and its "TRY...CATCH" error handling, we have to deal with what we have. If you want your transactions, and by extension, your applications to be robust you cannot ignore errors. It isn't really that difficult and it can save you some pretty significant headaches down the road.

      In the next article I'll tackle Transaction Isolation Levels; which I think is a big enough subject to rate its own article.


      By Don Peterson Tuesday, October 05, 2004更多精彩文章及讨论，请光临枫下论坛 rolia.net

More Topics

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论