What isolation level does autocommit transaction use in SQL Server?

Question

When I work with update conflict problem with SNAPSHOT Isolation Level, it seems that autocommit transactions use the isolation level used at last time.

Condition: ALLOW_SNAPSHOT_ISOLATION is ON, READ_COMMITTED_SNAPSHOT is OFF

Step 1 : Execute update statement without transaction

using (var sqlconn = new SqlConnection("Data source=..."))
using (var sqlcmd = sqlconn.CreateCommand())
{
    sqlconn.Open();
    sqlcmd.CommandText = "Update ..."
    sqlcmd.ExecuteNonQuery();
}

Then I take a look in sys.md_exec_sessions and found the transaction isolation level is READCOMMITTED.

Step 2 : Execute update statement with transaction in SNAPSHOT isolation level

using (var sqlconn = new SqlConnection("Data source=..."))
{
    sqlconn.Open();

    using (var sqltran = sqlconn.BeginTransaction(IsolationLevel.Snapshot))
    using (var sqlcmd = sqlconn.CreateCommand())
    {
        sqlconn.Open();
        sqlcmd.CommandText = "Update ..."
        sqlcmd.ExecuteNonQuery();
    }
}

The isolation level is Snapshot, works good.

Step 3 : do step 1 again

The isolation level is Snapshot. I expect step 3 shows READCOMMITTED cause READ_COMMITTED_SNAPSHOT is OFF.

There is two ideas that I supposed, but I cannot conclude.

dm_exec_sessions will not contains information about autocommit transactions
autocommit transaction actually uses SNAPSHOT

Any idea will be appreciated.

Thanks,

score 1 · Accepted Answer · answered Oct 21 '15 at 07:29

There isn't a specific isolation level for autocommit transactions. They use whatever isolation level has last been declared for a connection (or the server default).

Unfortunately, however, in the face of connection pooling¹, what you think of as a "new" connection may in fact be a re-used one. So in some circumstances, you'll pick up a connection that uses an isolation level different from the SQL Server default (Read Committed).

My advice would be - if you use explicit isolation levels anywhere, you also need to make sure that you use different connection string, or you need to turn off connection pooling, if you don't want to explicitly set the isolation level everywhere. I'd usually prefer the first, so that you can still benefit from connection pooling, but have separate pools for each required isolation level.

¹ Connect Issue which seems to indicate that this may or may not be true for SQL Server 2014 and later.

What isolation level does autocommit transaction use in SQL Server?

1 Answers1

Linked