C# Transactions - best performance

Question

My webapplication has a class WorkItem with a RecordID (Guid as Primary Key) and a FriendlyID (string) that consists of Type-Date-RandomNumbers.

If I create a new WorkItem, I create a new FriendlyID as well.
The format of the FriendlyID cannot be changed (client specification) and is like <Type (one char)>-<Current Date (yyymmdd)>-<6 random numbers>.

private string GenerateFriendlyID()
{
    string res = String.Empty;
    // code omited
    // ...
    // IT'S NOT THE QUESTION HOW TO PROGRAM THIS METHOD!
    // It's about the fastest and best way/design to make 
    // sure the generated ID is unique! (see below)
    return res; // sth like "K-20110930-158349"
}

public override void Create()
{
    if (String.IsNullOrEmpty(friendlyID))
    {
        GenerateFriendlyID();
    }
    base.Create();
}

This code does fails under heavy load, so I get the same FriendlyIDs multiple times.
What is the best way to make sure that my friendly ID is unique?

1. Make a UNIQUE-Constraint on FriendlyID in the DB.
   
   • Begin a transaction, generate a FriendlyID, insert and commit
   • Rollback and try again if I get a SQLException.
2. Just create it.
   • Select all WorkItems with this.FriendlyID.
   • If selection is > 1, repeat until it's == 1

I'm sure there is another way, but I guess #1 should be the preferred.

Are there any ways I'm missing or is #1 the way to go? I hate to use Exceptions for my workflow though and I know that they're really slow.

Answer 1

Use the KeyGenerator pattern from PoEAA by M.Fowler. Here is a sample for a file system solution and it uses a mutex for cross process locking. In a case of MS SQL you can use a transaction instead of mutex.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.IO;
using System.Runtime.CompilerServices;

namespace ConsoleApplication1
{
    public class KeyGenerator
    {
        private string FileName;
        private long IncrementedBy;
        private long NextId;
        private long MaxId; 

        public KeyGenerator(string filename, long incrementedby)
        {
            FileName = filename;
            IncrementedBy = incrementedby;
            NextId = MaxId = 0; 
        }


        //[MethodImpl(MethodImplOptions.Synchronized)]
        public long NextID()
        {


            if (NextId == MaxId)
            {
                reserveIds();
            }

            return NextId++; 

        }

        private void reserveIds()
        {

            Mutex m = new Mutex(false, "Mutex " + FileName.Replace(Path.DirectorySeparatorChar, '_'));
            try
            {
                m.WaitOne();
                string s = File.ReadAllText(FileName);
                long newNextId = long.Parse(s);
                long newMaxId = newNextId + IncrementedBy; 
                File.WriteAllText(FileName, newMaxId.ToString());
                NextId = newNextId;
                MaxId = newMaxId;
                // Simulate some work.
                Thread.Sleep(500);

            }
            finally
            {
                m.ReleaseMutex();
            }
        }

    }

}

Answer 2

My suggestion is in any case, whatever kind of id you want to generate, do this in the SQL in a stored procedure and not from .NET client code. It is always better to have an atomic entry point which takes some parameters and does the job, so you can call the stored and get your record saved and the id back to you as out parameter, even more than one, like a unique code and a guid.

in this way, then, you move concurrency issues from the .NET client code to the Database Server and db servers are designed to handle concurrency well.

Answer 3

As your RecordID is already based on a GUID, I'd parse that to create the friendly ID. Guid.ToByteArray() may be a useful place to start.