deadlocks and datareaders

[Guest]

We are reguarly getting deadlock errors on a page when it calls
SqlDataReader.Read(). If DataReaders are read only, forward only 'firehoses',
why would this be causing deadlocks? What can be done to resolve the problem?

**************************************************
Error description
**************************************************
Message: Transaction (Process ID 160) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Source: .Net SqlClient Data Provider
Stack Trace: at System.Data.SqlClient.SqlDataReader.Read()

 

[ijustok]

Only you run the program.

I think maybe someone do a transaction when you read

 

[William \(Bill\) Vaughn]

Any query that returns rows creates share locks on the rows and pages it
needs to access. Other applications can also read those pages but other
applications are blocked from updating the rows while they were locked. If
the application you're executing is holding those locks and another
application (or even your own on another connection) is holding resources
(pages, rows) you'll get deadlocks. Transactions can exacerbate this
problem.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik [MVP]]

Dan,

A bad way to fix this problem is to use Select NOLOCK or run your queries
under the ReadUncommitted isolation level - that will resolve the deadlocks,
but it will give you dirty reads out of the database (which is why it is
bad).

BTW, are your transactions running under a higher isolation level - such as
serializable?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx