NHibernate allow to easy manage optimistic-locking through a where-all, where-changed or a version column.
Yesterday I saw a new way to implement versioning using NHibernate.
The behavior I showing is related to this test:
public void SaveUpdate()
{
using (ISession session = sessionFactory.OpenSession())
{
using (ITransaction transaction = session.BeginTransaction())
{
var sample = new Sample { Description = "sample" };
session.Save(sample);
sample.Description = "modified sample";
transaction.Commit();
}
}
}
Peace & Love versioning
As the POID even the version shouldn’t have business-meaning. We are using the version only to have a more efficient way of optimistic-lock; nothing more, nothing less.
So given this class
public class Sample
{
public virtual int Version { get; private set; }
public virtual string Description { get; set; }
}
the mapping is
<class name="Sample">
<id type="int">
<generator class="hilo"/>
</id>
<version name="Version" access="backfield"/>
<property name="Description"/>
</class>
and queries results of the test are
INSERT
INTO
Sample
(Version, Description, id)
VALUES
(@p0, @p1, @p2);
@p0 = 1, @p1 = 'sample', @p2 = 32768
UPDATE
Sample
SET
Version = @p0,
Description = @p1
WHERE
id = @p2
AND Version = @p3;
@p0 = 2, @p1 = 'modified sample', @p2 = 32768, @p3 = 1
You can see the optimistic-lock working in the UPDATE and in both queries the batcher is working.
Resuming: an INSERT, an UPDATE with optimistic-lock, 2 round-trips.
Mortal Combat versioning
How we can complicate the Peace&Love versioning ? In few steps.
- Remove the HighLow generator and use your best friend “identity”.
- Give a business meaning to the version as, for example, last-update-moment.
- To have a more consistent last-update-moment make it generated by MsSQL forgetting the problem MsSQL have with DateTime precision.
- Override the insert SQL, generated by NH, using a trigger (please don’t ask me why).
Now the class will look like this
public class SampleDt
{
public virtual DateTime Version { get; private set; }
public virtual string Description { get; set; }
}
and the mapping look like this
<class name="SampleDt">
<id type="int">
<generator class="identity"/>
</id>
<version name="Version" access="backfield" type="Timestamp" generated="always"/>
<property name="Description"/>
</class>
<database-object>
<create>
<![CDATA[
CREATE TRIGGER t_sampleInsert ON SampleDt
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO SampleDt (
[Version],
[Description])
SELECT
GetDate(),
[Description]
FROM inserted
SELECT scope_identity();
END
]]>
</create>
<drop>
<![CDATA[
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[t_sampleInsert]'))
DROP TRIGGER [dbo].[t_sampleInsert]
]]>
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
</database-object>
And now the result is:
INSERT
INTO
SampleDt
(Description)
VALUES
(@p0);
select
SCOPE_IDENTITY();
@p0 = 'sample'
SELECT
sampledt_.Version as Version0_
FROM
SampleDt sampledt_
WHERE
sampledt_.id=@p0;
@p0 = 1
UPDATE
SampleDt
SET
Description = @p0
WHERE
id = @p1
AND Version = @p2;
@p0 = 'modified sample', @p1 = 1, @p2 = 21/05/09 08:23:47
SELECT
sampledt_.Version as Version0_
FROM
SampleDt sampledt_
WHERE
sampledt_.id=@p0;
@p0 = 1
Resuming: an immediate INSERT outside the batcher and overriden by something outside the trace, a SELECT to retrieve the new Version, an UPDATE with optimistic-lock, a SELECT to retrieve the new Version, 4 round-trips, something more to maintain each time you need to change the class or if you want change the RDBMS (somebody should maintain the trigger) and you should pray to not have 2 updates inside 3ms (the precision of DateTime in MsSQL).
Definition
Using my father words : “L’arte di trasformare una cosa facile in una difficile attraverso un procedimento inutile.”
Translation : “The art of turning an easy thing in a difficult one, through a unnecessary procedure.”
