隔离级别和 DIRT READ SQL SERVER 2005(高级问题)
我将描述我的问题以获得更简单的解释:
我有一个表,我的软件正在使用事务访问它(更新、插入)。
问题是我想在此表中启用 DIRT READ。但我不能在我的 sql 语句中使用 with (nolock),因为我无法更改软源。所以我正在考虑在开始事务的sql进程中启用脏读。
可以看到命令“SET ISOLATION LEVEL ...”和“WITH (NOLOCK)”在访问锁定表的语句中执行...这就是我试图避免的。我想在开始交易的语句中启用脏读...
提前致谢!
I will describe my problem for an easier explanation:
I have a table and my soft is accessing it (update, insert) using transaction.
The problem is that I want to enable DIRT READ in this table. But I cant use with (nolock) in my sql statements because I cant change the soft source. So I was thinking in enable dirty read in the sql process that begin the transaction.
It seens that the command "SET ISOLATION LEVEL ..." and "WITH (NOLOCK)" are executed in the statements that do access the locked table... that's what I'm try to avoid. I want to enable dirt read in the statement that begin the transaction...
thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
对于争用确实不可避免且架构设计正确的极少数情况,答案永远不是启用脏读,而是转向 快照隔离:
更改数据库...设置 ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;
对于少数由快照隔离引入的行版本控制开销可见的部署,他们有专业人员来缓解该问题。
For the rare cases when contention is indeed unavoidable and the schema is correctly designed, the answer is never to enable dirty reads, but to turn to snapshot isolation:
ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;
For the few deployments where the overhead of row-versioning introduced by snapshot isolation is visible, they have professionals at their disposal to alleviate the problem.