The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

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.
Saturday, March 15, 2008
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:
Kyralessa Send private email
Saturday, March 15, 2008
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 Send private email
Saturday, March 15, 2008
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.
Bryan Oakley Send private email
Saturday, March 15, 2008
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)
Paolo Marino Send private email
Sunday, March 16, 2008
"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.
Sunday, March 16, 2008
temporary file names?


1. Delphi uses a built in function called FileSetAttr()

2. in VB or scriptable stuff you have to use the file system object:

Const ReadOnly = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("C:\Scripts\Test.vbs")

If objFile.Attributes AND ReadOnly Then
    objFile.Attributes = objFile.Attributes XOR ReadOnly
End If
Michael Johnson Send private email
Tuesday, March 18, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz