数据库或用户级锁 [英] Database- or user-level locks

问题描述

我目前面临以下问题:
我有一个C#.NET应用程序连接到数据库(使用NHibernate).该应用程序基本上显示数据库内容，并允许用户对其进行编辑.由于该应用程序的多个实例同时(在同一工作站和不同工作站上)正在运行，因此一旦两个用户同时修改同一条记录，我就会遇到并发问题.

I'm currently facing the following problem:
I have a C# .NET application connecting to a database (with the use of NHibernate). The application basically displays the database content and lets the user edit it. Since multiple instances of the application are running at the same time (on the same and on different workstations) i'm having concurrency problems as soon as two users modify the same record at the same time.

目前，我已经通过乐观锁定解决了问题.但这不是完美的解决方案，因为一个用户仍然松动其更改.

Currently I kind of solved the issues with optimistic locking. But this is not the perfect solution since one user still looses its changes.

现在，我想到了让应用程序每次在从数据库中加载一个新条目时都锁定一个条目的想法，并在用户切换到另一个条目时立即释放该锁.因此，基本上所有当前显示给用户的条目都被锁定在数据库中.如果另一个用户加载了锁定的条目，它将以只读模式显示它们.

Now i came up with the idea of having the application lock an entry every time it loads a new one from the database and release the lock as soon as the user switches to another entry. So basically all entries which are currently displayed to the user are locked in the database. If another user loads locked entries it will display them in a read-only mode.

现在我的实际问题是:
在数据库级别进行锁定是个好主意吗?这意味着每次用户加载新条目并将其锁定时，我都会打开一个新事务.还是最好通过锁定表"来做到这一点，例如，锁定表"持有表中所有锁定条目的键?

Now to my actual question:
Is it a good idea to do the locking on database level? Which means i would open a new transaction every time a user loads a new entry and lock it. Or would it be better to do it through a "Lock Table" which holds for example a key to all locked entries in a table?

感谢您的帮助！

推荐答案

在数据库级别锁定是个好主意吗?

Is it a good idea to do the locking on database level?

是的，在某些情况下还可以.

Yes, it is fine in some cases.

因此，基本上，当前显示给用户的所有条目都是 锁定在数据库中.
...
还是最好通过锁定表"来做到这一点?锁定表"例如拥有表中所有锁定条目的键?

So basically all entries which are currently displayed to the user are locked in the database.
...
Or would it be better to do it through a "Lock Table" which holds for example a key to all locked entries in a table?

那么您在页面加载时锁定了一堆条目吗?你什么时候释放它们?如果编辑需要很多时间(例如，开始编辑条目然后去吃午餐)，该怎么办?如果用户在不编辑所有这些锁定条目的情况下关闭页面，条目将保持锁定状态有多长时间，该怎么办?
悲观锁定和锁定表"有助于避免乐观锁定的某些问题，但又带来了新的问题.

So you lock a bunch of entries on page load? And when would you release them? What if the editing will take lots of time (e.g. had started editing entry and then went for a lunch)? What if user would close the page without editing all these locked entries, for how long entries would remain locked?
Pessimistic locking and "Lock Table" help to avoid some problems of optimistic locking but bring new.

目前，我已经通过乐观锁定解决了问题.但这不是完美的解决方案，因为一个用户仍然会放弃其更改.

Currently I kind of solved the issues with optimistic locking. But this is not the perfect solution since one user still looses its changes.

不同意这种说法，因为在您的情况下，如果将验证和提交阶段作为单个原子操作执行，则条目不会被破坏，并且只有一个事务会成功(假设它是第一个) ，另一个将回滚(第二个).
根据 NHibernate的乐观并发控制

Can't agree that this is loosing, because in your case if validate and commit phases are performed as a single atomic operation then entry wouldn't be corrupted and only one transaction would be successful (let suppose it is the 1st), another would be rolled back (2nd).
According to NHibernate's Optimistic concurrency control

如果这些数据库事务中只有一个(最后一个)，将是原子的 一个)存储更新的数据，所有其他数据仅读取数据.

It will be atomic if only one of these database transactions (the last one) stores the updated data, all others simply read data.

唯一与高并发性和高一致性相一致的方法 可伸缩性是带有版本控制的乐观并发控制. NHibernate提供了三种可能的编写方法 使用乐观并发的应用程序代码.

The only approach that is consistent with high concurrency and high scalability is optimistic concurrency control with versioning. NHibernate provides for three possible approaches to writing application code that uses optimistic concurrency.

因此，第二笔交易将被适当地回滚，并且在该用户被通知他必须进行新的编辑(新交易)或跳过该条目之后，会被通知.

So the 2nd transaction would be gracefully rolled back and after that user could be notified that he has either to make new edit (new transaction) or skip this entry.

但是，一切都取决于您的业务逻辑和要求.如果您对数据的争用不高，因此不会有很多冲突，那么我建议您使用乐观锁定.

But everything depends on your business logic and requirements. If you don't have high contention for the data and thus there wouldn't be lots of collisions then I suggest you to use Optimistic locking.

这篇关于数据库或用户级锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！