Conditional INSERT/UPDATE Race Condition

Conditional INSERT/UPDATE Race Condition

 

 

I often see conditional INSERT/UPDATE code like:

CREATE PROCEDURE dbo.Insert_Or_Update_Foo

      @ID int,

      @Bar int

AS

 

SET NOCOUNT ON

 

IF EXISTS(SELECT * FROM dbo.Foo WHERE ID = @ID)

BEGIN

      UPDATE dbo.Foo

      SET bar = @bar

      WHERE ID = @ID

END

ELSE

BEGIN

      INSERT INTO dbo.Foo (ID, Bar)

      VALUES (@ID, @Bar)

END

 

RETURN @@ERROR

 

Despite its prevalence, this “UPSERT” code is fundamentally flawed because it assumes only a single user will execute the proc at a time.  A primary key violation error can occur when executed simultaneously on different connections with the same @ID value instead of the intended UPDATE. 

An even worse situation is when a surrogate key (e.g. IDENTITY) is used as the primary key and the conditional insert is based on a natural key that has no unique constraint or unique index.  In that case, the both INSERTs will succeed but duplicate data (same natural key) will be inserted.  A unique constraint (or unique index) should of course be specified on every key but that’s sometimes overlooked.

You can test the concurrency problem yourself by creating the following table for use with the above stored procedure on a multi-processor box:

CREATE TABLE dbo.Foo

(

      ID int NOT NULL

            CONSTRAINT PK_Foo PRIMARY KEY,

      Bar int NOT NULL

)

 

Then run the following script on different database connections after changing the WAITFOR TIME to the near future:

WAITFOR TIME ’08:00:00′

 

EXEC dbo.Insert_Or_Update_Foo

      @ID = 1,

      @Bar = 1

 

I ran this test and got a primary key violation because parallel execution of the IF EXISTS test returned false on both connections so the INSERT was attempted on both. 

Fortunately, there’s a simple solution to prevent the error:  1) add an explicit transaction and 2) specify SELECT locking hints.  Below is a modified version of the original stored procedure with these changes:

CREATE PROCEDURE dbo.Insert_Or_Update_Foo

      @ID int,

      @Bar int

AS

 

SET NOCOUNT, XACT_ABORT ON

 

BEGIN TRAN

 

IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE ID = @ID)

BEGIN

      UPDATE dbo.Foo

      SET bar = @bar

      WHERE ID = @ID

END

ELSE

BEGIN

      INSERT INTO dbo.Foo (ID, Bar)

      VALUES (@ID, @Bar)

END

 

COMMIT

 

RETURN @@ERROR

 

The UPDLOCK hint instructs SQL Server to use an update lock instead of the shared lock that would normally be acquired for the SELECT.  HOLDLOCK is needed in the default READ COMMITTED isolation level to ensure that the lock is held until the end of the transaction.  This more restrictive update lock will prevent simultaneous queries from either selecting or changing the locked resource.  If the row exists during the SELECT, the locked resource is the existing row.  If no row exists with the specified key, a range lock on the primary key is acquired to prevent inserts of the same key until the lock is released. 

Although not actually required, I also added SET XACT_ABORT ON to help ensure the explicit transaction is closed following a timeout or unexpected error.  See Use Caution with Explicit Transactions in Stored Procedures to see why I recommend this as a standard practice.

I should add that this explicit transaction and locking hints technique will prevent the above mentioned problems but it doesn’t technically prevent a race condition.  The issue with any “UPSERT”/MERGE technique is that the last one in the race wins.  If you run the above proc simultaneously on different connections with the same @ID value but different @Bar values, the last Bar value UPDATE will of course overwrite any previous Bar value.

The issue doesn’t apply only to IF statements.  Even intra-query subqueries like the example below can fall victim to the issue.  Just like IF EXISTS, there is nothing to prevent the same concurrency problem when the NOT EXISTS predicate is evaluated simultaneously on different connections.

CREATE PROCEDURE dbo.Insert_Foo

      @ID int,

      @Bar int

AS

 

SET NOCOUNT ON

 

INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)

WHERE NOT EXISTS

      (

      SELECT *

      FROM dbo.Foo

      WHERE ID = @ID

      )

 

RETURN @@ERROR

 

Below is the modified proc with the transaction and locking hint changes:

CREATE PROCEDURE dbo.Insert_Foo

      @ID int,

      @Bar int

AS

 

SET NOCOUNT, XACT_ABORT ON

 

BEGIN TRAN

 

INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)

WHERE NOT EXISTS

      (

      SELECT *

      FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK)

      WHERE ID = @ID

      )

 

COMMIT

 

RETURN @@ERROR

 

I’m not certain if the SQL 2008 MERGE statement suffers from the same concurrency issues since I don’t currently have an adequate (multi-processor) SQL 2008 test environment for the test.  I plan to install the next SQL 2008 CTP on a real (not virtual) machine and test the proc below.  I’ll post the results.

MERGE Stored Procedure

Use Caution with Explicit Transactions in Stored Procedures

Use Caution with Explicit Transactions in Stored Procedures

Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity.  However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used.  I’ll describe how to protect your application from problems following timeouts and other unexpected errors.

Consider the following stored procedure containing an explicit transaction:

CREATE PROCEDURE dbo.UpdateWithExplicitTransaction
    @MyKey int,
    @MyColumnValue int
AS

DECLARE @Error int

BEGIN TRAN

UPDATE dbo.Foo
SET MyColumn = @MyColumnValue
WHERE MyKey = @MyKey

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
    GOTO Done
END

UPDATE dbo.Bar
SET MyColumn = @MyColumnValue
WHERE MyKey = @MyKey

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
    GOTO Done
