Why am I Getting so Many Deadlocks From Windows Azure SQL Database?

The short answer is that you’re probably not using transactions.

I came across this issue a few time and decided to create the ReliableModel. When I modify the state of my database, it automatically wraps my code with a TransactionScope. By default, TransactionScopes are set to timeout after 30 minutes. This configuration is set in the machine.config and it cannot be modified on Windows Azure Roles. Therefor, if your statements need more than 30 minutes to execute, I strongly recommend using Stored Procedures. Be sure to use Transactions in your Stored Procedures.

In Windows Azure SQL Database the Transactions Isolation level is set to READ COMMITTED SNAPSHOT. Furthermore, both ‘snapshot_isolation_state’ and ‘is_read_committed_snapshot_on’ are activated. These settings are set by Windows Azure SQL Database Service when it creates the ‘master’ database. The ‘master’ database is read only.

Row versioning-based isolation levels improve read concurrency by eliminating locks for read operations. Microsoft SQL Server introduces two transaction isolation levels that use row versioning:

A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.

A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.

Under the hood

 

When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

If you are using Stored Procedures you can set the Transactions Isolation level as follows

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

BEGIN TRY
    
     -- UPDATE .. INSERT .. DELETE 
    SELECT top(10) * FROM [Your Table]

END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Available Isolation levels

• Read uncommitted
• Read committed
• Repeatable read
• Searializable
• Read committed snapshot
• Snapshot

Entity Framework

It came to my attention that Entity Framework 6 Alpha 3 also sets the default Transaction Isolation Level to READ COMMITTED SNAPSHOT for the databases created using Code First.

Using the ReliableModel

List<Car> cars = new List<Car>();
{
    // ....
}


ReliableModel.Do(model =>
    {
        cars.ForEach(c => model.Cars.Add(c));
        model.SaveChanges();
    }, () => new Model());

Model & Entity

public class Model : DbContext
{
    public DbSet<Car> Cars { get; set; }
}

public class Car
{
    public int Id { get; set; }
    public string Brand { get; set; }
}

Wrapping your code with a TransactionScope

List<Car> cars = new List<Car>();
{
    // ....
}

var tso = new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted
};

using (var ts = new TransactionScope(TransactionScopeOption.Required,
                                        tso))
{
    using (var model = new Model())
    {
        foreach (var car in cars)
        {
            model.Cars.Add(car);
        }
        model.SaveChanges();
    }
    ts.Complete();
}

Good to know

If all you are asking about is what the Isolation Level does, then understand – that all Select Statements (hey, all statements of any kind), – are in a transaction. The only difference between one that is explicitly in a transaction and one that is standing on it’s own is that the one that is standing alone starts it’s transaction immediately before it executes it, and commits or roll back immediately after it executes. Source: ‘Is there a difference between a select statement inside a transaction and one that is outside of it’ found on Stack Overflow.

Remember that the SQL Database session timeout will rollback uncommitted transactions in progress.

References

• Choosing Row Versioning-based Isolation Levels
• Understanding Row Versioning-Based Isolation Levels
• Using Row Versioning-based Isolation Levels
• DATABASEPROPERTYEX (Windows Azure SQL Database)
• IsolationLevel Enumeration
• TransactionScope Class