3

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

|improve this question
6

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.

|improve this answer
  • I understand that there is a deadlocked resource. However, this deadlock problem happens only after I get the linked server error. – kanu Jun 22 '12 at 19:11
  • Then you need to find out what exactly is causing the deadlock. – Thomas Stringer Jun 22 '12 at 19:12
  • The query fetches rows from linked servers to another server. There are many reads which are happening on the linked at the same time. My concern is that could concurrent reads be an on linked server ? – kanu Jun 22 '12 at 19:16
  • Concurrent reads (shared locks) will never cause a deadlock. Blocking, sure (depending on how pessimistic the isolation level is), but not deadlocks. Honestly, it's hard telling what's happening without seeing a trace capturing deadlock info. – Thomas Stringer Jun 22 '12 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.