Update checking

  • Thread starter Thread starter Tommy Malone
  • Start date Start date
T

Tommy Malone

I have an application that uses a web service to check for user updates
before calling more extensive code to sync data. My question is, how is
concurrency handled, if concurrency should even be my concern, where many
users call the same web service to check the same database (although each
user will have user-specific data not shared by other users) for changes? In
other words, am I going to run into trouble when a hundred users hit the
"User" table for a HasChanges value every minute? Is there another,
preferred strategy to communicate that there are db changes that need to be
downloaded? What happens to this code when it gets called at the same time
with two different users?

<WebMethod(CacheDuration:=3600)> Public Function Ping(ByVal UserName As
String) As Boolean

Dim datareader As SqlClient.SqlDataReader

Dim cmdUserChanges As New SqlClient.SqlCommand("SELECT HasChanges FROM
MohawkUsers WHERE UserName = '" & UserName & "'", connMohawk)

Try

connMohawk.Open()

datareader = cmdUserChanges.ExecuteReader

While datareader.Read

Ping = datareader.Item(0)

End While

connMohawk.Close()

Catch ex As Exception

Finally

connMohawk.Close()

End Try


End Function
 
First, get rid of that dynamic sql and replace it with a parameterized query
or a stored procedure http://www.knowdotnet.com/articles/storedprocsvb.html

Next, you can trap a concurrency exception and polling like you are asking
about is very inefficient. There are a few different ways to handle
concurrency but basically you can either do a last in wins, first in wins or
prompt the user
http://msdn.microsoft.com/library/d...skPerformingOptimisticConcurrencyChecking.asp
http://sqljunkies.com/Newsgroups/microsoft.public.dotnet.framework.adonet/2004/5/11/212008.aspx

In ADO.NET 2.0 there's a notification service register that will be able to
broadcast notifications that may help you but it's also not intended for
100's of users and won't be out for a while.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
 
In my case, it appears my terminology needs to be corrected. In short, is it
correct to say that concurrency will not be an issue for me, so long as my
users are not accessing/changing the same record? Does concurrency only
apply to records and not to tables?

My real problem (beyond the dynamic SQL) is twofold.

1. How do I efficiently poll for changes? Keep in mind, my database is
"accessible outside my trusted domain" via web service.
2. I need a better understanding of connection management (among many other
things, but that's another thread or 10). In your article you state "a
failure from the Database opening is much different from the proc failing".
How can I use a Try/Catch to minimize those failures without causing the
whole procedure to fail? In other words, what goes in the Catch block to
make the code try to open the connection again (and/or loop until it does it
successfully or fail gracefully)? Where does connection pooling come into
play?

And, finally, is it possible to close a connection during a read or is that
"managed" by VS? For example, could one procedure "conn.close" while another
is in a "while datareader.read"? If so, how is this avoided?

Thanks for your time.
 
In ADO.NET, concurrency exceptions happen if the data you queried has
changed in between the update and the original select. If you for instance,
walk through the datatable on your own and manually build a command one at a
time, calling executenonquery, you'll never have a concurrency exception.

You cna technically lock a whole table just as you can a row, but under the
scenario you are using you're not locking either except for that split
second it takes to fire the query.

1)The most efficient way to poll is going to probably employ messagequeus.
You could write the changes to a queue and then you can poll the queue which
may not seem like it's more efficient than pollign the db but it is.
However, this isn't a trivial task. To that end, polling in general,even if
you just fire a standard select statement isn't going to be problem free.
You are going to invariably pull over more data than you need and consume
more resources than you need. To that end, i'd say the short answer is that
polling of this sort isn't an efficient beast no matter how you implement it
2) You'd try/catch seperate pieces of the proc. The first of whichis open.
Let's say the connetion's attempt to open blows up. You may have a standby
server that you could try to connect against or you could send back a
notification and ask the user if they want to work in an offline context
etc.. The same with the query. If the query blows up you could see what it
was and basically let the user work in a similar offline mode. It's pretty
much the same way Outlook works in this regard...pretty much loses a lot of
functionalityg wihtout network connectivity but at least having the offline
option is a lot better
3) Yikes. I'm assuming you are talking about a multithreaded environment?
Well, sharing an active between connections is bad news. You can trap the
Statechange http://www.knowdotnet.com/articles/connections.html event to
find out if this happens but generally, this couldn't really happen b/c the
while loop would block unless you did have a async scenario. in this
instance if you wanted to allow the user to cancel you may want to have a
while(rdr.Read() && ShouldContinue){

}

