Basic Transactions

In transactions’ basic form, three commands are required: BEGIN TRANSACTION (to start the transaction), -COMMIT TRANSACTION (to save the data), and ROLLBACK TRANSACTION (to undo the changes that were made). It’s as simple as that.

For example, consider the case of building a stored procedure to modify two tables. Call these tables table1 and table2. You’ll modify table1, check the error status, and then modify table2 (these aren’t real tables, just syntax examples):

 BEGIN TRY

    BEGIN TRANSACTION;

       UPDATE table1

       SET value = 'value';

       UPDATE table2

       SET value = 'value';

    COMMIT TRANSACTION;

 END TRY

 BEGIN CATCH

       ROLLBACK TRANSACTION;

       THROW 50000,'An error occurred',16

 END CATCH

Now, if some unforeseen error occurs while updating either table1 or table2, you won’t get into the case where table1 is updated and table2 is not. It’s also imperative not to forget to close the transaction (either save the changes with COMMIT TRANSACTION, or undo the changes with ROLLBACK TRANSACTION), because the open transaction that contains your work is in a state of limbo, and if you don’t either complete it or roll it back, it can cause a lot of issues just hanging around in an open state. For example, if the transaction stays open and other operations are executed within that transaction, you might end up losing all work done on that connection. You may also prevent other connections from getting their work done, because each connection is isolated from one another messing up or looking at their unfinished work. Another user who needed the affected rows in table1 or table2 would have to wait (more on why this is throughout this chapter). The worst case of this I saw a number of years back was a single connection that was open all day with a transaction open after a failure because there was no error handling on the transaction. We lost a day’s work because we finally had to roll back the transactions when we killed the process.

There’s an additional setting for simple transactions known as named transactions, which I’ll introduce for completeness. (Ironically, this explanation will take more ink than introducing the more useful transaction syntax, but it is something good to know and can be useful in rare circumstances!) You can extend the functionality of transactions by adding a transaction name, as shown:

 BEGIN TRANSACTION <tranName> or <@tranvariable>;

This can be a confusing extension to the BEGIN TRANSACTION statement. It names the transaction to make sure you roll back to it, for example:

 BEGIN TRANSACTION one;

 ROLLBACK TRANSACTION one;

Only the first transaction mark is registered in the log, so the following code returns an error:

 BEGIN TRANSACTION one;

 BEGIN TRANSACTION two;

 ROLLBACK TRANSACTION two;

The error message is as follows:

Unfortunately, after this error has occurred, the transaction is still left open. For this reason, it’s seldom a good practice to use named transactions in your code unless you have a very specific purpose. The specific use that makes named transactions interesting is when named transactions use the WITH MARK setting. This allows marking the transaction log, which can be used when restoring a transaction log instead of a date and time. A common use of the marked transaction is to restore several databases back to the same condition and then restore all of the databases to a -common mark.

The mark is only registered if data is modified within the transaction. A good example of its use might be to build a process that marks the transaction log every day before some daily batch process, especially one where the database is in single-user mode. The log is marked, and you run the process, and if there are any troubles, the database log can be restored to just before the mark in the log, no matter when the process was executed. Using the AdventureWorks2012 database, I’ll demonstrate this capability. You can do the same, but be careful to do this somewhere where you know you have a proper backup (just in case something goes wrong).

We first set up the scenario by putting the AdventureWorks2012 database in full recovery model.

 USE Master;

 GO

 ALTER DATABASE AdventureWorks2012

       SET RECOVERY FULL;

Next, we create a couple of backup devices to hold the backups we’re going to do:

 EXEC sp_addumpdevice 'disk', 'TestAdventureWorks2012',

                       'C:SQLBackupAdventureWorks2012.bak';

 EXEC sp_addumpdevice 'disk', 'TestAdventureWorks2012Log',

                       'C:SQLBackupAdventureWorks2012Log.bak';

Next, we back up the database to the dump device we created:

 BACKUP DATABASE AdventureWorks2012 TO TestAdventureWorks2012;

Now, we change to the AdventureWorks2012 database and delete some data from a table:

 USE AdventureWorks2012;

 GO

 SELECT COUNT(*)

 FROM Sales.SalesTaxRate;

 BEGIN TRANSACTION Test WITH MARK 'Test';

 DELETE Sales.SalesTaxRate;

 COMMIT TRANSACTION;

This returns 29. Run the SELECT statement again, and it will return 0. Next, back up the transaction log to the other backup device:

 BACKUP LOG AdventureWorks2012 to TestAdventureWorks2012Log;

Now, we can restore the database using the RESTORE DATABASE command (the NORECOVERY setting keeps the database in a state ready to add transaction logs). We apply the log with RESTORE LOG. For the example, we’ll only restore up to before the mark that was placed, not the entire log:

 USE Master

 GO

 RESTORE DATABASE AdventureWorks2012 FROM TestAdventureWorks2012

                          WITH REPLACE, NORECOVERY;

 RESTORE LOG AdventureWorks2012 FROM TestAdventureWorks2012Log

                          WITH STOPBEFOREMARK = ’Test’, RECOVERY;

Now, execute the counting query again, and you can see that the 29 rows are in there.

 USE AdventureWorks2012;

 GO

 SELECT COUNT(*)

 FROM Sales.SalesTaxRate;

If you wanted to include the actions within the mark, you could use STOPATMARK instead of STOPBEFOREMARK. You can find the log marks that have been made in the MSDB database in the -logmarkhistory table.

Nesting Transactions

Yes, I am aware that the title of this section probably sounds a bit like Marlin Perkins is going to take over and start telling of the mating habits of transactions, but I am referring to starting a transaction after another transaction has already been started. The fact is that you can nest the starting of transactions like this:

 BEGIN TRANSACTION;

    BEGIN TRANSACTION;

        BEGIN TRANSACTION;

Technically speaking, there is really only one transaction being started, but an internal counter is keeping up with how many logical transactions have been started. To commit the transactions, you have to execute the same number of COMMIT TRANSACTION commands as the number of BEGIN TRANSACTION commands that have been executed. To tell how many BEGIN TRANSACTION commands have been executed without being committed, you can use the @@TRANCOUNT global variable. When it’s equal to one, then one BEGIN TRANSACTION has been executed. If it’s equal to two, then two have, and so on. When @@TRANCOUNT equals zero, you are no longer within a transaction context.

The limit to the number of transactions that can be nested is extremely large (the limit is 2,147,483,647, which took about 1.75 hours to reach in a tight loop on my old 2.27-GHz laptop with 2GB of RAM—clearly far, far more than any process should ever need).

As an example, execute the following:

 SELECT @@TRANCOUNT AS zeroDeep;

 BEGIN TRANSACTION;

 SELECT @@TRANCOUNT AS oneDeep;

It returns the following results:

Then, nest another transaction, and check @@TRANCOUNT to see whether it has incremented. Afterward, commit that transaction, and check @@TRANCOUNT again:

 BEGIN TRANSACTION;

 SELECT @@TRANCOUNT AS twoDeep;

 COMMIT TRANSACTION; --commits very last transaction started with BEGIN TRANSACTION

 SELECT @@TRANCOUNT AS oneDeep;

This returns the following results:

Finally, close the final transaction:

 COMMIT TRANSACTION;

 SELECT @@TRANCOUNT AS zeroDeep;

This returns the following result:

As I mentioned earlier in this section, technically only one transaction is being started. Hence, it only takes one ROLLBACK TRANSACTION command to roll back as many transactions as you have nested. So, if you’ve coded up a set of statements that end up nesting 100 transactions and you issue one rollback transaction, all transactions are rolled back—for example:

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 BEGIN TRANSACTION;

 SELECT @@trancount as InTran;

 ROLLBACK TRANSACTION;

 SELECT @@trancount as OutTran;

