NoLock vs ReadPast

I have been experiencing deadlock errors with two stored procs that I am using.

SP1 is a read query that gets all unprocessed orders (status = 0)

SP2 is an insert query that inserts new orders (Status = 0) uses a transaction.

I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises.

If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct?

Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct?

So I think that it is better to use READPAST than NOLOCK. Any orders that have status = 0 not picked up will get picked up on the next round when SP1 is executed again.

Any thougths or suggestions are always appreciated.

Jawahar

I have a lot of experience with NOLOCK. The basic different in NOLOCK and READPAST is that NOLOCK will read the uncommitted data, preventing blocking because of that specific read. However, READPAST skips those rows. If you are updating a row, then NOLOCK reads the uncommitted data if the transaction is still open. If you use READPAST, it doesn't read the row at all, as I understand it.

Since I have a lot of experience with NOLOCK, I would suggest NOLOCK because I understand it a lot better.

I'm not all that convinced that your reads are causing DEADLOCKS however. I wonder if you are getting multiple inserts or updates that are blocking each other. The read locks (READ COMMITTED), by default, will just wait until the insert is done and then read the new inserted row. NOLOCK just tells it not to wait and reads UNCOMMITTED.

To test this, I would use NOLOCK and see if you still have the same problem.

|||

I would almost never suggest you use NOLOCK in a production environment. You don't want to have any chance that you have two users fetching the same row to process. So I would suggest you use readpast to read only committed rows, but skip those that are being inserted, in case that row fails.

As for deadlocks, I can't exactly fathom why this is occurring from the limited information. It is possible that you have indexing problems, causing full table scans to occur on selects... Can you post the table structure and queries.

|||I disagree about the production comment. If you are pulling reports, you want to be able to pull the same record quickly--regardless of locks. As for selecting the same record by two users at the same time, that would occur anyway whether you use NOLOCK or not--assuming that there is not an open transaction modifying the situation.|||There is a possibility for rows to show up twice when using NOLOCK because of page splitting so beware of the hint unless you know exactly how your data is used. This does not mean you should never use NOLOCK of course, there are many situations in which it is completely sane to use it but just remember the consequences.|||

Chris:

I think you have misunderstood what Louis means. I think here when he says "production" he means as part of a record-updating process -- in which case if you are reading a record for update you would never want a dirty read -- using a NOLOCK hint -- but always want a "clean" read of the data.

Dave

|||

Dave,

I think you are correct. I agree with the statement in that case :)

|||

All thank you for your insight

Here are my two sp that cause the Deadlock to occur sometims - I have poseted the queries ony not the whole SP with input paramters etc but the queries are the guts of the SP. I did tunr on Deadlock tracing and these SP were identified as the cause of the deadlock situtation - Many thanks -Jawahar

