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

February 28, 2013 — 3 Comments

Cartoon_explosion

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

    Trackbacks and Pingbacks:

    1. Dew Drop – March 1, 2013 (#1,507) | Alvin Ashcraft's Morning Dew - March 1, 2013

      […] Why am I Getting so Many Deadlocks From Windows Azure SQL Database? (Alexandre Brisebois) […]

      Like

    2. Reading Notes 2013-03-04 | Matricis - March 4, 2013

      […] Why am I Getting so Many Deadlocks From Windows Azure SQL Database? (alexandrebrisebois.wordpress.com) – Really great post that explains every detail of the problem and of its solution, even for EF.  […]

      Like

    3. Reading Notes 2013-03-11 | Matricis - March 13, 2013

      […] Why am I Getting so Many Deadlocks From Windows Azure SQL Database?(Alexandre Brisebois) – Really great post that explains every detail of the problem and of its solution, even for EF. […]

      Like

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s