This returns the following results:

This is, by far, the trickiest part of using transactions in your code leading to some messy error handling and code management. It’s a bad idea to just issue a ROLLBACK TRANSACTION command without being cognizant of what will occur once you do—especially the command’s influence on the following code. If code is written expecting to be within a transaction and it isn’t, your data can get corrupted.

In the preceding example, if an UPDATE statement had been executed immediately after the ROLLBACK command, it wouldn’t be executed within an explicit transaction. Also, if COMMIT TRANSACTION is executed immediately after the ROLLBACK command, an error will occur:

 SELECT @@trancount

 COMMIT TRANSACTION

This will return

Savepoints

In the previous section, I explained that all open transactions are rolled back using a ROLLBACK TRANSACTION call. This isn’t always desirable, so a tool is available to roll back only certain parts of a transaction. Unfortunately, it requires forethought and a special syntax. Savepoints are used to provide “selective” rollback.

For this, from within a transaction, issue the following statement:

 SAVE TRANSACTION <savePointName>; --savepoint names must follow the same rules for

                       --identifiers as other objects

For example, use the following code in whatever database you desire. In the source code, I’ll continue to place it in the tempdb, because the examples are self-contained.

 CREATE SCHEMA arts;

 GO

 CREATE TABLE arts.performer

 (

    performerId int NOT NULL identity,

    name varchar(100) NOT NULL

    );

 GO

 BEGIN TRANSACTION;

 INSERT INTO arts.performer(name) VALUES ('Elvis Costello'),

 SAVE TRANSACTION savePoint;

 INSERT INTO arts.performer(name) VALUES ('Air Supply'),

 --don't insert Air Supply, yuck! …

 ROLLBACK TRANSACTION savePoint;

 COMMIT TRANSACTION;

 SELECT *

 FROM arts.performer;

The output of this listing is as follows:

In the code, there were two INSERT statements within the transaction boundaries, but in the output, there’s only one row. Obviously, the row that was rolled back to the savepoint wasn’t persisted.

Note that you don’t commit a savepoint; SQL Server simply places a mark in the transaction log to tell itself where to roll back to if the user asks for a rollback to the savepoint. The rest of the operations in the overall transaction aren’t affected. Savepoints don’t affect the value of @@trancount, nor do they release any locks that might have been held by the operations that are rolled back, until all nested transactions have been committed or rolled back.

Savepoints give the power to effect changes on only part of the operations transaction, giving you more control over what to do if you’re deep in a large number of operations.

I’ll mention savepoints later in this chapter when writing stored procedures, as they allow the rolling back of all the actions of a single stored procedure without affecting the transaction state of the stored procedure caller, though in most cases, it is usually just easier to roll back the entire transaction. Savepoints do, however, allow you to perform some operation, check to see if it is to your liking, and if it’s not, roll it back.

You can’t use savepoints in a couple situations:

• When you’re using MARS and executing more than one batch at a time
• When the transaction is enlisted into a distributed transaction (the next section discusses this)

Distributed Transactions

It would be wrong not to at least bring up the subject of distributed transactions. Occasionally, you might need to update data on a server that’s different from the one on which your code resides. The Microsoft Distributed Transaction Coordinator service (MS DTC) gives us this ability.

If your servers are running the MS DTC service, you can use the BEGIN DISTRIBUTED TRANSACTION command to start a transaction that covers the data residing on your server, as well as the remote server. If the server configuration 'remote proc trans' is set to 1, any transaction that touches a linked server will start a distributed transaction without actually calling the BEGIN DISTRIBUTED TRANSACTION command. However, I would strongly suggest you know if you will be using another server in a transaction (check sys.configurations or sp_configure for the current setting, and set the value using sp_configure). Note also that savepoints aren’t supported for distributed transactions.

The following code is just pseudocode and won’t run as is, but this is representative of the code needed to do a distributed transaction:

 BEGIN TRY

    BEGIN DISTRIBUTED TRANSACTION;

    --remote server is a server set up as a linked server

    UPDATE remoteServer.dbName.schemaName.tableName

    SET value = 'new value'

    WHERE keyColumn = 'value';

    --local server

    UPDATE dbName.schemaName.tableName

    SET value = 'new value'

    WHERE keyColumn = 'value';

    COMMIT TRANSACTION;

 END TRY

 BEGIN CATCH

    ROLLBACK TRANSACTION;

    DECLARE @ERRORMessage varchar(2000);

    SET @ERRORMessage = ERROR_MESSAGE();

    THROW 50000, @ERRORMessage,16;

 END CATCH

The distributed transaction syntax also covers the local transaction. As mentioned, setting the configuration option 'remote proc trans' automatically upgrades a BEGIN TRANSACTION command to a BEGIN DISTRIBUTED TRANSACTION command. This is useful if you frequently use distributed transactions. Without this setting, the remote command is executed, but it won’t be a part of the current -transaction.

Explicit vs. Implicit Transactions

Before finishing the discussion of transaction syntax, there’s one last thing that needs to be covered for the sake of completeness. I’ve alluded to the fact that every statement is executed in a transaction (again, this includes even SELECT statements). This is an important point that must be understood when writing code. Internally, SQL Server starts a transaction every time a SQL statement is started. Even if a transaction isn’t started explicitly with a BEGIN TRANSACTION statement, SQL Server automatically starts a new transaction whenever a statement starts and commits or rolls it back depending on whether or not any errors occur. This is known as an autocommit transaction. When the SQL Server engine commits the transaction, it starts for each statement-level transaction.

SQL Server gives us a setting to change this behavior of automatically committing the transaction: SET IMPLICIT_TRANSACTIONS. When this setting is turned on and the execution context isn’t already within a transaction, such as one explicitly declared using BEGIN TRANSACTION, BEGIN TRANSACTION is automatically (logically) executed when any of the following statements are executed: INSERT, UPDATE, DELETE, SELECT, TRUNCATE TABLE, DROP, ALTER TABLE, REVOKE, CREATE, GRANT, FETCH, or OPEN. This will mean that a COMMIT TRANSACTION or ROLLBACK TRANSACTION command has to be executed to end the transaction. Otherwise, once the connection terminates, all data is lost (and until the transaction terminates, locks that have been accumulated are held, other users are blocked, and pandemonium might occur).

SET IMPLICIT_TRANSACTIONS isn’t a typical setting used by SQL Server programmers or administrators but is worth mentioning because if you change the setting of ANSI_DEFAULTS to ON, IMPLICIT_TRANSACTIONS will be enabled!

I’ve mentioned that every SELECT statement is executed within a transaction, but this deserves a bit more explanation. The entire process of rows being considered for output, then transporting them from the server to the client is contained inside a transaction. The SELECT statement isn’t finished until the entire result set is exhausted (or the client cancels the fetching of rows), so the transaction doesn’t end either. This is an important point that will come back up in the “Isolation Levels” section, as I discuss how this transaction can seriously affect concurrency based on how isolated you need your queries to be.

Stored Procedures

Stored procedures, simply being compiled batches of code, use transactions as previously discussed, with one caveat. The transaction nesting level cannot be affected during the execution of a procedure. In other words, you must commit at least as many transactions as you begin in a stored procedure if you want everything to behave smoothly.