SP 1 - Read (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
set rowcount @.nMaxMessages
select A.* from MsgRequest A
where ProcessingCode = 0
and RequestID = ( select Min(RequestID) from MsgRequest B where A.ClientID= B.ClientID and B.ProcessingCode = 0 )
and A.ClientID NOT IN (select ClientID from MsgRequest where ProcessingCode = 1)
order by ReceiveDT ASC
END

SP 2 Insert (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
BEGIN TRAN
insert into MsgRequest ( ReceiveDT, ClientID, CommandCode, ProcessingCode, MessageLen, QueryParameterString,
MessageData, ServiceOrderID, NewServiceOrderFlag, ClientVersionNumber)
Values ( getdate(), @.ClientID, @.CommandCode, @.ProcessingCode, @.MessageLen, @.QueryParameterString,
@.MessageData, @.ServiceOrderID, @.NewServiceOrderFlag, @.ClientVersionNumber)
if (@.@.error != 0)
BEGIN
RAISERROR 20001 'Error in csp_MsgRequestInsert'
ROLLBACK TRAN
RETURN(1)
END
COMMIT
return (0)
END

|||

Jawahar:

Is the RequestID column unique in the MsgRequest table? That is, is the RequestID a key to this table? Also, is the data returned by this procedure used as a select list or is this potentially used as the basis for a record update?

Dave

|||

Dave,

Yes the RequestID is the Primary key

The data from the select is processed row by row using a multithreaded system. We run a windows service (multithreaded) to process the data (new orders).

Jawahar

|||

Jawahar:

There are a few other things that I need to know:

How many rows are in the MsgRequest table ?|||

You need to first determine the cause of the deadlock before trying to use locking hints. What version of SQL Server are you using? Did you enable trace flag # 1204? In SQL Server 2005, there is a new trace flag# 1222 and trace events that will help identify the cause. See link below for troubleshooting steps for SQL Server 2000:

http://support.microsoft.com/default.aspx/kb/832524/

|||

Jawahar:

Umachandar is right about the need to fully understand your deadlock. But I went ahead and attempted to mock up your select query.

First, I created an index on the MsgRequest table based on (1) ProcessingCode, (2) ClientID and (3) requestID. I figured that the requestID component was not completely necessary but I wanted to avoid some bookmark lookups so I included. When I got all done testing I tried recreating the index without the requestID component and found that when I removed the requestID component from the index my logical reads increased from 38 to 4007 so I left it in.

I was able to improve on the performance of your select query by eliminating one of the scans through the MsgRequest table. The query I used was:

select A.*
from msgRequest A
inner join
( select clientId,
max (processingCode) as max_processingCode,
min (requestID) as min_requestID
from msgRequest p
where processingCode between 0 and 1
group by clientId
) b
on a.requestID = b.min_requestID
and max_processingCode = 0
order by ReceiveDT ASC

This query returned for me the same rows as your original query was returning and ran with far less logical IO. I don't like the A.* syntax, but like you said ... Sometimes you got what you you got. Hopefully, this query will provide (1) a smaller lock profile and (2) a much smaller IO profile.

Dave

|||

Jawahar:

One thing that I failed to point out is that my test was based on a relatively low cardinality of the '0' and '1' processing codes compared to all other processing codes. I used a 1000000 row test table with 8000 rows in the 1 process state and 800 rows in the 0 process state because it looks to me like we are talking about a "new record" process.

Dave

|||

Dave

Thanks for your new suggestion on the Selection query - sorry for the late reply

I am using SQL Sever 2000

I did run the trace DBCC Trace 1204 and 3605 and found that the two SP I have listed are the ones that are involved in the Deadlock. The Select SP being the victim each time. Currently I only have an index on the RequestID column, but adding the ClientID and Processing code is a good Idea.

Out table at present has 600, 000 records. 99.33 % of the records have already been processed and have a Processing code of = 2 and we never touch those records. At any given time we should not have more than 15 to 20 records of the Processing code = 0 and 15 to 20 records Processing code = 1.

would the DBCC trace be helpful to you?

Thanks again for your suggestions I will try yor revised query. Another process that I might add is to archive Rows that have already been processd to an archive table to help reduce the size of the request table.

-jawahar

NOLOCK and READPAST on same table?

Is it possible to use With (NOLOCK) and With (READPAST) in the same
SELECT query and what whould be the syntax?

@.param int

SELECT
myRow
FROM
dbo.myTable WITH (NOLOCK)
WHERE
myRow = @.param

Thanks,
lqlaurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is it possible to use With (NOLOCK) and With (READPAST) in the same
> SELECT query and what whould be the syntax?

The syntax would be

SELECT ... FROM tbl WITH (NOLOCK, READPAST)

But I got the error message:

Server: Msg 650, Level 16, State 1, Line 1
You can only specify the READPAST lock in the READ COMMITTED or
REPEATABLE READ isolation levels.

Which makes sense. READPAST means that you skip rows that you would be
blocked on, and you will not be blocked with NOLOCK.

What are you trying to achieve?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 17 Sep 2005 14:04:40 -0700, laurenq uantrell wrote:

>Is it possible to use With (NOLOCK) and With (READPAST) in the same
>SELECT query and what whould be the syntax?

Hi Lauren,

The NOLOCK hint specifies that all locks should be disregarded. The
READPAST hint specifies that locked rows should be skipped. This means
that these lock hints are mutually exclusive.

The syntax for combining hints is
WITH (NOLOCK, READPAST)
which will result in an error for this combination. The use of several
hints might be useful for other combinations, though:
WITH (TABLOCK, XLOCK)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have a table dbo.myTableName to which hundreds of users are
UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
against that table 24/7.
I am tring to remove the slowdown caused by rows that might be in use
when users run a query that looks like: SELECT myID, myRow1 etc. FROM
dbo.myTableName WITH (READPAST) WHERE StartDate = @.DateParam|||So which should result in a faster scan of the table with fewer
possibilty of locking in a situation where:

I have a table dbo.myTableName to which hundreds of users are
UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
against that table 24/7.
I am tring to remove the slowdown caused by rows that might be being
written to when users run a query that looks like: SELECT myID, myRow1
FROM dbo.myTableName WITH (READPAST) <OR> WITH (NOLOCK) WHERE StartDate
= @.DateParam

?|||On 18 Sep 2005 08:28:14 -0700, laurenq uantrell wrote:

>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
>I have a table dbo.myTableName to which hundreds of users are
>UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
>against that table 24/7.
>I am tring to remove the slowdown caused by rows that might be being
>written to when users run a query that looks like: SELECT myID, myRow1
>FROM dbo.myTableName WITH (READPAST) <OR> WITH (NOLOCK) WHERE StartDate
>= @.DateParam
>?

Hi Lauren,

Here's an answer you probably don't want to hear :-)

Try to use neither. In both cases, you'll return information that is
besides the truth. In the case of (READPAST), rows will be missing in
your result set that should be included. In the case of (NOLOCK), you'll
return data that is currently being changed, but might still be rolled
back (e.g. because it violates a business rule).

You should not be trying to get "a faster scan of the table" - you
should be trying to eliminate table scans at all. Especially on a table
that is under heavy use by hundreds of users. Making sure that all
inserts, updates and selects can use appropriate indexes will go a long
way toward preventing table scans. This will also mean that you'll spend
far less time waiting for a lock to be released on a row you didn;t want
to see after all!!

If you're still facing blocking issues after this, you might want to
consider duplicating the table: one "live" table for all the inserts and
updates, and a "reporting" copy for all the selects. Set up a routine
that will periodically (e.g. every 5 minutes, or whatever time delay is
acceptable in your situation) copy over all changes from the "live"
table to the "reporting" copy.

Now to your original question:
>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
(snip)
>WITH (READPAST) <OR> WITH (NOLOCK)

The only way to find out is to test them both. If I were forced to
guess, I'd say that NOLCOK might be faster as it doesn't check for
existing locks, nor take any locks, whereas NOLOCK still checks for
locks and takes a lock if the row is not currently locked.

But as I said - that's just a guess.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I have a table dbo.myTableName to which hundreds of users are
> UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
> against that table 24/7.
> I am tring to remove the slowdown caused by rows that might be in use
> when users run a query that looks like: SELECT myID, myRow1 etc. FROM
> dbo.myTableName WITH (READPAST) WHERE StartDate = @.DateParam

I echo what Hugo said: try to avoid NOLOCK and READPAST as long as you
can. Rather investigate if indexes can help. A query like the one
above, might excute faster with an index on StartDate.

As for whether you should use NOLOCK or READPAST, there are two things
consider: a) what result do you want and b) and what is your blocking
problem?

