2000/2005 Handling UPDATE with mixed locking hints differently

2000/2005 Handling UPDATE with mixed locking hints differently by Bill Curnow

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	126124
Opened:	2/9/2006 2:28:54 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We've recently begun planning our upgrade to SQL 2005 from SQL 2000 (see versions below). During testing we discovered we can no
longer specify conflicting locking hints within an UPDATE statement. For example, our SQL 2000 systems have code similar to this (line numbers added):

1| UPDATE dbo.flSalesDetail WITH (ROWLOCK)
2| SET net_amt = gross_amt + storage_amt - credit
3| FROM dbo.flSalesSummary AS summary WITH (NOLOCK)
4| INNER JOIN dbo.flSalesDetail AS detail WITH (NOLOCK)
5| ON detail.invoice_no = summary.invoice_no
6| WHERE summary.batch_no = @batch_no

When the code above is executed in SQL 2005 the following error is generated:

Msg 1047, Level 15, State 1, Procedure spCreateBatchMillInvoices, Line 152
Conflicting locking hints specified.

Changing the locking hint on line 4 from NOLOCK to ROWLOCK, fixes the problem.

To me this makes sense, although I haven't found anything in BOL to support this. I'm wondering why the code worked at all under SQL 2000. Is this a new 2005 bug, or is this a 2000 bug that's been corrected in 2005? Is this simply a behavior change? If so, why didn't we see anything about this in the upgrade literature?

Current Servers: Microsoft SQL Server 2000 - 8.00.997 (Intel X86)
Test 2005 Server: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Details (expand)

English

SQL Server 2005 - Enterprise Edition (32)

SQL Engine

Windows Server 2003

US English

As Northwind has been replaced by AdventureWorks it's difficult to give an example. Basically, create an UPDATE statement that uses two joined tables as the source of the update. Use ROWLOCK on the table being updated, use NOLOCK on the source tables. The table being updated should be a source table.

The following error is generated:

Msg 1047, Level 15, State 1, Procedure spCreateBatchMillInvoices, Line 152
Conflicting locking hints specified.

No error.

0 attachments

Bill,

Thanks for reporting this issue. You hit a narrow scenario that indeed escaped our attention when preparing the upgrade documentation. The source of the issue is a bug in SQL 2000 where the locking hints are not checked for conflicts when (1) the table in the FROM clause is aliased, and (2) the same table is referenced as target of the UPDATE statement without aliasing. In this case, the locking hints supplied in the FROM clause will be ignored, but no error will be issued (if either of the two conditions is not met, an error will be issued in SQL 2000). In SQL 2005, this bug has been fixed. What we did not realize was that this became a breaking change. Since this is such a narrow scenario (you are, to our knowledge, the first user hitting this issue), we are not planning to fix it at this point, but we do plan to add this case to the Upgrade Advisor and to the upgrade documentation.

If this issue is critical for your business, please let us know via your support specialist, and we'll consider providing a QFE.

Thank you,
Eugene Zabokritski, SQL Engine