NHibernate 5, which is used by the NHibernate persistence starting from Version 8.1, defaults to using
DateTime2(7) T-SQL data type when accessing the database. Because the
DateTime2(7) type is more precise than the
DateTime type, this has been deemed a safe change as it does not cause any data loss. It does, however, cause issues when using
DateTime columns for optimistic concurrency enforcement.
Optimistic concurrency checking is used when the persistence needs to read a value from a database, calculate a derived value and store that derived value back in the database. To make sure such it correctly works the persister needs to ensure the value the calculation was based on has not been modified in the meantime. An example would be incrementing a counter.
Processes A and B read value
5 as the current value of the counter and both processes increment the value in memory. Then both processes update the database. Without the optimistic concurrency check, the outcome is
6 which is incorrect because it is expected to increment the value twice.
With optimistic concurrency check the processes would append
where counter = 5 clause to the update statement to ensure that the value being updated has not been modified in the meantime. One of the processes would succeed and the other would fail (no rows updated) and would have to re-try, this time reading value
6 as the current counter.
By default, NHibernate persistence uses all Saga fields for optimistic concurrency control. The generated update statement contains a
where clause with all previously read Saga property values.
DateTime columns into a .NET
DateTime type is a lossy conversion because the database type's precision is 1/300 of a second, a value that cannot be represented in the .NET type. This means that when the least significant digit of the millisecond value is not 0, the fraction part is lost. It can be observed how SQL Server rounds up the value to the 1/300 increments by executing the following code
DECLARE @datetime datetime = '2016-10-23 12:45:37.335';
SELECT @datetime AS '@datetime'
The actual number of milliseconds stored is 336 and two-thirds but when the value is read in .NET, it gets rounded up to 337.
Later, when building the update command, NHibernate passes that converted value as the
DateTime2(7) type. This means that the database understands that the value is as precise as this new type permits:
Because the value in the table is of
DateTime type the SQL Server engine up-converts it to match the more precise
DateTime2(7) in order to compare equality. Based on the new conversions rules in SQL Server 2016 the conversion yields
2016-10-23 12:45:37. which is not equal to
2016-10-23 12:45:37.. As a result, the update statement does not modify any rows, which triggers NHibernate to raise an
NHibernate. exception to indicate that the value has been changed since it has been read.
NHibernate 5 defaults to
DateTime2(7) type, all newly created saga tables will use that new type for storing .NET
DateTime values. These new sagas will work correctly with NHibernate persistence version 8.1.
All existing sagas that use
DateTime type in their tables stop working with Version 8.1 of NHibernate persistence due to the optimistic concurrency problem manifesting in
NHibernate. being thrown.
There are three upgrade paths to make these existing sagas work.
- Alter the schema to use the
- Use an explicit version column for optimistic concurrency control
- Enforce NHibernate 4 backward-compatibility mode
- Set SQL compatibility mode to SQL Server 2014
DateTime2(7) and to use an explicit version column.
Manually alter the schema of existing saga tables to use the new
DateTime2(7) data type. Such alteration can be done in-place without any data/precision loss.
ALTER TABLE [MySagaTable] ALTER COLUMN [OneOfMyColumns] DATETIME2 NULL
NHibernate does not alter columns manually.
NHibernate persistence has an option to use an explicit version column for optimistic concurrency checks (OCC) and during an update or delete, only a single
int column will be compared.
This requires adding a version property to the saga code, as well as altering the database saga table schema by adding a column.
NHibernate adds this column if the endpoint is started or created with installers enabled.
If altering the database schema is not an option, NHibernate offers a configuration switch to fall back to using the classic
DateTime data type
var nhConfiguration = new Configuration
[Environment.SqlTypesKeepDateTime] = bool.TrueString
var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
The downside of this approach is that this is a global setting and affects all tables mapped using that NHibernate configuration object.
Because the comparison in the update statement fails due to improved conversion rules of SQL Server 2016, another way to solve the problem is to switch the database compatibility mode to SQL Server 2014 (120).
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120