Concurrency is the ability to have multiple users (or processes/requests) access (change) shared data at the same time. The key is that when multiple processes or users are accessing the same resources, each user expects to see a consistent view of the data and certainly expects that other users will not be stomping on his or her results. Fortunately, SQL Server’s execution engine can give its full attention to a task as long as it needs to, even if that is just microseconds (unlike we humans, as I’ve had to correct several mistakes I made as a result of trying to write this sentence while talking to someone else).

The topics of this chapter will center on understanding why and how you should write your database code or design your objects to make them accessible concurrently by as many users as you have in your system. In this chapter, I’ll discuss the following:

• OS and hardware concerns: I’ll briefly discuss various issues that are out of the control of SQL code but can affect concurrency.
• Transactions: I’ll give an overview of how transactions work and how to start and stop them in T-SQL code.
• SQL Server concurrency methods: I’ll explain the two major types of concurrency controls used in SQL Server—pessimistic (using locks) and optimistic (using row versions).
• Coding for concurrency: I’ll discuss methods of coding data access to protect from users simultaneously making changes to data and placing data into less-than-adequate situations. You’ll also learn how to deal with users stepping on one another and how to maximize concurrency.

Like the previous chapter, we will look at the differences in how the on-disk technologies contrast with the newer in-memory OLTP technologies. One of the largest differences between the two (once you get past some of the in-memory limitations) is how each handles concurrency.

The key goal of this chapter is to acquaint you with many of the kinds of things SQL Server does to make it fast and safe to have multiple users doing the same sorts of tasks with the same resources and how you can optimize your code to make it easier for this to happen. A secondary goal is to help you understand what you need to design into your code and structures to deal with the aftermath of SQL Server’s coordination.

Because of the need to balance the amount of work with the user’s perception of the amount of work being done, there are going to be the following trade-offs:

• Number of concurrent users: How many users can (or need to) be served at the same time?
• Overhead: How complex are the algorithms to maintain concurrency?
• Accuracy: How correct must the results be? (This probably sounds horrible, but some concurrency tuning techniques lead to results that are actually wrong.)
• Performance: How fast does each process finish?
• Cost: How much are you willing to spend on hardware and programming time?

As you can probably guess, if all the users of a database system never needed to run queries at the same time, life in database-system–design land would be far simpler. You would have no need to be concerned with what other users might want to do. The only real performance goal would be to run one process really fast and move to the next process.

If no one ever shared resources, multitasking server operating systems would be unnecessary. All files could be placed on a user’s local computer, and that would be enough. And if we could single-thread all activities on a server, more actual work might be done, but just like the old days, people would sit around waiting for their turn (yes, with mainframes, people actually did that sort of thing). Internally, the situation is still technically the same in a way, as a computer cannot process more individual instructions than it has cores in its CPUs, but it can run and swap around fast enough to make hundreds or thousands of people feel like they are the only users. This is especially true if your system engineer builds computers that are good as SQL Server machines (and not just file servers) and the architects/programmers build systems that meet the requirements for a relational database (and not just what seems expedient at the time).

A common scenario for a multiuser database involves a sales and shipping application. You might have 50 salespeople in a call center trying to sell the last 25 closeout items that are in stock. It isn’t desirable to promise the last physical item accidentally to multiple customers, since two users might happen to read that it was available at the same time and both be allowed to place an order for it. In this case, stopping the first order wouldn’t be necessary, but you would want to disallow or otherwise prevent the second (or subsequent) orders from being placed, since they cannot be fulfilled as would be expected.

Most programmers instinctively write code to check for this condition and to try to make sure that this sort of thing doesn’t happen. Code is generally written that does something along these lines:

• Check to make sure that there’s adequate stock.
• Create a shipping row.

That’s simple enough, but what if one person checks to see if the product is available at the same time as another, and more orders are placed than you have adequate stock for? This is a far more common possibility than you might imagine. Is this acceptable? If you’ve ever ordered a product that you were promised in two days and then found out your items are on backorder for a month, you know the answer to this question: “No! It is very unacceptable.” When this happens, you try another retailer next time, right? If your database system is used to schedule vehicles like aircraft or trains, it is even less unacceptable.

I should also note that the problems presented by concurrency aren’t quite the same as those for parallelism, which is having one task split up and performed by multiple resources at the same time. Parallelism involves a whole different set of problems and luckily is more or less not your problem. In writing SQL Server code, parallelism is done automatically, as tasks can be split among resources (sometimes, you will need to adjust just how many parallel operations can take place, but in practice, SQL Server does most of that work for you). When I refer to concurrency, I generally mean having multiple different operations happening at the same time by different connections to shared SQL Server resources. Here are just a few of the questions you have to ask yourself:

• What effect will there be if a query modifies rows that have already been used by a query in a different batch?
• What if the other query creates new rows that would have been important to the other batch’s query? What if the other query deletes others?
• Most importantly, can one query corrupt another’s results?

You must consider a few more questions as well. Just how important is concurrency to you, and how much are you willing to pay in performance? The whole topic of concurrency is basically a set of trade-offs between performance, consistency, and the number of simultaneous users.

OS and Hardware Concerns

SQL Server is designed to run on a variety of hardware types, from a simple laptop with one processor to massive machines with at processors, and once Windows Server 2016 arrives, it could be far larger. What is amazing is that essentially the same basic code runs on a low-end computer as well as a clustered array of servers that rivals many supercomputers. Every machine running a version of SQL Server, from Express to Enterprise edition, can have a vastly different concurrency profile. Each edition will also be able to support different amounts of hardware: Express supports up to 1GB of RAM and one processor socket (with up to four cores, which is still more than our first SQL Server machine had one 486 processor and 16MB of RAM), and at the other end of the spectrum, Enterprise edition can handle as much hardware as a manufacturer can stuff into one box. Additionally, a specialized configuration called Parallel Data Warehouse is built specifically for data warehousing loads, and is the basis for the Azure SQL Data Warehouse product. Generally speaking, though, in every version, the very same concerns exist about how SQL Server handles multiple users using the same resources seemingly simultaneously. In this section, I’ll briefly touch on some of the issues governing concurrency that our T-SQL code needn’t be concerned with, because concurrency is part of the environment we work in.

SQL Server and the OS balance all the different requests and needs for multiple users. My goal in this chapter isn’t to delve too deeply into the gory hardware details, but it’s important to mention that concurrency is heavily tied to hardware architecture. For example, consider the following subsystems:

• Processor: It controls the rest of the computer subsystems, as well as doing any calculations needed. If you have too few processors, less work can be done simultaneously, and excessive time can be wasted switching between requests.
• Disk subsystem: Disk is always the slowest part of the system (even with solid-state drives [SSDs] becoming almost the status quo). A slow disk subsystem is the downfall of many systems, particularly because of the expense involved. Each drive can only read one piece of information at a time, so to access disks concurrently, it’s necessary to have multiple disk drives, and even multiple controllers or channels to disk drive arrays. I won’t go any deeper into disk configuration because the tech changes faster than you can imagine, but even SSDs won’t solve every IO issue.
• Network interface: Bandwidth to the users is critical but is usually less of a problem than disk access. However, it’s important to attempt to limit the number of round-trips between the server and the client. This is highly dependent on whether the client is connecting over a dialup connection or a gigabit Ethernet (or even multiple network interface cards). Using SET NOCOUNT ON in all connections and coded objects, such as stored procedures and triggers, is a good first step, because otherwise, a message is sent to the client for each query executed, requiring bandwidth (and processing) to deal with them.
• Memory: One of the cheapest commodities that you can improve substantially on a computer is memory. SQL Server can use a tremendous amount of memory within the limits of the edition you use (and the amount of RAM will not affect your licensing costs like processor cores either.)

Each of these subsystems needs to be in balance to work properly. You could theoretically have 128 CPUs and 1TB of RAM, and your system could still be slow. In this case, a slow disk subsystem could be causing your issues. The goal is to maximize utilization of all subsystems—the faster the better—but it’s useless to have super-fast CPUs with a super-slow disk subsystem. Ideally, as your load increases, disk, CPU, and memory usage would increase proportionally, though this is a heck of a hard thing to do. The bottom line is that the number of CPUs, disk drives, disk controllers, and network cards and the amount of RAM you have all affect concurrency.

For the rest of this chapter, I’m going to ignore these types of issues and leave them to others with a deeper hardware focus, such as the MSDN web site (msdn.microsoft.com) or great blogs like Glenn Berry’s (sqlserverperformance.wordpress.com). I’ll be focusing on design- and coding-related issues pertaining to how to write better SQL code to manage concurrency between SQL Server processes.

Transactions

No discussion of concurrency can really have much meaning without an understanding of the transaction. Transactions are a mechanism that allows one or more statements to be guaranteed either to be fully completed or to fail totally. It is an internal SQL Server mechanism that is used to keep the data that’s written to and read from tables consistent throughout a batch, as required by the user.

In this section we will first discuss a few details about transactions, followed by an overview of the syntax involved with base transactions.

Transaction Overview

Whenever data is modified in the database, the changes are not written to the physical table structures directly, but first to a page in RAM and then a log of every change is written to the transaction log immediately before the change is registered as complete (though you can change the log write to be asynchronous as well with the DELAYED DURABILITY database setting). Later, the physical disk structure is written asynchronously. Understanding the process of how modifications to data are made is essential, because while tuning your overall system, you have to be cognizant that when every modification operation is logged, you need to consider how large to make your transaction log, and when a database is written to frequently, the data files are often less important than the log files.

Beyond being a container for modification operations, transactions provide a container mechanism to allow multiple processes access to the same data simultaneously, while ensuring that logical operations are either carried out entirely or not at all, as well as defining boundaries for making sure one transaction’s actions aren’t affected by another more than is expected.

To explain the purpose of transactions, there’s a common acronym: ACID. It stands for the following:

• Atomicity: Every operation within a transaction appears to be a singular operation; either all its data modifications are performed, or none of them is performed.
• Consistency: Once a transaction is completed, the system must be left in a consistent state. This means that all the constraints on the data that are part of the RDBMS definition must be honored, and physical data written is as expected.
• Isolation: This means that the operations within a transaction must be suitably isolated from other transactions. In other words, no other transactions ought to see data in an intermediate state, within the transaction, until it’s finalized. This is done by several methods, covered in the “SQL Server Concurrency Methods” section later in this chapter.
• Durability: Once a transaction is completed (committed), all changes must be persisted if desired. The modifications should persist even in the event of a system failure. (Note that in addition to delayed durability, in-memory tables allow non-durable tables, which are empty on a server restart).

Transactions are used in two different ways. The first way is to provide for isolation between processes. Every DML and DDL statement, including INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, CREATE INDEX, and even SELECT statements, that is executed in SQL Server is within a transaction. If you are in the middle of adding a column to the table, you don’t want another user to try to modify data in the table at the same time. If any operation fails, or if the user asks for an operation to be undone, SQL Server uses the transaction log to undo the operations already performed.

Second, the programmer can use transaction commands to batch together multiple commands into one logical unit of work. For example, if you write data to one table successfully, and then try unsuccessfully to write to another table, the initial writes can be undone. This section will mostly be about defining and demonstrating this syntax.

How long the log is stored is based on the recovery model under which your database is operating. There are three models:

• Simple: The log is maintained only until the operation is executed and a checkpoint is executed (manually or automatically by SQL Server). A checkpoint operation makes certain that the data has been written to the data files, so it is permanently stored.
• Full: The log is maintained until you explicitly clear it out.
• Bulk logged: This keeps a log much like the full recovery model but doesn’t fully log some operations, such as SELECT INTO, bulk loads, index creations, or text operations. It just logs that the operation has taken place. When you back up the log, it will back up extents that were added during BULK operations, so you get full protection with quicker bulk operations.

