How to migrate primary key generation from "increment" to "hi-lo"?

Question

I'm working with a moderate sized SQL Server 2008 database (around 120 tables, backups are around 4GB compressed) where all the table primary keys are declared as simple int columns.

At present, primary key values are generated by NHibernate with the increment identity generator, which has worked well thus far, but precludes moving to a multiprocessing environment.

Load on the system is growing, so I'm evaluating the work required to allow the use of multiple servers accessing a common database backend.

Transitioning to the hi-lo generator seems to be the best way forward, but I can't find a lot of detail about how such a migration would work.

Will NHibernate automatically create rows in the hi-lo table for me, or do I need to script these manually?

If NHibernate does insert rows automatically, does it properly take account of existing key values?

If NHibernate does take care of thing automatically, that's great. If not, are there any tools to help?

Update

NHibernate's increment identifier generator works entirely in-memory. It's seeded by selecting the maximum value of used identifiers from the table, but from that point on allocates new values by a simple increment, without reference back to the underlying database table. If any other process adds rows to the table, you end up with primary key collisions. You can run multiple threads within the one process just fine, but you can't run multiple processes.

For comparison, the NHibernate identity generator works by configuring the database tables with identity columns, putting control over primary key generation in the hands of the database. This works well, but compromises the unit of work pattern.

The hi-lo algorithm sits inbetween these - generation of primary keys is coordinated through the database, allowing for multiprocessing, but actual allocation can occur entirely in memory, avoiding problems with the unit of work pattern.

Answer 1

Apart from the traditional HiLo you may also want to look into the new enhanced id generators. These can use a table (or sequence if the database support that) similar in spirit to the way HiLo works, but with builting support for separate number series for different entities (if you want). With the enhanced id generators you also have the option of using either the HiLo algoritm, or a pooled algorithm. The benefit of "pooled" is that the id generator table shows the actual value, not just a part of it.

These are new in NHibernate 3.3. The reference documentation doesn't mention them yet, but the Hibernate documentation do. They work the same in NHibernate.

Answer 2

I prefer using HILO as it does not break UOW and allows me to send multiple insert statements to the server.

Now for your questions:-

Will NHibernate automatically create rows in the hi-lo table for me, or do I need to script these manually?

You will need to create your hilo table, hilo comes in two flavours, a single number across all your tables or a number for any of your tables. I prefer the latter.

If NHibernate does insert rows automatically, does it properly take account of existing key values?

You will need to set the maxhi/maxlo manually, the lo is in the mappings and the hi is in the table, so you will need to change your XML mappings to :-

<id name="Id" column="Id" unsaved-value="0">
    <generator class="hilo">
        <param name="column">NextHi</param>
        <param name="where">TableName='CmsLogin'</param>
        <param name="max_lo">100</param>
    </generator>
</id>

The following SQL can then be generated (by hand):-

CREATE TABLE hibernate_unique_key (
  TableName varchar(25) NOT NULL,
  NextHi bigint NOT NULL
)

then add a row into the database for every table you wish to use the hilo for: e.g.

CmsLogin,123
Address, 456

Note the 123 here would start my next insert id to (123 x 100) = 12300 therefore as long as 12300 is bigger than my current identity then all should be good!

and if you dont like the default table name hibernate_unique_key then you can throw this into the mix

<param name="table">HiloValues</param>

Answer 3

To use the hi-lo generator you will need to create the lookup table that will store the next value for the "Hi" part of the generated keys. You have the choice of creating a separate column for each entity table, a single column that will be used by all entities, or a combination of the two options.

If a shared column is used then each generated key will only be used by a single entity. This may be preferable if there are many entity tables, but it reduces the total number of Ids that can be generated.

For example, our project uses a HiLoLookup table with three columns:

NextEntityId BIGINT NOT NULL,
NextAuthenticationLogId BIGINT NOT NULL,
NextConfigurationLogId BIGINT NOT NULL

The log tables have a high-volume of inserts, so have been given a separate pool of Hi values. The primary key columns of our regular entity tables use the 64-bit BIGINT data type so there's is no danger of overflowing even if there are large gaps in the sequence of ids. A shared pool of ids is used to reduce administration overhead.

The hi-lo generator doesn't have built-in support for initializing itself with starting values that don't conflict with existing keys - so this will need to be performed manually. The value to use as the starting "hi" value depends on several considerations:

• The maximum existing id value - the generated ids will need to be higher than this to avoid duplicates
• How many ids should be generated before requesting a new "hi" value from the db (max_lo) - a bigger value improves concurrency but increases the potential for ids to be wasted, especially if the service is restarted frequently

The max_lo value that is provided in your entity mappings is critical when determining what your starting 'hi' values should be. For example, consider a table with a maximum existing id value of 12345. The number of ids that should be generated before going back to the database is 1000. In this case, the starting hi value should be (12345 / 1000) + 1 = 13, the first generated id will be 13000. Due to a quirk in the HiLoGenerator implementation, the max_lo value provided in the entity configuration needs to be 999, not 1000.

If using .hbm mappings:

<generator class="hilo">
    <param name="table">dbo.HiLoLookup</param>
    <param name="column">NextEntityId</param>
    <param name="max_lo">999</param>
</generator>