锁定数据库记录编辑 [英] Locking database record for editing

问题描述

我有一个SQL Server 2008数据库和asp.net前端。

I have a SQL Server 2008 database and an asp.net frontend.

我想实现当用户正在编辑记录，但不能确定这是最好的办法锁定。

I would like to implement a lock when a user is currently editing a record but unsure of which is the best approach.

我的想法是有记录的 isLocked 列，当用户拉该记录，这意味着所有其他用户只读访问，直到它被设置为true第一个用户完成编辑。

My idea is to have an isLocked column for the records and it gets set to true when a user pulls that record, meaning all other users have read only access until the first user finishes the editing.

但是，如果会话超时和他/她从不保存/更新记录，该记录将保持与 isLocked = TRUE ，这意味着别人不能编辑它，对吧？

However, what if the session times out and he/she never saves/updates the record, the record will remain with isLocked = true, meaning others cannot edit it, right?

我怎样才能出实施某种形式的会议时间，并有 isLocked 被自动设置为false时，会话超时（或predefined期后）

How can I implement some sort of session time out and have isLocked be automatically set to false when the session times out (or after a predefined period)

如果有这样的在asp.net端或SQL端实现？

Should this be implemented on the asp.net side or the SQL side?

推荐答案

不要做它。使用乐观并发来代替。

Don't do it at all. Use optimistic concurrency instead.

悲观锁是可能的，但不能从.NET应用程序。 .net应用程序农场在技术上能够保持一个长期居住的会话保持锁定（通过的 sp_getapplock 或更糟的是，通过真实的数据锁定获得），因为.net应用程序农场：

Pessimistic locking is possible, but not from .Net applications. .Net app farms are not technically capable of maintaining a long lived session to keep a lock (obtained via sp_getapplock or, worse, obtained by real data locking) because .Net app farms:

• 跨实例负载均衡请求


• 不要让HTTP调用之间的请求栈


• 回收应用程序域

在你说'我没有一个农场，只有一个IIS服务器'我要指出，你可能只有一个IIS服务器的现在，如果你依赖它，你永远不会能够扩展出来，你的还是的有应用域回收的问题。

Before you say 'I don't have a farm, is only one IIS server' I will point out that you may only have one IIS server now and if you rely on it you will never be able to scale out, and you still have the problem of app-domain recycle.

通过模拟特定应用程序的更新锁（如is_locked字段）是真正的使用存在严重缺陷，原因你已经开始看到，等等。当推来推这是的唯一方法可以的进行工作，但我从未听说过的任何的说'哎呀，我真的很高兴我们实现了悲观锁有数据写入！。没有人，永远。

Simulating locking via app specific updates (eg. 'is_locked' field) is deeply flawed in real use, for reasons you already started to see, and many more. When push comes to shove this is the only approach that can be made to work, but I never heard of anyone saying 'Gee, I'm really happy we implemented pessimistic locking with data writes!'. Nobody, ever.

应用层锁定也行不通，整整净农场不能使用后端锁定（负载均衡，缺乏调用之间的上下文，应用程序域回收）同样的原因。编写分布式锁定应用程序的协议仅仅是行不通的，这条路铺上了尸体。

App layer locking is also not workable, for exactly the same reasons .Net farms cannot use back-end locking (load-balancing, lack of context between calls, app-domain recycle). Writing a distributed locking app-protocol is just not going to work, that road is paved with bodies.

只是不去做。乐观并发SOOOOO在各方面要好得多。

Just don't do it. Optimistic concurrency is sooooo much better in every regard.

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