This article is a mirror article of machine translation, please click here to jump to the original article.

Architect_Programmer_Code Agriculture Network»Architect › Programming › .Net/C # › Entity Framework Query Optimization WITH(NOLOCK)
View: 45345|Reply: 5

[Source] Entity Framework Query Optimization WITH(NOLOCK)

[Copy link]
Posted on 1/6/2021 2:52:16 PM | | | |
First of all, this article andNot by adding WITH(NOLOCK) to improve query performanceInstead, the data is read through the isolation mechanism of the transaction.

In SQL Server databases, when the same database table is frequently read and written at the same time, there will be a lock problem, that is, you cannot read until the previous insert, update, and delete transaction is completed, and you must wait until the operation is completed before you can perform the select operation, in order to prevent concurrent operations and read dirty data.

SELECT statement with WITH (NOLOCK) to solve the blocking deadlock.

Note:Using WITH(NOLOCK) will result in dirty data reading。

ef displays the generated SQL statements
https://www.itsvse.com/thread-3813-1-1.html
The test code is as follows:

Use Func delegate to package the code as follows:

The resulting SQL statement is as follows:

2021-01-06 14:32:09.9505 [ thread] DEBUG DEBUG LogCenter - sql statement: SELECT TOP (1)
    [Extent1]. [ID] AS [ID],
    [Extent1]. [Name] AS [Name]
    FROM [dbo]. [Itsvse] AS [Extent1]
    WHERE ([Extent1].[ Name] = @p__linq__0) OR (([Extent1].[ Name] IS NULL) AND (@p__linq__0 IS NULL))
2021-01-06 14:32:09.9515 [ thread] DEBUG DEBUG LogCenter - sql statement:

2021-01-06 14:32:09.9525 [ thread] DEBUG DEBUG LogCenter - sql statement: -- p__linq__0: 'itsvse.com' (type = string, size = 4000)

2021-01-06 14:32:09.9525 [ thread] DEBUG DEBUG LogCenter - sql statement: -- Executing at 2021-01-06 14:32:09 +08:00

2021-01-06 14:32:09.9545 [ thread] DEBUG DEBUG LogCenter - sql statement: -- Completed in 1 ms with result: SqlDataReader

2021-01-06 14:32:09.9555 [ thread] DEBUG DEBUG LogCenter - sql statement:

2021-01-06 14:32:09.9555 [ thread] DEBUG DEBUG LogCenter - sql statement: Closed connection at 2021-01-06 14:32:09 +08:00
select query does not include the nolock keyword.

(End)





Previous:.NET/C# implements simple logging based on BlockingCollection
Next:.NET (CLS) public language specification

Related Posts
 Landlord| Posted on 1/6/2021 2:53:17 PM |
Use: Add the following statement to the Application_Start() of the Global.cs, and the generated SQL will automatically add with(nolock)
//ef命令拦截器
DbInterception.Add(new WithNoLockInterceptor());
If the SQL statement needs to be locked, add the following extension


1. Fixed the error problem of regular expressions provided on the Internet under special circumstances

 Landlord| Posted on 1/10/2021 10:27:32 AM |
Testing SQL scripts:

Test results:

No transaction is added by default, and the default isolation level is correct:



Duration: 17561 milliseconds

IsolationLevel.ReadUncommitted isolation level, as shown in the following image:



Time: 519 ms

Source:



 Landlord| Posted on 5/2/2023 11:45:35 AM |
ADO.NET settings allow dirty readingReadUncommitted, the code is as follows:


There is another way to write it, the dapper code is as follows:

There are no problems with both writing tests,It is recommended to use the first one directly！！！


Other ways to write:



 Landlord| Posted on 12/22/2025 1:26:15 PM |
Microsoft.Data.SqlClient.SqlException (0x80131904): Memory-optimized tables do not support transaction isolation level "READ UNCOMMITTED".
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
         at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
         at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

SqlTransaction and TransactionScope leak isolation levels(The.)The bug has been around for 8 years, so far it has not been repaired! I guess a lot of people might be affected without realizing it):The hyperlink login is visible.
Query the SQL statement at the current session isolation level as follows:

 Landlord| Posted on 12/23/2025 8:53:39 AM |
Using ReadUncommitted BeginTransaction can cause dirty reads for out-of-transaction queries:The hyperlink login is visible.
When the connection is closed, the transaction isolation level is not reset:The hyperlink login is visible.
Disclaimer:
All software, programming materials or articles published by Code Farmer Network are only for learning and research purposes; The above content shall not be used for commercial or illegal purposes, otherwise, users shall bear all consequences. The information on this site comes from the Internet, and copyright disputes have nothing to do with this site. You must completely delete the above content from your computer within 24 hours of downloading. If you like the program, please support genuine software, purchase registration, and get better genuine services. If there is any infringement, please contact us by email.
Mail To:help@itsvse.com