{"id":471,"date":"2019-03-20T20:14:02","date_gmt":"2019-03-20T19:14:02","guid":{"rendered":"https:\/\/blog.pjsen.eu\/?p=471"},"modified":"2022-02-22T11:39:13","modified_gmt":"2022-02-22T10:39:13","slug":"things-ive-learned-about-sql-server-the-hard-way","status":"publish","type":"post","link":"https:\/\/blog.pjsen.eu\/?p=471","title":{"rendered":"Things I’ve learned about SQL Server the hard way"},"content":{"rendered":"

In this post I am presenting a couple of things I’ve learned from the analysis of a problem, that manifested itself in an occasional HTTP 500 errors in production instance of an ASP.NET application. This time I don’t aim at exhaustively explaining every single point, because each of them could be a subject of a dedicated blog post.<\/p>\n

The story begins with SQL error: SQLEXCEPTION: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.<\/em><\/p>\n

    \n
  1. \nIn any reasonably modern version of SQL Server Management Studio<\/a> there is an XEvent<\/code> session system_health<\/code> under Management<\/i> \u2192 Extended Events<\/i>. It allows for viewing some important server logs, among which xml_deadlock_report<\/code> in particularly interesting. It is very important to have an access to the production instance of database server in order to be able to watch the logs.\n<\/li>\n
    \n
    \"\"
    System health XEvent session<\/figcaption><\/figure>\n<\/div>\n
  2. \nIn this particular case, these xml_deadlock_reports<\/code> contained one suspicious attribute: isolationlevel = Serializable (4)<\/i> and the SQL code was a SELECT<\/code>. I would not expect my SELECT<\/code>s running with Serializable<\/i> isolation level.\n<\/li>\n
    \n
    \"\"
    Details of a deadlock<\/figcaption><\/figure>\n<\/div>\n
  3. \nThe isolation level is an attribute of a connection between a client and the database server. A connection is called session<\/i> in SQL Server terminology. An explicit BEGIN TRAN<\/code> is not necessary for the isolation level to be applied. Every SQL statement runs in its own statement-wide transaction. However, for such narrow-scoped transactions, in practice it may not make any difference whether you raise the isolation level or not. The difference can be observed when a transaction is explicit and spans multiple SQL statements.\n<\/li>\n
  4. \nThe cause of setting the serialization level to Serializable<\/i> was the behaviour of the TransactionScope<\/code> [1]<\/a>. If you use it, it raises the isolation level. It is just a peculiarity of this very API of the .NET framework. It is good to know this.\n<\/li>\n
  5. SQL Server, at last in 2012 and some (I am not sure exactly which ones) later versions, does not reset the isolation level when ADO.NET disposes of a connection. A connection returns back to the connection pool [2]<\/a> and is reused by subsequent SqlConnection<\/code> objects unless they have different connection string.\n<\/li>\n
  6. The connection pool size, if the connection pooling is active, poses the limit of how many concurrent connections to a database server a .NET application can make. If there are no free connections in the pool, an exception is thrown [3]<\/a>.\n<\/li>\n
  7. \nEliminating the usage of TransactionScope<\/code> did not solve the issue. Even if you run SELECT<\/code>s under the default Read Committed<\/i> isolation level, these still issues Shared locks<\/i> which may deadlock with Exclusive locks<\/i> of UPDATE<\/code>s. In any reasonably high production data traffic, where SELECT<\/code>s span multiple tables, which are also very frequently updated, it is highly probable, that a deadlock will occur.\n<\/li>\n
  8. \nThe difference between running SELECT<\/code> under Serializable<\/i> isolation level and Read Committed<\/i> level is that in the former, the locks are kept from the moment of executing the SELECT<\/code> until the transaction ends. You can observe it by manually beginning a Serializable<\/i> transaction, running any SELECT<\/code> and observing dm_tran_locks<\/code> DMV<\/i> and only then committing (or rolling back, whatever) the transaction. With Read Committed<\/i> level locks are not<\/strong> kept until an explicit transaction ends, they are released immediately after execution of a SELECT<\/code> finishes. These are the same kind of locks, Shared locks<\/i>. This implies one cannot observe the difference between executing a SELECT<\/code> under Serializable<\/i> and Read Committed<\/i>, when there is no explicit transaction and thus, there is only a statement-wide transaction which releases locks immediately after the results are returned.<\/li>\n
  9. Setting isolation level of Read Uncommitted<\/i> is practically equivalent to running a SELECT WITH(NOLOCK)<\/code> hint, even if you don’t explicitly open a transaction.\n<\/li>\n
  10. \nIn Entity Framework a SqlConnection<\/code> is opened for every materialization of the query, the results are returned, and the connection is immediately closed and returned back to the pool [5]<\/a>. The connection lifetime is by no means related to the scope of DbContext<\/code> object<\/strong>. I can see a kind of similarity between how Entity Framework uses SqlConnection<\/code>s and how ASP.NET makes use of threads when executing async<\/code> methods. A thread is released on every await<\/code> and can be used for doing something more valuable than waiting. Similarly, a SqlConnection<\/code> is released right after materialization and can be used for executing different command, in different request (in case of ASP.NET) even before DbContext<\/code> is disposed of.\n<\/li>\n
  11. It is not that obvious how to reset the isolation level of the connection. You see, every time your C# code using Entity Framework results in sending a SQL to the SQL Server, it can take different connection from the pool (if anyone knows if there is any ordering applied when retrieving connections from the pool, please feel free to comment). It may or may not be the same connection you used previously. Consequently, it is not easy to ‘catch’ the underlying connection using Entity Framework. You can call BeginTransaction<\/code> every time you use DbContext<\/code>, and then you are guaranteed to own the connection for all your SQL commands. But that way you are forcing opening transaction when you don’t really need one. What I recommend is to handle StateChange<\/code> event of DbConnection<\/code> object as described in [4]<\/a>. You can do it either on opening the connection or on closing it.<\/li>\n
  12. \nIn SQL Server you can monitor open sessions with the following query:<\/p>\n