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.
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.
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?
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)
Should this be implemented on the asp.net side or the SQL side?
Don't do it at all. Use optimistic concurrency instead.
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:
- load balance requests across instances
- do not keep a request stack between HTTP calls
- recycle the app domain
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.
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.
Just don't do it. Optimistic concurrency is sooooo much better in every regard.