Even in the simple model, you must be careful about log space, because if large numbers of changes are made in a single transaction or very rapidly, the log rows must be stored at least until all transactions are committed and a checkpoint takes place. This is clearly just a taste of transaction log management; for a more complete explanation, please see SQL Server Books Online.

Transaction Syntax

The syntax to start and stop transactions is pretty simple. I’ll cover four variants of the transaction syntax in this section:

• Transaction basics: The syntax of how to start and complete a transaction
• Nested transactions: How transactions are affected when one is started when another is already executing
• Savepoints: Used to selectively cancel part of a transaction
• Distributed transactions: Using transactions to control saving data on multiple SQL Servers

In the final part of this section, I’ll also cover explicit versus implicit transactions. These sections will give you the foundation needed to move ahead and start building proper code, ensuring that each modification is done properly, even when multiple SQL statements are necessary to form a single-user operation.

Transaction Basics

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 (particularly since you don’t realize it is still open). 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 may 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 (the connection was a pooled connection from a web site, so it was not a happy solution to management either).

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:

Msg 6401, Level 16, State 1, Line 7
Cannot roll back two. No transaction or savepoint of that name was found.

Unfortunately, after this error has occurred, the transaction is still left open (which you can tell by using SELECT @@TRANCOUNT;—if that does not return 0, there is an open transaction). 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 putting a mark in the transaction log, which can be used when restoring a transaction log, instead of trying to figure out the date and time when an operation occurred. 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 WideWorldImporters database (which replaced AdventureWorks  as the de facto standard example database for SQL Server 2016), I’ll demonstrate this capability.

We first set up the scenario by putting the WideWorldImporters database in full recovery model. The version I downloaded came with the SIMPLE recovery model.

USE Master;
GO
ALTER DATABASE WideWorldImporters
      SET RECOVERY FULL;

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

EXEC sp_addumpdevice ’disk’, ’TestWideWorldImporters ’,
                              ’C:	empWideWorldImporters.bak’;
EXEC sp_addumpdevice ’disk’, ’TestWideWorldImportersLog’,
                              ’C:	empWideWorldImportersLog.bak’;

SELECT  recovery_model_desc
FROM    sys.databases
WHERE   name = ’WideWorldImporters’;

EXEC sys.sp_dropdevice @logicalname = ’<name>’;

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

BACKUP DATABASE WideWorldImporters TO TestWideWorldImporters;

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

USE WideWorldImporters;
GO
SELECT COUNT(*)
FROM   Sales.SpecialDeals;
BEGIN TRANSACTION Test WITH MARK ’Test’;
DELETE Sales.SpecialDeals;
COMMIT TRANSACTION;

This returns 2 for the original amount of SpecialDeals rows. Run the SELECT statement again, and it will return 0. Next, back up the transaction log to the other backup device:

BACKUP LOG WideWorldImporters to TestWideWorldImportersLog;

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 WideWorldImporters FROM TestWideWorldImporters
                                                WITH REPLACE, NORECOVERY;
RESTORE LOG WideWorldImporters FROM TestWideWorldImportersLog
                                                WITH STOPBEFOREMARK = ’Test’, RECOVERY;

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

USE WideWorldImporters;
GO
SELECT COUNT(*)
FROM   Sales.SpecialDeals;

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.

Nested Transactions

Every time I hear the term “nested” transactions, I envision Marlin Perkins in some exotic locale about to tell us the mating habits of transactions, but you already know that is not what this is about (and unless you are of a certain age, have no idea who that is anyhow). I am referring to starting a transaction after another transaction has already been started. You can nest the starting of transactions like the following, allowing code to call other code that also starts a transaction:

BEGIN TRANSACTION;
    BEGIN TRANSACTION;
       BEGIN TRANSACTION;

In the engine, 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, use the @@TRANCOUNT global variable as previously mentioned. 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. If you have an actual use case, I will buy you lunch at the PASS Summit or any SQL Saturday!)

As an example, execute the following:

SELECT @@TRANCOUNT AS zeroDeep;
BEGIN TRANSACTION;
SELECT @@TRANCOUNT AS oneDeep;

It returns the following results:

zeroDeep
-----------
0
oneDeep
-----------
1

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 previous transaction started with BEGIN TRANSACTION
SELECT @@TRANCOUNT AS oneDeep;

This returns the following results:

twoDeep
-----------
2
oneDeep
-----------
1

Finally, close the final transaction:

COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS zeroDeep;

This returns the following result:

zeroDeep
-----------
0

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 ends 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:

InTran
-----------
7
OutTran
-----------
0

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, or anytime a transaction has not been started, an error will occur:

SELECT @@TRANCOUNT;
COMMIT TRANSACTION;

This will return

-----------
0
Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Autonomous Transactions

The concept of an autonomous transaction is that a transaction can occur within another transaction and commit even if the external transaction does not. SQL Server does not have the ability to do a user-defined autonomous transaction, but there is one example of such a thing in SQL Server. Back in Chapter 6, I introduced the SEQUENCE object, as well as a column using the IDENTITY property. The transactions that these objects use operate are autonomous to the external transaction. So if you fetch a new SEQUENCE or IDENTITY value, but then ROLLBACK the external transaction, the value is lost.

As an example, consider the following SEQUENCE and TABLE objects (built in a database named Chapter11 in the downloads…and I too cringe at the name Chapter11!):

CREATE SCHEMA Magic;
GO
CREATE SEQUENCE Magic.Trick_SEQUENCE AS int START WITH 1;
GO
CREATE TABLE Magic.Trick
(
        TrickId int NOT NULL IDENTITY,
        Value int CONSTRAINT DFLTTrick_Value DEFAULT (NEXT VALUE FOR Magic.Trick_SEQUENCE)
)

Now every time you execute the following code, you will get one row back, with incrementing numbers. You could run it on hundreds of connections, and you would get back two numbers that are growing larger.

BEGIN TRANSACTION;
INSERT INTO Magic.Trick DEFAULT VALUES; --just use the default values from table
SELECT * FROM Magic.Trick;
ROLLBACK TRANSACTION;

And “poof!” the rows disappear due to the rollback. There would never be more than one row in the output, but the values change. There are two transactions occurring for the IDENTITY and SEQUENCE, and those are not rolled back. Only the INSERT operation. This is interesting because, as we will see later, when we look at isolation of connections, other connections will not need to wait to see if that value is used, because that autonomous transaction has been committed.

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: savepoints. Unfortunately, using savepoints requires forethought and a special syntax, which makes them slightly difficult to use without careful planning. 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, I will use the following table:

CREATE SCHEMA Arts;
GO
CREATE TABLE Arts.Performer
(
    PerformerId int IDENTITY CONSTRAINT PKPeformer PRIMARY KEY,
    Name varchar(100)
 );

Next I will insert two different performers, one I like, and one that should never have been included in my book:

BEGIN TRANSACTION;
INSERT INTO Arts.Performer(Name) VALUES (’Elvis Costello’);
SAVE TRANSACTION savePoint; --the savepoint name is case sensitive, even if instance is not
                            --if you do the same savepoint twice, the rollback is to latest
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 code is as follows:

performerId name
----------- --------------------------
1           Elvis Costello

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 use 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 when the transaction is enlisted into a distributed transaction.

Distributed Transactions

It would be wrong not to at least bring up the subject of distributed transactions. Occasionally, you might need to view or update data on a server that’s different from the one on which your code resides. The Microsoft Distributed Transaction Coordinator (MS DTC) service 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 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.

Transaction State

There are three states that a transaction can be in, which can be detected by the return value from the XACT_STATE() system function:

• 1 (Active Transaction): A transaction has been started. No indication of how many levels of transaction have been nested are provided, just that there is an active transaction. As mentioned earlier, use @@TRANCOUNT for nested transaction information.
• 0 (No Active Transactionerror handlers): The connection is not currently in the context of a transaction
• -1 (Uncommittable Transaction): Also known as a “doomed” transaction, something has occurred that makes the transaction still active but no longer able to be committed.

The first two states are just as we have discussed earlier, but the uncommittable transaction is the special case that is most commonly associated with error handling and XACT_ABORT or triggers. If you hit an error in a TRY CATCH block while using XACT_ABORT or a trigger returns an error without rolling back, you can end up in an uncommittable state. As an example, let’s create the following table:

CREATE SCHEMA Menu;
GO
CREATE TABLE Menu.FoodItem
(
    FoodItemId int NOT NULL IDENTITY(1,1)
        CONSTRAINT PKFoodItem PRIMARY KEY,
    Name varchar(30) NOT NULL
        CONSTRAINT AKFoodItem_Name UNIQUE,
    Description varchar(60) NOT NULL,
        CONSTRAINT CHKFoodItem_Name CHECK (LEN(Name) > 0),
        CONSTRAINT CHKFoodItem_Description CHECK (LEN(Description) > 0)
);

The constraints make certain that the length of the string columns is > 0. Now create a trigger that prevents someone from inserting ‘Yucky’ food in our database. Clearly this is not a complete solution, but it will cause the trigger to not let the insert occur.

CREATE TRIGGER Menu.FoodItem$InsertTrigger
ON Menu.FoodItem
AFTER INSERT
AS --Note, minimalist code for demo. Chapter 7 and Appendix B
   --have more details on complete trigger writing
BEGIN
   BEGIN TRY
                IF EXISTS (SELECT *
                                        FROM Inserted
                                        WHERE Description LIKE ’%Yucky%’)
        THROW 50000, ’No ’’yucky’’ food desired here’,1;
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
          ROLLBACK TRANSACTION;
       THROW;
   END CATCH;
END
GO

In this initial version of the trigger, we do roll back the transaction. Later in the section we will remove the error handling from the trigger, as well as the ROLLBACK to see what occurs. To show how this works, we will use XACT_ABORT, which without error handling will stop the batch. It is a very useful tool when writing maintenance scripts.

SET XACT_ABORT ON;  
BEGIN TRY  
    BEGIN TRANSACTION;  
        --insert the row to be tested
        INSERT INTO Menu.FoodItem(Name, Description)
        VALUES (’Hot Chicken’,’Nashville specialty, super spicy’);
        SELECT  XACT_STATE() AS [XACT_STATE], ’Success, commit’  AS Description;
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
        IF XACT_STATE() = -1 --transaction not doomed, but open
          BEGIN
                SELECT -1 AS [XACT_STATE], ’Doomed transaction’  AS Description;
                ROLLBACK TRANSACTION;
          END
        ELSE IF XACT_STATE() = 0 --transaction not doomed, but open
          BEGIN
                SELECT 0 AS [XACT_STATE], ’No Transaction’  AS Description;;
          END  
        ELSE IF XACT_STATE() = 1 --transaction still active
          BEGIN
                SELECT 1 AS [XACT_STATE],
                       ’Transction Still Active After Error’  AS Description;
                ROLLBACK TRANSACTION;
          END  
END CATCH;  

Since this insert met the requirements of all constraints, it succeeds:

XACT_STATE Description
---------- ---------------
1          Success, commit

Next, we will use the following insert (wrapped with the error handler from above) with an empty string for the description because, honestly, no words could adequately describe what that food probably would taste like:

INSERT INTO Menu.FoodItem(Name, Description)
VALUES (’Ethiopian Mexican Vegan Fusion’,’’);

Because there was a constraint violated, the transaction is doomed, so we get the following:

XACT_STATE  Description
----------- ------------------
-1          Doomed transaction

When the trigger does a ROLLBACK because of the string “yucky” being found

INSERT INTO Menu.FoodItem(Name, Description)
VALUES (’Vegan Cheese’,’Yucky imitation for the real thing’);

we come out of the INSERT with no transaction:

XACT_STATE Description
---------- ---------------
0          No Transaction

