|
|
Posted onYesterday at 20:48
|
|
|
Requirements: Some data allows dirty reading, such as article content, page views, comments, etc., which is not as rigorous as financial data. To improve query performance, ReadUncommitted transactions may be used, but SqlTransaction and TransactionScope have leak-isolation level bugs that have not been fixed yet, so consider using the WITH (NOLOCK) solution.
Review:
SqlTransaction and TransactionScope leak isolation levels (this bug has existed for 8 years and still hasn't been fixed!) I estimate many people might be affected, but may not realize it):The hyperlink login is visible.
This article needs to reference the SqlScriptDOM to parse SQL statements and add WITH(NOLOCK). Refer to the open-source project:The hyperlink login is visible.
Nuget quotes as follows:
Create a new AllowDirtyReadAttribute.cs feature, code as follows:
Create a new WithNoLockInterceptor to override certain methods in DbCommandInterceptor. The code is as follows:
Inject into EF Core with the following configuration:
Use to add it above the controller method[AllowDirtyRead]Can.
Raw SQL statement:
SELECT [o]. [Id],
[o]. [CustomerName],
[o0]. [Id],
[o0]. [OrderId],
[o0]. [Product]
FROM [Orders] AS [o]
LEFT OUTER JOIN
[OrderLines] AS [o0]
ON [o]. [Id] = [o0]. [OrderId]
WHERE [o]. [Id] = 1
ORDER BY [o]. [Id];
Statement generated after adding NOLOCK:
SELECT [o]. [Id],
[o]. [CustomerName],
[o0]. [Id],
[o0]. [OrderId],
[o0]. [Product]
FROM [Orders] AS [o] WITH (NOLOCK)
LEFT OUTER JOIN
[OrderLines] AS [o0] WITH (NOLOCK)
ON [o]. [Id] = [o0]. [OrderId]
WHERE [o]. [Id] = 1
ORDER BY [o]. [Id]; |
Previous:(MSSQL) SQL Server Full-Text Search, Full-Text Index
|
