Database Concurrency Patterns - SIP and SUP

• Download source code - 428 KB

Introduction

Database Concurrency, a phrase that we wish would always describe a healthy state of reality instead of a problem to solve. Two interesting database concurrency problems are addressed by patterns introduced in this article:

1. Single Insert Pattern (SIP) - to always have unique rows without violating constraints
2. Single Update Pattern (SUP) - to always have safe, custom, unique identifiers

Background

I love searching for patterns for reuse. Patterns make life easier, and allow us to solve new and interesting problems instead of revisiting old ones. I have pondered over two challenges for some time:

1. Is there a simple way to prevent duplicate data? Does one always have to rely on uniqueness constraints to prevent duplicates?
2. Custom Identifiers. The following code is not safe when used to generate a custom unique identifier. Is there a safe, simple way of doing this?
SQL

select max(identifier) from mytable

So, I thought about this for some time. Luckily, SQL2005 has some very cool functionality that makes an elegant solution to these challenges possible:

• the OUTPUT clause
• the INSTEAD OF INSERT trigger

Amazing -- the two code constructs above allow us to have so much power. The first one allows SUP to be possible. The second one allows SIP to be possible. Now, let me describe these patterns in a little more detail. Allow me to present the following two patterns:

1. Single Insert Pattern (SIP) - this guarantees that for any given set of criteria, an Insert will always correspond to a unique row in a database table (regardless of concurrent database activity). Think of it this way: instead of worrying about violating a uniqueness constraint or having duplicates, how about preventing it from happening?
2. Single Update Pattern (SUP) - this guarantees that for "a single row database table", an Update will always result in a safe unique custom identifier for us to use. I don't think I need to say any more about this one -- other than that it is pretty cool and can be extremely helpful. :)

Using the code

In order to use the code, you have to attach the SQL2005 Express database included in the attached zip file. The attached zip file contains two C# projects:

• SingleInsertPattern
• SingleUpdatePattern

The key part of SIP pattern is the following trigger:

ALTER TRIGGER .[dbo].[SingleInsertTableTrigger] 
   ON  [dbo].[SingleInsertTable] 
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF(SELECT COUNT(*) FROM SingleInsertTable) = 0
    BEGIN
        INSERT INTO SingleInsertTable
        SELECT
        Update_Time,
        Operation,
        ThreadId
        FROM inserted
    END
    ELSE
    BEGIN
        UPDATE SingleInsertTable
        SET
        Update_Time = i.Update_Time,
        Operation = i.Operation,
        ThreadId = i.ThreadId
        FROM inserted i
    END
END

The key part of the SUP pattern is the following code within the SingleUpdateTableCall Stored Procedure:

-- CLAIM SOME NUMBERS - non-reset identifier
UPDATE SingleUpdateTable
SET
LatestIdentifier = LatestIdentifier + @NumberClaimed,
LatestThread = @ThreadId,
NumberClaimed = @NumberClaimed,
ClaimDate = cast(floor(CAST(GETDATE() as float)) as datetime)
OUTPUT INSERTED.* INTO @SingleUpdateTable

-- CLAIM SOME NUMBERS - identifier is reset every day
    -- UNCOMMENT the following statement instead of using the one above
    -- to reset the unique identifier every day.
/*    UPDATE SingleUpdateTable
    SET
    LatestIdentifier = 
    case when cast(floor(CAST(GETDATE() as float)) > 
              ClaimDate then 1 else LatestIdentifier end 
    + @NumberClaimed,
    LatestThread = @ThreadId,
    NumberClaimed = @NumberClaimed,
    ClaimDate = cast(floor(CAST(GETDATE() as float)) as datetime)
    OUTPUT INSERTED.* INTO @SingleUpdateTable
*/

The included C# projects have been written as test harnesses for the patterns introduced here. For the SingleInsertPattern test harness, you have the option of specifying the following variables:

• Number of threads to create.
• Number of tests to perform per thread.

The following tabs exist in the SingleInsertPattern test harness:

• Results - Results received for inserts applied to the database.
• Database - History of inserts as stored in the database. This is useful for comparison to the results tab.

For the SingleUpdatePattern test harness, you have the option of specifying the following variables:

• Number of threads to create.
• Number of tests to perform per thread.
• Maximum number of numbers to claim - this allows us to simulate a variable number of identifiers to use per test.

The following tabs exist in the SingleUpdatePattern test harness:

• Results - These are the results received from the database.
• Database - This is a history of unique identifier update activity.
• Actions - This is a history of the actual unique identifiers generated as stored in the database. This is useful for comparison to results received during unique identifier generation.

That's it - I hope you find these patterns as useful as I have.

Points of interest

The danger of being trigger happy.... During the time when I was first writing this article, I had the opportunity to fully experience the danger of being "trigger happy". I inherited some production code that had a trigger on a table (performing fine). This table also had a trigger on that table's history table which was slowing down inserts to the main table because it was using full table scans. This took some time to track down. A SQL Profiler trace helped us spot the problem late in the evening on Friday the 13^th of all days. Since SIP uses triggers ... it made me think that if you use SIP, you should SIP efficiently. :) OK, lousy pun.

History

• 29-Jul-2008 - Initial release.