In a system with several users we must ensure that they modify shared data without any conflicts. This is called concurrency control and is traditionally handled by locking mechanisms. ANSI SQL defines a set of problems that can occur in a system without any concurrency control, and different levels of isolations that prevents the problems. The more strict isolation level, the more problems are avoided.
The following table summarizes different concurrency problems defined by ANSI SQL.
|Lost Update||Two transactions both update a row, and then the second transaction aborts, causing both changes to be lost.|
|Dirty Read||One transaction reads changes made by another transaction that hasn’t yet been committed. This is dangerous, because those changes may later be rolled back.|
|Unrepeatable Read||A transaction reads a row twice and reads different state each time. For example, another transaction may have written to the row, and committed between the two reads.|
|Second lost update problem||Two concurrent transactions both read a row, one writes to it and commits, and then the second writes to it and commits. The changes made by the first writer are lost.|
|Phantom Read||A transaction executes a query twice, and the second result set includes rows that weren’t visible in the first result set. This situation is caused by another transaction inserting new rows between the execution of the two queries.|
The following table summarizes different isolation levels defined by ANSI SQL.
|Read Uncommitted||Permits dirty reads but not lost updates. One transaction may not write to a row if another uncommitted transaction has already written to it. But any transaction may read any row.|
|Read Committed||Permits unrepeatable reads but not dirty reads. Reading transactions don’t block other transactions from accessing a row. But an uncommitted writing transaction blocks all other transactions from accessing the row.|
|Repeatable Read||Permits neither unrepeatable reads nor dirty reads. Phantom reads may occur. Reading transactions block writing transactions (but not other reading transactions), and writing transactions block all other transactions.|
|Serializable||Provides the strictest transaction isolation. It emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently.|
SQL server supports all ANSI SQL isolation levels. The default isolation level for SQL Server is usually Read Committed. Which level of isolation to select depends on the particular requirements of the system. In most scenarios Read Commited provides to low isolation which results in corrupt data and should be avoided. Serializable is the most strict form but result in very high performance penalty and should also be avoided.
|Lost Update||Dirty Read||Unrepeatable Read||Second Lost Problem||Phantom Read|
In a system with transactions spanning over a user interaction, the locking mechanisms in the database doesn’t scale very well. In those cases, NHibernate includes an additional locking mechanism handled by the application tier called optimistic locking. In optimistic locking the system assumes that current transactions modification will not conflict with another transaction. If it does, it will be rolled back and the user has to try again. In optimistic locking, the data must be versioned and preferable include an attribute for the current version. NHibernate automatically increments the version and check for conflicts. When a transaction synchronizes changes with the database, NHibernate checks the version and if current transaction uses stale data a StaleObjectStateException will be thrown.
To achieve a higher level of isolation, you can also use other mechanism than locking. NHibernate use a first level cache that prevents unrepeatable reads (and some phantom reads). Optimistic locking prevents Second Lost Problem. So by combing the database isolation level Read Comitted, optimistic locking and first level cache we achieve a high level of isolation with low impact on performance.
To enable optimistic locking in NHibnernate:
1) The recommendation is to use an additional attribute to store the version of the entity. Add it as a property in the entity and as new column in the underlying database table.
2) Enable optimistic locking in the mapping file for the entity.
The version attribute can be either an integer or a timestamp. An integer performs better and is considered to be safer than a timestamp.
To enable optimistic locking, add the attribute optimistic locking with the value version to the class element of the mapping file. In the mapping file we must also enter the attribute used for versioning. The tag <version> is used to register which property in the entity and which column in the database is used. The tag must be placed after the <id> element.
<?xml version=”1.0″ encoding=”utf-8″ ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2″ namespace=”NHibPOC” assembly=”NHibPOC”>
<class name=”Person” table=”Person” optimistic-lock=”version”>
<id name=”Id” column=”Id” type=”Int32″>
<version column=”version” name=”version”></version>
<property name=”FirstName” column=”FirstName” type=”String”/>