{"id":919,"date":"2010-06-22T00:00:00","date_gmt":"2010-06-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/developing-modifications-that-survive-concurrency\/"},"modified":"2021-09-29T16:21:58","modified_gmt":"2021-09-29T16:21:58","slug":"developing-modifications-that-survive-concurrency","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/developing-modifications-that-survive-concurrency\/","title":{"rendered":"Developing Modifications that Survive Concurrency"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">Just like queries, modifications that work perfectly well in the isolated world of the test database, can suddenly start misbehaving, intermittently, when run in a production environment, under conditions of concurrent access. There are a number of different problems that might occur when &#8220;competing&#8221; connections try to simultaneously update the same data, some of the most common of which are:<\/p>\n<ul>\n<li><b>Lost modifications,<i> a.k.a.<\/i> lost updates<\/b> &#8211; such problems occur when modifications performed by one connection are overwritten by another. Typically such problems occur silently; no errors are raised.  <\/li>\n<li><b>Resource contention errors<\/b> &#8211; such as deadlocks and lock timeouts  <\/li>\n<li><b>Primary key and unique constraint violations<\/b> &#8211; such problems occur when different modifications attempt to insert one and the same row.<\/li>\n<\/ul>\n<p>The sort of situation in which a lost update or another error can occur, in other words when the result of the operation is dependent on the sequence or timing of other events, is known as a <span class=\"STBold\">race condition<\/span>. It is the job of the defensive programmer to guard against potential race conditions in their software. The most common solutions for such problems include:<\/p>\n<ul>\n<li>Serializing modifications against the same data, so that race conditions do not occur.  <\/li>\n<li>Detecting and handling errors caused by concurrency  <\/li>\n<li>Rewriting code so that it better withstands race conditions or avoids them altogether.<\/li>\n<\/ul>\n<p>We shall discuss a few, all-too-common, examples that demonstrate the sort of problems that can arise, and then show different ways of solving them. Of course, it is possible that the problems that you encounter with concurrent modifications will be different from those described here. However, the basic approaches described for solving such problems are very likely to be useful in your situation.<\/p>\n<h1>Understanding Lost Modifications<\/h1>\n<p>Lost modifications can occur when multiple connections modify the same row of data. For example, one connection (A) reads a row of data, with the intent to update that row, based on certain criteria. Meanwhile after A has read the data, but before it updates it, a second connection (B) updates the same row. Connection A then performs its update, potentially causing the update made by B t be &#8216;lost&#8217;.<\/p>\n<p>The classic example of a lost update involves reading data into a user form, for subsequent modification. For example, an employee loads into a user form, data pertaining to a particular customer, in order to update their address. Meanwhile, an automated process updates the same row, assigning to the customer a new status, such as &#8220;loyal customer&#8221;. If the employee submits the address update and the application updates the whole row, rather than the column that was changed, then the customer could be rest back to their old status, and the effect of the automated process will be lost.<\/p>\n<p>Aside from having a more intelligent logic attached to the form, so only the column modified is updated in the database (we&#8217;ll discuss this in more detail shortly), there are essentially two concurrency control approaches that we can use in order to avoid such &#8216;lost updates&#8217;:<\/p>\n<p><b>Optimistic approach<\/b>: even though we have selected a row, other sessions can modify it, but we optimistically assume that this will not happen. When the selected row is updated, we have logic in place that will test to see if the row has been modified by someone else, since it was queried. If it has, then the employee would get a message saying that the row has been changed, and asking if we still want to make the requested change. This approach is preferable when selected rows are rarely modified, or when a typical modification takes a lot of time, such as modifying data via on-screen forms.<\/p>\n<p><b>Pessimistic approach<\/b>: Here, we pessimistically assume that rows will get modified by another process, between reading them and updating them, unless we do something to prevent it. When the employee selects a row, or list of rows, the system makes sure that nobody else can modify those rows. With this approach, he automated process would be blocked until the employee had made the address update (and we&#8217;d need logic in place to allow it to retry). This approach is most useful when selected rows are very likely to be modified and\/or the modification does not take much time. Typically this approach is not used when users modify data via on-screen forms.<\/p>\n<p>In the example we&#8217;ve used here, the two modifying transactions do not, from the database&#8217;s perspective, overlap. The automated update starts and finishes before the employee&#8217;s address update has started. In such circumstances, and given user forms are involved, we&#8217;re likely to take an optimistic approach to concurrency control.<\/p>\n<p>In cases where only automated processes involved, where we have quick transactions attempting to (almost) simultaneously change the same rows of data, we are also in risk of lost updates, and are likely to adopt a pessimistic approach to concurrency control, in order to avoid them.<\/p>\n<p>Let&#8217;s take a look at some fairly typical examples of when a &#8216;lost update&#8217; can occur.<\/p>\n<h1>Non-overlapping Modifications<\/h1>\n<p>From the point of view of the database engine, the modifications in this example do not overlap; they occur at different times. Still, the second modification overwrites the changes made by the first one, and some information is lost.<\/p>\n<p>Suppose that a ticket has been created in our bug tracking system to report that a very important business report for our Californian office has suddenly stopped working. Listing 1 shows the table that stores tickets. We have already used a table named Tickets in previous <a href=\"http:\/\/www.amazon.com\/gp\/product\/1906434492\">chapters of my book<\/a>; make sure to create a new database to run the examples, or, at the very least, make sure to drop the table Tickets if it exists.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Tickets\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; TicketID INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; Problem VARCHAR(50) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; CausedBy VARCHAR(50) NULL ,\n&#160;&#160;&#160;&#160;&#160; ProposedSolution VARCHAR(50) NULL ,\n&#160;&#160;&#160;&#160;&#160; AssignedTo VARCHAR(50) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; Status VARCHAR(50) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; CONSTRAINT PK_Tickets PRIMARY KEY ( TicketID )\n&#160;&#160;&#160; ) ;\n<\/pre>\n<p class=\"caption\">Listing 1: Creating the <span class=\"STCodeinTextChar\">dbo.Tickets<\/span> table<\/p>\n<p>Of course, in real life this table would have more columns, such as <span class=\"STCodeinTextChar\">Priority<\/span>, and possibly some columns would have different types, and there would be some constraints. However, as usual in this book all the details that are not relevant to this simple example are omitted. Listing 2 shows the ticket that was created.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT&#160; INTO dbo.Tickets\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( TicketID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\nVALUES&#160; ( 123 ,\n&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;'TPS report for California not working' , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NULL , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NULL , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'TPS report team' , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Opened'\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ; \n<\/pre>\n<p class=\"caption\">Listing 2: The ticket in <span class=\"STCodeinTextChar\">dbo.Tickets<\/span> table, reporting a problem with the TPS report<\/p>\n<p>This is a very important ticket, so two developers &#8211; let&#8217;s call them Arne and Brian &#8211; immediately start troubleshooting. Brian starts the bug tracking GUI and opens the ticket. In the meantime, Arne starts his investigation and quickly realizes that one of the tables used in the report is empty; possibly it has been accidentally truncated. He opens the same ticket in his on-screen form in the bug-tracking GUI and immediately updates the ticket, describing the likely cause of the problem. He also reassigns the ticket to the DBA team. The resulting SQL is shown in Listing 3<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- Arnie loads data into form\nSELECT&#160; TicketID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status\nFROM&#160;&#160;&#160; dbo.Tickets\nWHERE&#160;&#160; TicketID = 123\nGO\n&#160;\n-- Arnie updates the form\nBEGIN TRAN ;\nUPDATE&#160; dbo.Tickets\nSET&#160; AssignedTo = 'DBA team' ,\n&#160;&#160;&#160;&#160; CausedBy = 'The dbo.Customers table is empty' ,\n&#160;&#160;&#160;&#160; Problem = 'TPS report for California not working' ,\n&#160;&#160;&#160;&#160; ProposedSolution =\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Restore dbo.Customers table from backup'\nWHERE&#160;&#160; TicketID = 123 ;\nCOMMIT ;\n<\/pre>\n<p class=\"caption\">Listing 3: The SQL that was issued by Arne&#8217;s bug tracking form<\/p>\n<p>Meanwhile, Brian decided to start by ascertaining whether it was just the report that had failed, or whether it was also affecting their Ohio office. He runs the report for Ohio and gets the same problem so, from his onscreen view of the ticket, which he opened before Arne made his update, Brian updates the <span class=\"STCodeinTextChar\">Problem<\/span> field to reflect this. The resulting SQL is shown in Listing 4.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--Brian updates the form\nBEGIN TRAN ;\nUPDATE&#160; dbo.Tickets\nSET&#160; AssignedTo = 'TPS report team' ,\n&#160;&#160;&#160;&#160; CausedBy = NULL ,\n&#160;&#160;&#160;&#160; Problem =\n&#160;&#160;&#160;&#160;&#160; 'TPS report for California and Ohio not working' ,\n&#160;&#160;&#160;&#160; ProposedSolution = NULL\nWHERE&#160;&#160; TicketID = 123 ;\nCOMMIT ;\n<\/pre>\n<p class=\"caption\">Listing 4: The SQL that was issued by Brian&#8217;s bug tracking form<\/p>\n<p>The changes saved by Arne were completely lost. Clearly, our bug tracking system is susceptible to lost updates, and so has a big problem. There are two approaches to this issue that we must consider in order to prevent the lost update: <\/p>\n<ul>\n<li>Writing logic into the client\/data access layer so that only columns are updated in the database, not the entire row  <\/li>\n<li>Using concurrency control logic  <\/li>\n<li>Let&#8217;s consider each in turn.<\/li>\n<\/ul>\n<h1>Only Updating Changed Columns<\/h1>\n<p>In this simple example, the problem is pretty blatant: the SQL generated by the user form updates all the fields from the screen, not just the one <span class=\"STCodeinTextChar\">Problem<\/span> field that Brian modified. In this case, the problem could be solved by designing a better data access layer that only updates those columns that were modified in the form.<\/p>\n<p>Nevertheless, this is only a partial solution and will not be adequate in every case. If Brian, in addition, to recording that the TPS report for Ohio was also not working, had suggested as interim solution such as, &#8220;temporarily expose yesterday&#8217;s TPS report&#8221;, then Arne&#8217;s much more sensible solution would have been overwritten, regardless.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">UPDATE&#160; dbo.Tickets\nSET&#160;&#160;&#160;&#160; Problem = \n&#160;&#160;&#160;&#160;&#160; 'TPS report for California and Ohio not working' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution = \n&#160;&#160;&#160; 'Expose yesterdays'' TPS report instead of live one'\nWHERE&#160;&#160; TicketID = 123 ;\n<\/pre>\n<p class=\"caption\">Listing 5: Brian proposes a poor solution, overwrites a much better one suggested by Arne.<\/p>\n<p>Furthermore, while updating only changed columns, while feasible, is far from an ideal solution. Let&#8217;s count how many different <span class=\"STCodeinTextChar\">UPDATE<\/span> statements would be required in order to modify only the columns that were actually updated on the screen. There are five columns that may be modified, which gives us a total 2^5 = 32 different update combinations. Should we generate <span class=\"STCodeinTextChar\">UPDATE<\/span> commands on the fly? Should we wrap 32 updates in stored procedures? Surely developing all this code manually is out of the question. Although generating such code would by quite easy, neither choice seems very attractive.<\/p>\n<h2>Using Concurrency Control Logic<\/h2>\n<p>Ultimately, any system that has the potential for &#8216;update conflicts&#8217;, which could result in lost updates, needs some concurrency control logic in place to either prevents such conflicts from occurring, or to determine what should happen when they do.<\/p>\n<p>In <a href=\"http:\/\/www.amazon.com\/gp\/product\/1906434492\">previous chapters of my book<\/a>, we discussed the use of isolation levels to mediate the potential interference of concurrent transactions. Unfortunately, in our bug tracking example, isolation levels alone will not help us. Although from a user&#8217;s point of view the problem is caused by &#8220;concurrent updates of the database&#8221;, from the database&#8217;s perspective the modifying transactions never overlap. The basic format of the example was:<\/p>\n<ol>\n<li>Session 1 queries data into form  <\/li>\n<li>Session 2 queries same data into form  <\/li>\n<li>Sessions 2 starts transaction to update data  <\/li>\n<li>Sessions 2 completes transaction to update data  <\/li>\n<li>Session 1 starts transaction to update data  <\/li>\n<li>Sessions 1 completes transaction to update data  <\/li>\n<li>Sessions 2&#8217;s update is lost<\/li>\n<\/ol>\n<p>Although the example was run in the default <span class=\"STCodeinTextChar\">READ COMMITTED<\/span>, the result would have been the same using any of the other transaction isolation levels. In order for isolation levels to have any effect, the transactions must overlap, and in order for that to happen, we&#8217;d need to adopt a pessimistic approach, and start the transactions much earlier, as soon as the data was queried into the form, and essentially lock the data from that point. As discussed earlier, this pessimistic approach is often not feasible in situations where data is held in user forms for a long time; to do so would inevitably grind the whole system to a halt. So, when the bug tracking system opens ticket number 123 for both Arne and Brian, it should not keep the transactions open after their screen forms have been rendered.<\/p>\n<p>If it is possible to start the transactions earlier, then there may be some cases where high levels such as <span class=\"STCodeinTextChar\">SERIALIZABLE<\/span>, or certainly <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> (as we will discuss shortly) can help. Note though that cannot always prevent lost updates in this manner. In our previous example, we would simply be in danger of reversing the problem, and losing Brian&#8217;s, rather than Arne&#8217;s, update.<\/p>\n<p>If you wish to implement a pessimistic approach, without locking resources as soon as the data is queried, then the situation is difficult. Unfortunately, there is no built in mechanism to implement pessimistic concurrency control for longer than a lifetime of a transaction. If we need to implement such an approach, we need to roll it out ourselves. For more information on how to accomplish this, refer to the book &#8220;Expert SQL Server 2005 Development&#8221; by Adam Machanic with Hugo Kornelis and Lara Rubbelke, where the author shows how to roll out your own locks, persist them in a table, and use triggers to verify if rows to be modified are locked.<\/p>\n<p>A more straightforward approach, for examples such as this, is to implement optimistic concurrency control, where we &#8220;optimistically assume&#8221; that the rows won&#8217;t be modified in the time between querying them and updating them. Of course, with no control logic, the &#8216;conflicting update&#8217; just proceeds and a lost update occurs, as we saw. However, with proper application of the optimistic approach, we&#8217;d have logic in place that raised a warning and prevented the conflicting update from proceeding. So, in our previous example, at the point Brian tried to update the system, the form data would be refreshed and Brian would get a warning that the data had changed since he queried it, and his update would not proceed. <\/p>\n<h2>Optimistic Concurrency Control to Detect and Prevent Lost Updates<\/h2>\n<p>Let&#8217;s take a look at three examples of how to implement optimistic concurrency control in our bug tracking example.<\/p>\n<h3>Saving the original values<\/h3>\n<p>To detect lost updates, our code needs to &#8220;remember&#8221; the values of the columns before they were modified, and submit those old values along with the modified ones. The following, rather large, stored procedure performs the update only if no columns were changed.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE PROCEDURE dbo.UpdateTicket\n&#160;&#160;&#160; @TicketID INT ,\n&#160;&#160;&#160; @Problem VARCHAR(50) ,\n&#160;&#160;&#160; @CausedBy VARCHAR(50) ,\n&#160;&#160;&#160; @ProposedSolution VARCHAR(50) ,\n&#160;&#160;&#160; @AssignedTo VARCHAR(50) ,\n&#160;&#160;&#160; @Status VARCHAR(50) ,\n&#160;&#160;&#160; @OldProblem VARCHAR(50) ,\n&#160;&#160;&#160; @OldCausedBy VARCHAR(50) ,\n&#160;&#160;&#160; @OldProposedSolution VARCHAR(50) ,\n&#160;&#160;&#160; @OldAssignedTo VARCHAR(50) ,\n&#160;&#160;&#160; @OldStatus VARCHAR(50)\nAS \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET NOCOUNT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET XACT_ABORT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.Tickets\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; Problem = @Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy = @CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution = @ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo = @AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status = @Status\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; TicketID = @TicketID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND ( Problem = @OldProblem )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND ( AssignedTo = @OldAssignedTo )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND ( Status = @OldStatus ) \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- conditions for nullable columns \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- CausedBy and ProposedSolution\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- are more complex\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND ( CausedBy = @OldCausedBy\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; OR ( CausedBy IS NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND @OldCausedBy IS NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND ( ProposedSolution =\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;@OldProposedSolution\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; OR ( ProposedSolution IS NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;AND @OldProposedSolution IS NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF @@ROWCOUNT = 0 \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; RAISERROR('Ticker number %d not found\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;or modified after it was read',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;16, 1, @TicketID) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160; END ;\n<\/pre>\n<p class=\"caption\">Listing 6: stored procedure only modifies if the ticket has not been changed.<\/p>\n<p>As you can see by the size of this procedure, it takes a significant amount of code, both on the server and on the client, to implement this approach. Still, let&#8217;s see how it works. We&#8217;ll rerun our bug tracking example (Listings 10-3 and 10-4) using this stored procedure. First, we need to delete and reinsert test data.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DELETE FROM dbo.Tickets ;\n<\/pre>\n<p class=\"caption\">Listing 7: Deleting modified test data.<\/p>\n<p>To restore the test data, rerun Listing 2. Arne&#8217;s update, which was originally performed by Listing 3, is now submitted using the <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure, as shown in Listing 8.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXECUTE dbo.UpdateTicket\n&#160;&#160;&#160;@TicketID = 123 \n&#160; ,@Problem = 'TPS report for California not working' \n&#160; ,@CausedBy = 'The Customers table is empty' \n&#160; ,@ProposedSolution =&#160; 'Restore Customers table\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;from backup' \n&#160; ,@AssignedTo = 'DBA team' \n&#160; ,@Status = 'Opened'\n&#160; ,@OldProblem = 'TPS report for California not working' \n&#160; ,@OldCausedBy = NULL\n&#160; ,@OldProposedSolution = NULL\n&#160; ,@OldAssignedTo = 'TPS report team'\n&#160; ,@OldStatus = 'Opened' ;\n<\/pre>\n<p class=\"caption\">Listing 8: Using the <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure to save Arne&#8217;s changes.<\/p>\n<p>Brian&#8217;s update from Listing 5 is also submitted via the same stored procedure, which detects a lost update, as shown in Listing 9.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXECUTE dbo.UpdateTicket\n&#160;&#160;@TicketID = 123\n&#160; ,@Problem = 'TPS report for California and Ohio\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;not working'\n&#160; ,@CausedBy = NULL\n&#160; ,@ProposedSolution = 'Expose yesterdays'' TPS report'\n&#160; ,@AssignedTo = 'TPS report team'\n&#160; ,@Status = 'Opened'\n&#160; ,@OldProblem = 'TPS report for California not working'\n&#160; ,@OldCausedBy = NULL\n&#160; ,@OldProposedSolution = NULL\n&#160; ,@OldAssignedTo = 'TPS report team'\n&#160; ,@OldStatus = 'Opened' ;\n\n&#160;\nMsg 50000, Level 16, State 1, Procedure UpdateTicket, Line 47\nTicker number 123 modified after it was read\n<\/pre>\n<p class=\"caption\">Listing 9: Stored procedure detects a lost update and does not save Brian&#8217;s changes.<\/p>\n<p>Although this approach works in detecting and preventing lost updates, there is a more efficient one, using the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column.<\/p>\n<h3>Using ROWVERSION<\/h3>\n<p>A <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column in a table is simply a column with a data type of <span class=\"STCodeinTextChar\">ROWVERSION<\/span>, which contains a number that auto-increments every time the row is modified. In other words, there is no way to modify a row without incrementing its <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column. We can use this feature to detect and prevent lost updates.<\/p>\n<p>In the simplest case, where we load a single row into a screen form, we can retrieve the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> along with other columns. When we save the modified data in the database, we can match the saved <span class=\"STCodeinTextChar\">ROWVERSION<\/span> against the current <span class=\"STCodeinTextChar\">ROWVERSION<\/span> of the row that we are going to modify. If the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> value has changed, then the row must have been modified since we read it. <\/p>\n<p>In order to demonstrate this approach, we first need to add a <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column to the <span class=\"STCodeinTextChar\">Tickets<\/span> table, as shown in Listing 10.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER TABLE dbo.Tickets\n&#160; ADD CurrentVersion ROWVERSION NOT NULL ;\n<\/pre>\n<p class=\"caption\">Listing 10: Adding a <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column to the <span class=\"STCodeinTextChar\">Tickets<\/span> table<\/p>\n<p>To populate the changed table, simply rerun scripts 10-7 and 10-2. Listing 11 shows how to modify our <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure to use the new <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column. It compares the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> of the row to be modified against the original <span class=\"STCodeinTextChar\">ROWVERSION<\/span> value, passed as a parameter, and modifies the row only if these <span class=\"STCodeinTextChar\">ROWVERSION<\/span> values match. <\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER PROCEDURE dbo.UpdateTicket\n&#160;&#160;&#160; @TicketID INT ,\n&#160;&#160;&#160; @Problem VARCHAR(50) ,\n&#160;&#160;&#160; @CausedBy VARCHAR(50) ,\n&#160;&#160;&#160; @ProposedSolution VARCHAR(50) ,\n&#160;&#160;&#160; @AssignedTo VARCHAR(50) ,\n&#160;&#160;&#160; @Status VARCHAR(50) ,\n&#160;&#160;&#160; @version ROWVERSION\nAS \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET NOCOUNT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET XACT_ABORT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.Tickets\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; Problem = @Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy = @CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution = @ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo = @AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status = @Status\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; TicketID = @TicketID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND CurrentVersion = @version ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF @@ROWCOUNT = 0 \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; RAISERROR('Ticker number %d not found\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;or modified after it was read',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;16, 1, @TicketID) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160; END ;\n<\/pre>\n<p class=\"caption\">Listing 11: The <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure saves changes only if the saved <span class=\"STCodeinTextChar\">ROWVERSION<\/span> matches the current <span class=\"STCodeinTextChar\">ROWVERSION<\/span> of the row being modified<\/p>\n<p>Listing 12 shows how our new <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure works in our bug tracking example.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @version ROWVERSION ;\n&#160;\n-- both Brian and Arne retrieve the same version\nSELECT&#160; @version = CurrentVersion\nFROM&#160;&#160;&#160; dbo.Tickets\nWHERE&#160;&#160; TicketID = 123 ;\n&#160;\n-- Arne saves his changes\nEXECUTE dbo.UpdateTicket @TicketID = 123,\n&#160; @Problem = 'TPS report for California not working',\n&#160; @CausedBy = 'The dbo.Customers table is empty',\n&#160; @ProposedSolution = 'Restore dbo.Customers table from\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;backup',\n&#160; @AssignedTo = 'DBA team', \n&#160; @Status = 'Opened', \n&#160; @version = @version ;\n&#160;\n-- Brian tries to save his changes\nEXECUTE dbo.UpdateTicket @TicketID = 123,\n&#160; @Problem = 'TPS report for California and Ohio not\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;working',\n&#160; @CausedBy = NULL,\n&#160;&#160;@ProposedSolution = 'Expose yesterdays'' TPS report',\n&#160; @AssignedTo = 'TPS report team', \n&#160; @Status = 'Opened', \n&#160; @version = @version ;\n&#160;\n-- Verify that Arne's changes are intact\nSELECT ProposedSolution\nFROM&#160;&#160; dbo.Tickets\nWHERE&#160; TicketID = 123;\n\nMsg 50000, Level 16, State 1, Procedure UpdateTicket, Line 28\nTicker number 123 not found or modified after it was read\nProposedSolution\n--------------------------------------------------\nRestore dbo.Customers table from backup\n<\/pre>\n<p class=\"caption\">Listing 12: Detecting and preventing lost updates with <span class=\"STCodeinTextChar\">ROWVERSION<\/span>.<\/p>\n<p>The stored procedure successfully saves Arne&#8217;s changes, because the row has not been changed between the time when he read the data into the bug tracker GUI, and the time when he updated the ticket. <\/p>\n<p>However, when we invoke the stored procedure to save Brian&#8217;s changes, our <span class=\"STCodeinTextChar\">UpdateTicket<\/span> stored procedure detects that ticket 123 has been modified since Brian initially queried the data, as indicated by the fact that the value of the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column has changed, so the attempt to save Brian&#8217;s changes fails and a lost update is averted.<\/p>\n<p>Up to now, all the cases we&#8217;ve discussed involved displaying information for the user and having the user perform some changes. Typically, in such cases, we do not keep the transaction open between the time we read a row and the time we modify it, so the only built in mechanism to detect lost updates was the <span class=\"STCodeinTextChar\">ROWVERSION<\/span>. <\/p>\n<p>If, however, the data is modified programmatically, and quickly, then we can afford to keep the transaction open between the time we read a row and the time we modify it. In such cases, we can use Snapshot isolation to detect and prevent lost updates.<\/p>\n<h3>Using Snapshot Isolation level<\/h3>\n<p>In the first example, we&#8217;ll prevent a lost update using the <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation level. Before running the example, we need to establish some test data, as shown in Listing 13.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DELETE&#160; FROM dbo.Tickets ;\n&#160;\nINSERT&#160; INTO dbo.Tickets\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( TicketID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\nVALUES&#160; ( 123 , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'TPS report for California not working' , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NULL , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Restored Customers table from backup' , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'DBA team' , \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Closed'\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ; \n<\/pre>\n<p class=\"caption\">Listing 13: Adding test data.<\/p>\n<p>Suppose that we have a process that reads tickets one-by-one, determines if they are eligible for removal from the system, and deletes those that are. Listing 14 mimics the case where this automated process has opened a transaction and read ticket number 123.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;\nSET XACT_ABORT ON ;\nBEGIN TRANSACTION ;\n&#160;\nSELECT&#160; TicketID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status\nFROM&#160;&#160;&#160; dbo.Tickets\nWHERE&#160;&#160; TicketID = 123 ; \n&#160;\n\/*\nDELETE dbo.Tickets \nWHERE&#160;&#160; TicketID = 123 ; \nCOMMIT TRANSACTION ;\n*\/\n<\/pre>\n<p class=\"caption\">Listing 14: Opening transaction and reading ticket number 123.<\/p>\n<p>At roughly the same time, another connection modifies the same ticket, as shown in Listing 15 (which should be run from a different tab).<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET NOCOUNT OFF ;\nUPDATE&#160; dbo.Tickets \nSET&#160;&#160;&#160;&#160; AssignedTo = 'ETL team' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy = 'ETL truncates Customers table' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem = 'TPS report for California not working' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution = 'Fix ETL' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status = 'Opened'\nWHERE&#160;&#160; TicketID = 123 ;\n<\/pre>\n<p class=\"caption\">Listing 15: Ticket number 123 is modified.<\/p>\n<p>Clearly the situation has changed and the ticket should not be deleted. Highlight the commented <span class=\"STCodeinTextChar\">DELETE<\/span> statement in Listing 14 and execute it. Fortunately, under <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation, the potential lost update is detected and prevented, as shown in Listing 16.<\/p>\n<pre>Msg 3960, Level 16, State 2, Line 1\nSnapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Tickets' directly or indirectly in database 'Test4' 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.\n<\/pre>\n<p class=\"caption\">Listing 16: A lost update is prevented.<\/p>\n<p>The initial transaction, to retrieve and then delete the ticket, fails. Note that when we started this transaction, we did nothing to prevent other connections from modifying the ticket. Instead, we chose to detect the potential problem and handle it. This is yet another typical example of optimistic concurrency control.<\/p>\n<p>Note that the error message explicitly suggests that we should &#8220;Retry the transaction or change the isolation level for the update\/delete statement&#8221;. However, we need to be very careful when we consider such recommendations. We need to determine which action makes sense on case- by-case basis. Here, we do not want to change the isolation level because <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation did a very good job in detecting an error that we want to avoid. Should we retry the transaction? Maybe, but not automatically: we should consider retrying the transaction only after taking into account the new changes. In this particular case, the reopened ticket 123 should stay in the system. <\/p>\n<p>As we have seen, <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation is very useful for detecting lost updates in this case. However, <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation detects lost updates only for the duration of the transaction and so using this approach will not help if the transactions do not overlap, as was the case in our first example in this article (Listings 1 to 5).<\/p>\n<p>Before moving on, please make sure that Snapshot isolation is disabled for your test database, as subsequent examples will run in normal, <span class=\"STCodeinTextChar\">READ COMMITTED<\/span> mode. <\/p>\n<h1>Pessimistic Concurrency Control to Prevent Lost Updates<\/h1>\n<p>Let&#8217;s switch our attention now to ways in which we can implement pessimistic concurrency control, to prevent lost updates. This approach is appropriate when many short transactions are attempting to simultaneously modify the same rows. We&#8217;ll discuss two approaches:<\/p>\n<ul>\n<li>Using the <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint\n<\/p>\n<\/li>\n<li>Using <span class=\"STCodeinTextChar\">sp_getapplock<\/span><\/li>\n<\/ul>\n<p>In these examples, the data is read and modified by a program, without any human interaction, and in a very short time. In such cases it is feasible to read and modify within the same transaction.<\/p>\n<p>Again, these approaches only help us deal with concurrency for the duration of a transaction; they cannot prevent lost updates when transactions do not overlap. As such, they usually should not be used when users open screen forms to edit data and save their modifications at different times, because from the database&#8217;s point of view these modifications are not concurrent.<\/p>\n<h2>Serializing updates with UPDLOCK hint<\/h2>\n<p>We&#8217;ll rerun the ticket deletion\/archive example using <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint instead of <span class=\"STCodeinTextChar\">SNAPSHOT<\/span> isolation level. First, rerun the script from Listing 13 to restore the modified data to its original state. Next, in one SSMS tab, retrieve the ticket 123 in a transaction under <span class=\"STCodeinTextChar\">READ<\/span> <span class=\"STCodeinTextChar\">COMMITTED<\/span> isolation level and using the <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint, as shown in Listing 17.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\nSET XACT_ABORT ON ;\nBEGIN TRANSACTION ;\n&#160;\nSELECT&#160; TicketID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AssignedTo ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status \nFROM&#160;&#160;&#160; dbo.Tickets WITH(UPDLOCK) \nWHERE&#160;&#160; TicketID = 123 ; \n&#160;\n--DELETE dbo.Tickets \n--WHERE&#160;&#160; TicketID = 123 ; \n--COMMIT TRANSACTION ;\n<\/pre>\n<p class=\"caption\">Listing 17. Reading ticket 123 with <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint.<\/p>\n<p>As in the previous example, modify the ticket being archived (deleted) in another tab, as per Listing 15.<\/p>\n<p>Unlike in the previous example, this time the modification does not complete; it stays in lock-waiting state, as it is blocked by our outstanding transaction in the first tab. At the beginning of the transaction in the first tab, we selected data for ticket 123, and the <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint guarantees that this data cannot be modified by other connections for the life of the transaction, though it can still be read.<\/p>\n<p>Return to the first tab and uncomment and run the <span class=\"STCodeinTextChar\">DELETE<\/span> statement, in order to delete ticket 123 and commit the transaction. The second tab will now finish too, but the row that was targeted by the <span class=\"STCodeinTextChar\">UPDATE<\/span> no longer exists, so it could not be updated.<\/p>\n<p>As we have seen, <span class=\"STCodeinTextChar\">UPDLOCK <\/span>hint has prevented the second update from modifying ticket 123. This is typical of pessimistic concurrency control solutions.<\/p>\n<p>The <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint is best-suited to cases where our modifications are simple and short. In this example, we were dealing with a single row modification, and <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hint works perfectly well. However, if we need to touch multiple rows, maybe in more than one table, and we hold locks for the duration of a long transaction, then our modifications are very prone to deadlocks (as demonstrated in <a href=\"http:\/\/www.amazon.com\/gp\/product\/1906434492\">Chapter 9 of my book<\/a>). <\/p>\n<p>The need to modify multiple rows in multiple tables in one transaction is very common. For example, saving a screen form with a customer&#8217;s order may result in inserting or updating rows in <span class=\"STCodeinTextChar\">Orders<\/span>, <span class=\"STCodeinTextChar\">OrderItems<\/span>, and <span class=\"STCodeinTextChar\">OrderComments<\/span> tables. In such cases, we can still use the locks that are implicitly acquired as the transaction progresses, and we can use <span class=\"STCodeinTextChar\">UPDLOCK<\/span> hints to get a better control over locking. This approach can work but is complex, as we often have to consider many possible combinations of different modifications, all occurring at the same time. <\/p>\n<p>There is a simpler alternative in such cases: at the very beginning of our transaction, we can explicitly acquire one application lock for the whole <span class=\"STCodeinTextChar\">Order<\/span> object, which spans several rows in the involved tables,<span class=\"STCodeinTextChar\"> Orders<\/span>, <span class=\"STCodeinTextChar\">OrderItems<\/span>, and <span class=\"STCodeinTextChar\">OrderComments<\/span>. Let&#8217;s see how it works.<\/p>\n<p class=\"STHeading2\">Using sp_getapplock to prevent collisions<\/p>\n<p>In this example, our transactions will explicitly acquire an application lock, using <span class=\"STCodeinTextChar\">sp_getapplock<\/span>. This effectively serializes modifications, because only one connection can hold an exclusive application lock on the same resource. Other modifications to the same data will be forced to wait for that lock to be released, so there will be no collisions whatsoever. This is an example of pessimistic concurrency control, used to its fullest extent.<\/p>\n<p>Note that application locks are different from other locks in that:<\/p>\n<ul>\n<li>The resource they lock is not a row or a page or a table but a name, as will be demonstrated in the following example.  <\/li>\n<li>They are acquired explicitly, rather than implicitly<\/li>\n<\/ul>\n<p>Note that when transactions commit or rollback, all application locks are released, so they must be acquired in the context of an outstanding transaction, after we have explicitly started the transaction.<\/p>\n<p>To demonstrate this approach, we first need to restore the modified data to its original state (Listing 13). Next, from one SSMS tab, begin a transaction, acquire an application lock, and start archiving the ticket 123, as shown in Listing 18.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- run this script in the first tab\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\n&#160;\nBEGIN TRANSACTION ; \n&#160;\nDECLARE @ret INT ;\nSET @ret = NULL ;\nEXEC @ret = sp_getapplock @Resource = 'TicketID = 123',\n&#160;&#160;&#160; @LockMode = 'Exclusive', @LockTimeout = 1000 ;\n&#160;\n-- sp_getapplock return code values are: \n-- &gt;= 0 (success), or &lt; 0 (failure)\nIF @ret &lt; 0 \n&#160;&#160;&#160; BEGIN;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RAISERROR('Failed to acquire lock', 16, 1) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK ;\n&#160;&#160;&#160; END ;\n&#160;\n--DELETE dbo.Tickets \n--WHERE&#160;&#160; TicketID = 123 ; \n--COMMIT TRANSACTION ;\n<\/pre>\n<p class=\"caption\">Listing 18: Begin a transaction and acquire an application lock.<\/p>\n<p>After running the script, uncomment and highlight the <span class=\"STCodeinTextChar\">DELETE<\/span> and <span class=\"STCodeinTextChar\">COMMIT<\/span> commands at the bottom, but do not execute them just yet. In a second tab, we&#8217;ll attempt to acquire an exclusive application lock and modify the same ticket, as shown in Listing 19.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- run this script in the second tab\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\n&#160;\nBEGIN TRANSACTION ; \n&#160;\nDECLARE @ret INT ;\nSET @ret = NULL ;\n&#160;\n-- The @LockTimeout setting makes sp_getapplock\n-- wait for 10 seconds for other connections \n-- to release the lock on ticket number 123\nEXEC @ret = sp_getapplock @Resource = 'TicketID = 123',\n&#160;&#160;&#160; @LockMode = 'Exclusive', @LockTimeout = 10000 ;\n&#160;\n-- sp_getapplock return code values are: \n-- &gt;= 0 (success), or &lt; 0 (failure)\nIF @ret &lt; 0 \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RAISERROR('Failed to acquire lock', 16, 1) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK ;\n&#160;&#160;&#160; END ;\nELSE \n&#160;&#160;&#160; BEGIN ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.Tickets\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; AssignedTo = 'TPS report team' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CausedBy = 'Bug in TPS report' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Problem = 'TPS report truncates\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; dbo.Customers' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProposedSolution = 'Fix TPS report' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Status = 'Reopen'\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; TicketID = 123 ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF @@ROWCOUNT = 0 \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; RAISERROR('Ticket not found', 16, 1) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;&#160;&#160;&#160;&#160; \n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT ;\n&#160;\n&#160;&#160;&#160; END ;\n<\/pre>\n<p class=\"caption\">Listing 19: Begin a transaction, attempt to acquire an application lock and modify the ticket being archived, if the application lock has been acquired<\/p>\n<p>Immediately return to the first tab and run the highlighted <span class=\"STCodeinTextChar\">DELETE<\/span> statement; this script will raise a <span class=\"STBold\">&#8216;Ticket not found<\/span>&#8216; error. If we wait longer than 10 seconds before trying to run this <span class=\"STCodeinTextChar\">DELETE<\/span>, then Listing 19 will raise a &#8216;<span class=\"STBold\">Failed to acquire lock<\/span>&#8216; error. Either way, lost updates have been prevented.<\/p>\n<p>This proves that if all modifications that wish to modify a ticket are programmed to acquire the corresponding application lock before touching it, then lost updates cannot occur. However, this approach <span class=\"STItalic\">only<\/span> works if all modifications are programmed to acquire application locks. A failure to acquire an application lock, whether by accident or deliberately, bypasses our protection, and as such may result in lost updates or other problems, such as deadlocks. <\/p>\n<p>To demonstrate this, restore the original data, comment out the command that invokes <span class=\"STCodeinTextChar\">sp_getapplock<\/span>, in Listing 19, and then rerun the same example, as follows: <\/p>\n<ul>\n<li>In Listing 18, make sure that <span class=\"STCodeinTextChar\">DELETE<\/span> and <span class=\"STCodeinTextChar\">COMMIT<\/span> command are commented out.  <\/li>\n<li>Run Listing 18  <\/li>\n<li>In a second tab run Listing 19  <\/li>\n<li>Return to Listing 19, uncomment the <\/li>\n<\/ul>\n<p><span class=\"STCodeinTextChar\">DELETE<\/span> and <span class=\"STCodeinTextChar\">COMMIT<\/span> commands at the bottom, highlight them, and execute them.<\/p>\n<p>When the <span class=\"STCodeinTextChar\">DELETE<\/span> completes, you&#8217;ll find that ticket number 123 is gone, which means that we&#8217;ve suffered a lost update. In short, <span class=\"STCodeinTextChar\">sp_getapplock<\/span> is only useful when it is consistently used by all relevant modifications. If such consistency is not possible, we will need to use other methods.<\/p>\n<h1>T-SQL Patterns that Fail High Concurrency Stress Tests<\/h1>\n<p>In many cases, our T-SQL code works perfectly well when we execute it from one connection at a time, but intermittently fails when it runs in production systems, under high concurrency. <\/p>\n<p>In this section, we&#8217;ll examine the following two common T-SQL patterns and prove that they are generally unreliable under concurrent loads:<\/p>\n<ul>\n<li>IF EXISTS(&#8230;) THEN  <\/li>\n<li>UPDATE &#8230; IF (@@ROWCOUNT = 0) BEGIN<\/li>\n<\/ul>\n<p>We&#8217;ll then examine a third technique, <span class=\"STCodeinTextChar\">MERGE<\/span>, which is robust under concurrency.<\/p>\n<p>The most important lesson to be learned is that if our code is supposed to run under high concurrency, then we need to stress test under such loads, and against realistic data volumes. If our production table has about 10M rows, we should not run our tests against a tiny table of just 100 rows.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Important Note:<\/b> If any of these scripts in this section run for too long on your server, and you cancel them, make sure to close the tabs or rollback the transactions. Otherwise, you could end up with an outstanding transaction holding locks, and subsequent examples may not work as expected. <\/p>\n<\/div>\n<h2>Problems with IF EXISTS(&#8230;) THE<\/h2>\n<p>The <span class=\"STCodeinTextChar\">IF<\/span> <span class=\"STCodeinTextChar\">EXISTS(&#8230;)<\/span> <span class=\"STCodeinTextChar\">THEN<\/span> pattern, as follows, is quite common and yet it frequently fails under high concurrency.<\/p>\n<p class=\"STCodeinText\">IF EXISTS(&#8211;enter some condition here<br \/>) BEGIN ;<br \/>&#160; &#8212; perform some action here<br \/>END ;<\/p>\n<p>Before we prove that the technique will cause our optimistic concurrency solution (using the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> column) to fail under heavy concurrent loads, let&#8217;s first examine a much simpler example, which demonstrates the general problem with this pattern.<\/p>\n<p class=\"STHeading3\">May cause Data Integrity Issues under Concurrent Access<\/p>\n<p>To keep the example as simple and short as possible, we&#8217;ll use a table with just four columns, as shown in Listing 20.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.WebPageStats\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; WebPageID INT NOT NULL PRIMARY KEY,\n&#160;&#160;&#160;&#160;&#160; NumVisits INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; NumAdClicks INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; version ROWVERSION NOT NULL\n&#160;&#160;&#160;&#160; ) ;\nGO\n&#160;\nSET NOCOUNT ON ;\nINSERT&#160; INTO dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( WebPageID, NumVisits, NumAdClicks )\nVALUES&#160; ( 0, 0, 0 ) ;\n&#160;\nDECLARE @i INT ;\nSET @i = 1 ;\nWHILE @i &lt; 1000000&#160; \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT&#160; INTO dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( WebPageID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumVisits ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumAdClicks \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; WebPageID + @i ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumVisits ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumAdClicks\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.WebPageStats ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @i = @i * 2 ;\n&#160;&#160;&#160; END ;\nGO\n<\/pre>\n<p class=\"caption\">Listing 20: Create and populate the <span class=\"STCodeinTextChar\">WebPageStats<\/span> table<\/p>\n<p>We&#8217;ll <span class=\"STCodeinTextChar\">INSERT<\/span> or <span class=\"STCodeinTextChar\">UPDATE<\/span> rows in a loop using the following simple logic, as expressed in Listing 21: if a row with given <span class=\"STCodeinTextChar\">ID<\/span> exists, update it; otherwise insert a new one. Cut and paste this code into two tabs, switch each tab into text mode, and run the code simultaneously in each tab.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- hit Ctrl+T to execute in text mode\nSET NOCOUNT ON ;\nDECLARE @WebPageID INT ,\n&#160;&#160;&#160; @MaxWebPageID INT ;\nSET @WebPageID = 0 ;\n&#160;\nSET @MaxWebPageID = ( SELECT&#160;&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 100000 ;\n&#160;\nWHILE @WebPageID &lt; @MaxWebPageID \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @WebPageID = ( SELECT&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRY ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF EXISTS ( SELECT&#160; *\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;dbo.WebPageStats --WITH(UPDLOCK)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = @WebPageID )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; NumVisits = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = @WebPageID ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT&#160; INTO dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( WebPageID, NumVisits, NumAdClicks )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; VALUES&#160; ( @WebPageID, 0, 0 ) ;\n&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END TRY\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN CATCH ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; ERROR_MESSAGE() ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END CATCH ;\n&#160;&#160;&#160; END ; \n<\/pre>\n<p class=\"caption\">Listing 21: Inserting or updating rows in a loop<\/p>\n<p>You should see <span class=\"STCodeinTextChar\">PRIMARY KEY<\/span> violations. You may be wondering if our pessimistic technique, using <span class=\"STCodeinTextChar\">UPDLOCK<\/span>, would help us out here: unfortunately it won&#8217;t. To try this out, uncomment the hint, comment out all <span class=\"STCodeinTextChar\">BEGIN<\/span>\/<span class=\"STCodeinTextChar\">COMMIT<\/span>\/<span class=\"STCodeinTextChar\">ROLLBACK<\/span> <span class=\"STCodeinTextChar\">TRANSACTION<\/span> commands, and re-run the test. You will still see PK violations. The <span class=\"STCodeinTextChar\">UPDLOCK<\/span> does not help as there is no row to be locked if the <span class=\"STCodeinTextChar\">NOT EXISTS<\/span> is true. So if both connections simultaneously check for existence of the same row, both will find it does not exist (so they won&#8217;t acquire a U lock), and both will try to <span class=\"STCodeinTextChar\">INSERT<\/span> the row, leading to the violation.<\/p>\n<p>The most important point to remember is that code that performs perfectly in single-user test cases, may behave very differently under when multiple processes are attempting to access and modify the same data. The defensive programmer must test on a case-by-case basis, and test as many different scenarios as possible. With that in mind, I encourage you to play with this simple example a little bit, exploring how small changes affect the behavior of our code under high concurrency. For example:<\/p>\n<ul>\n<li>Increase the isolation level in one or both tabs, and see how that affects the behavior.  <\/li>\n<li>Run different scripts in the tabs, such as with commented hint in one tab and uncommented in another, and see what happens.<\/li>\n<\/ul>\n<h3>May Break Optimistic Concurrency Solutions<\/h3>\n<p>Having demonstrated how unreliable the <span class=\"STCodeinTextChar\">IF EXISTS<\/span> pattern may be when it executes under high concurrency, let&#8217;s now prove that it will cause our optimistic concurrency solution to fail, under similarly high concurrency.<\/p>\n<p>We&#8217;ll develop a stored procedure to update the <span class=\"STCodeinTextChar\">WebPageStats<\/span> table and then execute it in rapid succession, from two connections. Of course, we could do the same thing with <span class=\"STCodeinTextChar\">UpdateTickets<\/span> procedure, but these examples involving loops are quite large, so I decided to use a narrower <span class=\"STCodeinTextChar\">WebPageStats<\/span> table just to keep the examples shorter.<\/p>\n<p>Listing 22 shows the <span class=\"STCodeinTextChar\">UpdateWebPageStats<\/span> stored procedure, which will detect any version mismatches when it saves changes.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE PROCEDURE dbo.UpdateWebPageStats\n&#160;&#160;&#160; @WebPageID INT ,\n&#160;&#160;&#160; @NumVisits INT ,\n&#160;&#160;&#160; @NumAdClicks INT ,\n&#160;&#160;&#160; @version ROWVERSION\nAS \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET NOCOUNT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET XACT_ABORT ON ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @ret INT ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF EXISTS ( SELECT&#160; * \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = @WebPageID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND version = @version ) \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; NumVisits = @NumVisits ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumAdClicks = @NumAdClicks\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = @WebPageID ;\n\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @ret = 0 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @ret = 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN @ret ;\n&#160;&#160;&#160; END ; \n<\/pre>\n<p class=\"caption\">Listing 22: Create the dbo.UpdateWebPageStats stored procedure<\/p>\n<p>Of course, before testing how the stored procedure works under concurrency, we should make sure that it works without it. Testing the stored procedure without concurrency is left as an exercise for the reader.<\/p>\n<p>The following two scripts will invoke the<span class=\"STCodeinTextChar\"> WebPageStats<\/span> stored procedure multiple times in loops. Running these two scripts simultaneously from two connections will expose <span class=\"STCodeinTextChar\">WebPageStats<\/span> to high concurrency, and we shall see how it holds up.<\/p>\n<p>The first script, in Listing 23, increments the column <span class=\"STCodeinTextChar\">NumVisits<\/span> for a single row, and does so 100,000 times, in a loop. Cut-and-paste this code into a tab, but do not run it yet.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @NumVisits INT ,\n&#160;&#160;&#160; @NumAdClicks INT ,\n&#160;&#160;&#160; @version ROWVERSION ,\n&#160;&#160;&#160; @count INT ,\n&#160;&#160;&#160; @ret INT ;\nSET @count = 0 ;\nWHILE @count &lt; 10000 \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @NumVisits = NumVisits + 1 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @NumAdClicks = NumAdClicks ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @version = version\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = 5 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; EXEC @ret = dbo.UpdateWebPageStats 5,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;@NumVisits, @NumAdClicks, @version ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF @ret = 0\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @count = @count + 1 ;\n&#160;&#160;&#160; END ;\n<\/pre>\n<p class=\"caption\">Listing 23: A loop that invokes <span class=\"STCodeinTextChar\">Update<\/span>WebPageStats to increment NumVisits for one and the same row 10,000 times in a loop<\/p>\n<p>Our second script, in Listing 24, increments another column, <span class=\"STCodeinTextChar\">NumAdClicks<\/span>, also 10,000 times in a loop. Cut-and-paste it into a second tab and run both scripts simultaneously.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @NumVisits INT ,\n&#160;&#160;&#160; @NumAdClicks INT ,\n&#160;&#160;&#160; @version ROWVERSION ,\n&#160;&#160;&#160; @count INT ,\n&#160;&#160;&#160; @ret INT ;\nSET @count = 0 ;\nWHILE @count &lt; 10000 \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @NumVisits = NumVisits ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @NumAdClicks = NumAdClicks + 1 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @version = version\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = 5 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; EXEC @ret = dbo.UpdateWebPageStats 5,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;@NumVisits, @NumAdClicks, @version ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF @ret = 0\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @count = @count + 1 ;\n&#160;&#160;&#160; END ;\n<\/pre>\n<p class=\"caption\">Listing 24: A loop that invokes <span class=\"STCodeinTextChar\">UpdateWebPageStats <\/span>to increment <span class=\"STCodeinTextChar\">NumAdClicks<\/span> for the same row 10,000 times in a loop<\/p>\n<p>These scripts may take some time to complete. When both scripts finish, we would expect both <span class=\"STCodeinTextChar\">NumVisits<\/span> and <span class=\"STCodeinTextChar\">NumAdClicks<\/span> to have the same value of 10,000. However, this is not the case, as Listing 25 demonstrates. Each time we run these two scripts, we will get different numbers but, every time, neither column will have the expected value of 10000.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; NumVisits ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; NumAdClicks\nFROM&#160;&#160;&#160; dbo.WebPageStats\nWHERE&#160;&#160; WebPageID = 5 ;\n&#160;\nNumVisits&#160;&#160; NumAdClicks\n----------- -----------\n9999&#160;&#160;&#160;&#160;&#160;&#160;&#160; 1056\n<\/pre>\n<p class=\"caption\">Listing 25: <span class=\"STCodeinTextChar\">NumVisits<\/span> and <span class=\"STCodeinTextChar\">NumAdClicks<\/span> should both be 10000, but they do not have the expected values<\/p>\n<p>As we can see, <span class=\"STCodeinTextChar\">NumVisits<\/span> and <span class=\"STCodeinTextChar\">NumAdClicks<\/span> do not have the expected value of 10,000. This means that many updates were lost. How could that happen? Suppose that both connections retrieve the version at approximately the same time, and then invoke the same stored procedure at approximately the same time. Clearly in both executions the condition in the <span class=\"STCodeinTextChar\">IF<\/span> statement evaluates as <span class=\"STCodeinTextChar\">TRUE<\/span>. As a result, both executions will enter the branch with the <span class=\"STCodeinTextChar\">UPDATE<\/span> command. <\/p>\n<p class=\"MsoNormal\"><span class=\"STCodeinTextChar\">UPDATE<\/span> commands will execute one after another, and the second one will overwrite the changes of the first one, because the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> value is not tested again in the actual <span class=\"STCodeinTextChar\">UPDATE<\/span> statement. Adding this test to the <span class=\"STCodeinTextChar\">UPDATE<\/span> will not help, though. If we do that, then the first one will increment the <span class=\"STCodeinTextChar\">ROWVERSION<\/span> value, and the second one will not update the row at all because the condition (<span class=\"STCodeinTextChar\">version = @version<\/span>) in the <span class=\"STCodeinTextChar\">WHERE<\/span> clause will return <span class=\"STCodeinTextChar\">FALSE<\/span>, but the procedure will still return 0 to indicate success to the caller even though the requested update was not made, and the caller will not try the update again.<\/p>\n<p class=\"STHeading2\">UPDATE &#8230; IF (@@ROWCOUNT = 0) BEGIN<\/p>\n<p>Another common approach is to attempt, first, to <span class=\"STCodeinTextChar\">UPDATE<\/span> an existing row that matches the search criteria, and if there is no matching row, then <span class=\"STCodeinTextChar\">INSERT<\/span> a new row. It is also unreliable.<\/p>\n<p>In order to demonstrate this, we need to modify our loop from Listing 21 so that it uses the <span class=\"STCodeinTextChar\">UPDATE<\/span> <span class=\"STCodeinTextChar\">&#8230;IF<\/span> <span class=\"STCodeinTextChar\">(@@ROWCOUNT<\/span> <span class=\"STCodeinTextChar\">=<\/span> <span class=\"STCodeinTextChar\">0)<\/span> <span class=\"STCodeinTextChar\">BEGIN<\/span> pattern, as shown in Listing 26.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- hit Ctrl+T to execute in text mode\nSET NOCOUNT ON ;\nDECLARE @WebPageID INT ,\n&#160;&#160;&#160; @MaxWebPageID INT ;\nSET @WebPageID = 0 ;\n&#160;\nSET @MaxWebPageID = ( SELECT&#160;&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 100000 ;\n&#160;\nWHILE @WebPageID &lt; @MaxWebPageID \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @WebPageID = ( SELECT&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRY ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; NumVisits = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; WebPageID = @WebPageID ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF ( @@ROWCOUNT = 0 ) \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT&#160; INTO dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( WebPageID, NumVisits, NumAdClicks )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; VALUES&#160; ( @WebPageID, 0, 0 ) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END TRY\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN CATCH ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; ERROR_MESSAGE() ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END CATCH ;\n&#160;&#160;&#160; END ; \n<\/pre>\n<p class=\"caption\">Listing 26: A loop that uses the <span class=\"STCodeinTextChar\">UPDATE &#8230; IF (@@ROWCOUNT = 0)<\/span> pattern<\/p>\n<p>When we run script 10-26 simultaneously from two tabs, we get <span class=\"STCodeinTextChar\">PRIMARY KEY<\/span> violations, just as when we ran script 10-21 in our previous example.<\/p>\n<p>In short, the <span class=\"STCodeinTextChar\">UPDATE&#8230;IF<\/span> <span class=\"STCodeinTextChar\">(@@ROWCOUNT = 0)<\/span> pattern is also unreliable under high concurrency. As before, we can (and should!) try out different isolation levels and hints. For example, I encourage you to add <span class=\"STCodeinTextChar\">WITH(SERIALIZABLE)<\/span> hint to the <span class=\"STCodeinTextChar\">UPDATE<\/span> command and see what happens. This is left as an advanced exercise for the readers.<\/p>\n<h1>Stress Testing the MERGE Command<\/h1>\n<p>If we are running SQL Server 2008, we can use the <span class=\"STCodeinTextChar\">MERGE<\/span> command to implement the same logic <span class=\"STItalic\">i.e.<\/span> <span class=\"STCodeinTextChar\">UPDATE<\/span> rows if they exist, otherwise <span class=\"STCodeinTextChar\">INSERT<\/span>. In the context of our loop, <span class=\"STCodeinTextChar\">MERGE<\/span> may also intermittently fail but, with the help of a hint, it always completes without a single error. Let&#8217;s modify the script 10-26 to use <span class=\"STCodeinTextChar\">MERGE<\/span> command, as shown in Listing 27.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- hit Ctrl+T to execute in text mode\nSET NOCOUNT ON ;\nDECLARE @WebPageID INT ,\n&#160;&#160;&#160; @MaxWebPageID INT ;\nSET @WebPageID = 0 ;\n&#160;\nSET @MaxWebPageID = ( SELECT&#160;&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 100000 ;\n&#160;\nWHILE @WebPageID &lt; @MaxWebPageID \n&#160;&#160;&#160; BEGIN ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @WebPageID = ( SELECT&#160;&#160; MAX(WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; dbo.WebPageStats\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) + 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRY ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; MERGE dbo.WebPageStats --WITH (HOLDLOCK)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS target\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; USING \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( SELECT&#160;&#160;&#160; @WebPageID \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) AS source ( WebPageID&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON (target.WebPageID = source.WebPageID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHEN MATCHED \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; THEN \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE SET NumVisits = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHEN NOT MATCHED \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; THEN&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT( WebPageID, NumVisits,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;NumAdClicks )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; VALUES\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( @WebPageID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 0 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 0 \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COMMIT TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END TRY\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN CATCH ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; ERROR_MESSAGE() ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROLLBACK TRANSACTION ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END CATCH ;\n&#160;&#160;&#160; END ; \n<\/pre>\n<p class=\"caption\">Listing 27: Implement our loop using the <span class=\"STCodeinTextChar\">MERGE<\/span> command<\/p>\n<p>When we run this script in two tabs at the same time, we should get <span class=\"STCodeinTextChar\">PRIMARY KEY<\/span> violations. As usual, if we cancel a query, we must make sure to commit or rollback the outstanding transaction in that tab.<\/p>\n<p>Next, uncomment the hint in both tabs and rerun the scripts; in this particular case, with the help of the <span class=\"STCodeinTextChar\">HOLDLOCK<\/span> hint, <span class=\"STCodeinTextChar\">MERGE<\/span> holds up under high concurrency perfectly well. Of course, this does not mean that we can always use this new command without stress testing. However, it means that we should at least consider using it whenever we <span class=\"STCodeinTextChar\">INSERT<\/span> or <span class=\"STCodeinTextChar\">UPDATE<\/span> under high concurrency. <\/p>\n<p>For example, we can consider rewriting our <span class=\"STCodeinTextChar\">UpdateWebPageStats<\/span> stored procedure using the <span class=\"STCodeinTextChar\">MERGE<\/span> command, as well as exposing this new version of the procedure to the same thorough testing. This is left as an advanced exercise.<\/p>\n<p>One final comment: in the examples in this article we only stress test how one stored procedure runs from multiple connections. In real life, this might not be good enough. If we have two different stored procedure modifying the same table, and if it is possible than these different modules will try to modify the same data concurrently, then we need to include such cases in our stress testing.<\/p>\n<h1>Creating New Objects may hurt Concurrency<\/h1>\n<p>In some cases, when we create an index, an indexed view, or a trigger, we may introduce serious issues, such as blocking or deadlocks. Let me provide an example of how creating an indexed view increases the probability of blocking and deadlocks. Consider the table, <span class=\"STCodeinTextChar\">ChildTable<\/span>, shown in Listing 28.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.ChildTable\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; ChildID INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; ParentID INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; Amount INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; CONSTRAINT PK_ChildTable PRIMARY KEY ( ChildID )\n&#160;&#160;&#160; ) ;\n<\/pre>\n<p class=\"caption\">Listing 28: Creating the <span class=\"STCodeinTextChar\">ChildTable<\/span> table.<\/p>\n<p>Let&#8217;s subject our table to concurrent modification. In one tab, run the script in Listing 29.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">BEGIN TRAN ;\nINSERT&#160; INTO dbo.ChildTable\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ChildID, ParentID, Amount )\nVALUES&#160; ( 1, 1, 1 ) ;\n-- ROLLBACK TRAN ;\n<\/pre>\n<p class=\"caption\">Listing 29: The modification to run in the first tab<\/p>\n<p>In the second tab, run the script in Listing 30.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">&#160;\nBEGIN TRAN ;\nINSERT&#160; INTO dbo.ChildTable\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ChildID, ParentID, Amount )\nVALUES&#160; ( 2, 1, 1 ) ;\nROLLBACK TRAN ;\n<\/pre>\n<p class=\"caption\">Listing 30: The modification to run in the second tab<\/p>\n<p>The second modification completes right away. Return to the first tab and rollback the transaction. As we have seen, these two modifications do not block each other. However, what happens if we create an indexed view, based on our table, as shown in Listing 31.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE VIEW dbo.ChildTableTotals WITH SCHEMABINDING\nAS\nSELECT ParentID, \nCOUNT_BIG(*) AS ChildRowsPerParent, \nSUM(Amount) AS SumAmount\nFROM dbo.ChildTable\nGROUP BY ParentID ;\nGO\n&#160;\nCREATE UNIQUE CLUSTERED INDEX ChildTableTotals_CI \nON dbo.ChildTableTotals(ParentID) ;\n<\/pre>\n<p class=\"caption\">Listing 31: Create the indexed view<\/p>\n<p>Rerun script 10-29 followed by 10-30. This time the script 10-30 will not complete; it will be blocked by the script 10-29, because both modifications also need to modify the same row in the indexed view, and so script 10-30 is waiting for an exclusive lock on the view. Return to the first tab and rollback or commit the transaction to release the locks, and the script 10-35 will complete right away.<\/p>\n<p>Similarly, creating new indexes or triggers may affect concurrent modifications. This means that if we stress test modules to determine how they handle concurrency, we may need to repeat stress testing when we add new indexes, indexed views, or triggers.<\/p>\n<p>Of course, not all indexed views, indexes and so on will cause such blocking, and there are no general rules, which is why I stress the need to test on a case-by-case basis. <\/p>\n<h1>Conclusion<\/h1>\n<p>We have seen that when modifications run concurrently from multiple connections, we may end up with inconsistent results or errors. &#160;We also investigated two T-SQL patterns that are in common use, and yet can fail under high concurrency, resulting either in lost updates or in blocking or deadlocks.<\/p>\n<p>We have investigated several approaches, both pessimistic and optimistic, for avoiding lost updates and, for SQL Server 2008 users, demonstrated how <span class=\"STCodeinTextChar\">MERGE<\/span> can improve the robustness of our code.<\/p>\n<p>The most important point of this article is this: our modules need to be concurrency-proof. We need to expose our modules to concurrency during stress testing, expose vulnerabilities in our code and proactively fix them.<\/p>\n<p>Hopefully, this article, like the <a href=\"http:\/\/www.amazon.com\/gp\/product\/1906434492\">entire book<\/a>, has served not only to provide several techniques that will make your code more robust, but also as an eye-opener as to just what situations your database code has to contend with, when deployed on a live, production system. I haven&#8217;t covered every possible case of what can go wrong; that would be impossible. Hopefully, however,&#160; the common cases that have been covered will prove useful in making your code more robust; when a defensive programmer becomes aware of a frailty in one case, he or she knows that very careful testing will be needed in other, similar, cases.<\/p>\n<p>More generally, however, I hope I&#8217;ve convinced you that we, as SQL Server programmers, need to be proactive and creative in our testing. After all, &#8220;<i>a hard drill makes an easy battle<\/i>&#8220;.  <\/p>\n<p class=\"note\">Alex&#8217;s book &#8216;Defensive Database Programming with SQL Server&#8217; is now available to <a href=\"http:\/\/www.amazon.com\/gp\/product\/1906434492\">buy from Amazon.<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>You can create a database under the assumption that SQL looks after all the problems of concurrency. It will probably work fine under test conditions: Then, in the production environment, it starts losing data in subtle ways that defy repetition. It is every Database Developer&#8217;s nightmare. In an excerpt from his acclaimed book, Alex explains why it happens, and how you can avoid such problems. &hellip;<\/p>\n","protected":false},"author":6776,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5119,4168,5225,5224,4933,4150,4252],"coauthors":[],"class_list":["post-919","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-alex-kuznetsov","tag-database","tag-defensive-database-programming","tag-download","tag-ebook","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/919","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/6776"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=919"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/919\/revisions"}],"predecessor-version":[{"id":40353,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/919\/revisions\/40353"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=919"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=919"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}