DevGuide_Locks

Developer Guide: Locking, Consistency, Isolation

Contents

• Compound documents and consistency
• VITA locking API
• MS SQL Server - enabling snapshot isolation
• MS SQL Server - NoLock option

Compound documents and consistency

In many cases the application data is logically organized in compound multi-entity documents. These documents are stored in the database as multiple rows in multiple tables per single document. For example, an Invoice document may include a header entity/row and multiple detail rows. It is important to ensure the integrity and internal consistency of these documents - Invoice total in the header must match sum of amounts in detail rows.

To ensure the integrity of the documents and its read copies, we must ensure that:

• No two client processes can update the same document concurrently
• No concurrent read is allowed while the document is being updated
• Reads do not block other concurrent reads and can run in parallel - this is an extra requirement for performance

Database servers provide facilities that allow applications to meet these requirements. For a detailed discussion of locking and concurrency issues in relational databases we refer you to the article:

Using locks to manage concurrent access to compound documents.

VITA supports all the appropriate locking mechanisms described in the article.

VITA locking API

The lock-related methods are implemented as extensions in a static class in Vita.Entities.Locking namespace. If you add a using clause for this namespace, you will see extra methods available for IEntitySession:

  session.GetEntity<TEntity>(object primaryKey, LockOptions options);
  session.EntitySet<TEntity>(LockOptions options);
  session.ReleaseLocks();

Here is the LockOptions flag enumeration:

  [Flags]
  public enum LockOptions {
    None = 0, 
    NoLock = 1, 
    SharedRead = 1 << 1, 
    ForUpdate = 1 << 2,
  }

The GetEntity method with lock option starts a transaction and executes a select-by-id SQL with extra locking clause, dependent on the server type. Use this method to load and lock the document header.

For a READ operation:

• Load document header using SharedRead option; this will start a transaction;
• Load document details - all other records that your code needs that are part of the document;
• call session.ReleaseLocks to release the lock(s). The method simply aborts the current transaction;

For a WRITE operation:

• Load document header using ForUpdate option; this will start a transaction;
• Execute all read/write operations over document parts;
• Call session.SaveChanges(); the method will apply changes and commit transaction, thus releasing the lock.

Notes: If any of the read or update database operations fail, VITA automatically aborts the current transaction thus releasing the lock.

MS SQL Server - enabling snapshot isolation

In the case of MS SQL Server locking facility relies on snapshot isolation mode which must be enabled for the database. You need to do it just once in a lifetime of the database instance.

Make sure you run the following commands against your database (substitute the database name):

  ALTER DATABASE (dbname) SET ALLOW_SNAPSHOT_ISOLATION ON;
  ALTER DATABASE (dbname) SET READ_COMMITTED_SNAPSHOT ON;

MS SQL Server - NoLock option

This option has effects only in MS SQL Server.

There's an extra value of LockOptions enum: NoLock. With this option the actual SQL will have a hint 'With(NOLOCK)' added to table references. Roughly, it instructs the server engine to read the data without establishing any locks or waiting for any locks already there.

Note that it is different from LockOptions.None: with None, no hints are added to table reference in SQL, it is equivalent of regular GetEntity without lock option parameter.

Read more about NoLock SQL Server hint online - it is well covered out there.