是否可以使用ReadCommitted隔离级别覆盖特定事务的ReadCommittedSnapshot隔离级别？ [英] Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?

问题描述

我的数据库的默认隔离级别是"读取提交的快照"。 

我的表格"Tickets"在我的数据库中包含以下数据。

TicketID    AssignedTo       优先级为
----------- ----------- ---------------

1                6                     低¥
2                5                     高¥
3                6                     低

< span style ="font-size：11.5pt; font-family：'Arial'，sans-serif; color：＃242729">现在在连接1中，我正在启动一个隔离级别为"read committed"的新事务。
我将把#TicketID" 2"< 2"

 BEGIN TRAN的优先级更新为'medium' 
 
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
 
 UPDATE门票
 SET [优先级] ='中'
 WHERE TicketID = 2 
 

从连接2，我试图访问#TicketID" 2"的详细信息。并且它仍然从版本存储中获取
值，这是由默认快照隔离创建的，这不是预期的行为。

 BEGIN TRAN 
 
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
 
 SELECT * 
 FROM Tickets 
 WHERE TicketID = 2 

根据文档，如果使用"Read Committed"的事务，隔离级别，在事务执行提交/回滚之前，该行不能被任何其他事务访问
。

谁能告诉我为什么在给定的场景中没有发生？我也很想知道，为什么事务级别隔离
不能覆盖在数据库级别设置的默认隔离（这里是读提交的快照）级别？

解决方案

看起来您可以使用表提示覆盖它

https://docs.microsoft.com/ EN-US / SQL / T-SQL /查询/提示-与Transact-SQL表？视图= SQL服务器-2017

The default isolation level of my database is "read committed snapshot". 

I have the table "Tickets' with the following data in my database.

TicketID    AssignedTo       Priority
----------- ----------- ---------------
1                6                      low
2                5                      High
3                6                      Low

Now in connection 1, I am starting a new transaction with isolation level as "read committed". And I'm going to update the priority to 'medium' for the #TicketID "2"

BEGIN TRAN 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

UPDATE Tickets
SET [Priority] = 'Medium'
WHERE TicketID = 2

From Connection 2, I am trying to access the details of #TicketID "2". And it’s still taking the value from the version store which is created by the default snapshot isolation, which is not expected behavior.

BEGIN TRAN 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *
FROM Tickets
WHERE TicketID = 2

As per documentation, if a transaction using "Read Committed" isolation level, the row can't be accessed by any other transaction until the transaction performs a commit/Rollback.

Can anyone tell me why it is not happening in the given scenario? I am also curious to know, why a transaction level isolation is not able to override the default isolation (here read committed snapshot) level which is set at the database level?

解决方案

Looks like you are able to override it with the table hint

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

这篇关于是否可以使用ReadCommitted隔离级别覆盖特定事务的ReadCommittedSnapshot隔离级别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！