Ayende @ Rahien

It's a girl

NHibernate Mapping - Concurrency

NHibernate has several concurrency models that you can use:

  • None
  • Optimistic
    • Dirty
    • All
  • Versioned
    • Numeric
    • Timestamp
    • DB timestamp
  • Pessimistic

We will explore each of those in turn.

None basically means that we fall back to the transaction semantics that we use in the database. The database may throw us out, but aside from that, we don’t really care much about things.

Optimistic is more interesting. It basically states that if we detect a change in the entity, we cannot update it. Let us see a simple example of using optimistic dirty checking for changed fields only:

<class name="Person"
			 optimistic-lock="dirty"
			 dynamic-update="true"
			 table="People">

Using this with this code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Person>(1);
	person.Name = "other";
	tx.Commit();
}

Will result in:

image

Note that we have so specify dynamic-update to true. This is required because doing so will generally cause much greater number of query plan to exist in the database cache.

Setting optimistic-lock to all would result in:

image

If the update fails because the row was updated, we will get a StaleObjectException. Like all exceptions, this will make the session ineligible for use, and you would have to create a new session to handle it.

Usually a better strategy is to use an explicit version column. We can do it by specifying <version/>:

<version name="Version" column="Version"/>

And that would result in:

image

As you can probably guess, if the version doesn’t match, we will get StaleObjectException.

Instead of using numeric values, we can use a timestamp:

<version name="Version" column="Version" type="timestamp"/>

In this case, the property type should be DateTime, and the resulting SQL would be:

image

This is, of course, a less safe way of doing things, and I recommend that you would use a numeric value instead.

Another option is to use the database facilities to handle that. in MS SQL Server, this is the TimeStamp column, which is a 8 byte binary that is changed any time that the row is updated.

We do this by changing the type of the Version property to byte array, and changing the mapping to:

<version name="Version"
				 generated="always"
				 unsaved-value="null"
				 type="BinaryBlob">
	<column name="Version"
					not-null="false"
					sql-type="timestamp"/>
</version>

Executing the code listed above will result in:

image

We use the value of the timestamp to ensure that we aren’t overwriting the row data after it was changed. The database will ensure that the row timestamp will change whenever the row itself is updated. This plays well with system where you may need to update the underlying tables outside of NHibernate.

Pessimistic concurrency is also expose with NHibernate, by using the overloads that takes a LockMode. This is done in a database independent way, using each database facilities and syntax.

For example, let us example the following code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Person>(1,LockMode.Upgrade);
	person.Name = "other";
	tx.Commit();
}

This will result in the following SQL:

image

We can also issue a separate command to the database to obtain a lock on the row representing the entity:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Person>(1);
	session.Lock(person, LockMode.Upgrade);
	person.Name = "other";
	tx.Commit();
}

The Get() would generate a standard select, without the locks, but the Lock() method would generate the following SQL:

image

The behavior for conflict in this case is very simple, we wait. If we wait for too long, the timeout will expire and we will get a timeout exception, because we could not obtain the lock.

That is consistent with how we would use pessimistic concurrency elsewhere.

Comments

G.O
04/15/2009 07:49 AM by
G.O

Dude, that example with the SQL timestamp didn't work out very well ("Version='System.Byte[]')

Roger
04/15/2009 07:53 AM by
Roger

To me, there are not four but three concurrency models - optimistic, pessimistic and none. "Versioned" is just an impl detail of opt concurrency.

Ayende Rahien
04/15/2009 09:58 AM by
Ayende Rahien

G.O,

That is just how it is shown in the profiler. The database gets the actual timestamp value, which is binary.

Ayende Rahien
04/15/2009 09:59 AM by
Ayende Rahien

Roger,

You can say that, yes, but there is a distinct difference in the behavior of optimistic vs. version.

optimistic can work only on the changed fields, version always work on the full row version

Jose
04/15/2009 12:06 PM by
Jose

Thanks for this series of post. This is much betther than the nhibernate documentation.

Dermot
04/15/2009 02:46 PM by
Dermot

Jose,

don't rely on the documentation for an in depth understanding of NHibernate get the book Nhibernate in action.
www.amazon.co.uk/.../ref=sr11

David Cumps
04/16/2009 10:13 AM by
David Cumps

I tried to implement optimistic locking using the timestamp column today, but I'm having some trouble with deletes.

Before optimistic locking, I was able to simply create a new POCO object, with only the id properties filled in and then call session.Delete(theObject) and NHibernate would perform a simple "DELETE FROM .... WHERE Id = ...."

With optimistic locking enabled however, NHibernate refuses to perform a delete. It seems to only delete when I populate the Version property, but then does a "DELETE FROM ... WHERE id = ..... AND Version = ....", which ofcourse still doesn't delete me line (since I don't know the version)

Is there a way to simply delete an item regardless of whatever version is present in the database? (I wish to avoid having to roundtrip to first SELECT the object, then delete it, and doing that in a while loop in case of optimistic locking changes between the select and delete)

David Cumps
04/16/2009 10:16 AM by
David Cumps

