1

I have encountered an error on executing UPDATE statement.

Error:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Companies' directly or indirectly in database 'myDatabase' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Statement: 

UPDATE [ActiveSession] SET [LastActionExecutedAt] = GETDATE() WHERE [SessionID]=@id

I ran above statement through ASP.NET application.

What is curious is that the statement is executed WITHOUT transaction. The code is like below:

using(var sqlconn = new SqlConnection("connection string")
using(var sqlcmd = sqlconn.CreateCommand())
{
    sqlconn.Open();
    sqlcmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = id;

    sqlcmd.CommandText = "SELECT * FROM [ActiveSession] WHERE @id=id";
    using(var sqlreader = sqlcmd.ExecuteReader())
    {
        // read info
        reader.Close();
    }

    sqlcmd.CommandText = "UPDATE ....";
    Sqlcmd.ExecuteNonQuery();
}

I have googled and only find that the error occurs when I use SNAPSHOT isolation level transaction. BUT there is no transaction.

As described in this thread, INDEXING will help in some situations, but this does not help me.

Any idea about this error? Any help will be appreciated.

Thanks,

Community
  • 1
  • 1
  • 1
    There is *always* a transaction. By default, if you execute a DML statement and there's no transaction open, SQL Server will open one, run the statement and then either commit or rollback the transaction (depending on success or error). See [Autocommit transactions](https://technet.microsoft.com/en-us/library/ms187878(v=sql.105).aspx) – Damien_The_Unbeliever Oct 20 '15 at 09:53
  • Thanks Damien, does autocommit transaction use Snapshot Isolation Level? I set ALLOW_SNAPSHOT_ISOLATION ON, but READ_COMMITTED_SNAPSHOT OFF. Under this condition, I thought transaction with snapshot Isolation requires explicit specification like ADO.NET ([msdn](https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx#Anchor_3)). –  Oct 20 '15 at 10:49

1 Answers1

0

As described by Damien, the update statement uses autocommit transaction.

The autocommit transaction uses the isolation level had last been declared for connection not default isolation level (see this question).

This behavior makes the update statement executed both in READ COMMITTED and SNAPSHOT isolation level, and this is why update conflict occurs.

It seems that this problem is fixed in SQL Server 2014 CU6 but I still uses 2008 R2 :(

Thanks, Damien!

Community
  • 1
  • 1