views:

41

answers:

1

Hi, i am not sure if this is the correct term, but this is what id like to do: I have an application that uses a mssql database. This application can operate in 3 modes. mode 1) user does not alter, but only read the database mode 2) user can add rows (one at a time) onto a table in the database mode 3) user can alter several tables in the database (one person at a time)

question 1) how can i ensure that when a user in in mode 3 that the database will "lock" and all logged in users who operate in mode 2 or mode 3 will not be able to change the database until he finishes? question 2) how can i ensure that while there are several users in mode 2, that there will be no conflict while they all update the table? my guess here, is that before adding a new row, you make a server query for the table's current unique keys and add the new entry. will this be safe enough though?

Thanks

A: 

There are a variety of ways to approach #1. Assuming that your requirements are correct (I'd at least double-check them, because they sound weird), what I'd probably do is to create an ApplicationLock table that looks something like this:

CREATE TABLE ApplicationLock (SessionId UniqueIdentifier, CreateDate DateTime, LockType int, Active bit)

Then before you want to do something that requires a lock, check to see if there's an active lock of the sort that would prevent you from doing what you want. (And if there is, see if it's passed some defined timeout period. If it's been longer than some timeout period, you should be able to clear the lock.) If there isn't a lock that would block you, insert your own lock (you can create a SessionId on the Silverlight client with Guid.NewGuid()), do what you need to do, and then clear the lock. If all of this can happen in one web service method call, you should be sure to wrap it in a transaction, so that it will roll back the lock automatically if something fails.

The key is making sure your locks get cleared. If a lock only needs to hang around for one WCF method call, you should be able to handle it in your server-side code pretty easily. However, if they need to persist across method calls, I think you'll need a multi-part strategy. If you're using connection-oriented bindings in your WCF service (like Net.TCP), you can handle the event of the client disconnecting, which would allow you to automatically clear any locks that they've got open. However, I wouldn't depend on this, and I'd have some sort of timeout as a fallback.

For #2 (preventing conflicts when multiple people are editing the table), it depends on what sort of conflicts would be problematic at a business level. If you're just worried about inserting two rows that have the same primary key, there are easy ways around that. Assuming you've got a surrogate key as your PK, you should make that surrogate key either a UniqueIdentifier (e.g., a GUID, which allows you to safely create the key on the client), or you can make it an int/identity column, and then retrieve the value from the table using SCOPE_IDENTITY(). I prefer GUID's, but either would work.

If you need to prevent users from editing values that might have changed underneath them, then you're getting into optimistic vs. pessimistic locking. It's a fairly complicated topic, but you can start here.

On a side note, I mentioned that the requirements sound weird. You should at least look into the various SQL Transaction Isolation Levels, and see if setting any of those would give you what you need.

Ken Smith
hi thanks for the answer.why do you say weird? how do you mean that?i assume that you clear the lock when you exit the application. what happens if for some reason the lock doesn't work (browser crash, etc)?
immuner
Good point about the locks not getting cleared - I forgot you were dealing with WCF/Silverlight here. I edited my answer to reflect that.With respect to "weird", what I mean is something like this: When I've run into requirements like the ones you've described, I always try to find out the actual business reason for them. And quite often, what gets passed down to me as a "requirement" is actually somebody's attempt at a "solution" to the real underlying requirement. Once you get the real requirement, you may be able to find a better solution than the one originally proposed.
Ken Smith
cheers i will have a look at this. however what do you think about q2?i think that i might not have explained well why i need this (in terms of requirements) so i will try to explain better.my app contains a map which allows to add points which are stored on a db.mode 1 is for when anyone can use this to view points on the map. this only reads the db.mode 2 allows some users (when enabled from admin) to also add a point on the map. this goes to q2 which is important to solve.mode 3 is more like a mini cms which changes more data for the app which might be solved by the sessionid.
immuner
Added some detail for #2.
Ken Smith