I was working mostly on PostgreSQL, but recently I was assigned to project with SqlServer and I encountered very strange behavior of this engine. I am using transaction in my code and connect with server via System.Data.SqlClient library. The code in transaction is approximately 1000 lines long, so I would like to not copy it here, but transaction is handled via code below:

using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommited)) 
{
    //here code goes
    //1. inserting new table metadata via inserts and updates 
    //2. creating new tables according to users project
    //3. post execute actions via inserts and updates
    
    //here is intended transaction freeze
    await Task.Delay(1000 * 60 * 2);
}

During this execution I cannot perform any operation on database (query execution in SSMS or some code execution in application - doesn't matter). Simple selects f.e. SELECT * FROM "TableA" hangs, retrieving database properties in SSMS hangs etc. Any independent query waits for this one transaction to be completed.

I found several articles and answers here on SO, and based on those I tried following solutions:

1. Use WITH (NOLOCK) or WITH (READPAST) in SELECT statement
2. Changing database property Is Read Commited Snapshot ON to true
3. Changing transaction isolation level in code (all possible levels were tested)

None of the above solutions works. I tested on 3 different computers: desktop, two laptops - the same behavior (SqlServer and SSMS was installed with default options).

In this thread:

CodePudding user response：

There are number of best practices and suggestions that you can follow, in order to avoid the problems that you may face when using WITH (NOLOCK) table hint. Such suggestions include:

• Include only the columns that are really required in your SELECT query

• Make sure that your transaction is short, by separating different operations from each other. For example, do not include a huge SELECT statement between two UPDATE operations

• Try to find an alternative to the cursors

• Take care to utilize and benefit from the newly defined WAIT_AT_LOW_PRIORITY option to do an online rebuild for the indexes

• Study reporting vs maintenances schedules well

• Take care to utilize and benefit from the different SQL Server high availability solutions for reporting purposes, such as:

• Configure the Always On Availability Groups secondary replicas to be readable and use it for reporting

• Create database snapshots when using the SQL Server Database Mirroring and use it for reporting

• Use the SQL Server Replication subscriber database for reporting

• Use the secondary database of the SQL Server Log Shipping for reporting

If we could see the code, we could give a clear answer, probably due to the query structure. with(nolock) allows to continue database operations for different users, but sometimes using it in complex structures causes data to be returned incomplete and incorrectly, you can click on the link below to get more information

https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15#remarks