(Also, for some reason unsaved-value="undefined" doesn't do the trick, which I thought was the idea behind undefined, to default back to my Id columns)

Ayende Rahien
04/16/2009 12:38 PM by
Ayende Rahien

David,

What you are doing is a pretty nasty trick, and as a matter of fact, it probably would stop working at some point.

The problem is that what you are doing circumvent the cascade and delete behavior that NHibernate has.

Consider it a bug that it works.

David Cumps
04/16/2009 01:11 PM by
David Cumps

I guess I'll have an elaborate look at the way NHibernate works with it's first and second level cache and how session work in detail.

It just confused me a bit I have to first go fetch something from the database before deleting it. (since in SQL you can just DELETE WHERE... and it doesn't matter if it's there or not)

Milton Pividori
04/16/2009 05:24 PM by
Milton Pividori

What if I want to save the history of the changes? So, instead of update the row, I want to insert a new row with the new data and version. NHibernate, I think, should check (like you showed in the post) that the row with the higher version (the last inserted) is 'version - 1'.

Thank you very much for this serie of posts. It's helping me a lot.

Ayende Rahien
04/16/2009 10:36 PM by
Ayende Rahien

Milton,

If that is the case, you need to do this yourself.

You can extend NHibernate to do just that , by using the listeners architecture.

Marc
04/17/2009 08:03 AM by
Marc

Hi,

thanks for this nice article.

But I'm a little confused. Also having read the NHibernate docs, I tried to use the version-element in my mapping, but its not in the schema of the version I use, which is "urn:nhibernate-mapping-2.2"/ NHibernate 2.0.1.

Are the docs not in sync with the software or am I missing sth.???

Thanks,

Marc

Ayende Rahien
04/17/2009 08:09 AM by
Ayende Rahien

Marc,

The location of the version is important, it should be just after it id.

Marc
04/20/2009 10:44 AM by
Marc

Hmm,

having added the version-tag now in the right place, I get a database error when saving a loaded object.

I manually do a saveOrUpdate on a referenced child object (a localized text entity), but having configured the version-tag for each entity (the parent and the child) the saveOrUpdate now does an insert and causes a unique key constraint violation.

I don't know, why there is this insert statement now. Either I would expect an update or a delete and a following insert. In the latter case, the insert should work because the old row with the same unique key should have been deleted.

What am i missing? How does the version affect the saveOrUpdate() method? I thought, it would just check the value of the version column and increment it upon update (if not stale).

Thanks in advance,

Marc

Ayende Rahien
04/20/2009 10:59 AM by
Ayende Rahien

Please use nhusers for support, I can't figure out what the problem is.

When you post to nhusers, please include code + mapping

Marc
04/20/2009 11:08 AM by
Marc

Ok, I could figure it out for myself.

Problem was that the version number of my entity was set to 0 and I didn't explicitely specify a "unsaved-value" which by default also was 0. Thus, NHibernate was quite right to try an insert. My fault.

Thx,

Marc

Jimit Ndiaye
04/25/2009 06:27 PM by
Jimit Ndiaye

Hi,

Thanks for this post. It's saved me a lot of headache trying to get NHibernate to play nice with MS SQL Server's timestamp data type. Unfortunately it's created new ones. While I'm now able to use SchemaExport to create my database as I want it, saving entities to the database is proving difficult as NHibernate keeps trying to update the version column which is impossible with SQL Server. I've got a question on stackoverflow describing the situation. I'd appreciate any light you could shed on it:

stackoverflow.com/.../776802#776802

Ayende Rahien
04/26/2009 07:44 AM by
Ayende Rahien

Jimit,

You need to use sql-type="timestamp", as shown in the post

Jimit Ndiaye
04/27/2009 01:01 PM by
Jimit Ndiaye

This is my version mapping:

<version
unsaved-value="null">

<column

As you can see, it's identical to what you have above. Are you using yours together with a custom IUserVersionType? I thought binary blob implemented IVersionType.

It seems that for some reason, NHibernate is interpreting the Version property as dirty immediately after reading it from the database and thus attempts to do an update.

Jimit Ndiaye
04/27/2009 01:06 PM by
Jimit Ndiaye

Sorry about that... it seems my version mapping got swallowed by the ether:

<version
<column

Ayende Rahien
04/27/2009 01:11 PM by
Ayende Rahien

Jimit,

Please use NH Users for that, and take into account that I am using the latest NHibernate

Jimit Ndiaye
04/28/2009 09:44 AM by
Jimit Ndiaye

I using NH 2.1 Alpha2. I posted to nhusers and, getting no response, tried stackoverflow and finally your blog.

Gary
05/13/2009 02:31 PM by
Gary

Is it possible to use dynamic insert /update with versioning? I'm trying to do this with Oracle and when I update a column, the generated SQL tries to update the version column (ORA_ROWSCN). I have seen a post on stackoverflow that says the same thing happens with SQL Server timestamps.

Ayende Rahien
05/13/2009 04:10 PM by
Ayende Rahien

Gary,

Try using generate="always"

JesenkoS
06/12/2009 09:14 AM by
JesenkoS

To those having issue getting SQL timestamps to work,

if you are using NHibernate.Mapping.Attributes, [Column(...)] attribute is ignored under [Version] attribute. Workaround is to use [RawXml] to specify appropriate mapping.

Not sure whether it was already fixed in latest versions of NHibernate.Mapping.Attributes though.

Comments have been closed on this topic.