I have found one other thread on this topic, but the responses only
described "bad" ways to fix the problem. I'd like to know if there are
any "good" ways to fix the problem

I have a class with a private, instance-level SqlDataReader reference
(so it's not being shared by multiple objects at runtime). That
SqlDataReader is only used to ExecuteReader once, and then build a
DataTable out of the results. However, we occasionally get Deadlocks on
the READER when the ExecuteReader is attempting this read.

The "bad" fix that I read was to change the reader characteristics to
NOLOCK to allow dirty reads. This really isn't that bad, actually,
because I only care about the data as it was at the moment of
execution, if it changes after that, It will be caught in a subsequent
execution of this method.

But, are there any "good" ways to fix this issue?

Thanks for any help,

Jason

Hi,

Whether NOLOCK is good or bad is completely subjective to what you are
trying to do. This will allow the data to be read from the table as it
currently stands. As you can handle the fact that data may change this
should not bother you (especially in a stateless environment). An
alternative to NOLOCK is READPAST. When this is applied it will skip over a
row that is locked by another transaction and only read committed records.
This may result in rows that you would ordinarily expect in the result set
to been missing. These lock hints at the end of the day are provided for
situations where locking will occur frequently/infrequently and there is no
other reasonable alternative.

One thing you should try and do is find out what is coursing the locking.
SQL Server has three levels of locks: Table, Page and Row. The first thing
is to determine which lock is being used has this might have a strong
bearing on your selection of a lock hint. For example if a table lock is
being applied there would be no point in using READPAST. In Enterprise
Manager open the server that is running you database then navigate to
<server>/Management/Current Activity/

Under there are two sub folders 'Locks/Process ID' and 'Locks / Objects'. In
Locks/process ID it will display the user coursing the blocking and the
user(s) who is being blocked. Drill down into the blocking 'spid' and you
will see a list of tables and the lock type that has been applied to it. If
you double click on the table it will show you the query been executed
against it.

One problem I come across is where a programmer starts a transaction when
the user clicks edit then save the information as they progress from one
form to another and only committing the transaction when the user click save
/ or rolling back on cancel. As a result this is completely dependant on the
user interaction. In some cases they have been know to leave there computer
on and go on holiday.

- Mike

---------------------------------------------------------------------------------
<a href="http://www.cogitar.net"> Cogitar Software. (
http://www.cogitar.net ) </a>
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
---------------------------------------------------------------------------------

"jason" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have found one other thread on this topic, but the responses only
> described "bad" ways to fix the problem. I'd like to know if there are
> any "good" ways to fix the problem
>
> I have a class with a private, instance-level SqlDataReader reference
> (so it's not being shared by multiple objects at runtime). That
> SqlDataReader is only used to ExecuteReader once, and then build a
> DataTable out of the results. However, we occasionally get Deadlocks on
> the READER when the ExecuteReader is attempting this read.
>
> The "bad" fix that I read was to change the reader characteristics to
> NOLOCK to allow dirty reads. This really isn't that bad, actually,
> because I only care about the data as it was at the moment of
> execution, if it changes after that, It will be caught in a subsequent
> execution of this method.
>
> But, are there any "good" ways to fix this issue?
>
> Thanks for any help,
>
> Jason
>

Ahh, this is incredibly helpful information.

I agree, it sounded like the propriety of using NOLOCK seemed dependent
on the context of the application needs.

So I've looked at the lock lists in the current activity section of
enterprise manager. What I'm finding, is that there are quite a lot of
'DB' type locks. in fact the vast majority are 'DB' lock types. I only
find it alarming because of what it suggests, I have no idea what it
actually means. Most of these lock statements are very simple queries
from a single table. Would that cause a 'DB' lock? What exactly is a
'DB' lock type? Surely it's not locking the entire database? That would
certainly explain deadlock issues, but with this many locks, I would
actually expect a lot MORE deadlock issues.

Thanks again for the information,

Jason

Don't worry about the DB lock types you will probably find there on MSDB
database as well. If you look in Process Info it will show you who each ID
belongs to these will be system processes and background processes (lazy
writer etc..) and SQL Agent etc. Looking in the Locks/Process ID there are
several locks of which you probably won't see them but for your info these
are:

DB=Database
FIL=File
IDX=Index
PG or PAG (depends which version)=PAGE
KEY=Key (row lock within an index)
TAB=Table
Ext=Extent (not obvious but a lock 8 continuous pages)
RID=Row Identifier

The ones your interested in are TAB, PG and RID. Then you need to look at
the Mode. The three your interested in are S = Shared (read-only operations
can be run i.e. Select statements), U = Update and X = Exclusive which is
applied during data modifications by Insert and Delete. Update can also be
promoted to this as well (just in case BU = Bulk Update)

The last thing is the Status of which there are three but one you are very
unlikely to see. These are GRANT, which shows that the spid has the lock.
WAIT, which shows the spid has requested a look but is being blocked by
another process. Last is CNVT which basically means a lock is being
converted from one state to another.

The DB lock on msdb is by SQL Server to stop users from altering core data
and stats that it need to run correctly. You can however add DB lock to your
own database by simple restricting the access in the database property page
to a single user.


- Mike

---------------------------------------------------------------------------------
<a href="http://www.cogitar.net"> Cogitar Software. (http://www.cogitar.net)
</a>
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
---------------------------------------------------------------------------------

"jason" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Ahh, this is incredibly helpful information.
>
> I agree, it sounded like the propriety of using NOLOCK seemed dependent
> on the context of the application needs.
>
> So I've looked at the lock lists in the current activity section of
> enterprise manager. What I'm finding, is that there are quite a lot of
> 'DB' type locks. in fact the vast majority are 'DB' lock types. I only
> find it alarming because of what it suggests, I have no idea what it
> actually means. Most of these lock statements are very simple queries
> from a single table. Would that cause a 'DB' lock? What exactly is a
> 'DB' lock type? Surely it's not locking the entire database? That would
> certainly explain deadlock issues, but with this many locks, I would
> actually expect a lot MORE deadlock issues.
>
> Thanks again for the information,
>
> Jason
>

Thanks, I did do some digging and found out what the DB locks were.

What's odd, I suppose, is that at no point during the day did my little
random peeks at the object locks and process id locks find a table,
page, or row lock for the database in question.

I know through error messaging which stored procedures are facing the
deadlock condition, but I don't know the scope (row/page/table). And
I'm not sure if the frequency (which I've asked in a new question on
the sql group since it's not really a ADO.NET issue) is reasonable or
unreasonable. And if it is unreasonable, how to go about fixing it
exactly. I've read about alterations one can make to the resource
access order, but I haven't found details on that yet.

Anyway, thanks very much for your help.

Jason