Locking

I have a Stored Procedure serving an web application that returns data from
a
number of tables.
This dat resides on three seperate tables, two of which I join in a single
select statement, the third I use to lookup data dependent on some data
retrieved from the original select statement.
On some infrequent occasions the user will receive an error message stating
they are the victim of a deadlock etc, etc.
I have checked the logs on SQL but find no error message pertaining to this.
I then created a script in QA that access this Stored procedure in a loop at
1000 and then 10000 times, but whenever I run it, I receive no error
messages, just the recordset(s) returned correctly.
The data being accessed is live and updateable 24/7 by other users. This
Stored Procedure does not moify the data in any shape or form, simply reads
it.
Another developer I work with has suggested using the NOLOCK hint on all
reads, but I am loathed to use this as it may cause dirty reads.
1. Does the scenario indicate this is truly a deadlock situation or could
there be another cause for this error message (the web app is in .NET)
2. What is the general point of view on using the NOLOCK hint and should it
be used as a matter of course or very sparingly?
I am obliged for any assistance or guidance in this matter
(Note: This question has also been posted on SQLTeam Site)> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
Most probably. You didn't post the exact error message, but the way you phra
sed it sounds like the
error was indeed an SQL Server error message.

> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
Only use NOLOCK if you can foresee the consequences and accept them. But I s
uggest you look into
other possibilities first. These can include things like keeping locks for a
s short time as possible
(do not include things in the transaction that doesn't have to be there, tun
e queries etc),
accessing the objects in the same sequence, adding or removing indexes.
The reason you didn't see deadlocks in your loop is that you probably only h
ad one connection.
Deadlock (generally) occurs when you have two or more workers accessing data
in a way so that the
deadlock arises.
You can Google on deadlock (etc) and find plenty of text on the subject, inc
luding SQL server
specific text.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>I have a Stored Procedure serving an web application that returns data from
a
> number of tables.
> This dat resides on three seperate tables, two of which I join in a single
> select statement, the third I use to lookup data dependent on some data
> retrieved from the original select statement.
> On some infrequent occasions the user will receive an error message statin
g
> they are the victim of a deadlock etc, etc.
> I have checked the logs on SQL but find no error message pertaining to thi
s.
> I then created a script in QA that access this Stored procedure in a loop
at
> 1000 and then 10000 times, but whenever I run it, I receive no error
> messages, just the recordset(s) returned correctly.
> The data being accessed is live and updateable 24/7 by other users. This
> Stored Procedure does not moify the data in any shape or form, simply read
s
> it.
> Another developer I work with has suggested using the NOLOCK hint on all
> reads, but I am loathed to use this as it may cause dirty reads.
> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
> I am obliged for any assistance or guidance in this matter
> (Note: This question has also been posted on SQLTeam Site)|||The error message was thus...
Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable
dataTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ...etc, etc
which strongly indicates that SQL generated it.
In respect of the loop test, I was attempting emulate the web application
when it requests the data to ascertain whether it was on the server or not.
I
recently read an artcle on MSDN that indicated deadlocking problems could be
caused by Applications opening dual threads that block each other - leading
to a deadlock situation - but it didn't state what error message would be
created in this case over and above the regular SQL 1204/1205 error.
Any further thoughts of where to look for this?
"Tibor Karaszi" wrote:

> Most probably. You didn't post the exact error message, but the way you ph
rased it sounds like the
> error was indeed an SQL Server error message.
>
> Only use NOLOCK if you can foresee the consequences and accept them. But I
suggest you look into
> other possibilities first. These can include things like keeping locks for
as short time as possible
> (do not include things in the transaction that doesn't have to be there, t
une queries etc),
> accessing the objects in the same sequence, adding or removing indexes.
> The reason you didn't see deadlocks in your loop is that you probably only
had one connection.
> Deadlock (generally) occurs when you have two or more workers accessing da
ta in a way so that the
> deadlock arises.
> You can Google on deadlock (etc) and find plenty of text on the subject, i
ncluding SQL server
> specific text.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>sql