从Entity Framework 6调用存储过程的异常行为

I have a stored procedure that takes some time to execute (~60 seconds) that I call using EF like this:

我有一个存储过程需要一些时间来执行（~60秒），我使用EF调用这样：

context.Database.ExecuteSqlCommand(
    "myStoredProcedure @param1",
    new SqlParameter("param1", param1)
);

The stored procedure consists of two parts:

存储过程由两部分组成：

inserting a new row into table tblSomeTable and afterwards

在表tblSomeTable中插入一个新行，然后再插入
calculating some info from multiple tables that do not include tblSomeTable.

计算不包含tblSomeTable的多个表中的一些信息。

The second operation is the time consuming one. When I execute this stored procedure from SSMS I can see that a new row is added to tblSomeTable before the procedure finishes executing which is a normal behavior, but, when I run the same exact procedure using the above code then a new row in tblSomeTable is added only when the procedure finishes executing.

第二个操作是耗时的操作。当我从SSMS执行这个存储过程时，我可以看到在过程完成执行之前向tblSomeTable添加了一个新行，这是一种正常行为，但是，当我使用上面的代码运行相同的精确过程时，tblSomeTable中的新行是仅在过程完成执行时添加。

Also while the procedure is running, I cannot query tblSomeTable at all (both from EF and SSMS), from what I assume that there is a lock placed on tblSomeTable until the procedure finishes. Why is this happening?

同样在程序运行时，我根本无法查询tblSomeTable（来自EF和SSMS），我认为在tblSomeTable上有一个锁，直到程序结束。为什么会这样？

This is on SQL Server 2008.

这是在SQL Server 2008上。

2 个解决方案

#1

Why is this happening?

为什么会这样？

It is not an abnormal behavior. You can't see the data inserted into tblSomeTable because when you call context.Database.ExecuteSqlCommand a new SQL Server transaction is created. This is what the documentation says:

这不是一种异常行为。您无法看到插入到tblSomeTable中的数据，因为当您调用context.Database.ExecuteSqlCommand时，会创建一个新的SQL Server事务。这是文档说的：

If there isn't an existing local or ambient transaction a new transaction will be used to execute the command.

如果没有现有的本地或环境事务，则将使用新事务来执行该命令。

And the default isolation level used for the transaction by EF is the highest which is Serializable. It means that any SQL query that is executed outside of the transaction can't see any data that is currently inserted into tblSomeTable when the transaction is not committed. The data is invisible to your query executed in SSMS when your context.Database.ExecuteSqlCommand doesn't finish its work.

并且EF用于事务的默认隔离级别是最高的Serializable。这意味着在事务未提交时，在事务外部执行的任何SQL查询都无法看到当前插入到tblSomeTable中的任何数据。当context.Database.ExecuteSqlCommand未完成其工作时，在SSMS中执行的查询将看不到这些数据。

You can change the isolation level but I don't recommend it if the only purpose of that is to check what your stored procedure is doing when calling context.Database.ExecuteSqlCommand.

您可以更改隔离级别，但我不推荐它，如果唯一的目的是在调用context.Database.ExecuteSqlCommand时检查存储过程正在执行的操作。

Edit after @David Browne - Microsoft comment:

在@David Browne之后编辑 - 微软评论：

The default isolation level of EF is READ COMMITTED and not SERIALIZABLE as I said above. The inability to read data that is not committed is still apply.

如上所述，EF的默认隔离级别是READ COMMITTED而不是SERIALIZABLE。无法读取未提交的数据仍然适用。

Definition from documentation:

文件定义：

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

指定语句无法读取已修改但未由其他事务提交的数据。这可以防止脏读。数据可以由当前事务中的各个语句之间的其他事务更改，从而导致不可重复的读取或幻像数据。此选项是SQL Server的默认选项。

#2

According to the documentation :

根据文件：

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present

从EF6 Database.ExecuteSqlCommand（）开始，默认情况下会将命令包装在事务中（如果尚未存在）

So you won't see any changes in SSMS until it ends.

所以在结束之前你不会看到SSMS的任何变化。

#1