When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.

A pooled connection is a feature provided by dot net framework to reduce the cost of establishing connection between client application and SQL Server. When you first open a connection, it connects to the SQL Server then you can utilize it to do your work. Once you call close, the connection will be returned back to the pool without physically disconnecting the server. When you re-open it again, it will be taken from the pool directly. The framework assumes it’s still open. When you run the first command over the connection, a procedure sp_reset_connection is invoked to reset few things on server. But one thing will not be reset – Isolation level. If you set an isolation to serializable in one session and forget to set it back before closing, the isolation level will be carried over. sp_reset_connection will not reset it for you. See my test below:

SqlConnection connection = new SqlConnection("Data Source=localhost;Integrated Security=true;Pooling=true;");
connection.Open(); 
SqlCommand command = connection.CreateCommand();
command.CommandText = @"select transaction_isolation_level 
                    from sys.dm_exec_sessions 
                    where session_id = @@spid";
Console.WriteLine("Default isolation level: {0}", command.ExecuteScalar());
command.CommandText = @"
                    set transaction isolation level serializable
                    select transaction_isolation_level 
                    from sys.dm_exec_sessions 
                    where session_id = @@spid
                ";
Console.WriteLine("Current isolation level: {0}", command.ExecuteScalar());
connection.Close(); 
/*let's create a new connection*/
connection = new SqlConnection("Data Source=localhost;Integrated Security=true;Pooling=true;");
connection.Open();
command = connection.CreateCommand();
command.CommandText = @"select transaction_isolation_level 
                    from sys.dm_exec_sessions 
                    where session_id = @@spid";
Console.WriteLine("Current isolation level: {0}", command.ExecuteScalar());
connection.Close(); //return the connection back to the pool
Console.ReadKey();

Results:

Default isolation level: 2
Current isolation level: 4
Current isolation level: 4

But why you change the isolation level? this is because the default isolation level of dot net transaction classes is serializable, for instance TransactionScope. So it’s important to ensure the consistency of isolation level in your application.

Deadlock in Pooled Connections

You May Also Like

4 thoughts on “Deadlock in Pooled Connections

  1. why I test it get a different result:
    Default isolation level: 2
    Current isolation level: 4
    Current isolation level: 2

    1. Hi Sandy,

      I just used the code on my blog and tried it again. I still get
      Default isolation level: 2
      Current isolation level: 4
      Current isolation level: 4
      If you’re still not able to produce this result, optionally, I would like to get your code and have a shot.

      John H

      1. thank, i have get my error .
        create a new connection ‘ conncect string have little different with top connection.

Leave a Reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.