Finally, the last scenario I will show is the case where a trigger just returns an error, and does not roll back:

ALTER TRIGGER Menu.FoodItem$InsertTrigger
ON Menu.FoodItem
AFTER INSERT
AS --Note, minimalist code for demo. Chapter 7 and Appendix B
   --have more details on complete trigger writing
BEGIN
                IF EXISTS (SELECT *
                                        FROM Inserted
                                        WHERE Description LIKE ’%Yucky%’)
        THROW 50000, ’No ’’yucky’’ food desired here’,1;
END;

Now executing our batch with XACT_ABORT either ON or OFF returns

XACT_STATE  Description
----------- ------------------
-1          Doomed transaction

Wow, so many different outcomes! When building error handlers for your code, you need to either make sure that every possible outcome is handled or do a very good job of standardizing code in triggers, constraints, etc. to work only in one desired manner.

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, CREATE TABLE, ALTER INDEX, index reorganizations, etc.). 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; this is known as an autocommit transaction, as well as an implicit transaction. The SQL Server engine commits the transactions it starts for each statement-level transaction automatically, known as an autocommit.

This is not the default behavior for some RDBMSs, so SQL Server gives us a setting to change this behavior: SET IMPLICIT_TRANSACTIONS. When this setting is turned on and the execution context isn’t already within a transaction, BEGIN TRANSACTION is automatically (logically) executed when any of the following statements are executed: INSERT, UPDATE, DELETE, SELECT (when it touches a table), TRUNCATE TABLE, DROP, ALTER TABLE, REVOKE, CREATE, GRANT, FETCH, or OPEN. This means 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 and 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.

SQL Server Concurrency Methods

Until SQL Server 2005, there was only one concurrency method implemented in SQL Server. This was the pessimistic concurrency mechanism using locks. If a user had control of a row, a lock was placed, and all users who wanted incompatible use of the row (like two people can read a rows simultaneously, but only one could change the row, and readers have to wait, for example) were forced to wait. In 2005, a small taste of optimistic concurrency mechanisms was incorporated into the engine. Previous versions of data were written to tempdb to allow, when requested, a connection that would have been blocked from reading a row to see a version of the row that had been committed to be read. Writers could still block other users, and locks were still employed in all cases.

In SQL Server 2014, the new in-memory OLTP engine was implemented, which uses a complete optimistic concurrency mechanism, employing versions of rows rather than locks to manage concurrency. It is very different in many ways to what we have known of the engine for the past 20 years, which in most cases is a great thing. It is just different enough that one needs to understand what is going on first.

In this section I will introduce the settings that define how much one transaction can affect another, known as isolation levels, then I will explain and demonstrate how these work in pessimistic and optimistic concurrency enforcement.

What is interesting is that one transaction can mix different levels of concurrency implementation and locks. This will be particularly true when transactions enlist on-disk and in-memory OLTP tables, but can be true of any combination of table access, even within the same query by using isolation level hints.

Isolation Levels

In this section, we will identify, at an abstract level, how one transaction might affect another’s view of the world. Of course, the safest method to provide consistency in operations would be to take exclusive access to the entire database, do your operations, and then release control. Then the next user would do the same thing. Although this was relatively common in early file-based systems, it isn’t a reasonable alternative when you need to support 20,000 concurrent users.

Isolation levels generally control how isolated one connection is from another, in terms of a few phenomenas that can occur. Take for instance, this query on a given connection:

BEGIN TRANSACTION;
UPDATE tableA
SET status = ’UPDATED’
WHERE tableAId = ’value’;

On another connection, we have

BEGIN TRANSACTION;
INSERT tableA (tableAID, Status)
VALUES (100,’NEW’);

Finally, we have

BEGIN TRANSACTION;
SELECT *
FROM   tableA;

Consider these are all executing simultaneously. They are all inside of transactions that say that they are not yet committed durably to the data structures. Will the SELECT statement see the new row? The changed row? The answer is “it depends.” There are four specific phenomena that we need to deal with, and part of the answer depends on which query commits first, or rolls back.

The primary phenomena we are concerned with are

• Dirty reads: Seeing data that never may actually exist in a specific form. For example, when a second transaction selects a row that is in the process of being updated by a first transaction, the second transaction is reading data that has not been committed yet and may or may not be changed by the first transaction.
• Phantom rows: Seeing new rows in the results. Basically, when data is fetched once, the next time we fetch rows, any new rows are considered phantom rows.
• Nonrepeatable reads: Occur when data has been read in during a transaction, and when re-read the data has changed or has been deleted.
• Expired rows: Seeing data that no longer exists in the same form, but was consistent at a point in time.

While you can’t specify at a given level “I will accept dirty reads, but not expired rows,” there are five isolation levels that define an accepted set of phenomena for the different phenomena. The following list describes the isolation levels to adjust how one connection can affect another:

• READ UNCOMMITTED: Allows dirty reads, phantom rows, and nonrepeatable reads. Shows you data as it is, even if not fully committed, and as such it could change.
• READ COMMITTED: Allows phantom rows and nonrepeatable reads. So the same query could return different results in the same transaction, but you will not see uncommitted data.
• REPEATABLE READ: Allows phantom rows. New data is allowed to be added to the view of the query, but no data changes.
• SERIALIZABLE: Like REPEATABLE READ, but no new rows can come into view of the query either. Like the name suggests, you are serializing access through the use of this data for the purpose of changing the data.
• SNAPSHOT: Lets you see expired rows when changes have been made during your transaction. Your view of the database will be constant, but things may have changed before you complete your transaction.

The syntax for setting the isolation level is as follows:

SET TRANSACTION ISOLATION LEVEL <level>;

<level> is any of the five preceding settings. The default isolation level for a typical SQL Server query is READ COMMITTED and is a good balance between concurrency and integrity. It does bear mentioning that READ COMMITTED isn’t always the proper setting. Quite often, when only reading data, the SNAPSHOT isolation level gives the best results due to giving you a consistent view of data. For example, say you are using the READ COMMITTED isolation level you read in all of the invoices, and before you have finished, invoices are deleted. You are reading in the invoice line items, and things don’t quite match up. This scenario happens more often in things like ETL, but anything that easily happens in a large scale may still happen in a micro scale. If using the SNAPSHOT isolation level, you will see the data in a consistent state, even if it changes while you are reading it in.

When considering solutions, you must keep in mind locking and isolation levels. As more and more critical solutions are being built on SQL Server, it’s imperative to make absolutely sure to protect data at a level that’s commensurate with the value of the data. If you are building procedures to support a system on a space shuttle or a life support system, this is generally more important than it would be in the case of a sales system, a pediatrician’s schedule, or, like we set up in Chapter 6, a simple messaging system.

In some cases, losing some data really doesn’t matter. It is up to you when you are designing your system to truly understand that particular system’s needs.

When you are coding or testing, checking to see what isolation level you are currently executing under can be useful. To do this, you can look at the results from sys.dm_exec_sessions:

SELECT  CASE transaction_isolation_level
            WHEN 1 THEN ’Read Uncomitted’      WHEN 2 THEN ’Read Committed’
            WHEN 3 THEN ’Repeatable Read’      WHEN 4 THEN ’Serializable’
            WHEN 5 THEN ’Snapshot’             ELSE ’Something is afoot’
         END
FROM    sys.dm_exec_sessions
WHERE  session_id = @@spid;

Unless you have already changed it, the default isolation level (and what you should get from executing this query in your connection) is READ COMMITTED. Change the isolation level to SERIALIZABLE like so:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Then, reexecute the query, and the results will now show that the isolation level is currently SERIALIZABLE. In the following sections, I will show you why you would want to change the isolation level at all.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The most common illustration of why transaction isolation is needed is called the lost update, as illustrated in Figure 11-1.

The scenario in Figure 11-1 has two concurrent users. Each of these executes some SQL statements adding money to the balance, but in the end, the final value is going to be the wrong value, and that is not going to make anyone happy. Why? Because each user fetched a reality from the database that was correct at the time and then acted on it as if it would always be true. In the following two sections, we will look at the two major paradigms for isolation connections.

Pessimistic Concurrency Enforcement

Pessimistic concurrency enforcement is done by taking control of a resource when using it. Sometimes the control is exclusive, like when writing data, or shared, when simply viewing data. This is achieved by using markers known as locks. Locks are tokens laid down by the SQL Server processes to stake their claims to the different resources available, so as to prevent one process from stomping on another and causing inconsistencies or prevent another process from seeing data that has not yet been verified by constraints or triggers. How long the locks are held will be controlled by what the connection is doing as well as the isolation level.

Every SQL Server process using an on-disk table applies locks to anything it is doing to ensure that that other user processes know what they are doing as well as what they are planning to do and to ensure that other processes don’t get in its way. Minimally, a lock is always placed just to make sure that the database that is in use cannot be dropped.

Locks act as a message to other processes that a resource is being used, or at least probably being used. Think of a railroad-crossing sign. When the bar crosses the road, it acts as a lock to tell you not to drive across the tracks because the train is going to use the resource. Even if the train stops and never reaches the road, the bar comes down, and the lights flash. This lock can be ignored (as can SQL Server locks), but it’s generally not advisable to do so, because if the train does come, your next trip to Disney World may be exclusively to the Haunted Mansion. (Ignoring locks isn’t usually as messy as ignoring a train-crossing signal, but you could be creating the system that controls that warning signal.)

In this section, we will look at a few characteristics of locks:

• Type of lock: Indicates what is being locked
• Mode of lock: Indicates how strong the lock is

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 when a process is reading some data. 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 several 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 SQL Server uses each of them quite often. The list of locks types in Table 11-1 is not complete, but is a sample of the common types of locks we will encounter in modifying data in tables.

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 based on the isolation level. 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 much to lock the resource. For example, consider a construction site. Workers are generally allowed onto the site, but civilians who are not part of the process not permitted. 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 type/granularity, describes a locking situation. For example, an exclusive table lock would generally 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 that any additional users simply 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’s pessimistic concurrency 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 sections, I’ll demonstrate the locking in each of the isolation levels, which in pessimistic schemes determines how long locks are held, depending on the protection that the isolation level affords. 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.

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:

• PagLock: Force 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).

Before moving to the examples, an important term that 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:

Msg 1205, Level 13, State 51, Line 242
Transaction (Process ID ??) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

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, and if done correctly, will help you with dealing with optimistic concurrency issues).

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 reality 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 with concurrent connections, 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 sounds simply horrible to users: “chosen as deadlock victim”!).

The SNAPSHOT isolation level does not pertain to pessimistic concurrency controls, as it lets you see previous versions of data with the expectation that nothing will change. Readers don’t block readers, so the examples will generally be along the lines of “make a change to the state of the database in an open transaction, then see what another connection can see when executing a statement of some sort in another connection.”

Isolation Levels and Locking

In the following sections, I will demonstrate how locks are taken and honored in the following isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

In the next sections on pessimistic and optimistic concurrency enforcement, I’ll briefly discuss the different isolation levels and demonstrate how they work using the following tables (and alternate versions using in-memory OLTP):

CREATE SCHEMA Art;
GO
CREATE TABLE Art.Artist
(
    ArtistId int CONSTRAINT PKArtist PRIMARY KEY
    ,Name varchar(30) --no key on value for demo purposes
    ,Padding char(4000) default (replicate(’a’,4000)) --so all rows not on single page
);
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (1,’da Vinci’),(2,’Micheangelo’), (3,’Donatello’),
       (4,’Picasso’),(5,’Dali’), (6,’Jones’);  
