sql server 锁定表
我在一个 asp 解决方案中连接了多个 silverlight 应用程序，多个用户可能随时在同一组数据上使用同一个应用程序。 silverlight 应用程序的设计使得它们可以从数据库中提取数据，在本地处理数据，并在用户对修改感到满意后将其写回。然而，这样做的问题是，如果两个用户在同一个数据库上使用同一个应用程序，他们将覆盖彼此的更改。
任何时候都不会有大量用户使用同一个数据库，因为数据库有很多，并且不必有多个用户同时处理同一组数据。我的想法是锁定每个表，以便单个用户拥有对服务器读/写的独占访问权限，拒绝任何其他用户访问甚至从相关锁定的表中读取的权限。
我将如何应用表锁并释放它们？我知道这可以在每个语句的基础上完成，但我需要用户对此数据拥有完全的独占权利。我可以预见的一个问题是释放锁，需要某种超时，因此该表不会为单个用户永久锁定。
I have multiple silverlight applications bolted within an asp solution, multiple users may be using the same application on the same set of data at any one time. The silverlight applications are designed such that they pull data from the database, work on it locally, and write it back once they user is happy with their modifications. The problem with this however, is that if two users are using the same application on the same database, they are going to overwrite each others changes.
There isnt going to be a huge number of users using the same database at any one time, as there are numerous databases, and it isnt essential that more than one user should be working on the same set of data at any one time. My idea is to lock each table for a single user to have exclusive access to read/write to the server, denying any other user access to even read from the locked tables in question.
How would i go about applying the table locks and releasing them? I know it can be done on a per statement basis, but I need the user to have totally exclusive rights to this data. A problem that I can forsee is releasing the lock, there would need to be some kind of timeout, so the table isnt permanently locked for the one user.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我什至不会开始尝试这一点，因为超过 1 个用户会遇到可怕的并发问题 - 包括 1 个用户使用多个窗口，或者在系统崩溃后返回，等等。
一种简单的处理方法这将有一个时间戳或更改计数，如果自获取数据以来时间戳或更改计数已更改，则警告用户他们将覆盖其他人的工作。从这个起点开始，您可以更进一步，让他们选择更改、手动复制其他人的更改、比较更改等。
I wouldn't even begin to attempt this, as you'll have horrible concurrency problems with more than 1 user - including 1 user who uses more than one window, or returns after a crash on their system, etc.
A simple way to deal with this would be to have a timestamp or change count, and if the timestamp or change count has changed since the data was obtained, then warn the user they are going to over-write someone else's work. From that starting point you can go further and let them pick and choose changes, manually copy in changes from the other person's, compare changes, etc.
IMO 锁定数据库工件（物理上使用 SQL 锁）或逻辑上（使用您自己的锁定表）可能会出现问题 - 它限制了应用程序的可扩展性，并且当用户的同事出去喝咖啡时可能会导致用户感到沮丧。您的应用程序还需要进行设计，以便用户“签出”一个项目来表明意图，从而创建锁定（而不是只浏览不需要锁定的数据）
作为替代方案，请查看 多版本并发控制。
一种常见的非侵入性方法是向记录实体添加时间戳，并且每次在写入更新之前，再次获取记录并比较时间戳以查看其他用户是否在此期间进行了更改。
IMO locking databases artifacts (either physically using SQL locks) or logically (using your own locking tables) can be problematic - it limits the scalability of your app and can cause frustration for users when their colleagues go out for coffee. Your app also needs to be designed so that users 'check out' an item to indicate intent and hence create the lock (as opposed to just browsing data where you don't want a lock)
As an alternative, look at patterns of Multiversion concurrency control.
A common, non intrusive way is to add a timestamp to your record entities, and every time just before you write an update, fetch the record again and compare timestamps to see if another user has made a change in the interim.
我不确定您在数据库中处理的“事物”是什么，但我在应用程序中处理了类似的问题，而不是在表等上使用锁。
基本上，我有各种文档（例如订单或收据）可以打开然后编辑并保存。为了实现这些锁定，我所做的就是创建一个包含 Type、DocID、UserID、LockedTime 的表。 DocID 和类型上的主键（类型指示您要锁定的内容的类型，因此这允许您在多个表等上使用此方法）
然后，当用户尝试打开文档时，我将首先运行一个 SP 来检查是否该文档已锁定给另一个用户，如果锁定则不允许打开，我实现了只读 modfe。当用户保存更改时，它会解锁文档，让其他人再次访问。
我不确定这种方法对您是否有用，但它的实现非常简单且快速，因为它不需要对现有表进行复杂的更改。
Im not sure what the 'things' you are working on in your database are, but I had a similar issue which I handled in my application rather than using locks on tables etc.
Basically I had various documents (like an order or receipt) which could be opened and then edited and saved. What I did to implement locking on these was to have a a table with Type, DocID, UserID, LockedTime. Primary key on the DocID and Type (type indicates the type of thing you are going to lock so this allows you to use this approach on multiple tables etc)
Then when a user tries to open a document i would first run a SP that checks if the document is locked to another user, then if not allow opening if locked i implemented a read only modfe. When the user saves their changes it unlocks the document giving everyone else access again.
Im not sure if this approach would be of any use to you, but it is quite simple and quick to implement as it doesnt require complex changes to existing tables.