阅读未提交的内容和估计
有时，我想运行一个存储过程来粗略估计两个或三个不同表中有多少记录满足某些条件。如果在此估计过程中添加、删除或更新了新记录，那么实际上并不存在问题（我只是想要一个粗略的估计）。也就是说，我可以使用SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED来承担这个过程。但是，我对此有两个问题：
1）由于我只使用 SELECT COUNT(*) 指令，我是否真的需要将这些语句包装在 BEGIN/COMMIT TRANSACTION 中> 阻止？
2) 我是否需要在存储过程的末尾设置SET TRANSACTION ISOLATION LEVEL READ COMMITTED，或者一旦执行结束就会自动设置？
From time to time, I want to run a stored procedure to get a rough estimate of how many records in two or three different tables satisfy some criteria. If during this estimate new records are added, deleted or updated, there is not really a problem (I just want a rough estimate). That being, I can afford for this process using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. However, I have two questions about this:
1) Since I am only using SELECT COUNT(*) instructions, do I really need to wrap these statement in a BEGIN/COMMIT TRANSACTION block?
2) Do I need to SET TRANSACTION ISOLATION LEVEL READ COMMITTED back in the end of the stored procedure, or will this be automatically set once its execution ends?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
没有。读取不需要在事务中
SET 的作用域仅适用于存储过程。在这里查看我的答案：如果我在一个存储过程中调用另一个设置较低事务隔离级别的存储过程，可以吗？。但是，您可以使用 NOLOCK 提示而不是 SET：
SELECT COUNT(*) FROM myTable WITH (NOLOCK)。如果您想要不带 WHERE 过滤器的近似计数，请使用 sys.dm_db_partition_stats。在这里查看我的答案：在一个非常大的表中计算精确行数的最快方法？
No. Reads don't need to be in a transaction
The SET is scoped only for the stored procedure. See my answer here: Is it okay if from within one stored procedure I call another one that sets a lower transaction isolation level?. However, you'd use the NOLOCK hint rather then SET:
SELECT COUNT(*) FROM myTable WITH (NOLOCK).If you want an approximate count without WHERE filters, then use sys.dm_db_partition_stats. See my answer here: Fastest way to count exact number of rows in a very large table?
1) 否。如果您不指定事务范围，它会对每个语句使用隐式事务。您不必设置显式事务范围来使“设置事务隔离级别发挥作用”。
2）您不必将其重置为原始状态。它将由 SQL Server 处理。请参阅此 SO 条目：事务隔离级别范围
1) No. It uses implicit transaction for each statement if you do not specify the transaction scope. You do not have to put explicit transaction scope for making 'set transaction isolation level to work'.
2) You don't have to reset it to original. It will be taken care by SQL Server. Please refer to this SO entry: Transaction Isolation Level Scopes