获取锁-C#SQL Server [英] Acquiring lock - C# SQL server

问题描述

数据库:SQL Server 2005

Database : SQL server 2005

编程语言:C#

我有一个方法，可以对传递给它的User对象进行一些处理.我想控制当同一用户对象的多个线程调用此方法时的工作方式.我实现了一个利用数据库的简单锁定.我不能使用C#的lock语句，因为此方法位于API上，该API将在不同的计算机上传递.但是数据库是集中的.

I have a method that does some processing with the User object passed to it. I want to control the way this method works when it is called by multiple threads with the same user object. I have implemented a simple locking which make use of database. I can't use the C#'s lock statement as this method is on a API which will be delivered on different machines. But the database is centralized.

以下代码显示了我所拥有的. (为清楚起见，省略了对异常的处理)

Following code shows what I have. (Exception handled omitted for clarity)

例如:

void Process(User user)
{
    using(var transaction = BeginTransaction())
    {
        if(LockUser()) {
            try {               
                /* Other processing code */
            }
            finally {
                UnLockUser();
            }
        }
    }
}

LockUser()将新条目插入数据库表.该表对用户标识具有唯一约束.因此，当第二个线程尝试插入相同的数据时，约束将被违反，并且将成为异常. LockUser()捕获它并返回false. UnlockUser只是从锁定表中删除该条目.

LockUser() inserts a new entry into a database table. This table has got a unique constraint on the user id. So when the second thread tries to insert the same data, constraint gets violated and will be an exception. LockUser() catches it and return false. UnlockUser just deletes the entry from the lock table.

注意:请不要考虑未正确删除锁的可能性.我们有一个SQL作业可以清除长时间锁定的项目.

Note: Please don't consider the possibilities of lock not getting deleted correctly. We have a SQL job that cleans items that are locked for long time.

问题

考虑两个线程同时执行此方法，并且两个线程都启动了事务.由于事务仅在所有处理逻辑之后才提交，因此在线程2上启动的事务是否会看到线程1插入到锁定表中的数据?

Consider two threads executing this method at same time and both of them started the transaction. Since transaction is committed only after all processing logic, will the transaction started on thread2 see the data inserted by thread1 to the lock table?

此锁定逻辑可以吗?您认为这种方法有什么问题吗?

Is this locking logic OK? Do you see any problems with this approach?

推荐答案

如果通过在数据库表中插入条目来获取锁是同一事务的一部分，则该锁的全部更改或全部都不更改该事务将对第二个线程可见.对于默认的隔离级别(ReadCommitted)，这是正确的.

If the acquisition of the lock - by virtue of inserting an entry into the database table - is part of the same transaction then either all or none of the changes of that transaction will become visible to the second thread. This is true for the default isolation level (ReadCommitted).

换句话说:无论哪个线程成功提交了该单个事务，也都成功获取了锁(=成功将条目插入到数据库中).

In other words: Whichever thread has a successful commit of that single transaction has also successfully acquired the lock (= inserted successfully the entry into the database).

在您的代码示例中，我缺少对Commit()/Rollback()的处理.确保将其视为实施的一部分.

In your code example I'm missing the handling of Commit()/Rollback(). Make sure you consider this as part of your implementation.

这篇关于获取锁-C#SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！