-3

I started off a few days ago thinking that I could write a simple toolkit for locking (restricting access to) and unlocking resources in our application. So for example by opening a project it would get locked to that user in some way, then when the next person came to open it they would not be able to modify it. Or an operation needs to write to certain files and read from certain files, so it locks them (in some metadata record in the database) as such before performing the operation.

Now that I've done a fair bit of design it is becoming apparent that there are actually quite a lot of little gotchas: I'm now in a situation where to describe the functionality I need I have 5 different types of lock defined (owner, write, read-only, delete, no-delete) and need to specify the relationships between them in the context of being applied to the same object and parent-child hierarchies.

That will translate into a fair amount code to maintain and testing and given this I'm wondering if there are any free .NET libraries available that do this sort of thing out of the box (I'm off on Christmas hols next week and I think it would be easier for one of the other devs to plug in a library rather than finish off my tooklit and plug it into the application).

We're using C# .NET and SQL Server 2008 R2.

UPDATE: By resources I mean files referenced by metadata in a database.

BryanH
  • 5,826
  • 3
  • 34
  • 47
satnhak
  • 9,407
  • 5
  • 63
  • 81
  • 2
    Have you looked at changing your model to use [transactional locking](http://www.sqlteam.com/article/introduction-to-locking-in-sql-server) built right in to SQL? Also your program will not scale very well if just having the program open takes locks out. What happens if a employee forgets to close the program, locks his workstation, and goes on a two week vacation? – Scott Chamberlain Dec 13 '12 at 15:21
  • There are even more problems to solve, for example you have to deal with locks left behind by a crashed client or a user that went to lunch while another user really needs access to something. Getting such a thing right is not easy but I am not aware of any (freely) available solution. – Daniel Brückner Dec 13 '12 at 15:28
  • @Scott Chamberlain - In this case transactions are not appropriate as the action of opening a project for example could last indefinitely: transactions are really only meant for operations that last a very short amount of time in a desktop application as an open transaction locks everything down. What I'm saying is there are a set of resources that a user needs to claim a certain level of access to for an indefinite period of time. That access guarantees things about those resources, like them not being deleted, or like them not being modified. – satnhak Dec 13 '12 at 15:43
  • @Daniel Brückner - yes it's a pain, luckily the user numbers are low enough and the application corporate enough for recovery to be handled by an administrator for the time being. – satnhak Dec 13 '12 at 15:44
  • Files are on a share? Are the files for the application itself (.e.g. xaml) or files managed by the application. – paparazzo Dec 13 '12 at 15:57
  • Yep, files are currently on a share and are managed by the application. – satnhak Dec 13 '12 at 15:58
  • An app cannot manage what it cannot control. If the server app assigns owner then owner can take file rights away from the server. The only way for the app to control the files is to serve them up directly like a document management application. – paparazzo Dec 13 '12 at 16:12
  • @Blam SQL2008 and above has features for managing files transactionally via the [FileStream datatype](http://msdn.microsoft.com/en-us/magazine/dd695918.aspx) – Scott Chamberlain Dec 13 '12 at 16:18
  • @ScottChamberlain OP stated files are currently on a share. If the design is FileStream then SQL is the app that serves up the files and is a control point. If the client accesses a file directly on a fileshare the app is not a control point. – paparazzo Dec 13 '12 at 16:30
  • @ScottChamberlain - looks like a really good way of managing files; wish I'd known about it earlier :( – satnhak Dec 13 '12 at 16:47
  • Strange as it may seem, you might want to look into SharePoint. It sounds like you're talking about a check-in/check-out system, and that's built into SharePoint document libraries. – John Saunders Dec 21 '12 at 01:29

2 Answers2

0

chances are you're over-designing. DBs are meant to be concurrently accessible and use Transactions for critically interdependent operations. You should reconsider your data flow and allow multiple users to work together. For example if a user downloads the parts of a project they are working on and then commits them all at once. Some merging may be required of course. Depending on your task you could also use a version control system (SharpSVN) instead of or along with DB

Sten Petrov
  • 10,943
  • 1
  • 41
  • 61
0

You are describing a pessimistic locking strategy; this is best handled by enlisting your code into the database transaction (aka distributed transactions), so your code can commit/rollback the transaction, and hence release any locks. This can be achieved using COM+ Transactions. However, it may be better to use an optimistic locking stragegy instead; see here for a more detailed answer.

Community
  • 1
  • 1
Polyfun
  • 9,479
  • 4
  • 31
  • 39
  • 1
    This question is not about locking database objects, it is about logically locking objects from the application domain. User X is editing project/user/product Y, nobody is allowed to make changes until he is done. Pessimistic locking ensures the user can save his changes, optimistic locking may tell the user "Sorry, somebody else also modified this object." - now you have the choice between forcing the user to make the changes again or provide a kind of merge tool, a non-trivial task on its own. – Daniel Brückner Dec 13 '12 at 16:00
  • @DanielBrückner - thanks, you've explained it better than I did. – satnhak Dec 13 '12 at 16:04