Msg 1205, deadlocked on lock|communication buffer resources

Hi:
the proc ABC with just select statement to return a record set, which ran 4 minuts on SQL2000 SVR2000 with 8GB memory and 1000 DBS.

Due to above performance issue, it is moved to SQL2005 SVR2005 sp1 with 16 gb. Enterprise 64 bit. It runs only 18 seconds. However, once in a while, the following message appears:

Msg 1205, Level 13, State 52, Procedure ABC

Transaction (Process ID 877) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

With the same table structures and procs for every customer DB, we have moved 100 databases from the same SQL2000 SVR2000 to the same 2005 server SVR2005 without above errors. DBCC checkdb with 0 error.

thanks
-D

I have checked sysprocesses where blocked <> 0, with 0 records.
And there is no "Update" or "INsert" nor "Delete" nor "update statistic" at all in the proc.

From kb article, may help:

Error 1205
Severity Level 13

Message Text

Your transaction (process ID #%d) was deadlocked with another process
and has been chosen as the deadlock victim. Rerun your transaction.


Explanation

This error occurs when Microsoft SQL Server encounters a deadlock. A deadlock occurs when two (or more) processes attempt to access a resource that the other process holds a lock on. Because each process has a request for another resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the command that has the least processing time and returns this error message 1205 to the client application. This error is not "fatal" and may not cause the batch to be terminated.

Action

In some instances, a deadlock condition will cause a DB-Library function (such as dbsqlexec, dbsqlok, dbresults, or dbnextrow) to return FAIL. It is always the responsibility of the program to check the return codes from each DB-Library function. If FAIL is returned by one of these DB-Library functions, the program should cancel the batch and not attempt to continue. In some cases, it is possible to continue execution of subsequent functions in the batch. However, because a deadlock situation occurred and the functions that caused it were rolled back, later functions in the batch will probably fail with a more serious error, such as "object not found."

In other instances, a deadlock condition will not cause a DB-Library function to return FAIL. In these cases, the program must check for error message 1205 in the message handler and use the dbsetuserdata function to communicate this to the application (For more information, see "dbsetuserdata" in SQL Server Books Online.) The program must then check for the deadlock indicator after every DB-Library call and should cancel the batch if a deadlock is detected.

While it may seem unnecessary to cancel a batch after receiving a 1205 deadlock message, it is necessary because the server does not always terminate the batch in a deadlock situation. If the batch is not canceled, any attempt to submit a new batch can result in a DB-Library error 10038 "Results Pending."

You can also use the SET DEADLOCK_PRIORITY statement (LOW or NORMAL). SET DEADLOCK_PRIORITY controls how the session reacts when in a deadlock situation. If set to LOW, the process will be the preferred victim of a deadlock situation. If set to NORMAL, the session will use the default deadlock-handling method.

If a deadlock situation continues, it is often useful to use trace flag 1204 to gather more information. Trace flag 1204 prints out the deadlock chains and victim, as shown in this sample output:

*** Deadlock Detected ***
==> Process 7 chosen as deadlock victim
== Deadlock Detected at: 1998-09-10 16:39:29.17
== Session participant information:
SPID: 7 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 2

SPID: 8 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 1

== Deadlock Lock participant information:
== Lock: KEY: 2:117575457:1 (010001000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 7 ECID 0 Mode "S"
- Requested by: SPID 8 ECID 0 Mode "X"
== Lock: KEY: 2:117575457:1 (020002000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 8 ECID 0 Mode "S"
- Requested by: SPID 7 ECID 0 Mode "X"

This deadlock information can be interpreted as follows:

The first section displays the deadlock victim and time of deadlock along with the sessions involved in the deadlock. For each session, the current SPID, statement type, and a portion of the input buffer is displayed.
The second section displays details about the locks involved in the deadlock. From the output above, note that the deadlock involves key locks on table t1, index i1. The deadlock output shows which processes own the locks involved in the deadlock and which sessions are waiting for the locks to be granted as well as the associated lock modes.
The process that has generated the least amount of log volume will, by default, be chosen as the deadlock victim and be rolled back automatically. To influence which session is rolled back, set the DEADLOCK_PRIORITY for a session.

I have re-checked on both servers dbs comes from SQL2000 and moved to SQL2005:

1. There is no blocked field > 0 in both sysprocess table
2. There is no blocking at all on both server

Finally, I changed the subquery of 'select distinct xxx_ID from table tblXXXX where zipcode <> 90900' to a #temp table at the begining of the proc, then the locking error is gone on SQL2005. This proc has been run 4 years on the SQL2000 without any problem, but on the 1st day of moving to SQL2005 with 'locking error'.

what is the real cause?
-D