Thread Safing "Select" Linq Queries?

Question

I understand how editing rows can cause concurrency issues, but concurrency issues being caused by selecting rows is something I do not understand.

If a query selects data from a database, how can a concurrency issue arise? Is it if there is a change made to the data I'm selecting, things will blow up?

In any case, if there is a concurrency issue caused by a select query, what is the best way to handle it? This is what I have in mind, but I wouldn't be surprised at all if it were wrong.

try
{
    var SelectQuery = 
    from a DB.Table
    where a.Value == 1
    select new {Result = a};
}
catch 
{
    //retry query??
}

Answer 1

In this case your select operation essentially amounts to a read / query. Even read only operations can cause concurrency issues in an application.

The simplest example is when the object being read from has thread affinity and the read occurs from a different thread. This can cause a race since the data is being accessed in an improper way.

The best way to handle a concurrency issue is to simply avoid it. If you have 2 threads playing with the same peice of data using a lock to serialize access to the data is probably the best approach. Although a definitive solution requires a bit more detail.

Can you explain what is happening here and why the race is occurring? Do other threads modify this object while you are reading it?

Answer 2

When your query is run, a SQL query will be generated to correspond to your query. If other threads (or anything else) are attempting to modify the tables involved in your query, the database server will generally detect this and take care of the logic necessary to keep this from causing any real problems. It may take a little longer for your query to execute if it keeps bumping heads with update statements, but the only real problem would be if the system detects that some combination of running transactions is actually causing a deadlock. In this case, it will kill one of those connections. I believe this would only happen if your statements are attempting to update database values--not from selects.

A more important point to make, looking at your example, is that the code that you put in the try/catch block doesn't actually do any querying. It just builds an expression tree. The SQL query will not actually be run until you do something that causes this expression tree to be evaluated, like calling SelectQuery.ToList().

Keep in mind that there are a number of things that can "go wrong" when you're trying to query a database. Maybe somebody's doing massive updates of the data you're trying to select, and your connection times out before finishing the query. Maybe a cable gets bumped, or a random bit of cosmic radiation causes a bit somewhere to get lost. Then again, maybe your query has something wrong with it, or maybe the database context you're using is not synchronized to the database schema. Some of the things that could go wrong are only intermittent, and you could just try again like your question suggests. Other things might be longer-lasting, and will keep recurring. For these latter cases, if you try to repeat your action until you stop getting errors, your thread may hang there for a very long time.

So when you're deciding how to handle database connection problems, pay attention to how often you expect each type of problem to occur. I have seen code that attempts to run a transaction three times before giving up, like this. But when it comes to everyday queries, this sort of thing happens so rarely that I personally would just allow the exception to trickle up to where the user interface can say "There was an unexpected error. Please try again. If the problem persists, contact your administrator." Or something like that.