CODEWITHSUNDEEP
c#sqlsql-serversilverlight
Simon Griffiths
Simon Griffiths

Reputation: 165

Locking table sql server

I have multiple silverlight applications bolted within an asp solution, multiple users may be using the same application on the same set of data at any one time. The silverlight applications are designed such that they pull data from the database, work on it locally, and write it back once they user is happy with their modifications. The problem with this however, is that if two users are using the same application on the same database, they are going to overwrite each others changes.

There isnt going to be a huge number of users using the same database at any one time, as there are numerous databases, and it isnt essential that more than one user should be working on the same set of data at any one time. My idea is to lock each table for a single user to have exclusive access to read/write to the server, denying any other user access to even read from the locked tables in question.

How would i go about applying the table locks and releasing them? I know it can be done on a per statement basis, but I need the user to have totally exclusive rights to this data. A problem that I can forsee is releasing the lock, there would need to be some kind of timeout, so the table isnt permanently locked for the one user.

Upvotes: 1

Views: 1559

Answers (3)

Purplegoldfish
Purplegoldfish

Reputation: 5284

Im not sure what the 'things' you are working on in your database are, but I had a similar issue which I handled in my application rather than using locks on tables etc.

Basically I had various documents (like an order or receipt) which could be opened and then edited and saved. What I did to implement locking on these was to have a a table with Type, DocID, UserID, LockedTime. Primary key on the DocID and Type (type indicates the type of thing you are going to lock so this allows you to use this approach on multiple tables etc)

Then when a user tries to open a document i would first run a SP that checks if the document is locked to another user, then if not allow opening if locked i implemented a read only modfe. When the user saves their changes it unlocks the document giving everyone else access again.

Im not sure if this approach would be of any use to you, but it is quite simple and quick to implement as it doesnt require complex changes to existing tables.

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113242

I wouldn't even begin to attempt this, as you'll have horrible concurrency problems with more than 1 user - including 1 user who uses more than one window, or returns after a crash on their system, etc.

A simple way to deal with this would be to have a timestamp or change count, and if the timestamp or change count has changed since the data was obtained, then warn the user they are going to over-write someone else's work. From that starting point you can go further and let them pick and choose changes, manually copy in changes from the other person's, compare changes, etc.

Upvotes: 6

StuartLC
StuartLC

Reputation: 107247

IMO locking databases artifacts (either physically using SQL locks) or logically (using your own locking tables) can be problematic - it limits the scalability of your app and can cause frustration for users when their colleagues go out for coffee. Your app also needs to be designed so that users 'check out' an item to indicate intent and hence create the lock (as opposed to just browsing data where you don't want a lock)

As an alternative, look at patterns of Multiversion concurrency control. A common, non intrusive way is to add a timestamp to your record entities, and every time just before you write an update, fetch the record again and compare timestamps to see if another user has made a change in the interim.

Upvotes: 1

Related Questions