sql server事务是原子的

发布于 2024-12-04 11:50:59 字数 340 浏览 1 评论 0原文

所以我有一个类似这样的存储过程（sql server 2008 r2），

BEGIN TRAN
BEGIN TRY


   //critical section
    select value        
    update value
       //end of critical section


    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

我不希望两个存储过程读取相同的值。换句话说，读取和更新应该是原子的。这段代码是这样做的吗？如果不是我该怎么办？

原文

so I have a stored procedure (sql server 2008 r2) something like this

BEGIN TRAN
BEGIN TRY


   //critical section
    select value        
    update value
       //end of critical section


    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

I want no two stored procedures read the same value. In other words read and update should be atomic.
This code does this? If not how do I do it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧城烟雨 2024-12-11 11:50:59

是的，它们是原子的，但这并不意味着您会在这里得到您想要的行为！您需要关注的属性是隔离。

要实现所需的排除，您需要对单个值进行 SELECT 操作互斥。您可以通过请求 Update 锁来做到这一点（确保可以通过索引找到 WHERE 谓词，以避免锁定不必要的额外行）

SELECT * FROM foo WITH(ROWLOCK,UPDLOCK) WHERE bar='baz'

请注意，此锁将一直保持到您的事务提交，但不会在关键部分结束时释放，但如果您无论如何更新了值，情况总是如此。

Yes they are atomic but that does not mean that you will get the behaviour that you want here! The property you need to look at is isolation.

To achieve the exclusion that you require you would need to make the SELECT operation on the single value mutually exclusive. You can do this by requesting an Update lock (make sure the WHERE predicate can be found through an index to avoid locking unnecessary extra rows)

SELECT * FROM foo WITH(ROWLOCK,UPDLOCK) WHERE bar='baz'

Note this lock will be held until your transaction commits however not released at the end of the critical section but that is always going to be the case if you have updated the value anyway.

回复收藏 0 原文

~没有更多了~