{"id":1359,"date":"2012-06-29T00:00:00","date_gmt":"2012-06-29T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/handling-constraint-violations-and-errors-in-sql-server\/"},"modified":"2021-09-29T16:21:48","modified_gmt":"2021-09-29T16:21:48","slug":"handling-constraint-violations-and-errors-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/handling-constraint-violations-and-errors-in-sql-server\/","title":{"rendered":"Handling Constraint Violations and Errors in SQL Server"},"content":{"rendered":"
\n

In this article, we’re going to take a problem and use it to explore transactions, and constraint violations, before suggesting a solution to the problem.<\/p>\n

The problem is this: we have a database which uses constraints; lots of them. It does a very solid job of checking the complex rules and relationships governing the data. We wish to import a batch of potentially incorrect data into the database, checking for constraint violations without throwing errors back at any client application, reporting what data caused the errors, and either rolling back the import or just the offending rows. This would then allow the administrator to manually correct the records and re-apply them. <\/p>\n

Just to illustrate various points, we’ll take the smallest possible unit of this problem, and provide simple code that you can use to experiment with. We’ll be exploring transactions and constraint violations<\/p>\n

Transactions<\/h2>\n

Transactions enable you to keep a database consistent, even after an error. They underlie every SQL data manipulation in order to enforce atomicity and consistency. They also enforce isolation, in that they also provide the way of temporarily isolating a connection from others that are accessing the database at the same time whilst a single unit of work is done as one or more SQL Statements. Any temporary inconsistency of the data is visible only to the connection. A transaction is both a unit of work and a unit of recovery. Together with constraints, transactions are the best way of ensuring that the data stored within the database is consistent and error-free.<\/p>\n

Each insert, update, and delete statement is considered a single transaction (Autocommit, in SQL Server jargon). However, only you can define what you consider a ‘unit of work’ which is why we have explicit transactions. Using explicit transactions in SQL Server isn’t like sprinkling magic dust, because of the way that error-handling and constraint-checking is done. You need to be aware how this rather complex system works in order to avoid some of the pitfalls when you are planning on how to recover from errors. <\/p>\n

Any good SQL Server database will use constraints and other DRI in order to maintain integrity and increase performance. The violation of any constraints leads to an error, and it is rare to see this handled well. <\/p>\n

Autocommit transaction mode<\/h2>\n

Let’s create a table that allows us to be able to make a couple of different constraint violations. You’ll have to imagine that this is a part of a contact database that is full of constraints and triggers that will defend against bad data ever reaching the database. Naturally, there will be more in this table. It might contain the actual address that relates to the PostCode(in reality, it isn’t a one-to-one correspondence).<\/p>\n

CREATE TABLE PostCode\n    (\n      Code VARCHAR(10)\n        PRIMARY KEY\n        CHECK ( Code LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\n                OR Code LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\n                OR Code LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' )\n    );\n<\/pre>\n
Listing 1: Creating the PostCodetable<\/p>\n
This means that PostCodes in this table must be unique and they must conform to a specific pattern. Since SQL Databases are intrinsically transactional, those DML (Data Manipulation Language) statements that trigger an error will be rolled back. Assuming our table is empty, try this…<\/p>\n
Delete from PostCode\n \nINSERT INTO PostCode (code)\nSELECT 'W6 8JB' AS PostCode\nUNION ALL SELECT 'CM8 3BY'\nUNION ALL SELECT 'CR AZY' --this is an invalid PostCode\nUNION ALL SELECT 'G2 9AG'\nUNION ALL SELECT 'G2 9AG'; --a duplicate\n \nSELECT * FROM PostCode--none there\n \nMsg 547, Level 16, State 0, Line 3\nThe INSERT statement conflicted with the CHECK constraint \"CK__PostCode__Code__4AB81AF0\". The conflict occurred in database \"contacts\", table \"dbo.PostCode\", column 'Code'.\nThe statement has been terminated.\nCode\n----------\n \n(0 row(s) affected)\n<\/pre>\n
Listing 2: Inserting rows in a single statement (XACT_ABORT OFF)<\/p>\n
Nothing there, is there? It found the bad PostCodebut never got to find the duplicate, did it? So, this single statement was rolled back, because the CHECK<\/code> constraint found the invalid PostCode. Would this rollback the entire batch? Let’s try doing some insertions as separate statements to check this.<\/p>\n
SET XACT_ABORT OFF -- confirm that XACT_ABORT is OFF (the default)\nDELETE FROM PostCode\n \nINSERT INTO PostCode (code) SELECT 'W6 8JB' AS PostCode\nINSERT INTO PostCode (code) SELECT 'CM8 3BY' \nINSERT INTO PostCode (code) SELECT 'CR AZY' --this is an invalid PostCode\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nINSERT INTO PostCode (code) SELECT 'G2 9AG'; --a duplicate. Not allowed\n \nSELECT * FROM PostCode\nMsg 547, Level 16, State 0, Line 5\nThe INSERT statement conflicted with the CHECK constraint \"CK__PostCode__Code__4AB81AF0\". The conflict occurred in database \"contacts\", table \"dbo.PostCode\", column 'Code'.\nThe statement has been terminated.\n \nMsg 2627, Level 14, State 1, Line 7\nViolation of PRIMARY KEY constraint 'PK__PostCode__A25C5AA648CFD27E'. Cannot insert duplicate key in object 'dbo.PostCode'.\nThe statement has been terminated.\nCode\n----------\nCM8 3BY\nG2 9AG\nW6 8JB\n<\/pre>\n
Listing 3: Single batch using separate INSERT statements (XACT_ABORT OFF)<\/p>\n
Not only doesn’t it roll back the batch when it hits a constraint violation, but just the statement. It then powers on and finds the UNIQUE<\/code> constraint violation. As it wasn’t judged as a severe ‘batch-aborting’ error, SQL Server only rolled back the two offending inserts. If, however, we substitute SET XACT_ABORT ON<\/code> then the entire batch is aborted at the first error, leaving the two first insertions in place. The rest of the batch isn’t even executed. Try it. <\/p>\n
By setting XACT_ABORT ON<\/code>, we are telling SQL Server to react to any error by rolling back the entire transaction and aborting  the batch.  By default, the session setting is OFF<\/span>. In this case, SQL Server merely rolls back the Transact-SQL statement that raised the error and the batch continues. Even with SET XACT_ABORT<\/code> set to OFF<\/code>, SQL Server will choose to roll back a whole batch if it hits more severe errors. <\/p>\n
If we want to clean up specific things after an error, or if we want processing to continue in the face of moderate errors, then we need to use SET XACT_ABORT OFF<\/code>, but there is a down-side: It is our responsibility now to make sure we can return the database to a consistent state on error…and use appropriate error handling to deal with even the trickier errors such as those caused by a cancel\/timeout of the session in the middle of a transaction. <\/p>\n
Just by changing the setting of XACT_ABORT<\/code>, we can rerun the example and end up with different data in the database. This is because, with XACT_ABORT ON<\/code>, the behavior is consistent regardless of the type of error. It simply assumes the transaction just can’t be committed, stops processing, and aborts the batch.<\/p>\n
 With XACT_ABORT OFF<\/code>, the behavior depends on the type of error. If it’s a constraint violation, permission-denial,  or a divide-by-zero, it will plough on. If the error dooms the transaction, such as when there is a conversion error or deadlock,  it won’t. Let’s illustrate this draconian batch-abortion.<\/p>\n
DELETE FROM PostCode\nGO \nSET XACT_ABORT ON--or off. Try it both ways\nINSERT INTO PostCode (code) SELECT 'CM8 3BY' \n \nINSERT INTO PostCode (code)\nSELECT 'W6 8JB' AS PostCode\nUNION ALL SELECT 'CM8 3BY'\nUNION ALL SELECT 'CR AZY' --this is an invalid PostCode\nUNION ALL SELECT 'G2 9AG'\nUNION ALL SELECT 'G2 9AG'; --a duplicate\nINSERT INTO PostCode (code) SELECT 'CM8 3BY' \nGO\n<\/pre>\n
Listing 4: Inserting rows in a batch using separate INSERT statements (XACT_ABORT ON) <\/p>\n
If you’ve got the XACT_ABORT ON<\/code> then you’ll get…<\/p>\n
Msg 2627, Level 14, State 1, Line 4\nViolation of PRIMARY KEY constraint 'PK__PostCode__A25C5AA648CFD27E'. Cannot insert duplicate key in object 'dbo.PostCode'.\nCode\n----------\nCM8 3BY\n<\/pre>\n
You’ll see that, in the second batch, the PostCode ‘G2 9AG’ never gets inserted because the batch is aborted after the first constraint violation.<\/p>\n
If you set XACT_ABORT OFF<\/code>, then you’ll get …<\/p>\n
Msg 2627, Level 14, State 1, Line 4  Violation of PRIMARY KEY constraint 'PK__PostCode__A25C5AA648CFD27E'. Cannot insert duplicate key in object 'dbo.PostCode'.\nThe statement has been terminated.\n \n(1 row(s) affected)\nCode\n----------\nCM8 3BY\nG2 9AG\n<\/pre>\n
And to our surprise, we can see that we get a different result depending on the setting of XACT_ABORT. <\/code>(Remember that GO<\/code> is a client-side batch separator!) You’ll see that, if we insert a GO <\/code>after the multi-row insert, we get the same two PostCodes in . Yes, With XACT_ABORT ON<\/code> the behavior is consistent regardless of the type of error. With XACT_ABORT OFF<\/code>, behavior depends on the type of error<\/p>\n
There is a great difference in the ‘abortion’ of a batch, and a ‘rollback’. With an ‘abortion’, any further execution of the batch is always abandoned. This will happen whatever you specified for XACT_ABORT<\/code>. If a type of error occurs that SQL Server considers too severe to allow you to ever commit the transaction, it is ‘doomed’. This happens whether you like it or not. The offending statement is rolled back and the batch is aborted.<\/p>\n
  Let’s ‘doom’ the batch by putting in a conversion error.<\/p>\n
SET XACT_ABORT OFF -- confirm that XACT_ABORT is OFF (the default)\nDELETE FROM PostCode\n \nINSERT INTO PostCode (code) SELECT 'W6 8JB' AS PostCode; \nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nINSERT INTO PostCode (code) SELECT 'CR AZY'+1; --this is an invalid PostCode\nINSERT INTO PostCode (code) SELECT 'G2 9AG'; --a duplicate. Not allowed\nPRINT 'that went well!' \nGO\nSELECT * FROM PostCode\nMsg 245, Level 16, State 1, Line 7\nConversion failed when converting the varchar value 'CR AZY' to data type int.\nCode\n----------\nCM8 3BY\nG2 9AG\nW6 8JB\n \n<\/pre>\n
Listing 5: Single batch using separate INSERT statements with a type conversion error (XACT_ABORT OFF)<\/p>\n
You’ll probably notice that execution of the first batch stopped when the conversion error was detected, and just that statement was rolled back. It never found the Unique Constraint error. Then the following batch…select * from PostCode<\/code>…was executed. <\/p>\n
You can combine several statements into a unit of work using wither explicit transactions or by setting implicit transactions on. The latter requires fewer statements but is less versatile and doesn’t provide anything new, so we’ll just stick to explicit transactions<\/p>\n
So let’s introduce an explicit transaction that encompasses several statements. We can then see what difference this makes to the behavior we’ve seen with autoCommit.<\/p>\n
Explicit Transactions<\/h2>\n
When we explicitly declare the start of a transaction in SQL by using the BEGIN TRANSACTION<\/code> statement, we are defining a point at which the data referenced by a particular connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION<\/code> can be rolled back to return the data to this known state of consistency. While it’s possible to get SQL Server to roll back in this fashion, it doesn’t do it without additional logic. We either have to specify this behavior by setting XACT_ABORT<\/code> to ON<\/code>, so that the explicit transaction is rolled back automatically, or by using a ROLLBACK<\/code>.<\/p>\n
Many developers believe that the mere fact of having declared the start of a transaction is enough to trigger an automatic rollback of the entire transaction if we hit an error during that transaction. Let’s try it.<\/p>\n
SET XACT_ABORT OFF\n \nDELETE FROM PostCode\nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nINSERT INTO PostCode (code) SELECT 'CR AZY'; --invalid PostCode\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nINSERT INTO PostCode (code) SELECT 'G2 9AG'; --a duplicate. Not allowed\nCOMMIT TRANSACTION\ngo \nSELECT * FROM PostCode;\n<\/pre>\n
Listing 6: Multi-statement INSERT (single batch) using an explicit transaction<\/p>\n
No dice. The result is exactly the same as when we tried it without the explicit transaction (see Listing 3). If we again use SET XACT_ABORT ON<\/code> then the batch is again aborted at the first error, but this time, the whole unit of work is rolled back.<\/p>\n
By using SET XACT_ABORT ON<\/code>, you make SQL Server do what most programmers think happens anyway. Since it is unusual not<\/i> to want to rollback a transaction following an error, it is normally safer to explicitly set it <\/code>ON<\/code>. <\/span>However, there are times when you’d want it OFF<\/code>. You might, for example, wish to know about every constraint violation in the rows being imported into a table, and then do a complete rollback if any errors happened.<\/p>\n
Most SQL Server clients set it to OFF<\/code> by default, though OLEDB <\/code>sets it to ON.<\/code><\/p>\n
SET XACT_ABORT OFF\nDELETE FROM PostCode\nDECLARE @Error INT\nSELECT @Error = 0\n \nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'CR AZY'; --invalid PostCode\nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'G2 9AG'; --a duplicate. Not allowed\nSELECT  @Error = @error + @@error;\nIF @error > 0 ROLLBACK TRANSACTION else COMMIT TRANSACTION\ngo \nSELECT * FROM PostCode;\nSELECT @@Trancount --to check that the transaction is done\nMsg 547, Level 16, State 0, Line 11\nThe INSERT statement conflicted with the CHECK constraint \"CK__PostCode__Code__4AB81AF0\". The conflict occurred in database \"contacts\", table \"dbo.PostCode\", column 'Code'.\nThe statement has been terminated.\n \n(1 row(s) affected)\nMsg 2627, Level 14, State 1, Line 15\nViolation of PRIMARY KEY constraint 'PK__PostCode__A25C5AA648CFD27E'. Cannot insert duplicate key in object 'dbo.PostCode'.\nThe statement has been terminated.\nCode\n----------\n \n<\/pre>\n
Listing 7: Multi-statement INSERT (single batch) using an explicit transaction <\/p>\n
In this batch, we execute all the insertions in separate statements, checking the volatile @@Error<\/code> value. Then, we check to see whether the batch hit errors or it was successful. If it completes without any errors, we issue a COMMIT TRANSACTION<\/code> to make the modification a permanent part of the database. If one or more errors are encountered, then all modifications are undone with a ROLLBACK TRANSACTION<\/code> statement that rolls back to the start of the transaction. <\/p>\n
The use of @@Error<\/code> isn’t entirely pain-free, since it only records the last error, and so, if a trigger has fired after the statement you’re checking, then the @@Error <\/code>value will be that corresponding to the last statement executed in the trigger, rather than your statement.<\/p>\n
If the transaction becomes doomed, all that happens is that the transaction is rolled back without the rest of the transaction being executed, just as would happen anyway if XACT_ABORT i<\/code>s set to ON<\/code>.<\/p>\n
SET XACT_ABORT OFF\nDELETE FROM PostCode\nDECLARE @Error INT\nSELECT @Error = 0\n \nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'CR AZY'; --invalid PostCode\nSELECT @Error = @error + @@error;\n  INSERT INTO PostCode (code) SELECT 'G2 9AG';\nSELECT @Error = @error + @@error;\nINSERT INTO PostCode (code) SELECT 'G2 9AG'+1; --a duplicate. Not allowed\nSELECT @Error = @error + @@error;\nIF @error > 0 ROLLBACK TRANSACTION else COMMIT TRANSACTION\ngo \nSELECT * FROM PostCode;\nSELECT @@Trancount; --to check that the transaction is complete\nMsg 245, Level 16, State 1, Line 6\nConversion failed when converting the varchar value 'W6 8JB' to data type int.\n \nCode\n----------\n<\/pre>\n
Listing 8: Multi-statement INSERT (single batch) with a doomed explicit transaction<\/p>\n
There is a problem with this code, because I’ve issued the rollback without any qualification. If this code is called from within another transaction is will roll back to the start of the outer transaction. Often this is not what you want. I should really have declared a SavePoint to specify where to rollback to. I must explain.<\/p>\n
Nested transactions and Savepoints <\/h2>\n
Transactions can be misleading because programmers equate them to program blocks, and assume that they can somehow be ‘nested’. All manner of routines can be called during a transaction, and some of them could, in turn, specify a transaction, but a rollback will always go to the base transaction.<\/p>\n
Support for nested transactions in SQL Server (or other RDBMSs) simply means that it will tolerate us embedding a transaction within one or more other transactions. Most developers will assume that such ‘nesting’ will ensure that SQL Server handles each sub-transaction in an atomic way, as a logical unit of work that can commit independently of other child transactions. However, such behavior is not<\/span> possible with nested transactions in SQL Server, or other RDMBSs; if the outer transaction was to allow such a thing it would be subverting the all-or-nothing rule of atomicity. SQL Server allows transactions within transactions purely so that a process can call transactions within a routine, such as a stored procedure, regardless of whether that process is within a transaction.<\/p>\n
The use of a SavePoint can, however, allow you to rollback a series of statements within a transaction.<\/p>\n
Without a Savepoint, a ROLLBACK<\/code> of a nested transaction can affect more than just the unit of work we’ve defined . If we rollback a transaction and it is ‘nested’ within one or more other transactions, it doesn’t just roll back to the last, or innermost BEGIN TRANSACTION<\/code>, but rolls all the way back in time to the start of the base transaction. This may not be what we want or expect, and could turn a minor inconvenience into a major muddle.<\/p>\n
SET XACT_ABORT OFF\nDELETE FROM PostCode\nDECLARE @Error INT\nSELECT @Error = 0\n \nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nBEGIN TRANSACTION --'nested' transaction\n    INSERT INTO PostCode (code) SELECT 'BY 5JR';\n    INSERT INTO PostCode (code) SELECT 'PH2 0QA'; \nROLLBACK--end of 'nesting'\nINSERT INTO PostCode (code) SELECT 'CR 4ZY';\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nCOMMIT TRANSACTION\ngo \nSELECT * FROM PostCode;\nSELECT @@Trancount; --to check that the transaction is complete\n \nMsg 3902, Level 16, State 1, Line 15\nThe COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.\nCode\n----------\nCR 4ZY\nG2 9AG\n<\/pre>\n
Listing 9: Rolling back a nested transaction without a Savepoint<\/p>\n
As you can see, SQL Server hasn’t just rolled back the inner transaction but all the work done since the outer BEGIN TRANSACTION<\/code>. You have a warning as well, 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION<\/code>‘ because the transaction count became zero after the rollback, it successfully inserted two rows and came to the COMMIT TRANSACTION <\/code>statement.<\/p>\n
Similarly, SQL Server simply ignores all commands to COMMIT<\/code> the transaction  within ‘nested’ transactions until the batch issues the COMMIT<\/code> that matches the outermost BEGIN TRANSCATION<\/code>.<\/p>\n
SET XACT_ABORT OFF\nDELETE FROM PostCode\nDECLARE @Error INT\nSELECT @Error = 0\n \nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nBEGIN TRANSACTION --'nested' transaction\n    INSERT INTO PostCode (code) SELECT 'BY 5JR';\n    INSERT INTO PostCode (code) SELECT 'PH2 0QA'; \nCOMMIT TRANSACTION--end of 'nesting'\nINSERT INTO PostCode (code) SELECT 'CR 4ZY';\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nRollback\ngo \nSELECT * FROM PostCode;\nSELECT @@Trancount; --to check that the transaction is complete \n \nCode\n----------\n \n<\/pre>\n
Listing 10: Attempting to COMMIT a nested transaction without a Savepoint<\/p>\n
The evident desire was to commit the nested transaction, because we explicitly requested that the changes in the transaction be made permanent, and so we might expect at least something to happen, but what does? Nothing; if we have executed a COMMIT TRANSACTION<\/code> in a nested transaction that is contained a parent transaction that is then rolled back, the nested transaction will also be rolled back. SQL Server ignores the nested COMMIT<\/code> command and, whatever we do, nothing is committed until the base transaction is committed. In other words, the COMMIT<\/code> of the nested transaction is actually conditional on the COMMIT<\/code> of the parent.<\/p>\n
One might think that it is possible to use the NAME<\/code> parameter of the ROLLBACK<\/code> TRANSACTION<\/code> statement to refer to the inner transactions of a set of named ‘nested’ transactions. Nice try,  but the only name allowed, other than a Savepoint, is the transaction name of the outermost transaction. By adding the name, we can specify that all of the nested transactions are rolled back leaving the outermost, or ‘base’, one, whereas if we leave it out then the rollback includes the outermost transaction. The NAME<\/code> parameter is only useful in that we’ll get an error if someone inadvertently wraps what was the base transaction in a new base transaction, By giving the base transaction a name, it makes it easier to identify when  we want to monitor the progress of long-running queries.<\/p>\n
We can sort this problem out by using a SavePoint. This will allow us to do quite a bit of what we might have thought was happening anyway by nesting transactions! Savepoints are handy for marking a point in your transaction. We then have the option, later, of rolling back work performed before the current point in the transaction but after a declared savepoint within the same transaction. <\/p>\n
SET XACT_ABORT OFF\nDELETE FROM PostCode\nDECLARE @Error INT\nSELECT @Error = 0\n \nBEGIN TRANSACTION \nINSERT INTO PostCode (code) SELECT 'W6 8JB'; \nINSERT INTO PostCode (code) SELECT 'CM8 3BY'; \nSAVE TRANSACTION here --create a savepoint called 'here'\nINSERT INTO PostCode (code) SELECT 'BY 5JR';\nINSERT INTO PostCode (code) SELECT 'PH2 0QA'; \nROLLBACK TRANSACTION here --rollback to the savepoint\nINSERT INTO PostCode (code) SELECT 'CR 4ZY';\nINSERT INTO PostCode (code) SELECT 'G2 9AG';\nCOMMIT TRANSACTION\ngo  \nSELECT * FROM PostCode;\nSELECT @@Trancount; --to check that the transaction is complete\nCode\n----------\nCM8 3BY\nCR 4ZY\nG2 9AG\nW6 8JB\n<\/pre>\n
 Listing 11: Using Savepoints to roll back to a ‘known’ point<\/p>\n
When we roll backto a save point, only those statements that ran after the savepoint are rolled back. All savepoints that were established later are, of course, lost.<\/p>\n
So, if we actually want rollback within a nested transaction , then we can create a savepoint at the start. Then, if a statement within the transaction fails, it is easy to return the data to its state before the transaction began and re-run it. Even better, we can create a transaction and call a series of stored procedures which do DML stuff. Before each stored procedure, we can create a savepoint. Then, if the procedure fails, it is easy to return the data to its state before it began and re-run the function with revised parameters or set to perform a recovery action. The downside would be holding a transaction open for too long.<\/p>\n
The Consequences of Errors.<\/h2>\n
In our example, we’re dealing mainly with constraint violations which lead to statement termination, and we’ve contrasted them to errors that lead to batch abortion, and demonstrated that by setting XACT_ABORT ON<\/code>, statement termination starts to behave more like batch-abortion errors. (‘scope-abortion’ happens when there is a compile error, ‘connection-termination’ only happens when something horrible happens, and ‘batch-cancellation’ only when the client of a session cancels it, or there is a time-out ) All this can be determined from the @@Error<\/code> variable but there is nothing one can do to prevent errors from being passed back to the application. Nothing, that is, unless you use TRY...CATCH<\/code><\/p>\n
TRY CATCH Behavior<\/h3>\n
It is easy to think that all one’s troubles are over with TRY..CATCH<\/code>, but in fact one still needs to be aware of other errors<\/p>\n
set XACT_ABORT on\nDELETE FROM PostCode\nBEGIN TRY\n INSERT INTO PostCode (code) SELECT 'W6 8JB' AS PostCode\n INSERT INTO PostCode(code) SELECT 'CM8 3BY' \n INSERT INTO PostCode (code) SELECT 'CR AZY' --'CR1 4ZY' for a valid one\n INSERT INTO PostCode(code) SELECT 'G2 9AG'\n INSERT INTO PostCode(code) SELECT 'G2 9AG';\n END TRY\nBEGIN CATCH\n    PRINT 'ERROR ' + CONVERT(VARCHAR(8), @@error) + ', ' + ERROR_MESSAGE()\nEND CATCH;\nSELECT * FROM PostCode\nERROR 547 The INSERT statement conflicted with the CHECK constraint \"CK__PostCode__Code__44FF419A\". The conflict occurred in database \"contacts\", table \"dbo.PostCode\", column 'Code'. \n \n(1 row(s) affected)\n \nERROR\nCode\n----------\nCM8 3BY\nW6 8JB\n \n(2 row(s) affected) \n<\/pre>\n
Listing 12: TRY…CATCH without a transaction <\/p>\n
This behaves the same way whether XACT_ABORT<\/code> is on or off. This catches the first execution error that has a severity higher than 10 that does not close the database connection. This means that execution ends after the first error, but there is no automatic rollback of the unit of work defined by the TRY<\/code> block:  No, we must still define a transaction. This works fine for most purposes though one must beware of the fact that certain errors such as killed connections or timeouts don’t get caught.<\/p>\n
Try-Catch behavior deals with statement termination but needs extra logic to deal well with batch-abortion. In other words, we need to deal with un-committable and doomed transactions. Here is what happens if we don’t do it properly.<\/p>\n
set XACT_ABORT on\nDELETE FROM PostCode\nBEGIN TRANSACTION \nSAVE TRANSACTION here --only if SET XACT_ABORT OFF\nBEGIN TRY\n INSERT INTO PostCode (code) SELECT 'W6 8JB' AS PostCode\n INSERT INTO PostCode(code) SELECT 'CM8 3BY' \n INSERT INTO PostCode (code) SELECT 'CR AZY' --'CR1 4ZY' for a valid one\n INSERT INTO PostCode(code) SELECT 'G2 9AG'\n INSERT INTO PostCode(code) SELECT 'G2 9AG';\n END TRY\nBEGIN CATCH\n ROLLBACK TRANSACTION here\n    PRINT 'ERROR ' + CONVERT(VARCHAR(8), @@error) + ', ' + ERROR_MESSAGE()END CATCH;\nSELECT * FROM PostCode)\nMsg 3931, Level 16, State 1, Line 16\nThe current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.\n<\/pre>\n
Listing 13: Mishandled Batch-abort<\/p>\n
This error will immediately abort and roll back the batch whatever you do, but the TRY-CATCH<\/code> seems to handle the problem awkwardly if you set XACT_ABORT ON,<\/code> and it passes back a warning instead of reporting the error. Any error causes the transaction to be classified as an un-committable or ‘doomed’ transaction. The request cannot be committed, or rolled back to a savepoint. Only a full rollback  to the start of the base transaction will do. No write operations can happen until it rolls back the transaction, only reads. <\/p>\n
If you set XACT_ABORT<\/code> off, then it behaves gracefully, but terminates after the first error it comes across, executing the code in the CATCH<\/code> block.<\/p>\n
To get around this, we can use the XACT_STATE()<\/code> function. This will tell you whether SQL Server has determined that the transaction is doomed.  Whilst we can use the  @@TRANCOUNT<\/code> variable to detect whether the current request has an active user transaction, we cannot use it to determine whether that transaction has been classified as an uncommitable transaction. Only XACT_STATE()<\/code> will tell us if the transaction is doomed, and only only @@TRANCOUNT<\/code> can be used to determine whether there are nested transactions.<\/p>\n
set XACT_ABORT off\nDeclare @xact_state int\nDELETE FROM PostCode\nBEGIN TRANSACTION \nSAVE TRANSACTION here --only if SET XACT_ABORT OFF\nBEGIN TRY\n INSERT INTO PostCode (code) SELECT 'W6 8JB' AS PostCode\n INSERT INTO PostCode(code) SELECT 'CM8 3BY' \n INSERT INTO PostCode (code) SELECT 'CR 4ZY' --'CR1 4ZY' for a valid one\n INSERT INTO PostCode(code) SELECT 'G2 9AG'\n INSERT INTO PostCode(code) SELECT 'G2 9AG';\n END TRY\nBEGIN CATCH\n    select @xact_state=XACT_STATE()\n    IF ( @xact_state ) = 1 --the transaction is commitable\n        ROLLBACK TRANSACTION here --just rollback  to the savepoint\n    ELSE \n        ROLLBACK TRANSACTION --back to base, because it's probably doomed\n    PRINT case when @xact_state= -1 then 'Doomed ' else '' end +'Error ' + CONVERT(VARCHAR(8), ERROR_NUMBER()) + ' on line '\n        + CONVERT(VARCHAR(8), ERROR_LINE()) + ', ' + ERROR_MESSAGE()\nEND CATCH;\nIF XACT_STATE() = 1 \n    COMMIT TRANSACTION --only if this is the base transaction\n --only if it hasn't been rolled back\nSELECT * FROM    PostCode\n<\/pre>\n
Listing 14: Both Statement-termination and Batch abort handled<\/p>\n
Reaching the Goal<\/h3>\n
So now, we can have reasonable confidence that we have a mechanism that will allow us to import a large number of records and tell us, without triggering errors, which records contain bad data, as defined by our constraints. <\/p>\n
Sadly, we are going to do this insertion row-by-row, but you’ll see that 10,000 rows only takes arount three seconds, so it is worth the wait. We have a temporary table full of 10,000 valid PostCodes, and we’ll add in a couple of rogues just to test out what happens.<\/p>\n
SET XACT_ABORT OFF \nDELETE FROM PostCode\nSET NOCOUNT ON \nDECLARE @II INT, @iiMax INT, @Code VARCHAR(10) \nDECLARE @TemporaryStagingTable TABLE \n(Code_ID INT IDENTITY(1,1) PRIMARY KEY, Code CHAR(10) ) \nDECLARE @Error TABLE \n (Error_ID INT IDENTITY(1,1) PRIMARY KEY, \nErrorCode INT, \nPostCodeVARCHAR(10), \nTransactionState INT, \nErrorMessage VARCHAR(255) \n) \nINSERT INTO @TemporaryStagingTable (code) SELECT code FROM PostCodeData \nUNION ALL SELECT 'W6 8JB' UNION ALL SELECT 'CM8 3BY' \nUNION ALL SELECT 'CR AZY' UNION ALL SELECT 'G2 9AG' \nUNION ALL SELECT 'G2 9AG' \n \nSELECT @ii=MIN(Code_ID),@iiMax=MAX(Code_ID) FROM @TemporaryStagingTable \nWHILE @ii<=@iiMax\nBEGIN \nBEGIN try \n    SELECT @Code=code FROM @TemporaryStagingTable WHERE Code_ID=@ii \n    INSERT INTO PostCode(code) SELECT @Code \nEND try     \nBEGIN CATCH \n    INSERT INTO @error(ErrorCode, PostCode,TransactionState,ErrorMessage) \n     SELECT ERROR_NUMBER(), @Code, XACT_STATE(), ERROR_MESSAGE() \nEND CATCH; \nSELECT @ii=@ii+1 \nEND \nSELECT * FROM @error\n<\/pre>\n
Listing 15: insert from staging table with error reporting but without rollback on error<\/p>\n
\"1515-img69.gif\"<\/p>\n
..and if you wanted to rollback the whole import process if you hit an error, then you could try this.<\/p>\n
SET XACT_ABORT OFF --to get statement-level rollbacks \nDELETE FROM PostCode--teardown last test \nSET NOCOUNT ON \nDECLARE @II INT, @iiMax INT, @Code VARCHAR(10) \nDECLARE @TemporaryStagingTable TABLE --to help us iterate through \n(Code_ID INT IDENTITY(1,1) PRIMARY KEY, Code CHAR(10) ) \nDECLARE @Error TABLE --to collect up all the errors \n(Error_ID INT IDENTITY(1,1) PRIMARY KEY, \nErrorCode INT, \nPostCodeVARCHAR(10), \nTransactionState INT, \nErrorMessage VARCHAR(255) \n) \nINSERT INTO @TemporaryStagingTable (code) SELECT code FROM PostCodeData \n--the good stuff \nUNION ALL SELECT 'W6 8JB' UNION ALL SELECT 'CM8 3BY' \nUNION ALL SELECT 'CR AZY' UNION ALL SELECT 'G2 9AG' --bad stuff \nUNION ALL SELECT 'G2 9AG' --bad stuff \n \n--get the size of the table \nSELECT @ii=MIN(Code_ID),@iiMax=MAX(Code_ID) FROM @TemporaryStagingTable \nBEGIN TRANSACTION --start a transaction \nSAVE TRANSACTION here --pop in a savepoint since we may already be in a transaction \n--and we don't want to mess it up \nWHILE @ii <= @iiMax AND XACT_STATE() <> -1 --if the whole transaction is doomed \n--then you've no option \nBEGIN \nBEGIN try --get the code first for our error record \n    SELECT @Code=code FROM @TemporaryStagingTable WHERE Code_ID=@ii \n    INSERT INTO PostCode(code) SELECT @Code --pop it in \nEND try     \nBEGIN CATCH --record the error \n    INSERT INTO @error(ErrorCode, PostCode,TransactionState,ErrorMessage) \n      SELECT ERROR_NUMBER(), @Code, XACT_STATE(), ERROR_MESSAGE() \nEND CATCH; \nSELECT @ii=@ii+1 \nEND \nIF EXISTS (SELECT * FROM @error) \nBEGIN     \nIF ( XACT_STATE() ) = 1 --the transaction is commitable \n    ROLLBACK TRANSACTION here --just rollback to the savepoint \nELSE \n    ROLLBACK TRANSACTION --we're doomed! Doomed! \nSELECT * FROM @error; END \nELSE COMMIT\n<\/pre>\n
Listing 16: insert from staging table with error-reporting and  rollback on error<\/p>\n
You can comment out the rogue PostCodes or change the XACT_ABORT<\/code> settings just to check if it handles batch aborts properly.<\/p>\n
Conclusion<\/h2>\n
To manage transactions properly, and react appropriately to errors fired by constraints, you need to plan carefully. You need to distinguish the various types of errors, and make sure that you react to all of these types appropriately in your code, where it is possible to do so. You need to specify the transaction abort mode you want, and the transaction mode, and you should monitor the transaction level and transaction state. <\/p>\n
You should be clear that transactions are never nested, in the meaning that the term usually conveys. <\/p>\n
Transactions must be short, and only used when necessary. A session must always be cleaned up, even when it times-out or is aborted, and one must do as much error reporting as possible when transactions have to be rolled back. DDL changes should be avoided within transactions, so as to avoid locks being placed on system tables. <\/p>\n
The application developer should be not be forced to become too familiar with SQL Server errors, though some will inevitably require handling within application code. As much as possible, especially in the case of moderate errors such as constraint violations or deadlocks should be handled within the application\/database interface.<\/p>\n
Once the handling of constraint errors within transactions has been tamed and understood, constraints will prove to be one of the best ways of guaranteeing the integrity of the data within a database.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
The database developer can, of course, throw all errors back to the application developer to deal with, but this is neither  kind nor necessary. How errors are dealt with is very dependent on the application, but the process itself isn’t entirely obvious. Phil became gripped with a mission to explain……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4151,4252],"coauthors":[],"class_list":["post-1359","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1359"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1359\/revisions"}],"predecessor-version":[{"id":92535,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1359\/revisions\/92535"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1359"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}