Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server X

Question

I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same query:

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

Does this mean that there is a limitation to the number of reads that can be performed through linked server ? If so, what is the limit ? or could this be happening because of some other reason ?

Also the query used is as follows :

SELECT  T.Col1, T.Col2, T.Col3, PG.Col1, PG.Col2, PG.Col3
INTO #TMP_OUTPUT 
FROM  linkedserver1.db1.dbo.table1 T WITH (NOLOCK)
      INNER JOIN #temp2 PG 
      ON T.Col1 = PG.Col1
WHERE T.Col3 = somevalue

Thanks, Kanu

Answer 1

No, this is happening because you have a deadlocked resource. What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.

This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim to be chosen (which is why you see the error you posted).

Deadlocks happen. If they are extremely infrequent then it's probably not an issue to worry about. But if they are happening all the time, then you need to drill down into what query(ies) is(are) causing them. You may need to rework your DML, or alter your transaction isolation level if it is appropriate (allowing dirty reads is not the answer), or even go with an optimistic isolation level like Snapshot Isolation or Read Committed Snapshot Isolation. But great consideration needs to be made here, as those optimistic isolation levels utilize row-versioning which will hammer tempdb, so if you don't have an optimial configuration for tempdb you could bring your performance to its knees.