GO
CREATE TABLE Art.ArtWork
(
    ArtWorkId int CONSTRAINT PKArtWork PRIMARY KEY
    ,ArtistId int NOT NULL
           CONSTRAINT FKArtwork$wasDoneBy$Art_Artist REFERENCES Art.Artist (ArtistId)
    ,Name varchar(30)
    ,Padding char(4000) default (replicate(’a’,4000)) --so all rows not on single page
    ,CONSTRAINT AKArtwork UNIQUE (ArtistId, Name)
);
INSERT Art.Artwork (ArtworkId, ArtistId, Name)
VALUES (1,1,’Last Supper’),(2,1,’Mona Lisa’),(3,6,’Rabbit Fire’);

READ UNCOMMITTED

A connection in READ UNCOMMITTED does not take locks on resources used (other than the database it is in), and does not honor locks by other connections other than metadata locks, such as a table that is in the process of being created. As mentioned, you still get locks on any data you modify, which is true of all isolation levels.

READ UNCOMMITTED is a great tool when you need to see the state of a data operation in-flight. Like if a transaction is loading a million rows into a table, instead of waiting for the transaction to complete, you can see the changes to the table. A second use of READ UNCOMMITTED is when you are the admin user and need to look at a production resource without blocking others. For example, say we add the following row to the Art.Artist table, but we do not commit the transaction:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --this is the default, just
                                                --setting for emphasis
BEGIN TRANSACTION;
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (7, ’McCartney’);

Then on a different connection we execute

--CONNECTION B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT ArtistId, Name
FROM Art.Artist
WHERE Name = ’McCartney’;

This will sit and not complete, because the row has not been committed. However, if we change this to READ UNCOMMITTED:

--CONNECTION B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT ArtistId, Name
FROM Art.Artist
WHERE Name = ’McCartney’;

we will immediately get back:

ArtistId    Name
----------- ------------------------------
7           McCartney

The problem is, when the process on Connection A decides to rollback their connection:

--CONNECTION A
ROLLBACK TRANSACTION;

Go back and execute that statement again and it is gone. Whether this is a problem or not is a matter for what the first half of the book was about: requirements. Too often though, if some process were to uppercase all of the names in this table, and another user saw that and called the service desk, by the time they checked, all would back to normal. While this might be a fun way to gaslight your service desk, not good practice.

Being able to see locked data is quite valuable, especially when you’re in the middle of a long-running process, 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.

READ COMMITTED

READ COMMITTED is the default isolation level as far as SQL Server is concerned, and as the name states, it only 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 accessing the resource. (Hence, if you are using 1,000 resources, it may take one lock, use a resource, release the lock, and access the next resource.) 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. As the data architect, you need to answer the questions “What if data changes?” and see if this matters.

As an example, we will start a transaction, and then view data in the Art.Artist table:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT ArtistId, Name FROM Art.Artist WHERE ArtistId = 7;

No rows are returned. Locks are only taken on resources as they are actually being used, so if you could run the lock viewer fast enough, you would just see one or so locks at a time. Next, on a separate connection, we will add a new row to the table, and commit it (I demonstrated in the previous section that we would be blocked from viewing the row in READ COMMITTED, since the row was in a dirty state):

--CONNECTION B
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (7, ’McCartney’);

Going back to Connection A, we reselect the data, still in the same transaction:

--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist WHERE ArtistId = 7;

It now returns:

ArtistId    Name
----------- ------------------------------
7           McCartney

Update the row on Connection B:

--CONNECTION B
UPDATE Art.Artist SET Name = ’Starr’ WHERE ArtistId = 7;

Then view and commit the transaction on the row again:

--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist WHERE ArtistId = 7;
COMMIT TRANSACTION;
ArtistId    Name
----------- ------------------------------
7           Starr

The bottom line is that this isolation level is great for most cases, particularly when you are doing a single query. However, as the number of statements increases, it becomes less desirable. For example, if part of your process had been to be to look to make sure that an artist named “McCartney” existed in the table. A transaction was started, to contain the operation, and the row was seen to exist. But after that the row changed to “Starr,” so your query to check for that row has been invalidated.

The other process may have done its own checks to make sure it could update the rows, which may have been prevented by some step in your process that has not yet completed. Which is to say this: concurrency can be complex, and it is very hard to test. The timing of the aforementioned scenario might be just microseconds, and it could never happen. It certainly is unlikely to occur on the developer’s workstation, and unless you do massive amounts of testing, it may not occur in your test system either. But like Murphy’s Law states: “Anything that can go wrong, will go wrong,” so you need to program defensively and consider “What if this happens?” testing using extra BEGIN TRANSACTION and often WAITFOR statements can be very helpful in slowing down time enough to cause collisions that would rarely if ever occur.

The next two isolation levels will be particularly useful in dealing with data integrity checks, as we can hold locks on the rows we have accessed longer than just to make sure they are not changed as we return them.

REPEATABLE READ

The REPEATABLE READ isolation level includes protection from data being changed or 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 are 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 ArtistId, Name FROM Art.Artist WHERE ArtistId >= 6;

This returns

ArtistId    Name
----------- ------------------------------
6           Jones
7           Starr

Then on another connection, execute the following:

--CONNECTION B
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (8, ’McCartney’);

If you go back to CONNECTION A and execute the SELECT again, you will see three rows. But if you try to delete the row with ArtistId = 6:

--CONNECTION B
DELETE Art.Artist
WHERE  ArtistId = 6;

you will find that you are blocked. Connection A holds a shared key lock on the row number 6 (and now 7 and 8 also since you have seen them), so that row cannot be deleted or updated, which would require an incompatible exclusive lock. Cancel the query on Connection B, and then COMMIT the transaction on Connection A to continue.

REPEATABLE READ isolation level is perfect for the cases where you want to make sure that some data still exists when your process has completed (like if you were implementing a foreign key–type check in code).

It is not ideal for the situation where you want to maintain a balance (what if a negative value is inserted?) or where you want to limit the cardinality of a relationship (new rows are fine). We will explore how to deal with that situation in the next section.

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 often 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 Art.Artist

For the duration of the execution of this query (and longer if in an explicit transaction), due to a range lock being taken on all keys in the table, no other user would be able to modify or add rows to the table until all rows have been returned and the transaction it was executing within has completed.

If lots of users are viewing data in the table in the SERIALIZABLE isolation level, 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. Execute this statement on a connection to simulate a user with a table locked:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT ArtistId, Name FROM Art.Artist;

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

--CONNECTION B
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (9, ’Vuurmann’); --Misspelled on purpose. Used in later example

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

--CONNECTION A
COMMIT TRANSACTION;
SELECT ArtistId, Name FROM Art.Artist;

This unblocks CONNECTION B, and you will see that the contents of the table are now the following:

ArtistId    Name
----------- ------------------------------
1           da Vinci
2           Micheangelo
3           Donatello
4           Picasso
5           Dali
6           Jones
7           Starr
8           McCartney
9           Vuurmann

It is important to be careful with the SERIALIZABLE isolation level. The name tells you that you will have single-threaded access to resources in this table, particularly when writing data to the table. I can’t stress enough that while multiple readers can read the same data, no one can change 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.

Interesting Cases

In this section I will present a few interesting cases that are more than simple isolation cases, including foreign keys and application locks that can help us implement a critical section of code.

Locking and Foreign Keys

One of the interesting cases when working with locking involves foreign keys. Consider our tables: Art.Artist and the child table, Art.Artwork. When you insert a new row into the Art.Artwork table, it must check the Art.Artist table to see if the key exists:

--CONNECTION A
BEGIN TRANSACTION;
INSERT INTO Art.ArtWork(ArtWorkId, ArtistId, Name)
VALUES (4,9,’Revolver Album Cover’);

Looking at the locks held, you will only see locks on the ArtWork table. So it looks like there is no guarantee of the row for ArtistId = 9 being deleted. This is true in terms of locked resources, but foreign keys protect both ways. So if on a different connection the following code is executed while this transaction is still open:

--CONNECTION B
DELETE FROM Art.Artist WHERE ArtistId = 9;

it will be blocked from executing because while there are no rows currently it needs to check is locked. So the new row will determine whether or not the artist can be deleted. On Connection A, execute

-- CONNECTION A
COMMIT TRANSACTION;

Then, Connection B will complete with a delete reference error, since it has child rows existing. However, to show that the Art.Artist row is not locked, you can update it:

--CONNECTION A
BEGIN TRANSACTION;
INSERT INTO Art.ArtWork(ArtWorkId, ArtistId, Name)
VALUES (5,9,’Liverpool Rascals’);

Now, on connection B execute

--CONNECTION B
UPDATE Art.Artist
SET  Name = ’Voorman’
WHERE artistId = 9;

It will succeed. Then back on Connection A, roll back the transaction, so that the new row is not added:

--CONNECTION A
ROLLBACK TRANSACTION;
SELECT * FROM Art.Artwork WHERE ArtistId = 9;

You will see just the one row returned. So what about cascading foreign keys? There are two different cases. If no child row exists, instead of taking no locks on the child table, an intent exclusive lock is placed on the table. This would keep anyone else from taking an exclusive lock on the table, but would otherwise allow usage. If a user tries to insert or update a row that uses the deleted key, the foreign key’s checking would bump up against the locked row just as before.

If rows do exist in the child to be deleted, they will be exclusively locked just as if you did a delete, and a range exclusive lock is set to prevent anyone from inserting a row that might match the parent. In the downloads, I alter the foreign key constraint and include a few deletes for seeing this occur.

Foreign keys do slow down some operations in the execution of database code, but fortunately, when using a locking scheme, that effect is not necessarily long felt, even when a row that references another table

Application Locks

SQL Server does have a built-in method you can use to implement a form of pessimistic locking: SQL Server application locks. The real downside is that enforcement and compliance are completely optional. If you write code that doesn’t follow the rules and use the proper application lock, you will get no error letting you know. However, for the cases where you need to single thread code for some reason, applocks are a great resource.

The commands that you have to work with application locks are as follows:

• sp_getAppLock: Use this to place a lock on an application resource. The programmer names application resources, which can be named with any string value. In the string, you could name single values or even a range.
• sp_releaseAppLock: Use this to release locks taken inside a transaction.
• APPLOCK_MODE: Use this to check the mode of the application lock.
• APPLOCK_TEST: Use this to see if you could take an application lock before starting the lock and getting blocked.

As an example, we’ll run the following code. We’ll implement this on a resource named ’invoiceId=1’, which represents a logical invoice object. We’ll set it as an exclusive lock so no other user can touch it. In one connection, we run the following code (note, applocks are database specific, so you will need to be in the same database on Connections A and B). By default, applocks are associated with a transaction, so you will need to be in an explicit transaction. You can specify a parameter of @LockOwner of Session and you will not need a transaction, but it will be around until you use sp_releaseAppLock or end the connection, which can be more difficult to manage.

--CONNECTION A
BEGIN TRANSACTION;
   DECLARE @result int;
   EXEC @result = sp_getapplock @Resource = ’invoiceId=1’, @LockMode = ’Exclusive’;
   SELECT @result;

This returns 0, stating that the lock was taken successfully. Other possible output is shown here:

Value       Result
----------- ------------------------------
          1 Lock granted after waiting for other locks
         -1 Timed out
         -2 Lock request cancelled
         -3 Deadlock victim
        999 Other error

You can see the type of applock taken by using APPLOCK_MODE():

SELECT APPLOCK_MODE(’public’,’invoiceId=1’);

This returns the mode the lock was taken in, in our case Exclusive. Now, if another user tries to execute the same code to take the same lock, the second process has to wait until the first user has finished with the resource ’invoiceId=1’:

--CONNECTION B
BEGIN TRANSACTION;
   DECLARE @result int;
   EXEC @result = sp_getapplock @Resource = ’invoiceId=1’, @LockMode = ’Exclusive’;
   SELECT @result;

This transaction has to wait. Let’s cancel the execution, and then execute the following code using the APPLOCK_TEST() function (which has to be executed in a transaction context) to see if we can take the lock (allowing the application to check before taking the lock):