END

COMMIT

Done:

IF @Error <> 0
BEGIN
    ROLLBACK
END

RETURN @Error

You execute the script below from SQL Server Management Studio or Query Analyzer with the query timeout option set to 30 seconds and the second UPDATE statement in the proc times out. 

timeout option set to 30 seconds and the second UPDATE statement in the proc times out.

EXEC dbo.UpdateWithExplicitTransaction
PRINT ‘execution completed’
GO
SELECT
    @@ERROR AS [@@ERROR],
    @@TRANCOUNT AS [@@TRANCOUNT]
GO

Assuming default session settings, check all that apply:

  1. Proc execution continues after the failed UPDATE
  2. @@ERROR is zero
  3. @@TRANCOUNT is zero
  4. The PRINT statement is executed

Let me first mention something important about timeouts before I provide the correct answer(s).  A command timeout occurs in the client application, not the SQL Server backend.  A timeout is basically just a cancel request that is sent by the client API when a command executes longer than the specified interval.  A timeout is very much like pressing the stop button in Query Analyzer or Management Studio because you feel a query has been running too long.  The only difference is that the stop is issued by the client API on behalf of the application.

Both “A” (proc continues) and “D” (PRINT executes) are false because the attention event from the client instructed SQL Server to cancel the currently executing batch in its entirety.  No code after the UPDATE executes, including the PRINT statement following the stored procedure execute.  This is logical since a query cancel or timeout wouldn’t be much use if SQL Server continued executing statements afterward.

“B” (zero @@ERROR) is true.  @@ERROR is zero because no error occurred on the backed; SQL Server successfully canceled the batch per the client cancel request after the timeout.  The timeout error is raised only on the client by the API to notify the application (SSMS/QA in this example) that the command timed out.  SSMS and QA simply catch the error and display the error message from the API.

“C” (zero @@TRANCOINT) is false because XACT_ABORT OFF is the default session setting.  With XACT_ABORT OFF, it is the client application’s responsibility to trap the timeout error and rollback the transaction if necessary.  The transaction is left open and uncommitted following the timeout error.  This can have serious and undesirable consequences if the application performs other work on the connection, unaware of the open transaction.

Using SET XACT_ABORT

SET XACT_ABORT specifies what action SQL Server should take following run-time errors.  The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues.  Depending on the severity of the error, the entire transaction may be rolled back and batch aborted, even with SET XACT_ABORT is OFF.   

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout.  To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.  

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool.  This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.  Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT. 

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases.  I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout.   I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly.  The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.  The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SQL Server error handling in general is a huge topic I focused on only on timeout errors and SET XACT_ABORT here.  For a thorough discussion of SQL Server error handling, I suggest perusing articles Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background by SQL Server MVP Erland Sommarskog.

SQL Trace Parameter values are not always as they seem

I stumbled across surprising SQL Trace/Profiler behavior I think is worth mentioning.  Parameter values reported in trace RPC starting/completed events are not the values that SQL Server uses to execute the query.  Here’s an example I recently discovered that shows this behavior.

I ran the following C# code to execute parameterized query “SELECT @DateTime” with the parameter value set to October 11, 2007.  The console message verified that SQL Server returned the expected date.

 C# Code 

Here is the SQL Profiler trace of the of the SQL:BatchCompleted and RPC:Completed events:

Appliction SQL Trace

I pasted the script from the trace and ran it from a SQL Server Management Studio query window.  Here’s the trace of the SSMS script execution:

SSMS SQL Trace

Even though the SQL looks the same, the SSMS query returned a different date (“November 10, 2007”) than the application (“October 11, 2007”)!  What’s up with that?  Why would the same script return different values when run from application code vs. Management Studio (or Query Analyzer), even with identical session settings?

The reason for the behavior difference is the trace event class.  The application trace showed an RPC:Completed event but the SSMS session trace showed SQL:BatchCompleted.  Both had the same “exec sp_executesql…” statement in the TextData.  Even though the trace reported the same TextData for both events, SQL Server processed the RPC differently than the SQL batch and the trace didn’t show the complete story for the RPC event.

The issue is that the datetime input parameter value for the RPC event was not really included in the SQL statement like the trace RPC:Completed event showed.  The actual datetime parameter value that SQL Server used during RPC execution was passed in native (i.e. binary) format via the low-level TDS protocol.  The trace TextData was only a reverse-engineered string representation of that value rather than the actual value SQL Server used.

In contrast, the batch sent by SSMS wasn’t parameterized so SQL Server needed to parse the datetime string value in the batch text.  The datetime string “2007-10-11 00:00:00:000” (generated by the trace, not passed by the app) is ambiguous and interpreted differently depending on the DATEFORMAT setting.  Due to the “DATEFORMAT DMY” setting in the script, the date string value was (mis)interpreted as November 10, 2007 instead of October 11, 2007.  The DATEFORMAT setting had no affect on the RPC (parameterized value) because the setting affects only string parsing and not the native datetime value provided by the application.

I discovered this issue when I was helping a user who was working with a French language SQL Server (that’s why I specified DATEFORMAT DMY) troubleshoot a datetime parameter problem.  The trace data led me down the wrong path because it didn’t immediately click with me that 1) DATEFORMAT doesn’t affect RPC datetime parameters and 2) trace RPC TextData isn’t used for query execution anyway. 

I filed Connect feedback on this to suggest that SQL Trace/Profiler be changed to serialize RPC datetime values in a DATEFORMAT neutral format like “2007-10-11T00:00:00:000” or “20071011 00:00:00:000”.  This will provide the same behavior when the trace TextData SQL is executed as a batch and be a bit more intuitive when analyzing trace data.