Under MSSQL 2000 I get a deadlock on a transaction. This victim transaction contains one single SELECT statement joining 2 tables using the READPAST hint. I thought this hint will cause the transaction not be blocked on locks. How can it happen that the transaction is part of a deadlock?

 The query looks like this:

SELECT MIN(POINTER.ID) AS IDX FROM POINTER, PROCESS (READPAST)
WHERE POINTER.PROCESS_ID = PROCESS.ID AND PROCESS.DEFINITION_ID IN (1, 2, 3)
AND POINTER.STATUS != 1 AND POINTER.STATUS != 2 GROUP BY PROCESS.ID

The transaction only contains this one query and still is part of a deadlock. I though that the READPAST hint will prevent any blocking to occur on this query? Do I have to use READPAST after each table in the join query? Or does it ignore the hint when handling indexes or what can be the cause of the lock?

I currently have no output from the profiler etc. Anyone has an idea how this could be possible?

Thanks
Tom

I would run sp_lock and examine the locking that's going on.  If I recall correctly, READPAST only reads past row locks; not page locks.

You may need better indexes or shorter transactions on the PROCESS table.   Hard to say without a bigger picture of what's going on.