NHibernate HI-LO Generator for non-primary keyed columns
GUID’s are nice, they allow us to create objects on the client and persist at will, they guarantee a globally unique number and allow us to never have to worry about identity columns again. But what happens when we need to communicate with legacy systems requiring long integer values and not Guids?
Currently, on the production code that I am working on, we have just that problem. For all intents and purposes, we use GUIDs as our object identifiers and primary keys in our databases. However, our system needs to send data to a 3rd party, and many of those 3rd parties cannot accept GUIDs as identifiers in their aging systems.
What about Identity columns? In our situation, our entire transaction needed to be atomic, from object creation, to persistence in our system, to persistence in the 3rd party system. Identity values are only returned when the transaction is committed. This simply would not work for us…we needed a more robust and scalable solution.
We knew the solution would be a Hi-Lo algorithm of some sort. NHibernate has a nice HI-LO algorithm built in, however this is only allowed to be used on ID columns, so we decided to roll our own.
The way it works:
- Grabs the current high value from the DB
- Check and see if the current low > max low, and if it is, create a new High value in the DB, and set current low to 1
- Increment current low by 1, and add it to Current Hi * Max Hi and return the resulting value as the unique key.
What this does is allow for an infinite number of instances of your application to be running at once. Each application instance stores the high and low values in a singleton is guaranteed to have a unique high value due to SQL table locking.
Database Schema: Table – UniqueKey
CS Class File
public class HiLoUniqueKeyGenerator : IUniqueKeyGenerator {
private static Dictionary<string, long> currentHi = new Dictionary<string, long>();
private static Dictionary<string, long> currentLo = new Dictionary<string, long>();
private static object loLock = new object();
// WARNING: Never change this number to be lower than the current value. It will significantly impact the generation of IDs!!!
// PLEASE THINK CAREFULLY BEFORE CHANGING THIS NUMBER.
private const int maxLo = 1000;
private readonly ISessionFactory sessionFactory;
private IDbConnection dbConnection;
private IStatelessSession session;
public HiLoUniqueKeyGenerator(ISessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
session = sessionFactory.OpenStatelessSession();
dbConnection = session.Connection;
}
public long NextKey(string keyName) {
lock (loLock) {
if (NeedsNextHi(keyName))
GetNextHi(keyName);
return (CurrentHi(keyName) * maxLo) + NextLo(keyName);
}
}
private long CurrentHi(string keyName) {
return currentHi[keyName];
}
private long NextLo(string keyName) {
var lo = currentLo[keyName];
currentLo[keyName] = lo + 1;
return lo;
}
private bool NeedsNextHi(string keyName) {
if (!currentHi.ContainsKey(keyName))
return true;
if (!currentLo.ContainsKey(keyName))
return true;
return currentLo[keyName] > maxLo;
}
private void GetNextHi(string keyName) {
// Hardcoded SQL to ensure that a
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
using (var transaction = dbConnection.BeginTransaction(IsolationLevel.RepeatableRead)) {
object value = null;
using (var selectCurrentValue = dbConnection.CreateCommand()) {
selectCurrentValue.Transaction = transaction;
selectCurrentValue.CommandText = "SELECT next_hi FROM UniqueKey WITH (ROWLOCK, XLOCK) WHERE key_name = '" + keyName.ToString() + "'";
value = selectCurrentValue.ExecuteScalar();
}
using (var insertNextValue = dbConnection.CreateCommand()) {
insertNextValue.Transaction = transaction;
if (value == null || value == DBNull.Value) {
currentHi[keyName] = 0;
insertNextValue.CommandText = "INSERT UniqueKey (key_name, next_hi) VALUES ('" + keyName.ToString() + "', " + (currentHi[keyName] + 1).ToString() + ")";
} else {
currentHi[keyName] = (long)value;
insertNextValue.CommandText = "UPDATE UniqueKey SET next_hi = " + (currentHi[keyName] + 1).ToString() + " WHERE key_name = '" + keyName.ToString() + "'";
}
insertNextValue.ExecuteNonQuery();
}
transaction.Commit();
}
currentLo[keyName] = 1;
}
}
This code is very efficient on the database as it only has to hit the DB at application startup and after every 1000 id’s that have been generated.
Singleton Container
We now need a container to hold the instance of the HiLoUniqueKeyGenerator and that class must be initialized once when the NHibernateSessionFactory has been initialized. Here is my class for the singleton.
public class UniqueKeyGenerator {
private static IUniqueKeyGenerator defaultInstance;
public static IUniqueKeyGenerator Default {
get {
if (defaultInstance == null)
throw new InvalidOperationException("Default instance of UniqueKeyGenerator has not been initialized.");
return defaultInstance;
}
}
public static void Initialize(IUniqueKeyGenerator defaultInstance) {
if (UniqueKeyGenerator.defaultInstance != null)
throw new InvalidOperationException("Default instance has already been initialized.");
UniqueKeyGenerator.defaultInstance = defaultInstance;
}
}
public interface IUniqueKeyGenerator {
long NextKey(string keyName);
}
Now all we have to do is initialize it with NHibernate and it is ready to use:
UniqueKeyGenerator.Initialize(
new HiLoUniqueKeyGenerator(NHibernateSessionFactory));
Using the UniqueKeyGenerator
myObject.MyProperty = UniqueKeyGenerator
.Default.NextKey("myObject.myProperty");
Discussion
No responses to "NHibernate HI-LO Generator for non-primary keyed columns"
There are no comments yet, add one below.
Leave a Comment