SQLServer: To Lock or NOLOCK

I don't think anyone would argue that one of the biggest problems on SQLServer (besides that it runs on Windows) is how terrible it's locking and lock escalation process is.

Then when things seem to go slow when on inserts, the first thing the developers do is modify all selects to use the NOLOCK hint. Yea, so in theory that makes sense, but not only does it imply that data results may be inconsistent, but it also ignores the real issue. We need to focus our tuning efforts on how we lock and if we are forcing lock escalation (from Row to Extent to Page to Table to ...)

The other not so known issue is that SQLServer has a bug that can actually cause SQLServer to crash (it happended to us) when a select with NOLOCK is used and a delete happens at the same time...

-----------------------------------------------------------------------

FIX: A query may fail with retail assertion when you use the NOLOCK hint or the READ UNCOMMITTED isolation level

Article ID:827714
Last Review:August 13, 2004
Revision:3.0
On this Page

SYMPTOMS

CAUSE

RESOLUTION

WORKAROUND

STATUS

MORE INFORMATION

SYMPTOMS

When you run a query with the NOLOCK hint or with the transaction isolation level set to READ UNCOMMITTED, and a concurrent user is deleting rows from the table that is accessed by the query, the query may fail with a retail assertion, and you may receive an assertion error message that is similar to the following:
Msg 3624Location: Drive:\sql\ntdbms\storeng\drs\include\record.inl:1447Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROWSPID: 63 Process ID: 1776-or- Msg 3624 Location: recbase.cpp:1378 Expression: m_offBeginVar <>

CAUSE

When the Microsoft SQL Server engine tries to search for a row when a query uses the NOLOCK hint or the READ UNCOMMITTED transaction isolation level, the target row may be deleted. Before you install this fix, SQL Server 2000 may incorrectly handle this situation and the query may fail with the assertion that is mentioned in the "Symptoms" section of this article.Note The READ UNCOMMITTED transaction isolation level is also known as a "dirty read."

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft SQL Server 2000 service pack that contains this hotfix.To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix.