我该如何在Entity Framework中使用NOLOCK
函数?是XML是唯一的方法吗?
我该如何在Entity Framework中使用NOLOCK
函数?是XML是唯一的方法吗?
//declare the transaction options
var transactionOptions = new System.Transactions.TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new System.Transactions.TransactionScope(
System.Transactions.TransactionScopeOption.Required,
transactionOptions)
)
//declare our context
using (var context = new MyEntityConnection())
{
//any reads we do here will also read uncomitted data
//...
//...
//don't forget to complete the transaction scope
transactionScope.Complete();
}
扩展方法可以使这更容易。
public static List<T> ToListReadUncommitted<T>(this IQueryable<T> query)
{
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions() {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
List<T> toReturn = query.ToList();
scope.Complete();
return toReturn;
}
}
public static int CountReadUncommitted<T>(this IQueryable<T> query)
{
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions() {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
int toReturn = query.Count();
scope.Complete();
return toReturn;
}
}
如果您需要大量处理事务,请按照以下步骤设置默认事务隔离级别,这种方法比每次启动transactionscope更少干扰:
在创建对象上下文后,在连接上设置默认的事务隔离级别,只需运行此简单命令即可:
this.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
CREATE TABLE ##Test(Col1 INT); BEGIN TRAN; SELECT * FROM ##Test WITH (TABLOCK, XLOCK);
。打开另一个查询(#2),并运行:SELECT * FROM ##Test;
。由于标签#1中仍然存在使用独占锁的事务,因此SELECT不会返回。取消#2中的SELECT。在标签#2中运行一次SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
。再次在标签#2中只运行SELECT,它将返回。一定要在标签#1中运行ROLLBACK
。 - Solomon RutzkyTransactionScope
的想法(均归功于它们各自在这里的答案)。我首先列出了这个想法,因为它更简单,应该产生了期望的效果。用户尝试了这个想法,并且它起作用了。http://stackoverflow.com/questions/26615535/refresh-data-from-stored-procedure/26717601#26717601 - Solomon Rutzky尽管我完全同意使用“Read Uncommitted”事务隔离级别是最好的选择,但有时您被要求使用NOLOCK提示,且没有任何反对理由。
使用Entity Framework 6,您可以像这样实现自己的DbCommandInterceptor:
public class NoLockInterceptor : DbCommandInterceptor
{
private static readonly Regex _tableAliasRegex =
new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
[ThreadStatic]
public static bool SuppressNoLock;
public override void ScalarExecuting(DbCommand command,
DbCommandInterceptionContext<object> interceptionContext)
{
if (!SuppressNoLock)
{
command.CommandText =
_tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
}
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
if (!SuppressNoLock)
{
command.CommandText =
_tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
}
}
}
有了这个类,你可以在应用程序启动时应用它:
DbInterception.Add(new NoLockInterceptor());
有条件地关闭当前线程中查询添加NOLOCK
提示的功能:
NoLockInterceptor.SuppressNoLock = true;
在 Doctor Jones 的答案基础上,使用 PostSharp 进行增强;
首先是 "ReadUncommitedTransactionScopeAttribute"。
[Serializable]
public class ReadUncommitedTransactionScopeAttribute : MethodInterceptionAspect
{
public override void OnInvoke(MethodInterceptionArgs args)
{
//declare the transaction options
var transactionOptions = new TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
//declare our context
using (var scope = new TransactionScope())
{
args.Proceed();
scope.Complete();
}
}
}
}
那么,每当你需要它时,
[ReadUncommitedTransactionScope()]
public static SomeEntities[] GetSomeEntities()
{
using (var context = new MyEntityConnection())
{
//any reads we do here will also read uncomitted data
//...
//...
}
}
拦截器可以添加“NOLOCK”是个不错的功能,但是在连接到其他数据库系统(比如Oracle)时将无法使用。
随着EF6的推出,Microsoft建议使用BeginTransaction()方法。
在EF6+和EF Core中可以使用BeginTransaction代替TransactionScope。
using (var ctx = new ContractDbContext())
using (var transaction = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
//any reads we do here will also read uncommitted data
}
Database.ExecuteSqlCommand()
或者DbSet<T>.SqlQuery()
来执行“本地”的SQL。 - Dunc(NOLOCK)
- 参见Bad Habits to kick - putting NOLOCK everywhere - 不建议在任何地方都使用它 - 相反,要避免这样做! - marc_s顶起这个帖子,因为它是在谷歌搜索中排名第一。EF查询结构已经有所改变。这个正则表达式也适用于联接查询。
另外,使用事务级别的read uncommitted可以工作(示例在transactionscope中),但仍然会阻止更新操作。使用表级别的nolock将允许更新继续进行。在执行此操作之前,请非常注意并进行一些关于使用脏读取的影响的研究
(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))
你可以使用任何正则表达式在线测试工具(例如regex101.com)来测试此示例
FROM [table1] AS [t]
INNER JOIN [table2] AS [t2] ON ([t].[ID] = [t2].[ID])
INNER JOIN [table3] AS [t3] WITH (NOLOCK) ON ([t].[ID] = [t3].[ID])
我也清理了这个示例,但是没有包括触发器的布尔标志。如果您想要添加,请随意。
public class NoLockInterceptor : DbCommandInterceptor
{
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
var finishedresult = base.ReaderExecuting(command.NoLockCommand(), eventData, result);
return finishedresult;
}
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
var finishedresult = base.ReaderExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
return finishedresult;
}
public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
{
var finishedresult = base.ScalarExecuting(command.NoLockCommand(), eventData, result);
return finishedresult;
}
public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
{
var finishedresult = base.ScalarExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
return finishedresult;
}
}
public static class DbCommandExtensions
{
private static Regex _tableAliasRegex = new Regex(@"(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
public static DbCommand NoLockCommand(this DbCommand command) {
string updatedCommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
command.CommandText = updatedCommandText;
return command;
}
}
一种选择是使用存储过程(类似于Ryan提出的视图解决方案),然后从EF执行存储过程。这样,存储过程执行脏读取,而EF只管传输结果。
transactionScope.Complete();
吗? 我习惯于大多数与数据库相关的使用范围自动执行此类操作。 - Wjdavis5