a) Both NOLOCK and READPAST can result in the queries giving incorrect
result.
NOLOCK means that you read uncommitted data, which could violate business
rules, and that will be rolled back the next second (or is in fact in the
process of being rolled back). In more devilish cases an updating process
may first delete some data to re-inserted it in some new version, leading
to that you get no data at all.

This last thing is also very typical for READPAST. "SELECT SUM(amt) FROM tbl
WITH (READPAST) WHERE date = @.somdate". Oops, a bunch of rows were locked,
and you get back a value which os 40% of the right one.

If the queries that run are reports that is mainly interested in general
trends, and not used for reconcilliation etc, then it may be OK to run
with NOLOCK, but you should really investigate the consequences.

b) READPAST will not help if SELECTs that performs table scans block
UPDATE statements, the SELECT gets a lock on table level, and the updaters
will have to wait. READPAST makes sense if selects are fast, but your
UPDATE/INSERT operations are complex and long-running.

Generally, first try to see if better indexing can help. But if you have
queries that comes from search functions where the user can select
conditions wildly can be difficult to have an index for everything.
Investing in a second server for reports, may be worth the effort.

In SQL 2005 there is a new isolation level, SNAPSHOT. With this isolation
level, SELECT statements can run on a snapshot of the state of the database
in a given moment. This can help a lot to prevnent this sort of problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp