Doesn't the column set returned by the SELECT * include the column rowDiffHash ?

In which case the hash is non-deterministic - in the sense that repeating the UPDATE multiple times...

Here is my guess as to what is happening. It's only a guess since we don't have full information.

The classic reason for a deadlock is that 2 processes try to...

We use ODBC and SQL Server for our entire application. There is definitely a problem with intermittent loss of connection resulting in "Communication Link Failure" - we see this...

I'm a little puzzled by this.

There seem to be two points.

Firstly, we have a long-running SELECT which must not prevent modifications to the tables being queried. In practice, shared locks...

Jonathan,

thanks for the links. Bart Duncan's explanation of the problem is excellent. I wish I'd seen it before !

You say 'This isn't what I would call a design flaw.'...

Jonathan,

you are right to some extent. The index I am using - designed to pick out a few hundred rows from hundreds of thousands - is the correct index; usually...

OK, I have attached a ZIP containing the DDL for all tables and indexes, plus execution plans and statistics for the two SELECT statements. These were measured this afternoon on...

There are many examples of the SELECT/UPDATE deadlock. Here are a few:

SELECT CJ.JobID,

CJ.NeedsVerification,

JC.Name,

...

Yes, I think SNAPSHOT ISOLATION would solve the problem - it is, after all, the way Oracle does it. Unfortunately it's not available in SQL Server 2000. When and if...

Thanks for 2 prompt replies.

My point about having the SELECT in its own transaction was that I wasn't doing a SELECT in a transaction where a previous UPDATE, say, had...

With the greatest respect - I disagree.

Admittedly I am using SQL Server 2000 so perhaps this is something that has since been resolved. But I work on a complex real-time...

I've been using ODBC for over 10 years, and never seen a problem. Most of the applications we are currently shipping open 2 or 3 simultaneous connection to SQL Server,...

Hang on a minute. What are you trying to do here ? You have a query which is essentially "SELECT xxx FROM UMF_SUM_MATCHLOG WHERE LOAD_ID = :1" (ignoring the joins)....

1. A view is nothing more than a way of packaging a SELECT statement in a convenient re-usable form. Querying a view is no different from running the equivalent SQL...

Kenneth is right: if both connections locked Table 1 before Table 2, the problem would go away. In fact, there is no other way to fix deadlocks.

If you really can't...