Single Insert Pattern Test Harness
Single Update Pattern Test Harness
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:
- Single Insert Pattern (SIP) - to always have unique rows without violating constraints
- 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:
- Is there a simple way to prevent duplicate data? Does one always have to rely on uniqueness constraints to prevent duplicates?
- 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?
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:
- 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? - 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:
UPDATE SingleUpdateTable
SET
LatestIdentifier = LatestIdentifier + @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. :) OK, lousy pun.
History
- 29-Jul-2008 - Initial release.