--CONNECTION B
BEGIN TRANSACTION;
SELECT  APPLOCK_TEST(’public’,’invoiceId=1’,’Exclusive’,’Transaction’) as CanTakeLock
ROLLBACK TRANSACTION;

This returns 0, meaning we cannot take this lock currently. The other output is 1, indicating the lock could be granted. APPLOCKs can be a great resource for building locks that are needed to implement locks that are “larger” than just SQL Server objects. In the next section, I will show you a very common and useful technique using APPLOCKs to create a pessimistic lock based on the application lock to single thread access to a given block of code.

Very often, it is troublesome for more than one connection to have access to a given section of code. For example, you might need to fetch a value, increment it, and keep the result unique among other callers that could be calling simultaneously. The general solution to the single-threading problem is to exclusively lock the resources that you need to be able to work with, forcing all other users to wait even for reading. In some cases, this technique will work great, but it can be troublesome in cases like the following:

• The code is part of a larger set of code that may have other code locked in a transaction, blocking users’ access to more than you expect. You are allowed to release the application lock in the transaction to allow other callers to continue.
• Only one minor section of code needs to be single threaded, and you can allow simultaneous access otherwise.
• The speed in which the data is accessed is so fast that two processes are likely to fetch the same data within microseconds of each other.
• The single threading is not for table access. For example, you may want to write to a file of some sort or use some other resource that is not table based.

The following technique will leave the tables unlocked while manually single threading access to a code block (in this case, getting and setting a value), using an application lock to lock a section of code.

To demonstrate a very common problem of building a unique value without using identities (for example, if you have to create an account number with special formatting/processing), I have created the following table:

CREATE SCHEMA Demo;
GO
CREATE TABLE Demo.Applock
(
    ApplockId int CONSTRAINT PKApplock PRIMARY KEY,  
                                --the value that we will be generating
                                --with the procedure
    ConnectionId int,           --holds the spid of the connection so you can
                                --who creates the row
    InsertTime datetime2(3) DEFAULT (SYSDATETIME()) --the time the row was created, so
                                                    --you can see the progression
);

Next, a procedure that starts an application lock fetches some data from the table, increments the value, and stores it in a variable. I added a delay parameter, so you can tune up the problems by making the delay between incrementing and inserting more pronounced. There is also a parameter to turn on and off the application lock (noted as @useApplockFlag in the parameters), and that parameter will help you test to see how it behaves with and without the application lock.

CREATE PROCEDURE Demo.Applock$test
(
    @ConnectionId int,
    @UseApplockFlag bit = 1,
    @StepDelay varchar(10) = ’00:00:00’
) AS
SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        DECLARE @retval int = 1;
        IF @UseApplockFlag = 1 --turns on and off the applock for testing
            BEGIN
                EXEC @retval = sp_getAppLock @Resource = ’applock$test’,
                                                    @LockMode = ’exclusive’;
                IF @retval < 0
                    BEGIN
                        DECLARE @errorMessage nvarchar(200);
                        SET @errorMessage =
                                CASE @retval
                                    WHEN -1 THEN ’Applock request timed out.’
                                    WHEN -2 THEN ’Applock request canceled.’
                                    WHEN -3 THEN ’Applock involved in deadlock’
                                    ELSE ’Parameter validation or other call error.’
                                END;
                        THROW 50000,@errorMessage,16;
                    END;
            END;
    --get the next primary key value. Reality case is a far more complex number generator
    --that couldn’t be done with a sequence or identity
    DECLARE @ApplockId int;   
    SET @ApplockId = COALESCE((SELECT MAX(ApplockId) FROM Demo.Applock),0) + 1;
    --delay for parameterized amount of time to slow down operations
    --and guarantee concurrency problems
    WAITFOR DELAY @stepDelay;
    --insert the next value
    INSERT INTO Demo.Applock(ApplockId, connectionId)
    VALUES (@ApplockId, @ConnectionId);
    --won’t have much effect on this code, since the row will now be
    --exclusively locked, and the max will need to see the new row to
    --be of any effect.
    IF @useApplockFlag = 1 --turns on and off the applock for testing
        EXEC @retval = sp_releaseApplock @Resource = ’applock$test’;
    --this releases the applock too
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    --if there is an error, roll back and display it.
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    SELECT CAST(ERROR_NUMBER() as varchar(10)) + ’:’ + ERROR_MESSAGE();
END CATCH;

Now, you can set up a few connections using this stored procedure, attempting multiple connections first without the application lock and then with it. Since we’re running the procedure in such a tight loop, it is not surprising that two connections will often get the same value and try to insert new rows using that value when not using the APPLOCK:

--test on multiple connections
WAITFOR TIME ’21:47’;  --set for a time to run so multiple batches
                       --can simultaneously execute
go
EXEC Demo.Applock$test @connectionId = @@spid
              ,@useApplockFlag = 0 -- <1=use applock, 0 = don’t use applock>
              ,@stepDelay = ’00:00:00.001’--’delay in hours:minutes:seconds.parts of seconds’;
GO 10000 --runs the batch 10000 times in SSMS

You will probably be amazed at how many clashes you get when you have application locks turned off. Doing 10,000 iterations of this procedure on three connections on a Surface Pro 4, i7, in an 8GB VM with 0 for the APPLOCK parameter, I got over 1,000 clashes pretty much constantly (evidenced by an error message: 2627:Violation of PRIMARY KEY constraint…Cannot insert duplicate key in object ’dbo.applock’…). With application locks turned on, all rows were inserted in slightly more time than the original time, without any clashes whatsoever.

To solidify the point that every connection has to follow the rules, turn off application locks on only a connection or two and see the havoc that will result. The critical section will now no longer be honored, and you will get tons of clashes quickly, especially if you use any delay.

This is not the only method of implementing the solution to the incrementing values problem. Another common method is to change the code where you get the maximum value to increment and apply locking hints:

SET @applockId =
      COALESCE((SELECT MAX(applockId)
                FROM APPLOCK WITH (UPDLOCK,PAGLOCK)),0) + 1;

Changing the code to do this will cause update locks to be held because of the UPDLOCK hint, and the PAGLOCK hint causes page locks to be held (SQL Server can ignore locks when a row is locked and it has not been modified, even if the row is exclusively locked).

The solution I presented is a very generic one for single threading a code segment in T-SQL code, allowing that the one procedure is the only one single threading. It does not take any locks that will block others until it needs to update the data (if there is no changing of data, it won’t block any other users, ever). This works great for a hotspot where you can clearly cordon off the things being utilized at a given level, like in this example, where all users of this procedure are getting the maximum of the same rows.

Optimistic Concurrency Enforcement

Whereas pessimistic concurrency is based on holding locks on resources, expecting multiple users utilizing resources for noncompatible reasons, optimistic concurrency is just the opposite. In optimistic concurrency control, we expect no overlap in use and optimize accordingly, giving users access to previous versions of resources that have been or are being modified. The outcome is a tremendous reduction in most waiting times, though it can be costlier when concurrency collisions do occur.

Starting with SQL Server 2005, there was one model of optimistic concurrency enforcement in use that was comingled with the locking we saw in the pessimistic locking scheme. This partial implementation did amazing things for performance, but still makes use of locks to deal with write contention. As such, it was not really used tremendously in total, but there was one feature that can do wonders to your lock contention issues.

In SQL Server 2014, Microsoft added the in-memory OLTP engine, which has a completely different concurrency model called Multi-Value Concurrency Control (MVCC), which is based completely on versions with no locks involved, other than locks purely for schema stability purposes. In Chapter 10, the structural differences were enumerated, but in this chapter we will see them in use.

In the following sections, I will cover the on-disk implementation of optimistic concurrency, followed by coverage of the in-memory OLTP engine considerations for concurrency.

Optimistic Concurrency Enforcement in On-Disk Tables

There are two main topics to look at when it comes to on-disk optimistic concurrency enforcement. The first is SNAPSHOT isolation level, which affects your connection at a transaction level, and the database setting READ COMMITTED SNAPSHOT, which affects your queries at a statement level (much like READ COMMITTED does now, you can get different results on different executions in the same transaction). For SNAPSHOT isolation level, two executions of the same query will return the same results, unless you change the table in your connection (which can lead to issues, as we will see).

SNAPSHOT Isolation Level

SNAPSHOT isolation lets you read the data as it was when the transaction started, regardless of any changes. No matter how much the data changes in the “real” world, your view stays the same. This makes it impossible to do things like check to see if a row exists in another table, since you will not be able to see it in your transaction.

The largest downside is the effect SNAPSHOT isolation can have on performance if you are not prepared for it. This history data for on-disk tables 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 a 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 to view corporate performance in the middle of the busiest part of the day, that query will never be blocked, and no other users will get stuck behind it either. The better news is that there will be no cases where a mistaken $10 million entry that one of the data-entry clerks added to the data that the check constraint/trigger 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 Chapter11
     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 Art.Artist table we used back in the “Pessimistic Concurrency Enforcement” section:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT ArtistId, Name FROM Art.Artist;

This returns the following results:

ArtistId    Name
----------- ------------------------------
1           da Vinci
2           Micheangelo
3           Donatello
4           Picasso
5           Dali
6           Jones
7           Starr
8           McCartney
9           Vuurmann

On a second connection, run the following:

--CONNECTION B
INSERT INTO Art.Artist(ArtistId, Name)
VALUES (10, ’Disney’);

This executes with no waiting. Going back to Connection A, without ending the transaction and 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 Art.Artist
WHERE  ArtistId = 3;

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

--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist;

This still returns the same nine rows. Commit the transaction, and check the differences.

ArtistId    Name
----------- ------------------------------
1           da Vinci
2           Micheangelo
4           Picasso
5           Dali
6           Jones
7           Starr
8           McCartney
9           Voormann
10          Disney

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

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
UPDATE Art.Artist
SET    Name = ’Duh Vinci’
WHERE  ArtistId = 1;
ROLLBACK;

But, if you have two connections competing and fetching/modifying the same row, you may get blocking. For example, say connection B has a lock from a transaction in a non-SNAPSHOT isolation level:

--CONNECTION B
BEGIN TRANSACTION
UPDATE Art.Artist
SET    Name = ’Dah Vinci’
WHERE  ArtistId = 1;

Then connection A updates it:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
UPDATE Art.Artist
SET    Name = ’Duh Vinci’
WHERE  ArtistId = 1;

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 the SNAPSHOT isolation level. When making changes to data in the SNAPSHOT isolation level, the changes still take locks, which will block any non-SNAPSHOT isolation transaction connection just like before. However, what is very different is how it deals with two SNAPSHOT isolation level connections modifying the same resource. There are two cases. First, if the row has not been yet cached, you can get blocked. For example, (after rolling back the data changes on both connections), start a new transaction, and update ArtistId 1 to Duh Vinci:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
UPDATE Art.Artist
SET    Name = ’Duh Vinci’
WHERE  ArtistId = 1;

Then, a second user changes the value, to Dah Vinci:

--CONNECTION B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE Art.Artist
SET    Name = ’Dah Vinci’
WHERE  ArtistId = 1;

You will get blocked, something that is not common to optimistic locking schemes. In this next (quite typical) case (again, after rolling back the transactions from the previous example), connection A caches the row, connection B updates, and connection A tries to update it also:

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT *
FROM   Art.Artist;

Then execute the following on Connection B, to change the data. It is not in an explicit transaction.

--CONNECTION B
UPDATE Art.Artist
SET    Name = ’Dah Vinci’
WHERE  ArtistId = 1;

Now when you try to update the row that you think exists:

--CONNECTION A
UPDATE Art.Artist
SET    Name = ’Duh Vinci’
WHERE  ArtistId = 1;

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

Msg 3960, Level 16, State 3, Line 586
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ’Art.Artist’ directly or indirectly in database ’Chapter11’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

For the most part, due to the way things work with SNAPSHOT intermingled with the pessimistic concurrency engine, it can be complex (or perhaps annoying is a better term) to work with. There is a key phrase in the error message: “retry the transaction.” This was a theme from deadlocks, and will be a major theme in the in-memory implementation of MVCC.

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. It is a very powerful tool for performance tuning, but it should be noted that it changes the way the entire database processes READ COMMITTED transactions, and because data may be changing, it will behoove you to think deeply about how the code you write to check data integrity may be affected by changes that are in flight when you do your checks.

The important part to understand is that this works on a “statement” level and not a “transaction” level. 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
ALTER DATABASE Chapter11
    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 wait for any in-process 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).

For places where you might need more safety, use 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.

As an example, after setting the database setting, on Connection B we will add a new row in a transaction, but not commit it. On Connection A, we will search for the rows of the table.

--CONNECTION A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT ArtistId, Name FROM Art.Artist;

This returns the table as we had in the last section:

ArtistId    Name
----------- ------------------------------
1           da Vinci
2           Micheangelo
4           Picasso
5           Dali
6           Jones
7           Starr
8           McCartney
9           Voormann
10          Disney
--CONNECTION B
BEGIN TRANSACTION;
INSERT INTO Art.Artist (ArtistId, Name)
VALUES  (11, ’Freling’ )
--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist;

Next, we update all of the rows, including the one we just added:

--CONNECTION B (still in a transaction)
UPDATE Art.Artist
SET  Name = UPPER(Name);
--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist;

This still returns the same data. We could delete all of the rows, and nothing would change. Commit the transaction on B, then go back and look at the data on Connection A:

--CONNECTION B
COMMIT;
--CONNECTION A
SELECT ArtistId, Name FROM Art.Artist;
COMMIT;

We see the changes in the data, then commit the transaction so we don’t mess up the next demos.

ArtistId    Name
----------- ------------------------------
1           DAH VINCI
2           MICHEANGELO
4           PICASSO
5           DALI
6           JONES
7           STARR
8           MCCARTNEY
9           VOORMANN
10          DISNEY
11          FRELING

Optimistic Concurrency Enforcement in In-Memory OLTP Tables

In the previous chapter, I discussed the differences between the on-disk and in-memory physical structures. In this chapter, we will take this farther and look at how code will work. While the in-memory OLTP tables are generally meant to work from the same code as their on-disk cousins, the internals, especially as it deals with concurrency, is quite different.

For an example, consider a table that has an identity column and one that holds the country. When we first start the server, and the in-memory columns are loaded, we have the structures like the following at timestamp 0:

TableNameId Country               OtherColumns...
----------- --------------------- ----------------------------
1           USA                   Values
2           USA                   Values
3           Canada                Values

Next, CONNECTION A starts a transaction at timestamp 50. CONNECTION B starts a transaction and updates the row with TableNameId = 2 to have the Country = ’Canada’. As we discussed in Chapter 10, there can only be the one “dirty” row, so no other user could try to update that same row while CONNECTION B is in the process. At timestamp 100, CONNECTION B commits their transaction, but CONNECTION A’s connection to the timestamp 50 version is still in use. So the live table looks like:

TableNameId Country               OtherColumns...
----------- --------------------- ----------------------------
1           USA                   Values
2           Canada                Values
3           Canada                Values

Internally, there are four rows, as both views of the table that we have seen so far can been viewed by one or more users. So we have the basic structure shown in Figure 11-2.

Now if the user searches on CONNECTION A, still stuck in timestamp 50 time, searching for Country = ’USA’ will return two rows, and after timestamp 100, it will return one row. In these structures, CONNECTION A will never be able to see what happens after timestamp 50, providing isolation that makes sure a user cannot change their view of the database. There are three isolation levels supported: SNAPSHOT, REPEATABLE READ, and SERIALIZABLE.

For reading data, this is done (for REPEATABLE READ, and SERIALIZABLE as SNAPSHOT allows any amount of change) at commit time to see if data has been modified or created that would change the view of the data in an incompatible way. So if a row was deleted, it would fail at COMMIT time with an error that says there has been an isolation level issue.

Not being able to see what has occurred on other connections means that you will likely need to use the isolation levels other than SNAPSHOT more frequently. For example, say you want to check that a row of a certain type exists, it is impossible to know if the row is deleted, or if one is inserted. However, unlike pessimistic locking scenarios, when employing optimistic versions, the impact on other connections is likely to be less, since you will not be preventing them from using their view of the data. It will make certain sorts of implementations very undesirable, however. For example, implementing a queue without locks could take lots of iterations in code, do all of the work, and then rolling back when you discover someone else has committed their changes.

For writing data, things get more interesting. As noted in Chapter 10, there is only one “dirty” version of a row. When two connections try to modify the same row, it is physical resource collision that gives an immediate error. If it is not a physical resource collision (for example, two connections insert duplicate values), the index pointers will not yet be set until COMMIT, when they will become a physical resource collision and you will get an error. In the following sections, I will demonstrate each of these cases to make it more clear.

A term that should be introduced here is cross container. The on-disk tables and in-memory table are in the same database, but in different containers. Once you access resources in one container or another it is part of a transaction. When you access resources in both containers (like accessing an in-memory table from interpreted T-SQL), it is considered cross container. So the transaction timestamp will start once you access an in-memory resource when running in interop mode (which all of the examples in this chapter will be).

For the examples, I will be using the same basic tables as we had in the previous sections on pessimistic concurrency control, but the schema will indicate that it is an in-memory OLTP object (note that this example only works on SQL Server 2016, and not the 2014 version of in-memory OLTP due to multiple uniqueness constraints and foreign keys):

--The download will include the code to add an in-memory filegroup
CREATE SCHEMA Art_InMem;
GO
CREATE TABLE Art_InMem.Artist
(
    ArtistId int CONSTRAINT PKArtist PRIMARY KEY  
                                       NONCLUSTERED HASH  WITH (BUCKET_COUNT=100)
    ,Name varchar(30) --no key on value for demo purposes, just like on-disk example
    ,Padding char(4000) --can’t use REPLICATE in in-memory OLTP, so will use in INSERT
) WITH ( MEMORY_OPTIMIZED = ON );
INSERT INTO Art_InMem.Artist(ArtistId, Name,Padding)
VALUES (1,’da Vinci’,REPLICATE(’a’,4000)),(2,’Micheangelo’,REPLICATE(’a’,4000)),
       (3,’Donatello’,REPLICATE(’a’,4000)),(4,’Picasso’,REPLICATE(’a’,4000)),
           (5,’Dali’,REPLICATE(’a’,4000)), (6,’Jones’,REPLICATE(’a’,4000));     
GO
CREATE TABLE Art_InMem.ArtWork
(
    ArtWorkId int CONSTRAINT PKArtWork PRIMARY KEY
                                         NONCLUSTERED HASH  WITH (BUCKET_COUNT=100)
    ,ArtistId int NOT NULL
        CONSTRAINT FKArtwork$wasDoneBy$Art_Artist REFERENCES Art_InMem.Artist (ArtistId)
    ,Name varchar(30)
    ,Padding char(4000) --can’t use REPLICATE in in-memory OLTP, so will use in INSERT
    ,CONSTRAINT AKArtwork UNIQUE NONCLUSTERED (ArtistId, Name)
) WITH ( MEMORY_OPTIMIZED = ON );
INSERT Art_InMem.Artwork (ArtworkId, ArtistId, Name,Padding)
VALUES (1,1,’Last Supper’,REPLICATE(’a’,4000)),(2,1,’Mona Lisa’,REPLICATE(’a’,4000)),
       (3,6,’Rabbit Fire’,REPLICATE(’a’,4000));

We need to cover a few things before moving on. First, in-memory OLTP objects cannot be involved in a transaction that crosses database boundaries unless that database is tempdb or master (master in a read-only manner). So the following is prohibited:

INSERT INTO InMemTable
SELECT *
FROM DBOtherThanTempdb.SchemaName.Tablename;

This can make things interesting in some situations, particularly when building a demo database. I usually have a copy of the data held in a separate database so I can reload. But to move from a different database in Transact-SQL, you need to move the data to tempdb first.

Second, when accessing memory-optimized tables in an explicit transaction, you generally need to specify the isolation level. For example, this works:

SELECT ArtistId, Name
FROM   Art_Inmem.Artist;

But the following:

BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM   Art_Inmem.Artist;
COMMIT TRANSACTION;

will return the following error:

Msg 41368, Level 16, State 0, Line 667
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

There are still some limitations on what can be mixed also. For example, if you try to access the table when the connection isolation level is REPEATABLE READ:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM   Art_Inmem.Artist WITH (REPEATABLEREAD);
COMMIT TRANSACTION;

you get the following:

Msg 41333, Level 16, State 1, Line 684
The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

You need to specify

BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM   Art_Inmem.Artist WITH (SNAPSHOT);
COMMIT TRANSACTION;

Or for the simple case where you want it to be SNAPSHOT isolation level unless specified, you can use

ALTER DATABASE LetMeFinish
  SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

I will not do this in my sample code, simply to make it clearer what isolation level I am using.

The following sections will first show how readers of data deal with chances to data by a different connection in SNAPSHOT, REPEATABLE READ, and SERIALIZABLE isolation levels, and then show the different ways that one writer is isolated from another writer. Unlike the pessimistic strategy used for on-disk tables that almost always results in a blocking situation, there are several interesting differences in the way one writer may affect the action of another.

Note that there are a few differences to how isolation is done using native code. I will cover this in Chapter 13 when I cover code design practices.

SNAPSHOT Isolation Level

When you read tables in SNAPSHOT isolation level, just like in the on-disk version, no matter what happens in another connection, things just look to you like nothing ever changes.

To demonstrate, on CONNECTION A, we will start a transaction, and then on CONNECTION B, we will create a new row:

--CONNECTION A
BEGIN TRANSACTION;
--CONNECTION B
INSERT INTO Art_InMem.Artist(ArtistId, Name)
VALUES (7, ’McCartney’);

Now on CONNECTION A, we look at the rows where ArtistId > 5:

--CONNECTION A
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 5;

This returns the following, which matches the current state in the database:

ArtistId    Name
----------- ------------------------------
7           McCartney
5           Dali
6           Jones

While the overall transaction container started with the BEGIN TRANSACTION started earlier, the in-memory transaction container starts when you cross the container barrier and then enlists in-memory resources. We started with three rows in Artist, so let’s add a row to both tables on CONNECTION B, and delete ArtistId = 6 and see what we can see:

--CONNECTION B
INSERT INTO Art_InMem.Artist(ArtistId, Name)
VALUES (8, ’Starr’);
INSERT INTO Art_InMem.Artwork(ArtworkId, ArtistId, Name)
VALUES (4,7,’The Kiss’);
DELETE FROM Art_InMem.Artist WHERE ArtistId = 5;

read all of the rows from Art_InMem.Artist where the ArtistId is 5 or greater. Back on CONNECTION A, if we read the data in the same transaction:

--CONNECTION A
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 5;
SELECT COUNT(*)
FROM  Art_InMem.Artwork WITH (SNAPSHOT);

our original view still persists:

ArtistId    Name
----------- ------------------------------
7           McCartney
5           Dali
6           Jones
-----------
3

If we COMMIT or ROLLBACK (we haven’t made any changes, so the net effect is the same) the transaction on CONNECTION A, our view of the data changes to what is current:

--CONNECTION A
COMMIT;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 5;
SELECT COUNT(*)
FROM  Art_InMem.Artwork WITH (SNAPSHOT);

Our view is consistent with the current reality:

