NHibernate read-write cache strategy isolation level verification

Short answer

NHibernate will not tell you whether the isolation level you requested actually took effect, so you must ask the database. The reliable approach is to open a session, run a provider-specific SQL query against the live connection, and compare the reported isolation level with what your read-write cache strategy requires (at least Repeatable Read). Wrapping that check in a startup integration test that fails the build is the right way to fail fast.

Why the configuration property is not enough

hibernate.connection.isolation is a request passed to the ADO.NET provider when a transaction is started. It is not a guarantee:

• Some providers silently ignore or downgrade the requested level.
• Connection pools may hand back connections whose session state was reset or never had the level applied.
• SQL Server, PostgreSQL, and Oracle all default to Read Committed, which is below what the read-write strategy's soft-lock protocol assumes.

Because a mismatch produces stale cache reads rather than exceptions, the failure mode is silent. That is exactly why verification must be explicit.

Querying the effective level per provider

Run the check through the NHibernate session's connection so you measure the same session the cache will use:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var level = session.CreateSQLQuery(
        "SELECT CASE transaction_isolation_level " +
        "WHEN 3 THEN 'RepeatableRead' WHEN 4 THEN 'Serializable' " +
        "ELSE 'Insufficient' END " +
        "FROM sys.dm_exec_sessions WHERE session_id = @@SPID")
        .UniqueResult<string>();
    tx.Rollback();
    // assert level is RepeatableRead or Serializable
}

Equivalents for other engines:

• PostgreSQL: SHOW transaction_isolation; (note this reflects the default; check inside an explicit transaction for the active level).
• MySQL: SELECT @@transaction_isolation;
• Oracle: Oracle has no Repeatable Read; you would need Serializable, which is a different trade-off worth flagging for review.

Important: run the query inside a transaction, because some providers only apply the requested level when a transaction begins. Querying an idle connection can report the server default and give you a false failure (or false pass).

Should you add a validator to the test suite?

Yes. A small integration test that opens a session through the real ISessionFactory (same configuration, same connection string, same pool as production), executes the provider query, and asserts the level is Repeatable Read or higher catches configuration drift, provider upgrades, and pool behavior changes. Run it in CI against the real database engine, not an in-memory substitute, since the whole point is verifying provider and server behavior.

Caveats

• Raising isolation globally increases lock contention and deadlock frequency; consider whether only cache-critical entities need the read-write strategy, or whether nonstrict-read-write with Read Committed is acceptable for your consistency tolerance.
• The exact DMV/catalog queries above are version-dependent; confirm them against your database version before committing the test.
• If your application enlists in ambient (TransactionScope) transactions, the effective level comes from the scope, not the NHibernate property — verify that path too.