MSSQL 可序列化事务与范围锁定

Question

我正在尝试使用实体框架进行范围锁定。假设我有一个包含以下列的表：

| Id    | int   |
| Type  | int   |
| Value | int   |

其中Id是具有聚集索引的主键，Type具有非聚集非唯一索引。

如果我想使用此代码在可序列化事务中选择一个值

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT Value  FROM MyTable WHERE Type = 5
SELECT * FROM sys.dm_tran_locks WHERE  request_session_id = @@SPID AND resource_type = 'KEY'
COMMIT 

它正确地将一行与Type = 5和下一行进行范围锁定。

如果我执行此查询：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT Id, Type, Value FROM MyTable WHERE Type = 5
SELECT * FROM sys.dm_tran_locks WHERE  request_session_id = @@SPID AND resource_type = 'KEY'
COMMIT 

它锁定所有行。不幸的是，实体框架选择所有列：

SELECT [Id], [Type], [Value] FROM ...

我

正在用外键过滤我的真实表，而这个列不是唯一的。我试图使Type列上的非聚集索引唯一，即使我选择所有列，它也会锁定正确的行。

我怎样才能得到与非唯一索引相同的信息？

Answer 1

锁定的内容取决于查询计划。计划读取的所有内容都会被锁定。因此，您需要使SQL Server找到要锁定的索引具有吸引力。首先为该查询创建最佳索引。

为什么希望出现特定的锁定模式？如果出于性能原因，这是完全有效的。如果是出于行为原因，那是非常不可靠的。

还可以通过不选择实体而是选择 DTP 对象（例如匿名类型）来使 EF 选择较少的列。

Answer 2

遗憾的是，当 WHERE 子句包含具有非唯一索引或没有索引的不同列时，可序列化事务无法使用聚集索引进行范围锁定。

我为实体框架找到了一个很好的解决方法。

如果要锁定具有特定值的行，例如类型=完成的所有行，请创建一个非唯一索引（如果列可以包含重复项）。

我们必须告诉 SQL DB 我们应该使用什么 INDEX。

var tables = context.MyTables.SqlQuery("SELECT * FROM dbo.MyTable WITH(INDEX(MyIndex)) WHERE Type='FINISHED'").ToList();

我使用了WITH（INDEX（MyIndex）），所以它锁定了Type='DONE'的所有行，即使它有非唯一索引

也许有人会带来比原始查询更好的解决方案。

编辑：范围锁定使用非唯一索引没有任何问题。它没有使用，因为数据库中没有足够的数据。