The object-relational mapper for .NET

How to

This page is converted from the old nhforge.org Wiki. First published by: JuanCho on 04-29-2009, Last revision by: JuanCho on 06-03-2009

NHibernate concurrency with Oracle's ORA_ROWSCN

I'm just starting to get my feet wet with NHibernate and one of the topics that popup here at the bar (you are thinking about NH when you have a couple of cold ones, aren't you? [B] ) was if we could use something like sql server's timestamp column to handle concurrency with NH and Oracle.

SQL Server's Timestamp column is not datetime as you might think, but an 8 byte binary that is changed any time that the row is updated.

Back at the office, I started researching and it figures out that Oracle (v9 or greater) provides its own flavor of this feature: welcome to ORA_ROWSCN.

Same concept, row gets updated, ORA_ROWSCN gets updated. Just make sure your tables are created with ROWDEPENDENCIES to guarantee a unique value per row, otherwise, it is possible more than one row will have the same ORA_ROWSCN.

The idea is to leave the handling of this row version column to the DBMS instead of coding around via a trigger, sequences, custom column or having nhibernate resposible for increasing the value once it is done with it. As a bonus, external processes will be able to interact with the data and NH will be aware of any changes.

This is a lot of prose for such little code, below are the configuration entries that your hbm files should have in order to use the version feature with NH.

NH 2.0:

<version name="_version" type="Int64" generated="always" column="ORA_ROWSCN" access="field" unsaved-value="null"/><o:p></o:p>


NH 2.1 (above syntax seems to also work with NH 2.1, this is just a variation)

<version name="_version" type="Int64" generated="always" access="field" unsaved-value="null"><o:p></o:p>

    <column name="ORA_ROWSCN" sql-type="Int64" /><o:p></o:p>


I hope this saves someone, a couple hours of googling.


NOTE: The usage of ORA_ROWSCN will incur on an extra db call (SELECT ORA_ROWSCN...) during INSERTS/UPDATES since its value is updated on COMMIT. In Nhibernate, this means that after the entity was modified, the version property won't have a valid value until a Flush() has been issued and a Refresh() has been performed.

NH does a Refresh(), but it seems to do it during the Flush()  and before the COMMIT, forcing to explicitly call a Refresh() afterward.


Some references:

NHibernate Mapping - Concurrency

"ORA_ROWSCN strange behavior", version 10.2.0

Oracle10g Row Timestamp

ORA_ROWSCN behavior: bug or feature?

© NHibernate Community 2024