Click here to Skip to main content
Click here to Skip to main content
Go to top

Database Concurrency Patterns - SIP and SUP

, 29 Jul 2008
Rate this:
Please Sign up or sign in to vote.
Take a look at two new patterns to help with database concurrency: SIP and SUP.
Single Insert Pattern Test Harness

SingleInsertPattern.JPG

Single Update Pattern Test Harness

SingleUpdatePattern.JPG

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?
  3. 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. Smile | :)

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 13th of all days. Since SIP uses triggers ... it made me think that if you use SIP, you should SIP efficiently. Smile | :) OK, lousy pun.

History

  • 29-Jul-2008 - Initial release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

George Zabanah
Architect
Australia Australia
George Zabanah has been architecting and developing solutions commercially from start to finish for over 11 years. He has been programming since he was 11 (for over 25 years now!) using many different technologies (almost all Microsoft). George is proficient in many languages and employs best practices wherever possible using design patterns, .NET, XSLT, XML, Regular Expressions, various flavours of SQL (to name just a few). His favourite tools, however, are the whiteboard and Sparx Enterprise Architect/Powerpoint. Many waking moments have been spent by George thinking about solutions in his head (even while watching tv). His best moments are the "Eureka" moments when he wakes up from a good sleep (or after watching tv) to find that his latest challenge has been solved!

Comments and Discussions

 
General[Message Deleted] Pinmemberit.ragester28-Mar-09 5:32 
[Message Deleted]

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 29 Jul 2008
Article Copyright 2008 by George Zabanah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid