Locking a 'document' based on database records

My application database (SQL Server) holds documents which are comprised of a number of database records.

To open a document means to retrieve x records and compose them into an rtf document.

At the moment, if 2 people open the same document and 1 of the parties decides to delete the document, they can! Whilst I espect people to talk to each other and that this should not happen, my design does not address this problem.

I have no problem resolving a case where the person with the document open wants to save after it has been deleted, they are prompted to save under another name. But if they just close the document after review. It will be gone.

How can I lock a 'document' based on an SQL Server database kind of like in the way Excel locks a document i.e. No deletes and open is read only.

Would appreciate any ideas.

Concurrent
Saturday, March 15, 2008

Deleting …Approving …

Sounds to me that the document is automatically composed based on the records they select...which means you need to lock the records (so no one else can so much as read them), not the document.

At a glance, it looks like "serializable" or "repeatable read" are what you're looking for, depending on whether other users should be able to do inserts while one user is editing a particular range:

This sounds to me like you don't want to use RDBMS locking at all, rather, this sounds like a case of some kind of status flag that you set on the document records to indicate that they are "checked out" by someone.

Greg
Saturday, March 15, 2008

Deleting …Approving …

What's the harm in letting someone delete the document when someone else has it open? Why can't the one that has it open continue to have it open, and when they close it the system can fully delete it then. In the mean time set a flag so no one else can open it as if it had been deleted.

Your software should adapt to the user; don't make the user adapt to the software. Obviously there is some sort of workflow that makes "deleting a file when someone else has it open" a natural event. Don't prevent it, support it.

I suggest you set up a "master" record in your db identifying the document (most probably you already have it) and manage a status in it.

Your problem would probably require a pessimistic lock (google around for pessimistic vs. optimistic locking) and this will also introduce the risk that the status may remain erroneously set. I.e. user X reads the document, this sets the status flag to "in use" preventing user Y from deleting (or modifying, too?) the record.

If user X doesn't release the document properly (i.e. his PC reboots, connection fails and so on) the document will remain "in use" forever. This is usually addressed by cleanup jobs which periodically check "in use" records and force a release based on time elapsed since the last actual access.

(So yes, you would need to manage a "last-upadted/last-accessed" timestamp on the document, too)

"At the moment, if 2 people open the same document and 1 of the parties decides to delete the document, they can!"

Sounds like a feature. Is it causing problems? If it is causing problems add a column for the number of users who have the document open. Then when they try to delete it tell them it is in use by someone else if it is. This can cause problems if a system crashed and a document isn't released. There are ways to work around that like aging ownership(say every 12 hours dec ownership by one or something), or a separate table that links users to documents and every time the user connects report all open documents. though you may still run into trouble if a user goes on vacation and doesn't unlock documents.