ArtistId    Name
----------- ------------------------------
7           McCartney
8           Starr
6           Jones
-----------
4

REPEATABLE READ Isolation Level

REPEATABLE READ isolation level provides the same protection using optimistic versions as it does with the pessimistic locking scheme. The difference is how you notice the change. Just like in the previous example, we will start with CONNECTION A reading in a set of rows, then CONNECTION B making a change. We will start with CONNECTION B adding a row, which will cause a phantom row for CONNECTION B, which is acceptable in REPEATABLE READ.

--CONNECTION A
BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (REPEATABLEREAD)
WHERE ArtistId >= 8;

This returns the one row we knew existed:

ArtistId    Name
----------- ------------------------------
8           Starr

Now, on CONNECTION B, we insert a new row, not in an explicit transaction.

--CONNECTION B
INSERT INTO Art_InMem.Artist(ArtistId, Name)
VALUES (9,’Groening’);

Then we go look at the row in the transaction context, and commit the transaction and look again:

--CONNECTION A
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 8;
COMMIT;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 8;

The first result set will return the same as before, but the second will have two rows:

ArtistId    Name
----------- ------------------------------
8           Starr
9           Groening

This time we will delete one of the rows after we fetch it on CONNECTION A:

--CONNECTION A
BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (REPEATABLEREAD)
WHERE ArtistId >= 8;
--CONNECTION B
DELETE FROM Art_InMem.Artist WHERE ArtistId = 9; --Not because I don’t love Matt!

Then access the rows before and after committing the transaction:

--CONNECTION A
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 8;
COMMIT;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 8;

We get two rows back, and then we get an error message:

Msg 41305, Level 16, State 0, Line 775
The current transaction failed to commit due to a repeatable read validation failure.

So if our transaction had been open for 10 minutes, or 10 hours, the work that had been done is gone, and the entire transaction rolled back. Keeping transactions short can mitigate the cost of this, but it is certainly something to understand.

SERIALIZABLE Isolation Level

SERIALIZABLE isolation level behaves just like REPEATABLE READ, except that you get an error whether you are inserting, deleting, or updating a row. The following is a simple demonstration of just that:

--CONNECTION A
BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SERIALIZABLE)
WHERE ArtistId >= 8;

This returns one row for ’Starr’. Add the one row on CONNECTION B:

--CONNECTION B
INSERT INTO Art_InMem.Artist(ArtistId, Name)
VALUES (9,’Groening’); --See, brought him back!

Then executing the following:

--CONNECTION A
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId >= 8;
COMMIT;

The result set will return the same one row, but the second call will fail:

Msg 41325, Level 16, State 0, Line 803
The current transaction failed to commit due to a serializable validation failure.

When we created the table, we did not put a key on the Name column. What if this is a key column that you access single rows for, particularly for editing? Would this have an effect? To see, let’s fetch a row by name on CONNECTION A:

--CONNECTION A
BEGIN TRANSACTION;
SELECT ArtistId, Name
FROM  Art_InMem.Artist WITH (SERIALIZABLE)
WHERE Name = ’Starr’;

On a different connection, let’s update a different row by name:

--CONNECTION B
UPDATE Art_InMem.Artist WITH (SNAPSHOT) --default to snapshot, but the change itself
                                        --behaves the same in any isolation level
SET    Padding = REPLICATE(’a’,4000) --just make a change
WHERE  Name = ’McCartney’;

Going back to CONNECTION A, if we try to commit

--CONNECTION A
COMMIT;

we see an error:

Msg 41305, Level 16, State 0, Line 825
The current transaction failed to commit due to a repeatable read validation failure.

Note that the error is a repeatable read validation failure, not serializable, since we updated a row. The message is a clue to what has gone wrong. The big point, though, is that just like on-disk tables, it is the rows it could touch that are serialized, not the rows that are actually returned. This is because an update could change the number of rows. If you are using a column as a key, it will behoove you to add a key.

ALTER TABLE Art_InMem.Artist
  ADD CONSTRAINT AKArtist UNIQUE NONCLUSTERED (Name) --A string column may be used to
                                                     --do ordered scans,
                                                     --particularly one like name

Repeat the experiment again and you will see no error on the commit. While you won’t see any blocking when using in-memory tables, you could see tons of issues that behave like deadlocks if you are not careful. And while deadlocks are not horrible for data integrity, they are not great for performance.

Write Contention

In the previous sections, we looked at how readers and writers were isolated. The pattern was: read-only connections would never be blocked, would always see a consistent view of the database, and would fail at COMMIT if need be. In this section, we will now look at a less obvious set of cases, that of what happens when a connection that is writing to the table (including updates and deletes) contends with another writing connection.

In the following examples, I will show several scenarios that will occasionally come up, such as:

• Two users update the same row
• Two users delete the same row
• Two users insert a row with uniqueness collision
• A new row is inserted that matches a row that is being deleted on another connection

Not that these are all scenarios that can occur, but there are some of the more interesting things that we will see. First, two users try to modify the same row. So we update the same row on CONNECTION A and CONNECTION B:

--CONNECTION A
BEGIN TRANSACTION;
UPDATE Art_InMem.Artist WITH (SNAPSHOT)
SET    Padding = REPLICATE(’a’,4000) --just make a change
WHERE  Name = ’McCartney’;

Then as soon as we execute the same on CONNECTION B (in or out of an explicit transaction):

--CONNECTION B
BEGIN TRANSACTION;
UPDATE Art_InMem.Artist WITH (SNAPSHOT)
SET    Padding = REPLICATE(’a’,4000) --just make a change
WHERE  Name = ’McCartney’;

we are greeted with

Msg 41302, Level 16, State 110, Line 3
The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
The statement has been terminated.

Only one “dirty” version is allowed, so we get the immediate failure. Turns out that any combination of deletes and updates of the same physical resource will cause this to occur. Remember that a delete in in-memory OLTP is not a removal of the resource immediately, but rather an update to the ending timestamp.

Inserts are also interesting. On two connections, we will execute the following. We will use different manually created surrogate keys, because if we were using an identity key, we would get two different values because of the autonomous transaction.

--CONNECTION A
ROLLBACK TRANSACTION --from previous example
BEGIN TRANSACTION
INSERT INTO Art_InMem.Artist (ArtistId, Name)
VALUES  (11,’Wright’);
--CONNECTION B
BEGIN TRANSACTION;
INSERT INTO Art_InMem.Artist (ArtistId, Name)
VALUES  (12,’Wright’);

Both of these actually succeed. Look at the data on either connection and you will see a unique view of the data. So if two connections spin up and start inserting the same data in a large transaction, we could make a lot of new data, only to have the first connection succeed:

--CONNECTION A
COMMIT;

and the second one:

--CONNECTION B
COMMIT;

will give us a serializable failure (not a UNIQUE constraint collision, unfortunately). You will need to try that operation again to see that failure.

The final case for this section is if one connection deletes a row, but hasn’t committed, what happens if an INSERT tries to reinsert that row? This one goes against the grain, actually.

--CONNECTION A
BEGIN TRANSACTION;
DELETE FROM Art_InMem.Artist WITH (SNAPSHOT)
WHERE ArtistId = 4;

Then execution on a different connection, and insert with the same data:

--CONNECTION B --in or out of transaction
INSERT INTO Art_InMem.Artist (ArtistId, Name)
VALUES (4,’Picasso’);

This will result in a uniqueness violation, not a “wait, someone is already working in this space” error.

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint ’AKArtist’. Cannot insert duplicate key in object ’Artist’. The duplicate key value is (Picasso).

Finally, ROLLBACK the transaction so we can move on to the next code:

--CONNECTION A
ROLLBACK; --We like Picasso

As you can certainly see, the in-memory OLTP implementation of concurrency is vastly different from the version we saw for the on-disk structures. However, the implementation does one thing completely different and much better than the on-disk implementation. Rather than blocking or being blocked by the other sessions, the in-memory OLTP engine validates data consistency at transaction commit time, throwing an exception and rolling back the transaction if rules are violated. This does, however, vastly reduce the possibility that you will stomp over changes and not realize it.

Foreign Keys

This section covers one last consideration of using the memory-optimized model of concurrency. You saw when dealing with on-disk structures that unless you were using CASCADE operations, no locks were held on the related table when you made changes. This is not the case for in-memory tables. When you insert a row and it checks the existence of the parent row (or vice versa), a REPEATABLE READ contract is started. So if any part of the row is changed, you will get a failure.

For example, let’s try to create a piece of artwork:

--CONNECTION A
BEGIN TRANSACTION
INSERT INTO Art_InMem.Artwork(ArtworkId, ArtistId, Name)
VALUES (5,4,’The Old Guitarist’);

While we are out getting something to drink, another user changes a column on this artist row:

--CONNECTION B
UPDATE Art_InMem.Artist WITH (SNAPSHOT)
SET    Padding = REPLICATE(’a’,4000) --just make a change
WHERE ArtistId = 4;

Now we come back and COMMIT the transaction:

--CONNECTION A
COMMIT;

We get the following error:

Msg 41305, Level 16, State 0, Line 82
The current transaction failed to commit due to a repeatable read validation failure.

SQL Server 2014 came out without foreign keys, because Microsoft didn’t think that people who would employ such a feature made for high performance would use them. I somewhat agree, and this particular quirk in the version we have currently could make them less palatable. However, to implement your own foreign keys in code, you will need to either say “no deletes” or use REPEATABLE READ isolation level. I suspect that many who need blazing performance will opt for the no delete policy, which if you need to cross containers and use on-disk structures is the policy that you will use anyhow.

Coding for Asynchronous Contention

One of the most important considerations we need to make is how our software deals with the effects of delays in execution. So far in this chapter, I’ve discussed at length the different mechanisms, such as transactions, isolation levels, and so on, that SQL Server uses to protect one connection from another at a physical level. Once your connections are not internally using the same resources, it is up to you as a programmer to make sure that the state of the data is the same when it executes as when you actually apply some changes. The time between this caching of rows could be microseconds in the case of two UPDATE statements that were blocking one another, or data may have been purposefully cached (either due to blocking, isolation collisions, or a caching layer, or just a user who opened a form, made some changes, and didn’t click “save” until after they finished their pepperoni Hot Pocket).

The general progression of events for most applications is the same: fetch some data for a user or a process to look at, operate on this data, and make changes to the data or make some decision based on the retrieved values. Once the users have performed their operations, they may make save some additional database changes based on what they have seen.

For example, consider a customer row in a call center. It is a low probability that two people with access to the same account is calling into your sales call center on two lines talking to two different agents, making changes to the same account. But that low probability is not zero probability that two changes will be made to the same row simultaneously. Another example is an inventory level. You check to see how many widgets are available, then offer to sell one to the customer. Even if there were 1,000 items there a moment before, you will still check as you save the order to ensure that is still the case.

The idea of optimistic change detection is basically the same as it is for SQL Server, except now we need to deal with asynchronous changes to data, not the synchronous ones that SQL Server handles. The problem for we programmers is that every synchronous contention issue turns into an asynchronous one. User A updates row R at the same time that User B updates row R, and one is turned away or blocked. Particularly when using locks, if User B was blocked, the person issuing the query would not ever know that the row had changed, or even felt the latency if it lasted 10 milliseconds. But the row may have changed, and the second update could blow away the changes.

There are generally four ways this asynchronous timing issues is handled:

