C# 来自实体框架的 SqlException - 不允许新事务，因为会话中有其他线程正在运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2113498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session
提问by Keith Barrows
I am currently getting this error:
我目前收到此错误：
System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session.
System.Data.SqlClient.SqlException：不允许新事务，因为会话中有其他线程正在运行。
while running this code:
运行此代码时：
public class ProductManager : IProductManager
{
#region Declare Models
private RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities(AppSettings.RivWorkEntities_connString);
private RivWorks.Model.NegotiationAutos.RivFeedsEntities _dbFeed = RivWorks.Model.Stores.FeedEntities(AppSettings.FeedAutosEntities_connString);
#endregion
public IProduct GetProductById(Guid productId)
{
// Do a quick sync of the feeds...
SyncFeeds();
...
// get a product...
...
return product;
}
private void SyncFeeds()
{
bool found = false;
string feedSource = "AUTO";
switch (feedSource) // companyFeedDetail.FeedSourceTable.ToUpper())
{
case "AUTO":
var clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
foreach (RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList)
{
if (companyFeedDetail.FeedSourceTable.ToUpper() == "AUTO")
{
var company = (from a in _dbRiv.Company.Include("Product") where a.CompanyId == companyFeedDetail.CompanyId select a).First();
foreach (RivWorks.Model.NegotiationAutos.Auto sourceProduct in client.Auto)
{
foreach (RivWorks.Model.Negotiation.Product targetProduct in company.Product)
{
if (targetProduct.alternateProductID == sourceProduct.AutoID)
{
found = true;
break;
}
}
if (!found)
{
var newProduct = new RivWorks.Model.Negotiation.Product();
newProduct.alternateProductID = sourceProduct.AutoID;
newProduct.isFromFeed = true;
newProduct.isDeleted = false;
newProduct.SKU = sourceProduct.StockNumber;
company.Product.Add(newProduct);
}
}
_dbRiv.SaveChanges(); // ### THIS BREAKS ### //
}
}
}
break;
}
}
}
Model #1 - This model sits in a database on our Dev Server. Model #1 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1/Model1.png
模型 #1 - 此模型位于我们开发服务器上的数据库中。 模型 #1 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1/Model1.png
Model #2 - This model sits in a database on our Prod Server and is updated each day by automatic feeds. alt text http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980d4/Model2.png
模型 #2 - 该模型位于我们 Prod 服务器上的数据库中，每天通过自动提要进行更新。 替代文字 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980d4/Model2.png
Note - The red circled items in Model #1 are the fields I use to "map" to Model #2. Please ignore the red circles in Model #2: that is from another question I had which is now answered.
注意 - 模型 #1 中的红色圆圈项是我用来“映射”到模型 #2 的字段。请忽略模型 #2 中的红色圆圈：这是我的另一个问题，现在已得到解答。
Note: I still need to put in an isDeleted check so I can soft delete it from DB1 if it has gone out of our client's inventory.
注意：我仍然需要进行 isDeleted 检查，以便在它从我们客户的库存中删除时我可以从 DB1 中软删除它。
All I want to do, with this particular code, is connect a company in DB1 with a client in DB2, get their product list from DB2 and INSERT it in DB1 if it is not already there. First time through should be a full pull of inventory. Each time it is run there after nothing should happen unless new inventory came in on the feed over night.
我想要做的就是，使用这个特定的代码，将 DB1 中的一家公司与 DB2 中的一个客户连接起来，从 DB2 获取他们的产品列表，如果它尚未存在，则将其插入到 DB1 中。第一次通过应该是完整的库存。每次它在那里运行后什么都不应该发生，除非新的库存在一夜之间进入饲料。
So the big question - how to I solve the transaction error I am getting? Do I need to drop and recreate my context each time through the loops (does not make sense to me)?
所以最大的问题 - 如何解决我遇到的交易错误？我是否需要每次通过循环删除并重新创建我的上下文（对我来说没有意义）？
采纳答案by Keith Barrows
After much pulling out of hair I discovered that the foreachloops were the culprits. What needs to happen is to call EF but return it into an IList<T>of that target type then loop on the IList<T>.
在拔出很多头发后，我发现foreach毛圈是罪魁祸首。需要发生的是调用 EF 但将其返回到IList<T>该目标类型，然后在IList<T>.
Example:
例子：
IList<Client> clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
// ...
}
回答by Drew Noakes
As you've already identified, you cannot save from within a foreachthat is still drawing from the database via an active reader.
正如您已经确定的那样，您无法foreach通过活动阅读器从仍在从数据库中提取的内容中进行保存。
Calling ToList()or ToArray()is fine for small data sets, but when you have thousands of rows, you will be consuming a large amount of memory.
调用ToList()orToArray()对小数据集很好，但是当你有数千行时，你会消耗大量内存。
It's better to load the rows in chunks.
最好分块加载行。
public static class EntityFrameworkUtil
{
public static IEnumerable<T> QueryInChunksOf<T>(this IQueryable<T> queryable, int chunkSize)
{
return queryable.QueryChunksOfSize(chunkSize).SelectMany(chunk => chunk);
}
public static IEnumerable<T[]> QueryChunksOfSize<T>(this IQueryable<T> queryable, int chunkSize)
{
int chunkNumber = 0;
while (true)
{
var query = (chunkNumber == 0)
? queryable
: queryable.Skip(chunkNumber * chunkSize);
var chunk = query.Take(chunkSize).ToArray();
if (chunk.Length == 0)
yield break;
yield return chunk;
chunkNumber++;
}
}
}
Given the above extension methods, you can write your query like this:
鉴于上述扩展方法，您可以像这样编写查询：
foreach (var client in clientList.OrderBy(c => c.Id).QueryInChunksOf(100))
{
// do stuff
context.SaveChanges();
}
The queryable object you call this method on must be ordered.This is because Entity Framework only supports IQueryable<T>.Skip(int)on ordered queries, which makes sense when you consider that multiple queries for different ranges require the ordering to be stable. If the ordering isn't important to you, just order by primary key as that's likely to have a clustered index.
您调用此方法的可查询对象必须是有序的。这是因为 Entity Framework 仅支持IQueryable<T>.Skip(int)有序查询，当您考虑到针对不同范围的多个查询需要排序稳定时，这是有道理的。如果排序对您不重要，只需按主键排序，因为这可能有聚集索引。
This version will query the database in batches of 100. Note that SaveChanges()is called for each entity.
这个版本会分批次查询数据库100条。注意SaveChanges()是针对每个实体调用的。
If you want to improve your throughput dramatically, you should call SaveChanges()less frequently. Use code like this instead:
如果你想显着提高你的吞吐量，你应该SaveChanges()减少调用频率。改用这样的代码：
foreach (var chunk in clientList.OrderBy(c => c.Id).QueryChunksOfSize(100))
{
foreach (var client in chunk)
{
// do stuff
}
context.SaveChanges();
}
This results in 100 times fewer database update calls. Of course each of those calls takes longer to complete, but you still come out way ahead in the end. Your mileage may vary, but this was worlds faster for me.
这导致数据库更新调用减少了 100 倍。当然，这些调用中的每一个都需要更长的时间才能完成，但最终您仍然遥遥领先。你的里程可能会有所不同，但这对我来说是更快的世界。
And it gets around the exception you were seeing.
它绕过了您看到的异常。
EDITI revisited this question after running SQL Profiler and updated a few things to improve performance. For anyone who is interested, here is some sample SQL that shows what is created by the DB.
编辑我在运行 SQL Profiler 后重新审视了这个问题，并更新了一些东西以提高性能。对于任何感兴趣的人，这里有一些示例 SQL，它显示了数据库创建的内容。
The first loop doesn't need to skip anything, so is simpler.
第一个循环不需要跳过任何东西，所以更简单。
SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM [dbo].[Clients] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
Subsequent calls need to skip previous chunks of results, so introduces usage of row_number:
后续调用需要跳过之前的结果块，因此引入了以下用法row_number：
SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM (
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], row_number()
OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
FROM [dbo].[Clients] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 100 -- the number of rows to skip
ORDER BY [Extent1].[Id] ASC
回答by Vinod T. Patil
I was also facing same issue.
我也面临同样的问题。
Here is the cause and solution.
这是原因和解决方案。
Make sure before firing data manipulation commands like inserts, updates, you have closed all previous active SQL readers.
确保在触发数据操作命令（如插入、更新）之前，您已关闭所有以前的活动 SQL 阅读器。
Most common error is functions that read data from db and return values. For e.g functions like isRecordExist.
最常见的错误是从 db 读取数据并返回值的函数。例如像 isRecordExist 这样的函数。
In this case we immediately return from the function if we found the record and forget to close the reader.
在这种情况下，如果我们找到了记录并忘记关闭阅读器，我们会立即从函数中返回。
回答by MikeKulls
I was getting this same issue but in a different situation. I had a list of items in a list box. The user can click an item and select delete but I am using a stored proc to delete the item because there is a lot of logic involved in deleting the item. When I call the stored proc the delete works fine but any future call to SaveChanges will cause the error. My solution was to call the stored proc outside of EF and this worked fine. For some reason when I call the stored proc using the EF way of doing things it leaves something open.
我遇到了同样的问题，但情况不同。我在列表框中有一个项目列表。用户可以单击一个项目并选择删除，但我使用存储过程来删除该项目，因为删除该项目涉及很多逻辑。当我调用存储过程时，删除工作正常，但以后对 SaveChanges 的任何调用都会导致错误。我的解决方案是在 EF 之外调用存储过程，这很好用。出于某种原因，当我使用 EF 做事方式调用存储过程时，它会打开一些东西。
回答by Mark Stafford - MSFT
We have now posted an official response to the bug opened on Connect. The workarounds we recommend are as follows:
我们现在已经发布了对 Connect 上打开的错误的官方回应。我们建议的解决方法如下：
This error is due to Entity Framework creating an implicit transaction during the SaveChanges() call. The best way to work around the error is to use a different pattern (i.e., not saving while in the midst of reading) or by explicitly declaring a transaction. Here are three possible solutions:
此错误是由于实体框架在 SaveChanges() 调用期间创建了一个隐式事务。解决该错误的最佳方法是使用不同的模式（即，在读取过程中不保存）或通过显式声明事务。以下是三种可能的解决方案：
// 1: Save after iteration (recommended approach in most cases)
using (var context = new MyContext())
{
foreach (var person in context.People)
{
// Change to person
}
context.SaveChanges();
}
// 2: Declare an explicit transaction
using (var transaction = new TransactionScope())
{
using (var context = new MyContext())
{
foreach (var person in context.People)
{
// Change to person
context.SaveChanges();
}
}
transaction.Complete();
}
// 3: Read rows ahead (Dangerous!)
using (var context = new MyContext())
{
var people = context.People.ToList(); // Note that this forces the database
// to evaluate the query immediately
// and could be very bad for large tables.
foreach (var person in people)
{
// Change to person
context.SaveChanges();
}
}
回答by Herman Van Der Blom
FYI: from a book and some lines adjusted because its stil valid:
仅供参考：从一本书和一些行调整，因为它仍然有效：
Invoking SaveChanges() method begins a transaction which automatically rolls back all changes persisted to the database if an exception occurs before iteration completes; otherwise the transaction commits. You might be tempted to apply the method after each entity update or deletion rather than after iteration completes, especially when you're updating or deleting massive numbers of entities.
调用 SaveChanges() 方法开始一个事务，如果在迭代完成之前发生异常，该事务会自动回滚所有持久保存到数据库的更改；否则事务提交。您可能倾向于在每次更新或删除实体后而不是在迭代完成后应用该方法，尤其是在更新或删除大量实体时。
If you try to invoke SaveChanges() before all data has been processed, you incur a "New transaction is not allowed because there are other threads running in the session" exception. The exception occurs because SQL Server doesn't permit starting a new transaction on a connection that has a SqlDataReader open, even with Multiple Active Record Sets (MARS) enabled by the connection string (EF's default connection string enables MARS)
如果您在处理完所有数据之前尝试调用 SaveChanges()，则会引发“不允许新事务，因为会话中有其他线程正在运行”异常。发生异常是因为 SQL Server 不允许在打开了 SqlDataReader 的连接上启动新事务，即使连接字符串启用了多个活动记录集 (MARS)（EF 的默认连接字符串启用 MARS）
Sometimes its better to understand why things are happening ;-)
有时最好了解事情发生的原因;-)
回答by user2918896
The code below works for me:
下面的代码对我有用：
private pricecheckEntities _context = new pricecheckEntities();
...
private void resetpcheckedtoFalse()
{
try
{
foreach (var product in _context.products)
{
product.pchecked = false;
_context.products.Attach(product);
_context.Entry(product).State = EntityState.Modified;
}
_context.SaveChanges();
}
catch (Exception extofException)
{
MessageBox.Show(extofException.ToString());
}
productsDataGrid.Items.Refresh();
}
回答by VeldMuijz
So in the project were I had this exact same issue the problem wasn't in the foreachor the .toList()it was actually in the AutoFac configuration we used.
This created some weird situations were the above error was thrown but also a bunch of other equivalent errors were thrown.
所以在项目中，我遇到了完全相同的问题，问题不在于我们使用的 AutoFac 配置中，foreach或者.toList()实际上是在我们使用的 AutoFac 配置中。这造成了一些奇怪的情况，即抛出了上述错误，但也抛出了一堆其他等效错误。
This was our fix: Changed this:
这是我们的修复：改变了这个：
container.RegisterType<DataContext>().As<DbContext>().InstancePerLifetimeScope();
container.RegisterType<DbFactory>().As<IDbFactory>().SingleInstance();
container.RegisterType<UnitOfWork>().As<IUnitOfWork>().InstancePerRequest();
To:
到：
container.RegisterType<DataContext>().As<DbContext>().As<DbContext>();
container.RegisterType<DbFactory>().As<IDbFactory>().As<IDbFactory>().InstancePerLifetimeScope();
container.RegisterType<UnitOfWork>().As<IUnitOfWork>().As<IUnitOfWork>();//.InstancePerRequest();
回答by Tomas Kubes
In my case, the problem appeared when I called Stored Procedure via EF and then later SaveChanges throw this exception. The problem was in calling the procedure, the enumerator was not disposed. I fixed the code following way:
就我而言，当我通过 EF 调用存储过程时出现问题，然后 SaveChanges 抛出此异常。问题出在调用过程中，枚举数没有被处理。我通过以下方式修复了代码：
public bool IsUserInRole(string username, string roleName, DataContext context)
{
var result = context.aspnet_UsersInRoles_IsUserInRoleEF("/", username, roleName);
//using here solved the issue
using (var en = result.GetEnumerator())
{
if (!en.MoveNext())
throw new Exception("emty result of aspnet_UsersInRoles_IsUserInRoleEF");
int? resultData = en.Current;
return resultData == 1;//1 = success, see T-SQL for return codes
}
}
回答by Usman
I am much late to the party but today I faced the same error and how I resolved was simple. My scenario was similar to this given code I was making DB transactions inside of nested for-each loops.
我参加聚会迟到了，但今天我遇到了同样的错误，我的解决方法很简单。我的场景类似于这个给定的代码，我在嵌套的 for-each 循环内进行数据库事务。
The problem is as a Single DB transaction takes a little bit time longer than for-each loop so once the earlier transaction is not complete then the new traction throws an exception, so the solution is to create a new object in the for-each loop where you are making a db transaction.
问题是单个 DB 事务比 for-each 循环花费的时间要长一点，所以一旦较早的事务没有完成，新的牵引就会抛出异常，所以解决方案是在 for-each 循环中创建一个新对象您在何处进行数据库事务。
For the above mentioned scenarios the solution will be like this:
对于上述场景，解决方案如下：
foreach (RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList)
{
private RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities(AppSettings.RivWorkEntities_connString);
if (companyFeedDetail.FeedSourceTable.ToUpper() == "AUTO")
{
var company = (from a in _dbRiv.Company.Include("Product") where a.CompanyId == companyFeedDetail.CompanyId select a).First();
foreach (RivWorks.Model.NegotiationAutos.Auto sourceProduct in client.Auto)
{
foreach (RivWorks.Model.Negotiation.Product targetProduct in company.Product)
{
if (targetProduct.alternateProductID == sourceProduct.AutoID)
{
found = true;
break;
}
}
if (!found)
{
var newProduct = new RivWorks.Model.Negotiation.Product();
newProduct.alternateProductID = sourceProduct.AutoID;
newProduct.isFromFeed = true;
newProduct.isDeleted = false;
newProduct.SKU = sourceProduct.StockNumber;
company.Product.Add(newProduct);
}
}
_dbRiv.SaveChanges(); // ### THIS BREAKS ### //
}
}
