views:

158

answers:

2

Hi All,

I have a WCF service which has two methods exposed:

Note: The wcf service and sql server is deployed in same machine. Sql server has one table called employee which maintains employee information.

  1. Read() This method retrieves all employees from sql server.
  2. Write() This method writes (add,update,delete) employee info in employee table into sql server.

Now I have developed a desktop based application through which any client can query, add,update and delete employee information by consuming a web service.

Question:

How can I handle the scenario, if mulitple clients want update the employee information at the same time? Is the sql server itself handle this by using database locks ??

Please suggest me the best approach!!

+3  A: 

Generally, in a disconnected environment optimistic concurrency with a rowversion/timestamp is the preferred approach. WCF does support distributed transactions, but that is a great way to introduce lengthy blocking into the system. Most ORM tools will support rowversion/timestamp out-of-the-box.

Of course, at the server you might want to use transactions (either connection-based or TransactionScope) to make individual repository methods "ACID", but I would try to avoid transactions on the wire as far as possible.


Re comments; sorry about that, I honestly didn't see those comments; sometimes stackoverflow doesn't make this easy if you get a lot of comments at once. There are two different concepts here; the waiting is a symptom of blocking, but if you have 100 clients updating the same record it is entirely appropriate to block during each transaction. To keep things simple: unless I can demonstrate a bottleneck (requiring extra work), I would start with a serializable transaction around the update operations (TransactionScope uses this by default). That way yes: you get appropriate blocking (ACID etc) for most scenarios.

However; the second issue is concurrency: if you get 100 updates for the same record, how do you know which to trust? Most systems will let the first update in, and discard the rest as they are operating on stale assumptions about the data. This is where the timestamp/rowversion come in. By enforcing "the timestamp/rowversion must match" on the UPDATE statement, you ensure that people can only update data that hasn't changed since they took their snapshot. For this purpose, it is common to keep the rowversion alongside any interesting data you are updating.

Marc Gravell
Thanks Marc for the reply. It will be great if you explain this more. One thing I observerd that whenever I call method of WCF service , it always call the constructor of the service class. (class which is derived from [ServiceContract] interface. I mean to say it always intialize the connection from sql server with every methods ( Read or write ). Please correct me if I am doing something wrong or this is not correct behaviour !!
Ashish Ashu
for example when Client 1 call the update method it will intialize the sql server connection and update the employee information. What if the two or more clients wants to update the same employee information at the same time. does the SQL server maintains ACID properties ?? if suppose there is 100 clients then , is the 100th client has to wait more to update the employee info..
Ashish Ashu
A: 

Hi Ashish,

Another alternative is that you could instantiate the WCF service as a singleton (InstanceContext.Single) - which means there is only one instance of it running ever. Then, you could keep a simple object in memory for the purpose of update locking, and lock in your update method based on that object. When update calls come in from other sessions, they will have to wait until the lock is released.

Regards, Steve

MrCraze