What is the best way for me to implement record locking?

Question

Hi, I have a question about locking. This doesn't have to be only about record locking, but anyway.

Let's say I'm writing a web accessible CMS. I am struggling with some ideas.

I could, on the moment when a user opens an article for editing, flag the article as being 'in use'. so far so good.

but when do I remove the flag? when the user saves the article? but what if the user doesn't feel like typing anymore and decides to close his browser and go to bed?

a time-out mechanism comes to mind, but how long does it take to write an article? 10 minutes too short, 30 minutes too long..

Maybe I am over-complicating this. I'd like to hear your thoughts on this subject.

Answer 1

My vote is for optimistic locking wherever possible.

In one place, where I have implemented actual locks, I had an admin page to remove locks. There was also a service running on the server to unlock any locks which did not have a corresponding active session..

Answer 2

Why not use timestamps? Don't actually worry about locking anything, just react to the event where the record (article) has changed.

Basically, before you save the article, check if your version (timestamp) is the same as what is on disk. If same, then you still have latest copy so write it, if not then ... offer to merge, offer to save as new, discard it - its application specific.

Answer 3

Use rowversion for mssql 2005 and up, timestamp for mssql 2000 and below. Use the hidden xmin field for postgresql.

Let all other users open the record. Along with saving the record, tag who saved it, and with the aid of rowversion, on catch(DbConcurrencyException) re-throw an error which indicate to other users who saved the record before they do, and request them to re-open the record to see the changes made by the user who saved the record first.