Although you can roll back any transaction, you shouldn’t roll it back unless the @@TRANCOUNT was zero when the procedure started. However, it’s better not to execute a ROLLBACK TRANSACTION statement at all in a stored procedure, so there’s no chance of rolling back to a transaction count that’s different from when the procedure started. This protects you from the situation where the procedure is executed in another transaction. Rather, it’s generally best to start a transaction and then follow it with a savepoint. Later, if the changes made in the procedure need to be backed out, simply roll back to the savepoint, and commit the transaction. It’s then up to the stored procedure to signal to any caller that it has failed and to do whatever it wants with the transaction.

As an example, let’s build the following simple procedure that does nothing but execute a BEGIN TRANSACTION and a ROLLBACK TRANSACTION:

 CREATE PROCEDURE tranTest

 AS

 BEGIN

    SELECT @@TRANCOUNT AS trancount;

    BEGIN TRANSACTION;

    ROLLBACK TRANSACTION;

 END;

Execute this procedure outside a transaction, and you will see it behaves like you would expect:

 EXECUTE tranTest;

It returns

However, say you execute it within an existing transaction:

 BEGIN TRANSACTION;

 EXECUTE tranTest;

 COMMIT TRANSACTION;

The procedure returns the following results:

The errors occur because the transaction depth has changed while rolling back the transaction inside the procedure. This error is one of the most frightening errors out there, because it usually says that you probably have been doing work that you expected to be in a transaction outside of a transaction, thus you will probably end up with out-of-sync data that needs to be repaired.

Finally, let’s recode the procedure as follows, putting a savepoint name on the transaction so we only roll back the code in the procedure:

 ALTER PROCEDURE tranTest

 AS

 BEGIN

    --gives us a unique savepoint name, trim it to 125 characters if the

    --user named the procedure really really large, to allow for nestlevel

    DECLARE @savepoint nvarchar(128) =

       cast(object_name(@@procid) AS nvarchar(125)) +

                    cast(@@nestlevel AS nvarchar(3));

    SELECT @@TRANCOUNT AS trancount;

    BEGIN TRANSACTION;

    SAVE TRANSACTION @savepoint;

    --do something here

    ROLLBACK TRANSACTION @savepoint;

    COMMIT TRANSACTION;

 END;

Now, you can execute it from within any number of transactions, and it will never fail, but it will never actually do anything either:

 BEGIN TRANSACTION;

 EXECUTE tranTest;

 COMMIT TRANSACTION;

Now, it returns

You can call procedures from other procedures (even recursively from the same procedure) or external programs. It’s important to take these precautions to make sure that the code is safe under any calling circumstances.

Naming savepoints is important. Because savepoints aren’t scoped to a procedure, you must ensure that they’re always unique. I tend to use the procedure name (retrieved here by using the object_name function called for the @@procId, but you could just enter it textually) and the current transaction nesting level. This guarantees that I can never have the same savepoint active, even if calling the same procedure recursively.