• Chaos: Just let it happen. If two users modify the same row in the database, the last user wins. This is not generally the best idea, as the first user might have had something important to say, and this method rejects the first user’s changes. I won’t cover this any further because it’s straightforward. Such an approach is never what I suggest for controlling your data resource modifications, but so far, I have not been elected data emperor, just data architect.
• Row-based: Protect your data at the row level by checking to see if the rows being modified are the same as in the table. If not, reject the operation, and have the interface refresh the data from the table, showing the user what was changed. When optimistic locking is implemented, this is by far the most common method used.
• Logical unit of work: A logical unit of work is used to group a parent record with all its child data to allow a single optimistic lock to cover multiple tables. For example, you’d group an invoice and the line items for that invoice. Treat modifications to the line items the same way as a modification to the invoice, for change detection purposes.
• Reality/hybrid: The reality of this situation is that if you are modifying thousands of rows, caching the row states, and then checking it may not always be possible. Most of the time this is a matter of performance, in that adding another join to the cache of data isn’t reasonable. In a true OLTP database, most large updates are done as maintenance during down time, so this may not be an issue. Using MVCC can also alleviate these issues.

Although it isn’t typically a good idea to ignore the problem of users overwriting one another altogether, this is a commonly decided upon method for some companies. On the other hand, the best plan is optimally a mixture of the row-based solution for most tables and a logical unit of work for major groups of tables that make up some common object.

In the following sections, I’ll cover row-based locking and the logical unit of work. The unchecked method ignores the concern that two people might modify the same row twice, so there’s no coding (or thinking!) required.

Row-Based Change Detection

A row-based scheme is used to check every row as to whether or not the data that the user has retrieved is still the same as the one that’s in the database. The order of events is fetch data, modify data in the cached copy, check to see that the row (or rows) of data are still the same as they were, and then commit the changes.

There are three common methods to implement row-based optimistic locking:

• Check all columns in the table: If you cannot modify the table structure, which the next two methods require, you can check to make sure that all the data you had fetched is still the same and then modify the data. This method is the most difficult, because any modification procedure you write must contain parameters for the previous values of the data, which isn’t a good idea. If you have any large datatypes, it can be much slower also. Checking all columns is typical when building bound data-grid types of applications, where there are direct updates to tables, especially if not all tables can follow the rather strict rules of the next two methods.
• Add a row modified time column to the table: Set the point in time value when the row is inserted and subsequently updated. Every update to the table is required to modify the value in the table to set the rowLastModifiedTime column. Generally, it’s best to use a trigger for keeping the column up to date, and often, it’s nice to include a column to tell which user last modified the data (you need someone to blame!). Later in this section, I’ll demonstrate a simple INSTEAD OF trigger to support this approach.
• Use a rowversion (previously known as timestamp) column: In the previous method, you used a manually controlled value to manage the optimistic lock value. This method uses a column with a rowversion datatype. The rowversion datatype automatically gets a new value for every command used to modify a given row in a table.

The next two sections cover adding the optimistic lock columns to your tables and then using them in your code.

Adding Validation Columns

In this section, we’ll add a column to a table to support adding either the datetime2 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. Here’s the structure:

CREATE SCHEMA Hr;
GO
CREATE TABLE Hr.person
(
     PersonId int 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. We’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), implement an INSTEAD OF trigger on the UPDATE of the Hr.Person table:

CREATE TRIGGER Hr.Person$InsteadOfUpdateTrigger
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
      --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
   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 Person.PersonId = inserted.PersonId;
   END TRY
      BEGIN CATCH
              IF XACT_STATE() > 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’,’19691212’);
SELECT *
FROM   Hr.Person;

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

PersonId    FirstName    MiddleName   LastName  DateOfBirth
----------- ------------ ------------ --------- -----------
1           Paige        O            Anxtent   1969-12-12  
RowLastModifyTime       RowModifiedByUserIdentifier
----------------------- ----------------------------
2016-06-11 14:43:45.576 SomeUserName

Next, update the row:

UPDATE Hr.Person
SET    MiddleName = ’Ona’
WHERE  PersonId = 1;
SELECT RowLastModifyTime
FROM   Hr.Person;

You should see that the update date has changed:

RowLastModifyTime
-----------------------
2016-06-11 14:44:48.791

If you want to set the value on INSERT, or implement RowCreatedBy -Date 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 UPDATE (typically I will add an INSTEAD OF INSERT TRIGGER, but will not for brevity, and I trust the programmer in this case to not set funky times for the 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 (note that the rowversion datatype is not supported in in-memory, and as I show in the downloads for Chapter 6, triggers are possible if tricky for in-memory change detection columns), and I almost always use a rowversion when implementing an optimistic mechanism. I usually have the row modification time and user columns on tables 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 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 set:

PersonId    RowVersion
----------- ------------------
1           0x00000000000007D1

Now, when the row is 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:

PersonId    RowVersion
----------- ------------------
1           0x00000000000007D2

Coding for Row-Level Change Detection

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, even when changing the key value if allowed
  --non-key columns
  and   FirstName = ’Paige’
  and   MiddleName = ’ona’
  and   LastName = ’Anxtent’
  and   DateOfBirth = ’19691212’;

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 = ’2016-06-11 14:52:50.154’;

Use a rowversion column:

UPDATE  Hr.Person
SET     FirstName = ’Fred’
WHERE   PersonId = 1
  and   RowVersion = 0x00000000000007D4;

Which is better performance-wise? Either of these generally performs just as well as the other (unless you have very large columns in the first case!), 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.

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 = 0x00000000000007D5;

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 checking for changes because it is far simpler and safer and cannot be overridden by any code, no matter how you implement the other columns.

Coding for Logical Unit of Work Change Detection

Although row-based optimistic change checks are helpful, they do have a slight downfall. In many cases, several tables together make one “object.” A good example is an invoice with line items. The idea behind a logical unit of work is that, instead of having a row-based lock on the invoice and all the line items, you might only implement one on the invoice and use the same value for the line items. This strategy does require that the code always fetch not only the invoice line items but at least the invoice’s timestamp into the client’s cache when dealing with the invoice line items. Assuming you’re using a rowversion column, I’d just use the same kind of logic as previously used on the Hr.Person table. In this example, we’ll build the procedure to do the modifications.

When the user wants to insert, update, or delete line items for the invoice, the procedure requires the @ObjectVersion parameter and checks the value against the invoice, prior to update. Consider that there are two tables, minimally defined as follows:

CREATE SCHEMA Invoicing;
GO
--leaving off who invoice is for, like an account or person name
CREATE TABLE Invoicing.Invoice
(
     InvoiceId int IDENTITY(1,1),
     Number varchar(20) NOT NULL,
     ObjectVersion rowversion not null,
     CONSTRAINT PKInvoice PRIMARY KEY (InvoiceId)
);
--also ignoring what product that the line item is for
CREATE TABLE Invoicing.InvoiceLineItem
(
     InvoiceLineItemId int NOT NULL,
     InvoiceId int NULL,
     ItemCount int NOT NULL,
     Cost int NOT NULL,
     CONSTRAINT PKInvoiceLineItem primary key (InvoiceLineItemId),
     CONSTRAINT FKInvoiceLineItem$references$Invoicing_Invoice
            FOREIGN KEY (InvoiceId) REFERENCES Invoicing.Invoice(InvoiceId)
);

For our delete procedure for the invoice line item, the parameters would have the key of the invoice and the line item, plus the rowversion value:

CREATE PROCEDURE InvoiceLineItem$del
(
    @InvoiceId int, --we pass this because the client should have it
                    --with the invoiceLineItem row
    @InvoiceLineItemId int,
    @ObjectVersion rowversion
) 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;
        --tweak the ObjectVersion on the Invoice Table
        UPDATE  Invoicing.Invoice
        SET     Number = Number
        WHERE   InvoiceId = @InvoiceId
          And   ObjectVersion = @ObjectVersion;
        IF @@Rowcount = 0
           THROW 50000,’The InvoiceId no longer exists or has been changed’,1;
        DELETE  Invoicing.InvoiceLineItem
        FROM    InvoiceLineItem
        WHERE   InvoiceLineItemId = @InvoiceLineItemId;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        --if the tran is doomed, and the entryTrancount was 0,
        --we can 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,1;
        RETURN -100;
     END CATCH;
 END;

Instead of checking the rowversion on an InvoiceLineItem row, we check the RowVersion (in the ObjectVersion column) on the Invoice table. Additionally, we must update the RowVersion value on the Invoice table when we make our change, so we update the Invoice row, simply setting a single column to the same value. There’s a bit more overhead when working this way, but it’s normal to update multiple rows at a time from the client.

Best Practices

The number-one issue when it comes to concurrency is data quality. Maintaining consistent data is why you go through the work of building a database in the first place. Generally speaking, if the only way to get consistent results was to have every call single threaded, it would be worth it. Of course, we don’t have to do that except in rare situations, and SQL Server gives us tools to make it happen with the isolation levels. Use them as needed. It’s the data that matters.

• Use transactions as liberally as needed: It’s important to protect your data, 100% of the time. Each time data is modified, enclosing the operation in a transaction isn’t a bad practice. This gives you a chance to check status, number of rows modified, and so on, and if necessary, to roll back the modification.
• Keep transactions as short as possible: The smaller the transaction, the less chance there is of it holding locks. Try not to declare variables, create temporary tables, and so on inside a transaction unless doing so is necessary. Make sure that all table access within transactions is required to be executed as an atomic operation.
• Recognize the difference between hardware limitations and SQL Server concurrency issues: If the hardware is maxed out (excessive disk queuing, 90%–plus CPU usage, and so on), consider adding more hardware. However, if you’re single threading calls through your database because of locking issues, you could add 20 processors and a terabyte of RAM and still see little improvement.
• Fetch all rows from a query as fast as possible: Depending on the isolation level and editability of the rows being returned, locks held can interfere with other users’ ability to modify or even read rows.
• Make sure that all queries use reasonable execution plans: The better all queries execute, the faster the queries will execute, and it follows that locks will be held for a shorter amount of time. Remember, too, that scans will require every row in the table to be locked, whereas a seek will lock far fewer rows.
• Use some form of optimistic mechanism in your code: Use some form of optimistic mechanism to catch when rows have changed between the time you cached it and the actual operation happens, preferably using a rowversion column, because it requires the smallest amount of coding and is managed entirely by SQL Server. Whether the difference in time is a millisecond or a day, it is important to make sure you are updating the row you expected.
• Consider using some form of the SNAPSHOT isolation level: For on-disk structures, either code all your optimistic-locked retrieval operations with SET SNAPSHOT ISOLATION LEVEL or change the database setting for READ_COMMITTED_SNAPSHOT to ON. This alters how the READ COMMITTED isolation level reads snapshot information at the statement level. All in-memory OLTP table access is in a flavor of snapshot. Be careful to test existing applications if you’re going to make this change, because these settings do alter how SQL Server works and might negatively affect how your programs work.

Summary

Concurrency is an important topic, and a difficult one. It seems easy enough: keep the amount of time a user needs to be in the database to a minimum, and have adequate resources on your machine. As of SQL Server 2014 it can be far more complicated if you decide to use the new in-memory OLTP feature.

The fact is that concurrency is a juggling act for SQL Server, Windows, the disk system, the CPUs, and so on. If you have reasonable hardware for your situation, use the SNAPSHOT isolation level for retrieval and READ COMMITTED for other calls, and you should have no trouble with large-scale blocking on your server. This solution sounds perfect, but the greater the number of users, the more difficult a time you’ll have making things perform the way you want. Concurrency is one of the fun jobs for a DBA, because it’s truly a science that has a good deal of artsy qualities. You can predict only so much about how your user will use the system, and then experience comes in to tune queries, tune hardware, and tweak settings until you have them right.

I discussed some of the basics of how SQL Server implements controls to support concurrent programming, such that many users can be supported using the same data with locks and transactions. Then, I covered isolation levels, which allow you to tweak the kinds of locks taken and how long they’re held on a resource. The most important part of this chapter was the part on optimistic locking. Because the trend for implementing systems is to use cached data sets, modify that set, and then flush it back, you must make sure that other users haven’t made changes to the data while it’s cached.