sql locking on silverlight app

Question

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

Answer 1

To avoid that the user does unnecessary (changes because he is not allowed to at that time), one does a pessimistic lock.

So the optimistic lock approach you suggest is from use perspective work flow something completely different. And the dilemma of the OP is, I assume, the same as ours.

Silverlight and service communication is asynchronous and one would have to put an open transaction into a Session or similar; and also one would need some sort of pooling because one can't have infinite connections open to the database.

There is another approach to pessimistic lock, not though the transaction, but to use a table where you write a "locktoken" (some information aboout what table and row is locked) that the application can use to notify another user who trys to select that data for edit that it is already being edited.

The problem with removing the lock is tough the same as when one uses the transaction approach.

Answer 2

If you were using WCF RIA Services you may opt to solve this by taking a different approach to the problem rather than locking up-front: if a user goes to commit changes that vary from what is in the database since they opened the data in their form you would inform them of the conflict(s) and give them the option to accept or reject the changes in part or whole. This is because RIA Services gives you the ability to have changesets before you submit changes to the server.

Yacine Khammal of Pluralsight training has a fantastic demo of this in play here (paywall, but the best $29 I have ever spent). See the video called "Demo : handling validation and concurrency errors."

Answer 3

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.