Let’s look briefly at how to code this into procedures using proper error handling:

 ALTER PROCEDURE tranTest

 AS

 BEGIN

    --gives us a unique savepoint name, trim it to 125

    --characters if the user named it really large

    DECLARE @savepoint nvarchar(128) =

              cast(object_name(@@procid) AS nvarchar(125)) +

                       cast(@@nestlevel AS nvarchar(3));

    --get initial entry level, so we can do a rollback on a doomed transaction

    DECLARE @entryTrancount int = @@trancount;

    BEGIN TRY

    BEGIN TRANSACTION;

    SAVE TRANSACTION @savepoint;

    --do something here

    THROW 50000, 'Invalid Operation',16;

    COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

    --if the tran is doomed, and the entryTrancount was 0,

    --we have to roll back

       IF xact_state()= -1 and @entryTrancount = 0

          rollback transaction;

       --otherwise, we can still save the other activities in the

       --transaction.

       ELSE IF xact_state() = 1 --transaction not doomed, but open

        BEGIN

           ROLLBACK TRANSACTION @savepoint;

           COMMIT TRANSACTION;

        END

       DECLARE @ERRORmessage nvarchar(4000);

       SET @ERRORmessage = 'Error occurred in procedure ''' + OBJECT_NAME(@@procid)

                      + ''', Original Message: ''' + ERROR_MESSAGE() + '''';

       THROW 50000, @ERRORmessage,16;

       RETURN -100

    END CATCH

 END

In the CATCH block, instead of rolling back the transaction, I checked for a doomed transaction, and if we were not in a transaction at the start of the procedure, I rolled back. A doomed transaction is one in which some operation has made it impossible to do anything other than roll back the transaction. A common cause is a trigger-based error message. It is still technically an active transaction, giving you the chance to roll it back so operations do occur outside of the expected transaction space.

If the transaction was not doomed, I simply rolled back the savepoint. An error is returned for the caller to deal with. You could also eliminate RAISERROR altogether if the error wasn’t critical and the caller needn’t ever know of the rollback. You can place any form of error handling in the CATCH block, and as long as you don’t roll back the entire transaction and the transaction does not become doomed, you can keep going and later commit the transaction.

If this procedure called another procedure that used the same error handling, it would roll back its part of the transaction. It would then raise an error, which, in turn, would cause the CATCH block to be called, roll back the savepoint, and commit the transaction (at that point, the transaction wouldn’t contain any changes at all). If the transaction is doomed, when you get to the top level, it is rolled back. You might ask why you should go through this exercise if you’re just going to roll back the transaction anyhow. The key is that each level of the calling structure can decide what to do with its part of the transaction. Plus, in the error handler we have created, we get the basic call stack for debugging purposes.

As an example of how this works, consider the following schema and table (create it in any database you desire, likely tempdb, as this sample is isolated to this section):

 CREATE SCHEMA menu;

 GO

 CREATE TABLE menu.foodItem

 (

    foodItemId int not null IDENTITY(1,1)

       CONSTRAINT PKmenu_foodItem PRIMARY KEY,

    name varchar(30) not null

       CONSTRAINT AKmenu_foodItem_name UNIQUE,

    description varchar(60) not null,

       CONSTRAINT CHKmenu_foodItem_name CHECK (name <> ''),

       CONSTRAINT CHKmenu_foodItem_description CHECK (description <> '')

 );

Now, create a procedure to do the insert:

 CREATE PROCEDURE menu.foodItem$insert

 (

    @name varchar(30),

    @description varchar(60),

    @newFoodItemId int = null output --we will send back the new id here

 )

 AS

 BEGIN

    SET NOCOUNT ON;

    --gives us a unique savepoint name, trim it to 125

    --characters if the user named it really large

    DECLARE @savepoint nvarchar(128) =

              cast(object_name(@@procid) AS nvarchar(125)) +

                       cast(@@nestlevel AS nvarchar(3));

    --get initial entry level, so we can do a rollback on a doomed transaction

    DECLARE @entryTrancount int = @@trancount;

    BEGIN TRY

    BEGIN TRANSACTION;

    SAVE TRANSACTION @savepoint;

    INSERT INTO menu.foodItem(name, description)

    VALUES (@name, @description);

    SET @newFoodItemId = scope_identity(); --if you use an instead of trigger,

                       --you will have to use name as a key

                       --to do the identity "grab" in a SELECT

                       --query

    COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

    --if the tran is doomed, and the entryTrancount was 0,

    --we have to roll back

    IF xact_state()= -1 and @entryTrancount = 0

        ROLLBACK TRANSACTION;

    --otherwise, we can still save the other activities in the

    --transaction.

    ELSE IF xact_state() = 1 --transaction not doomed, but open

        BEGIN

        ROLLBACK TRANSACTION @savepoint;

        COMMIT TRANSACTION;

        END

    DECLARE @ERRORmessage nvarchar(4000);

    SET @ERRORmessage = 'Error occurred in procedure ''' + object_name(@@procid)

                   + ''', Original Message: ''' + ERROR_MESSAGE() + '''';

    --change to RAISERROR (50000, @ERRORmessage,16) if you want to continue processing

    THROW 50000,@ERRORmessage, 16;

    RETURN -100;

    END CATCH

 END;

Next, try out the code:

 DECLARE @foodItemId int, @retval int;

 EXECUTE @retval = menu.foodItem$insert @name ='Burger',

        @description = 'Mmmm Burger',

                       @newFoodItemId = @foodItemId output;

 SELECT @retval as returnValue;

 IF @retval >= 0

    SELECT foodItemId, name, description

    FROM menu.foodItem

    where foodItemId = @foodItemId;

There's no error, so the row we created is returned:

Now, try out the code with an error:

 DECLARE @foodItemId int, @retval int;

 EXECUTE @retval = menu.foodItem$insert @name ='Big Burger',

                       @description = '',

                       @newFoodItemId = @foodItemId output;

 SELECT @retval as returnValue;

 IF @retval >= 0

    SELECT foodItemId, name, description

    FROM menu.foodItem

    where foodItemId = @foodItemId;

Because the description is blank, an error is returned:

Note that no code in the batch is executed after the THROW statement is executed. Using RAISERROR will allow the processing to continue if you so desire.

Triggers

Just as in stored procedures, you can start transactions, set savepoints, and roll back to a savepoint. However, if you execute a ROLLBACK TRANSACTION statement in a trigger, two things can occur:

• Outside a TRY-CATCH block, the entire batch of SQL statements is canceled.
• Inside a TRY-CATCH block, the batch isn’t canceled, but the transaction count is back to zero.

Back in Chapters 6 and 7, we discussed and implemented triggers that consistently used rollbacks when any error occurred. If you’re not using TRY-CATCH blocks, this approach is generally exactly what’s desired, but when using TRY-CATCH blocks, it can make things more tricky. To handle this, in the CATCH block of stored procedures I’ve included this code:

    --if the tran is doomed, and the entryTrancount was 0,

    --we have to roll back

    IF xact_state()= -1 and @entryTrancount = 0

       rollback transaction;

    --otherwise, we can still save the other activities in the

    --transaction.

    ELSE IF xact_state() = 1 --transaction not doomed, but open

        BEGIN

        ROLLBACK TRANSACTION @savepoint;

        COMMIT TRANSACTION;

        END

This is an effective, if perhaps limited, method of working with errors from triggers that works in most any situation. Removing all ROLLBACK TRANSACTION commands but just raising an error from a trigger dooms the transaction, which is just as much trouble as the rollback. The key is to understand how this might affect the code that you’re working with and to make sure that errors are handled in an understandable way. More than anything, test all types of errors in your system (trigger, constraint, and so on).

For an example, I will create a trigger based on the framework we used for triggers in Chapter 6 and 7, which is presented in more detail in Appendix B. Instead of any validations, I will just immediately cause an error with the statement THROW 50000,'FoodItem''s cannot be done that way',16. Note that my trigger template does do a rollback in the trigger, assuming that users of these triggers follow the error handing setup here, rather than just dooming the transaction. Dooming the transaction could be a safer way to go if you do not have full control over error handling.

 CREATE TRIGGER menu.foodItem$InsertTrigger

 ON menu.foodItem

 AFTER INSERT

 AS

 BEGIN

    SET NOCOUNT ON;

    SET ROWCOUNT 0; --in case the client has modified the rowcount

    --use inserted for insert or update trigger, deleted for update or delete trigger

    --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

    --that is equal to number of merged rows, not rows being checked in trigger

    DECLARE @msg varchar(2000), --used to hold the error message

    --use inserted for insert or update trigger, deleted for update or delete trigger

    --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

    --that is equal to number of merged rows, not rows being checked in trigger

    @rowsAffected int = (SELECT COUNT(*) FROM inserted);

    --@rowsAffected int = (SELECT COUNT(*) FROM deleted);

    --no need to continue on if no rows affected

    IF @rowsAffected = 0 RETURN;

    BEGIN TRY

       --[validation blocks][validation section]

    THROW 50000, 'FoodItem''s cannot be done that way',16

       --[modification blocks][modification section]

    END TRY

    BEGIN CATCH

          IF @@trancount > 0

             ROLLBACK TRANSACTION;

          THROW;

    END CATCH

 END

In the downloadable code, I have modified the error handling in the stored procedure to put out markers, so you can see what branch of the code is being executed:

    SELECT 'In error handler'

    --if the tran is doomed, and the entryTrancount was 0,

    --we have to roll back

    IF xact_state()= -1 and @entryTrancount = 0

       begin

        SELECT 'Transaction Doomed'

        ROLLBACK TRANSACTION

        end

     --otherwise, we can still save the other activities in the

    --transaction.

    ELSE IF xact_state() = 1 --transaction not doomed, but open

        BEGIN

        SELECT 'Savepoint Rollback'

           ROLLBACK TRANSACTION @savepoint

        COMMIT TRANSACTION

        END

Executing the code that contains an error that the constraints catch:

 DECLARE @foodItemId int, @retval int;

 EXECUTE @retval = menu.foodItem$insert @name ='Big Burger',

                       @description = '',

                       @newFoodItemId = @foodItemId output;

 SELECT @retval;

This is the output, letting us know that the transaction was still technically open and we could have committed any changes we wanted to:

You can see the constraint message, after the template error. Now, try to enter some data that is technically correct but is blocked by the trigger with the ROLLBACK:

 DECLARE @foodItemId int, @retval int;

 EXECUTE @retval = menu.foodItem$insert @name ='Big Burger',

                       @description = 'Yummy Big Burger',

                       @newFoodItemId = @foodItemId output;

 SELECT @retval;

These results are a bit more mysterious, though the transaction is clearly in an error state. Since the rollback operation occurs in the trigger, once we reach the error handler, there is no need to do any savepoint or rollback, so it just finishes:

For the final demonstration, I will change the trigger to just do a RAISERROR, with no other error handling:

 ALTER TRIGGER menu.foodItem$InsertTrigger

 ON menu.foodItem

 AFTER INSERT

 AS

 BEGIN

     DECLARE @rowsAffected int, --stores the number of rows affected

           @msg varchar(2000); --used to hold the error message

     SET @rowsAffected = @@rowcount;

     --no need to continue on if no rows affected

     IF @rowsAffected = 0 return;

     SET NOCOUNT ON; --to avoid the rowcount messages

     SET ROWCOUNT 0; --in case the client has modified the rowcount

    THROW 50000,'FoodItem''s cannot be done that way',16;

 END;

Then, reexecute the previous statement that caused the trigger error:

Hence, our error handler covered all of the different bases of what can occur for errors. In each case, we got an error message that would let us know where an error was occurring and that it was an error. The main thing I wanted to show in this section is that error handling is messy and adding triggers, while useful, complicates the error handling process, so I would certainly use constraints as much as possible and triggers as rarely as possibly (the primary uses I have for them were outlined in Chapter 7). Also, be certain to develop an error handler in your T-SQL code and applications that is used with all of your code so that you capture all exceptions in a manner that is desirable for you and your developers.

Lock Types

If you’ve been around for a few versions of SQL Server, you probably know that since SQL Server 7.0, SQL Server primarily uses row-level locks. That is, a user locking some resource in SQL Server does it on individual rows of data, rather than on pages of data, or even on complete tables.

However, thinking that SQL Server only locks at the row level is misleading, as SQL Server can use six different types of locks to lock varying portions of the database, with the row being the finest type of lock, all the way up to a full database lock. And each of them will be used quite often. The types of locks in Table 11-1 are supported.

At the point of request, SQL Server determines approximately how many of the database resources (a table, a row, a key, a key range, and so on) are needed to satisfy the request. This is calculated on the basis of several factors, the specifics of which are unpublished. Some of these factors include the cost of acquiring the lock, the amount of resources needed, and how long the locks will be held (the next major section, “Isolation Levels,” will discuss the factors surrounding the question “how long?”). It’s also possible for the query processor to upgrade the lock from a more granular lock to a less specific type if the query is unexpectedly taking up large quantities of resources.

For example, if a large percentage of the rows in a table are locked with row locks, the query processor might switch to a table lock to finish out the process. Or, if you’re adding large numbers of rows into a clustered table in sequential order, you might use a page lock on the new pages that are being added.

Lock Modes

Beyond the type of lock, the next concern is how strongly to lock the resource. For example, consider a construction site. Workers are generally allowed onto the site but not civilians who are not part of the process. Sometimes, however, one of the workers might need exclusive use of the site to do something that would be dangerous for other people to be around (like using explosives, for example.)

Where the type of lock defined the amount of the database to lock, the mode of the lock refers to how strict the lock is and how protective the engine is when dealing with other locks. Table 11-2 lists these available modes.

Each of these modes, coupled with the granularity, describes a locking situation. For example, an exclusive table lock would mean that no other user can access any data in the table. An update table lock would say that other users could look at the data in the table, but any statement that might modify data in the table would have to wait until after this process has been completed.

To determine which mode of a lock is compatible with another mode of lock, we deal with lock compatibility. Each lock mode may or may not be compatible with the other lock mode on the same resource (or resource that contains other resources). If the types are compatible, two or more users may lock the same resource. Incompatible lock types would require the any additional users simply to wait until all of the incompatible locks have been released.

Table 11-3 shows which types are compatible with which others.

Although locks are great for data consistency, as far as concurrency is considered, locked resources stink. Whenever a resource is locked with an incompatible lock type and another process cannot use it to complete its processing, concurrency is lowered, because the process must wait for the other to complete before it can continue. This is generally referred to as blocking: one process is blocking another from doing something, so the blocked process must wait its turn, no matter how long it takes.

Simply put, locks allow consistent views of the data by only letting a single process modify a single resource at a time, while allowing multiple viewers simultaneous utilization in read-only access. Locks are a necessary part of SQL Server architecture, as is blocking to honor those locks when needed, to make sure one user doesn’t trample on another’s data, resulting in invalid data in some cases.

In the next section, I’ll discuss isolation levels, which determine how long locks are held. Executing SELECT * FROM sys.dm_os_waiting_tasks gives you a list of all processes that tells you if any users are blocking and which user is doing the blocking. Executing SELECT * FROM sys.dm_tran_locks lets you see locks that are being held. SQL Server Management Studio has a decent Activity Monitor, accessible via the Object Explorer in the Management folder.

It’s possible to instruct SQL Server to use a different type of lock than it might ordinarily choose by using table hints on your queries. For individual tables in a FROM clause, you can set the type of lock to be used for the single query like so:

 FROM table1 [WITH] (<tableHintList>)

          join table2 [WITH] (<tableHintList>)

Note that these hints work on all query types. In the case of locking, you can use quite a few. A partial list of the more common hints follows:

• PageLock: Forces the optimizer to choose page locks for the given table.
• NoLock: Leave no locks, and honor no locks for the given table.
• RowLock: Force row-level locks to be used for the table.
• Tablock: Go directly to table locks, rather than row or even page locks. This can speed some operations, but seriously lowers write concurrency.
• TablockX: This is the same as Tablock, but it always uses exclusive locks (whether it would have normally done so or not).
• XLock: Use exclusive locks.
• UpdLock: Use update locks.

Note that SQL Server can override your hints if necessary. For example, take the case where a query sets the table hint of NoLock, but then rows are modified in the table in the execution of the query. No shared locks are taken or honored, but exclusive locks are taken and held on the table for the rows that are modified, though not on rows that are only read (this is true even for resources that are read as part of a trigger or constraint).

A very important term that’s you need to understand is “deadlock.” A deadlock is a circumstance where two processes are trying to use the same objects, but neither will ever be able to complete because each is blocked by the other connection. For example, consider two processes (Processes 1 and 2), and two resources (Resources A and B). The following steps lead to a deadlock:

1. Process 1 takes a lock on Resource A, and at the same time, Process 2 takes a lock on Resource B.
2. Process 1 tries to get access to Resource B. Because it’s locked by Process 2, Process 1 goes into a wait state.
3. Process 2 tries to get access to Resource A. Because it’s locked by Process 1, Process 2 goes into a wait state.

At this point, there’s no way to resolve this issue without ending one of the processes. SQL Server arbitrarily kills one of the processes, unless one of the processes has voluntarily raised the likelihood of being the killed process by setting DEADLOCK_PRIORITY to a lower value than the other. Values can be between integers –10 and 10, or LOW (equal to –5), NORMAL (0), or HIGH (5). SQL Server raises error 1205 to the client to tell the client that the process was stopped:

At this point, you could resubmit the request, as long as the call was coded such that the application knows when the transaction was started and what has occurred (something every application programmer ought to strive to do).

Deadlocks can be hard to diagnose, as you can deadlock on many things, even hardware access. A common trick to try to alleviate frequent deadlocks between pieces of code is to order object access in the same order in all code (so table dbo.Apple, dbo.Bananna, etc) if possible. This way, locks are more likely to be taken in the same order, causing the lock to block earlier, so that the next process is blocked instead of deadlocked.

An important consideration is that you really can’t completely avoid deadlocks. Frequent deadlocks can be indicative of a problem with your code, but often, if you are running a very busy server, deadlocks happen, and the best thing to do is handle them by resubmitting the last transaction executed (too many applications just raise the deadlock as an error that users don’t understand). Although frequent deadlocks are often an issue, it is very hard to code your system to be 100% safe from deadlocks (particularly when allowing users to share resources) so every call to the server should be aware that a deadlock could occur and, ideally, what to do with it.

Using SQL Server Profiler, you can add the DeadLock Graph event class to see deadlock events, which helps diagnose them. For more information about Profiler, check SQL Server Books Online.

READ UNCOMMITTED

Ignore all locks, and don’t issue locks. Queries can see any data that has been saved to the table, regardless of whether or not it’s part of a transaction that hasn’t been committed (hence the name). However, READ UNCOMMITTED still leaves exclusive locks if you do modify data, to keep other users from changing data that you haven’t committed.

For the most part, READ UNCOMMITTED is a good tool for developers to use to check the progress of operations and to look at production systems when SNAPSHOT isn’t available. It should not be used as a performance tuning tool, however, because all of your code should use only committed trustable data that has passed the requirements of the constraints and triggers you have implemented. For example, say you execute the following code on one connection:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --this is the default, just

                       --setting for emphasis

 BEGIN TRANSACTION

 INSERT INTO dbo.testIsolationLevel(value);

 VALUES('Value3'),

Then, you execute on a second connection:

 --CONNECTION B

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 SELECT *

 FROM dbo.testIsolationLevel;

This returns the following results:

Being able to see locked data is quite valuable, especially when you're in the middle of a long-running process. That’s because you won’t block the process that’s running, but you can see the data being modified. There is no guarantee that the data you see will be correct (it might fail checks and be rolled back), but for looking around and some reporting needs, this data might be good enough.

Finally, commit the transaction you started earlier:

 --CONNECTION A

 COMMIT TRANSACTION;

READ COMMITTED

READ COMMITTED is the default isolation level as far as SQL Server is concerned, and as the name states, it prevents you from seeing uncommitted data. Be careful that your toolset may or may not use it as its default (some toolsets use SERIALIZABLE as the default, which, as you will see is pretty tight and is not great for concurrency). All shared and update locks are released as soon as the process is finished using the resource. Exclusive locks are held until the end of the transaction. Data modifications are usually executed under this isolation level. However, understand that this isolation level isn’t perfect, as there isn’t protection for repeatable reads or phantom rows. This means that as the length of the transaction increases, there’s a growing possibility that some data that was read during the first operations within a transaction might have been changed or deleted by the end of the transaction. It happens extremely rarely when transactions are kept short, so it’s generally considered an acceptable risk—for example:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 BEGIN TRANSACTION;

 SELECT * FROM dbo.testIsolationLevel;

You see all the rows in the table from the previous section (though the testIsolationLevelId might be different if you had errors when you built your code). Then, on the second connection, delete a row:

 --CONNECTION B

 DELETE FROM dbo.testIsolationLevel

 WHERE testIsolationLevelId = 1;

Finally, go back to the other connection and execute, still within the transaction:

 --CONNECTION A

 SELECT *

 FROM dbo.testIsolationLevel;

 COMMIT TRANSACTION;

This returns the following results:

The first time you grasp this topic well (hopefully now, but it may take a bit of time for it to sink in), you may very well panic that your data integrity is in trouble. You are right in one respect. There are some holes in the default isolation level. However, since most referential integrity checks are done based on the existence of some data, the impact of READ COMMITTED is lessened by the fact that most operations in an OLTP database system are inserts and updates. The impact is further lessened because relationships are pretty well guarded by the fact that deleting the parent or child row in a relationship requires a lock on the other rows. So if someone tries to modify the parent and someone else tries to modify the child, one process will be blocked by the other.

Beyond the fact that relationships require locked checks in READ COMMITTED isolation, the key to the success of using this isolation level is simple probability. The chances of two users stepping on each other’s processes within milliseconds is pretty unlikely, even less likely is the scenario that one user will do the exact thing that would cause inconsistency. However, the longer your transactions and the higher the concurrent number of users on the system, the more likely that READ COMMITTED will produce anomalies.

In my 18 years of using SQL Server, the primary issues I have found with READ COMMITTED have centered exclusively on checking/retrieving a value and then going back later and using that value. If you do much of that, and it is important that the situation remain the same until you use the value, consider implementing your code using a higher level of isolation.

For example, consider the issues involved in implementing a system to track drugs given to a patient in a hospital. For a system such as this, you’d never want to give a user too much medicine accidentally because when you started a process to set up a schedule via a batch system, a nurse was administering the dosage off schedule. Although this situation is unlikely, as you will see in the next few sections, an adjustment in isolation level would prevent it from occurring at all.

REPEATABLE READ

The REPEATABLE READ isolation level includes protection from data being deleted from under your operation. Shared locks are now held during the entire transaction to prevent other users from modifying the data that has been read. You would be most likely to use this isolation level if your concern is the absolute guarantee of existence of some data when you finish your operation.

As an example on one connection, execute the following statement:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

 BEGIN TRANSACTION;

 SELECT * FROM dbo.testIsolationLevel;

This returns the following:

Then, on a different connection, run the following:

 --CONNECTION B

 INSERT INTO dbo.testIsolationLevel(value)

 VALUES ('Value4'),

This executes, but try executing the following code:

 --CONNECTION B

 DELETE FROM dbo.testIsolationLevel

 WHERE value = 'Value3';

You go into a blocked state: CONNECTION B will need an exclusive lock on that particular value, because deleting that value would cause the results from CONNECTION A to return fewer rows. Back on the first connection, run the following code:

 --CONNECTION A

 SELECT * FROM dbo.testIsolationLevel;

 COMMIT TRANSACTION;

This will return the following:

And immediately, the batch on the other connection will complete. Now, view the data (from either connection):

 --CONNECTION A

 SELECT * FROM dbo.testIsolationLevel;

This returns

The fact that other users may be changing the data you have locked can be a very serious concern for the perceived integrity of the data. If the user on connection A goes right back and the row is deleted, that user will be confused. Of course, nothing can really be done to solve this problem, as it is just a fact of life. In the “Optimistic Locking” section, I will present a method of making sure that one user doesn’t crush the changes of another; the method could be extended to viewed data, but generally, this is not the case for performance reasons.

In the end, you can implement most any scheme to protect the data, but all you are doing is widening the window of time where users are protected. No matter what, once the user relinquishes transactional control on a row, it will be fair game to other users without some form of workflow system in place (a topic that is well beyond the scope of my book, though once you are finished reading this book, you could design and create one!).

SERIALIZABLE

SERIALIZABLE takes everything from REPEATABLE READ and adds in phantom-row protection. SQL Server accomplishes this by taking locks not only on existing data that it has read but on any ranges of data that could match any SQL statement executed. This is the most restrictive isolation level and is the best in any case where data integrity is absolutely necessary. It can cause lots of blocking; for example, consider what would happen if you executed the following query under the SERIALIZABLE isolation level:

 SELECT *

 FROM dbo.testIsolationLevel;

No other user will be able to modify the table until all rows have been returned and the transaction it was executing within (implicit or explicit) is completed.

If lots of users are viewing data in the table under any of the previously mentioned isolation levels, it can be difficult to get any modifications done. If you’re going to use SERIALIZABLE, you need to be careful with your code and make sure it only uses the minimum number of rows needed (especially if you are not using SNAPSHOT isolation level for read processes, as covered in the next section).

Execute this statement on a connection to simulate a user with a table locked:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 BEGIN TRANSACTION;

 SELECT * FROM dbo.testIsolationLevel;

Then, try to add a new row to the table:

 --CONNECTION B

 INSERT INTO dbo.testIsolationLevel(value)

 VALUES ('Value5'),

Your insert is blocked. Commit the transaction on the A connection:

 --CONNECTION A

 SELECT * FROM dbo.testIsolationLevel;

 COMMIT TRANSACTION;

This returns the following:

The results are the same. However, this unblocks CONNECTION B, and running the SELECT again, you will see that the contents of the table are now the following:

It is important to be careful with the SERIALIZABLE isolation level. I can't stress enough that multiple readers can read the same data, but no one can update it while others are reading. Too often, people take this to mean that they can read some data and be guaranteed that no other user might have read it also, leading occasionally to inconsistent results and more frequently to deadlocking issues.

SNAPSHOT

SNAPSHOT isolation was one of the major cool new features in SQL Server 2005, and it continues to be one of my favorites (particularly the READ COMMITTED SNAPSHOT variant that is mentioned later in this section). It lets you read the data as it was when the transaction started, regardless of any changes. It’s a special case, because although it doesn’t technically allow for phantom rows, nonrepeatable reads, or dirty reads from any queries within the transaction, it doesn’t necessarily represent the current state of the data. You might check a value in a table at the beginning of the transaction and it’s in the physical table, but later, you requery the table and it is no longer there. As long as you are inside the same transaction, even though the value exists in your virtual table, it needn’t exist in the physical table any longer (in fact, the physical table needn’t exist either!). This provides that the results of your query will reflect a consistent state of the database at some time, which is generally very desirable.

What makes SNAPSHOT particularly useful is that it doesn’t use locks in the normal way, because it looks at the data as it was at the start of the transaction. Modifying data under this isolation level has its share of problems, which I’ll demonstrate later in this section. However, I don’t want completely to scare you off, as this isolation level can become a major part of a highly concurrent design strategy (particularly useful for reads in an optimistic locking strategy, which the last sections of this chapter cover).

The largest downside is the effect it can have on performance if you are not prepared for it. This history data is written not only to the log, but the data that will be used to support other users that are in a SNAPSHOT isolation level transaction is written to the tempdb. Hence, if this server is going to be very active, you have to make sure that tempdb is up to the challenge, especially if you’re supporting large numbers of concurrent users.

The good news is that, if you employ the strategy of having readers use SNAPSHOT isolation level , data readers will no longer block data writers (in any of the other isolation levels), and they will always get a transactionally consistent view of the data. So when the vice president of the company decides to write a 20-table join query in the middle of the busiest part of the day, all other users won’t get stuck behind him with data locks. The better news is that he won’t see the mistaken ten-million-dollar entry that one of the data-entry clerks added to the data that the check constraint hasn’t had time to deny yet (the vice president would have seen the error if you were using the READ UNCOMMITTED solution, which is the unfortunate choice of many novice performance tuners). The bad news is that eventually the vice president’s query might take up all the resources and cause a major system slowdown that way. (Hey, if it was too easy, companies wouldn’t need DBAs. And I, for one, wouldn’t survive in a nontechnical field.)

To use (and demonstrate) SNAPSHOT isolation level, you have to alter the database you’re working with (you can even do this to tempdb):

 ALTER DATABASE tempDb

     SET ALLOW_SNAPSHOT_ISOLATION ON;

Now, the SNAPSHOT isolation level is available for queries.

Let’s look at an example. On the first connection, start a transaction and select from the -testIsolationLevel table:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

 BEGIN TRANSACTION;

 SELECT * FROM dbo.testIsolationLevel;

This returns the following results:

On a second connection, run the following:

 --CONNECTION B

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 INSERT INTO dbo.testIsolationLevel(value)

 VALUES ('Value6'),

This executes with no waiting. Going back to Connection A, reexecuting the SELECT returns the same set as before, so the results remain consistent. On Connection B, run the following DELETE statement:

 --CONNECTION B

 DELETE FROM dbo.testIsolationLevel

 WHERE value = 'Value4';

This doesn't have to wait either. Going back to the other connection again, nothing has changed.

 --CONNECTION A

 SELECT * FROM dbo.testIsolationLevel;

This still returns

So what about modifying data in SNAPSHOT isolation level? If no one else has modified the row, you can make any change:

 --CONNECTION A

 UPDATE dbo.testIsolationLevel

 SET value = 'Value2-mod'

 WHERE testIsolationLevelId = 2;

This runs, but going back to the B connection, if you try to select this row

 --CONNECTION B

 SELECT * FROM dbo.testIsolationLevel

you will find the query is blocked, and the connection is forced to wait, because this row is new and has an exclusive lock on it, and connection B is not in SNAPSHOT ISOLATION level.

Commit the transaction in CONNECTION A, and you’ll see rows such as these:

 --CONNECTION A

 COMMIT TRANSACTION;

 SELECT * FROM dbo.testIsolationLevel;

This returns the current contents of the table:

The messy and troubling bit with modifying data under the SNAPSHOT isolation level is what happens when one user modifies a row that another user has also modified and committed the transaction for. To see this, in CONNECTION A run the following, simulating a user fetching some data into the cache:

 --CONNECTION A

 SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

 BEGIN TRANSACTION;

 --touch the data

 SELECT * FROM dbo.testIsolationLevel;

This returns the same results as just shown. Then, a second user changes the value:

 --CONNECTION B

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --any will do

 UPDATE dbo.testIsolationLevel

 SET value = 'Value5-mod'

 WHERE testIsolationLevelId = 5; --might be different surrogate key value in yours

Next, the user on CONNECTION A tries to update the row also:

 --CONNECTION A

 UPDATE dbo.testIsolationLevel

 SET value = 'Value5-mod'

 WHERE testIsolationLevelId = 5; --might be different in yours

As this row has been deleted by a different connection, the following error message rears its ugly head:

As such, strictly for simplicity’s sake, I recommend that almost all retrieval-only operations can execute under the SNAPSHOT isolation level, and the procedures that do data modifications execute under the READ COMMITTED isolation level. As long as data is only read, the connection will see the state of the database as it was when the data was first read.

A strong word of caution—if you do data validations under SNAPSHOT isolation level and you do data-checking logic such as in a trigger or procedure, the data might already be invalid in the live database, especially if the transaction runs long. This invalid data is far worse than REPEATABLE READ, where the data is always valid when the check is done but might be changed after the violation. However, you should note that FOREIGN KEY constraints, when doing a modification, are smart enough to use the same sorts of locks as READ COMMITTED would to protect against this sort of issue. My suggestion (if you ignore the suggestion not to do writes under SNAPSHOT) would be to manually code SET TRANSACTION ISOLATION LEVEL READ COMMITTED or REPEATABLE READ or SERIALIZABLE where necessary in your modification procedures or triggers to avoid this sort of issue.

READ COMMITTED SNAPSHOT (Database Setting)

The database setting READ_COMMITTED_SNAPSHOT changes the isolation level of READ COMMITTED to behave very much like SNAPSHOT isolation level on a statement level.

Note that I said “statement” and not “transaction.” In SNAPSHOT isolation level, once you start a transaction, you get a consistent view of the database as it was when the transaction started until you close it. READ_COMMITTED_SNAPSHOT gives you a consistent view of the database for a single statement. Set the database into this mode as follows:

 --must be no active connections other than the connection executing

 --this ALTER command Does not require ALLOW_SNAPSHOT_ISOLATION enabled.

 ALTER DATABASE <databasename>

    SET READ_COMMITTED_SNAPSHOT ON;

When you do this, every statement is now in SNAPSHOT isolation level by default. For example, imagine you’re at the midpoint of the following pseudo-batch:

 BEGIN TRANSACTION;

 SELECT column FROM table1;

 --midpoint

 SELECT column FROM table1;

 COMMIT TRANSACTION;

If you’re in SNAPSHOT isolation level, table1 could change completely—even get dropped—and you wouldn’t be able to tell when you execute the second SELECT statement. You’re given a consistent view of the database for reading. With the READ_COMMITTED_SNAPSHOT database setting turned on, in a READ COMMITTED isolation level transaction, your view of table1 would be consistent with how it looked when you started reading, but when you started the second pass through the table, it might not match the data the first time you read through. This behavior is similar to plain READ COMMITTED, except that you don’t see any new phantoms or nonrepeatable reads while retrieving rows produced during the individual statement (other users can delete and add rows while you scan through the table, but you won’t be affected by the changes), and SQL Server doesn’t need to take locks or block other users.

Using READ_COMMITTED_SNAPSHOT can actually perform tremendously better than just using READ COMMITTED, though it does suffer from the same (or maybe worse) issues with data. You should remember that in the previous section on READ COMMITTED, I noted that because SQL Server releases locks immediately after reading the data, another user could come behind you and change data that you just finished using to do some validation. This same thing is true for READ_COMMITTED_SNAPSHOT, but the window of time can be slightly longer because SQL Server reads only history as it passes through different tables. This amount of time is generally insignificant and usually isn’t anything to worry about, but it can be important based on the type of system you’re creating.

For places where you might need more safety, consider using the higher isolation levels, such as REPEATABLE READ or SERIALIZABLE. I would certainly suggest that, in the triggers and modification procedures that you build using this isolation level, you consider the upgraded isolation level. The best part is that basic readers who just want to see data for a query or report will not be affected. Later in this chapter, when I present the mechanism for optimistic locking, you will see that whether or not a reading user gets an old version doesn’t really matter. Users will never be allowed to modify anything but the rows that look exactly like the ones that they fetched.

SNAPSHOT isolation level and the READ_COMMITTED_SNAPSHOT settings are very important aspects of SQL Server’s concurrency feature set. They cut down on blocking and the need to use the dirty reads to look at active OLTP data for small reports and for read-only queries to cache data for user-interface processes.

Adding Optimistic Lock Columns

In this section, we’ll add an optimistic lock column to a table to support either adding the datetime column or the rowversion column. The first method mentioned, checking all columns, needs no table modifications.

As an example, let’s create a new simple table, in this case hr.person (again, use any database you like; the sample uses tempdb). Here’s the structure:

 CREATE SCHEMA hr;

 GO

 CREATE TABLE hr.person

 (

     personId int NOT NULL IDENTITY(1,1) CONSTRAINT PKperson primary key,

     firstName varchar(60) NOT NULL,

     middleName varchar(60) NOT NULL,

     lastName varchar(60) NOT NULL,

     dateOfBirth date NOT NULL,

     rowLastModifyTime datetime2(3) NOT NULL

        CONSTRAINT DFLTperson_rowLastModifyTime DEFAULT (SYSDATETIME()),

     rowModifiedByUserIdentifier nvarchar(128) NOT NULL

        CONSTRAINT DFLTperson_rowModifiedByUserIdentifier default suser_sname()

 );

Note the two columns for our optimistic lock, named rowLastModifyTime and rowModifiedByUserIdentifier. I’ll use these to hold the last date and time of modification and the SQL Server’s login name of the principal that changed the row. There are a couple ways to implement this:

• Let the manipulation layer manage the value like any other column: This is often what client programmers like to do, and it’s acceptable, as long as you’re using trusted computers to manage the timestamps. I feel it’s inadvisable to allow workstations to set such values, because it can cause confusing results. For example, say your application displays a message stating that another user has made changes, and the time the changes were made is in the future, based on the client’s computer. Then the user checks out his or her PC clock, and it’s set perfectly.
• Using SQL Server code: For the most part, triggers are implemented to fire on any modification to data.

As an example of using SQL Server code (my general method of doing this), I’ll implement an INSTEAD OF trigger on the update of the hr.person table (note that the errorLog$insert procedure was created back in Chapter 6 and has been commented out for this demonstration in case you don’t have it available):

 CREATE TRIGGER hr.person$InsteadOfUpdate

 ON hr.person

 INSTEAD OF UPDATE AS

 BEGIN

    --stores the number of rows affected

    DECLARE @rowsAffected int = @@rowcount,

          @msg varchar(2000) = ''; --used to hold the error message

    SET NOCOUNT ON; --to avoid the rowcount messages

    SET ROWCOUNT 0; --in case the client has modified the rowcount

    BEGIN TRY

          --[validation blocks]

          --[modification blocks]

          --remember to update ALL columns when building instead of triggers

          UPDATE hr.person

          SET firstName = inserted.firstName,

              middleName = inserted.middleName,

              lastName = inserted.lastName,

              dateOfBirth = inserted.dateOfBirth,

              rowLastModifyTime = default, -- set the value to the default

              rowModifiedByUserIdentifier = default

              FROM hr.person

                 JOIN inserted

                       on hr.person.personId = inserted.personId;

    END TRY

       BEGIN CATCH

             IF @@trancount > 0

                ROLLBACK TRANSACTION;

             THROW; --will halt the batch or be caught by the caller's catch block

       END CATCH

 END

Then, insert a row into the table:

 INSERT INTO hr.person (firstName, middleName, lastName, dateOfBirth)

 VALUES ('Paige','O','Anxtent','19391212'),

 SELECT *

 FROM hr.person;

Now, you can see that the data has been created:

Next, update the row:

 UPDATEhr.person

 SET middleName = 'Ona'

 WHERE personId = 1;

 SELECT rowLastModifyTime

 FROM hr.person;

You should see that the update date has changed (in my case, it was pretty doggone late at night, but such is the life):

If you want to set the value on insert, or implement rowCreatedByDate or userIdentifier columns, the code would be similar. Because this has been implemented in an INSTEAD OF trigger, the user or even the programmer cannot overwrite the values, even if they include it in the column list of an INSERT.

As previously mentioned, the other method that requires table modification is to use a rowversion column. In my opinion, this is the best way to go, and I almost always use a rowversion. I usually have the row modification columns on there as well, for the user’s benefit. I find that the modification columns take on other uses and have a tendency to migrate to the control of the application developer, and rowversion columns never do. Plus, even if the triggers don’t make it on the table for one reason or another, the rowversion column continues to work. Sometimes, you may be prohibited from using INSTEAD OF insert triggers for some reason (recently, I couldn’t use them in a project I worked on because they invalidate the identity functions).

Let’s add a rowversion column to our table to demonstrate using it as an optimistic lock:

 ALTER TABLE hr.person

    ADD rowversion rowversion;

 GO

 SELECT personId, rowversion

 FROM hr.person;

You can see now that the rowversion has been added and magically updated:

Now, when the row gets updated, the rowversion is modified:

 UPDATE hr.person

 SET firstName = 'Paige' --no actual change occurs

 WHERE personId = 1;

Then, looking at the output, you can see that the value of the rowversion has changed:

 SELECT personId, rowversion

 @@FROM hr.person;

This returns the following result:

Coding for Row-Level Optimistic Locking

Next, include the checking code in your stored procedure. Using the hr.person table previously created, the following code snippets will demonstrate each of the methods (note that I’ll only use the optimistic locking columns germane to each example and won’t include the others).

Check all the cached values for the columns:

 UPDATE hr.person

 SET firstName = 'Headley'

 WHERE personId = 1 --include the key

    and firstName = 'Paige'

    and middleName = 'ona'

    and lastName = 'Anxtent'

    and dateOfBirth = '19391212';

It’s a good practice to check your rowcount after an update with an optimistic lock to see how many rows have changed. If it is 0, you could check to see if the row exists with that primary key:

 IF EXISTS ( SELECT *

          FROM hr.person

          WHERE personId = 1) --check for existence of the primary key

    --raise an error stating that the row no longer exists

 ELSE

  --raise an error stating that another user has changed the row

Use a date column:

 UPDATE hr.person

 SET firstName = 'Fred'

 WHERE personId = 1 --include the key

    and rowLastModifyTime = ' 2012-02-19 22:33:53.845';

Use a rowversion column:

 UPDATE hr.person

 SET firstName = 'Fred'

 WHERE personId = 1

    and rowversion = 0x00000000000007D9;

Which is better performance-wise? Either of these generally performs just as well as the other, because in all cases, you’re going to be using the primary key to do the bulk of the work fetching the row and then your update. There’s a bit less overhead with the last two columns, because you don’t have to pass as much data into the statement, but that difference is negligible.

Deletions use the same WHERE clause, because if another user has modified the row, it’s probably a good idea to see if that user’s changes make the row still valuable:

 DELETE FROM hr.person

 WHERE personId = 1

    And rowversion = 0x00000000000007D9;

However, if the timestamp had changed since the last time the row was fetched, this would delete zero rows, since if someone has modified the rows since you last touched them, perhaps the deleted row now has value. I typically prefer using a rowversion column because it requires the least amount of work to always work perfectly. On the other hand, many client programmers prefer to have the manipulation layer of the application set a datetime value, largely because the datetime value has meaning to them to let them see when the row was last updated. Truthfully, I, too, like keeping these automatically modifying values in the table for diagnostic purposes. However, I prefer to rely on the rowversion column for locking because it is far simpler and safer and cannot be overridden by any code, no matter how you implement the other columns.