If you have it running async you could set the ShouldContinue to false which
would cause execution to stop. This could get a little complex but it's
definitely doable.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/
 
What is the newsgroup etiquette around asking so many follow-up questions?

I really appreciate your considerate and thorough answers. If I may press my
luck a bit further...

1. Since you brought up Outlook, it's that type of polling I would like to
accomplish. My thought was to use a simple query with a "username" param
against a "haschanges" bit field to signal more involved queries against the
db. The trouble, it seems, is the volume of calls against the db (wasting
resources) or the complexity of message queues. How is this accomplished
and/or mitigated by mail servers or applications with similar needs?

2. In asking about closing connections which are "in-use", I'm referring to
the web services scenario. Multiple users would call on a single web service
which accesses the db. If the end of the procedure is to close the
connection, what happens to another user still in the middle of the query?

Thank you again for your time.
 
--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/
Tommy Malone said:
What is the newsgroup etiquette around asking so many follow-up questions?
Click to expand...

AFAIK there aren't any rules regarding it and if there are, you aren't
breaking any of them that I know of.
I really appreciate your considerate and thorough answers.
Click to expand...
;-)
If I may press my
luck a bit further...

1. Since you brought up Outlook, it's that type of polling I would like to
accomplish. My thought was to use a simple query with a "username" param
against a "haschanges" bit field to signal more involved queries against the
db. The trouble, it seems, is the volume of calls against the db (wasting
resources) or the complexity of message queues.
Click to expand...
--Yep, this can be a challenge and unfortunately, there's not exactly any
hard and fast rules. In this environment there are a ton of things that are
going to affect performance -- it's not uncommon to see an effective polling
strategy get sluggish once a bunch of records continue to be added.
Moreover, it's not uncommon to see a plan work well when you have X users
but then start degrading when you have X + Y users. If performance is
critical you are probably going to want to spend some time regularly tuning
your queries, perhaps load balancing and all the other standard performance
tweaks.
How is this accomplished
and/or mitigated by mail servers or applications with similar needs?
Click to expand...

I'm not sure about how Outlook works but I suspect that it uses the Service
based architecture pattern. The server is registerd as a publisher and the
clients as subscribers. They all listen for messages sent out by the
server. However this is conjecture on my part as i'm barely proficient with
Outlook.
2. In asking about closing connections which are "in-use", I'm referring to
the web services scenario. Multiple users would call on a single web service
which accesses the db. If the end of the procedure is to close the
connection, what happens to another user still in the middle of the query?
Click to expand...

I guess it depends on how the service is set up but I can't imagine a
scenario where you'd want to use just one connection shared across all
users. This would almost certainly lead to terrible performance and data
consistency issues. Anyway, if your method instantiates a new connection,
hits the Db, grabs the data and closes the connection, that will be exlusive
to each user instance As such, whatever I'm doing with my connectino and
query will be completely isolated from what you are doing. To that
end,closing a connection in one session will have no effect on any one
else's session. The main thing to remember is to close those connections
ASAP so that they are returned to the connection pool - that and using the
same connection string where possible so the optimizations afforded by
pooling can be taken advantage of.

So basically you might have something like myService.GetDataSet and
myService.SubmitDataSet. Each would have its own instance of a connection.
you could call each of these asynchronously if that's what the app needed
but either way, I could fire a SumbitDataSet and then wait until it was done
for instance and call GetDataSet. Or you could do it midstream but depending
on how fast the updates occur and how big the query is you might end up with
stale data so you'll need to play with this a bit.

Either way, if you keep the connections and methods atomic you shouldn't
have a problem with process A cancelling process B's connection or vice
versa.

HTH,

Bill
 
Back
Top