事务会阻止其他代码读取不一致的数据吗?
我有一个存储过程,可以在单个事务中插入多个表。我知道事务可以通过允许在错误、电源故障等之后回滚来保持非并发情况下的数据一致性,但是如果其他代码在我提交事务之前从这些表中进行选择,它是否可能选择不一致的数据?
基本上,您可以选择未提交的交易吗?
如果是这样,那么人们通常如何处理这个问题?
I have a stored procedure that inserts into several tables in a single transaction. I know transactions can maintain data consistency in non-concurrent situations by allowing rollbacks after errors, power failure, etc., but if other code selects from these tables before I commit the transaction, could it possibly select inconsistent data?
Basically, can you select uncommitted transactions?
If so, then how do people typically deal with this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这取决于读取查询的隔离级别而不是事务。这可以在连接上集中设置或在 SELECT 提示中提供。
看:
连接端: http://msdn.microsoft.com/en- us/library/system.data.isolationlevel.aspx
数据库端:http://msdn.microsoft.com/en-us/library/ms173763.aspx
This depends on the ISOLATION LEVEL of the read query rather than the transaction. This can be set centrally on the connection or provided in the SELECT hint.
See:
Connection side: http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
Database side: http://msdn.microsoft.com/en-us/library/ms173763.aspx
正如 Aliostad 已经提到的,这取决于所选的隔离级别。维基百科文章提供了不同常见场景的示例。
所以是的,您可以选择获取未提交的数据,但只能通过选择。我从来没有这样做过,而且我不得不承认这个想法对我来说似乎有点……危险。但可能有合理的用例。
As already mentioned by Aliostad, this depends on the selected isolation level. The Wikipedia article has examples of the different common scenarios.
So yes, you can choose to get uncommitted data, but only by choice. I never did that and I have to admit that the idea seems a bit ... dangerous to me. But there are probably reasonable use cases.
扩展阿利奥斯塔的答案:
默认情况下,其他读取进程不会读取正在更改的数据(未提交,又称“脏读”)。这适用于所有客户端和驱动程序
您必须使用 NOLOCK 提示故意覆盖此默认值或更改隔离级别以允许“脏读”。
Extending Aliostad's answer:
By default, other reading processes won't read data that is being changed (uncommitted, aka "dirty reads"). This applies to all clients and drivers
You have to override this default deliberately with the NOLOCK hint or changing isolation level to allow "dirty reads".