Update locking behaviour

I have a confusion regarding update locks in combination with CTE's (or subqueries) as in the following simple example -

 ;with CTE1 as
 (select material_code, quantity
 from MtrlStockTable **with (updlock)**
 where material_code = @xxx)
 select * 
 from CTE1 
 where quantity < 100;

In the above example, which of the following 2 options is SQL Server's locking behavior?
1. The update lock is held on all rows where the material code = @xxx
2. The update lock is held on all rows where material code = @xxx AND quantity < 100.

more ▼

asked Oct 29, 2012 at 07:45 AM in Default

avatar image

177 8 11 14

have you tried reviewing the results of exec sp_lock when executing this query?

Oct 29, 2012 at 09:01 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It's going to do both. CTE is nothing but a fancy way to trick out a query. If you're curious what might be locked, take a look at the locks as @Fatherjack suggests. You can also look at the execution plan to see how SQL Server is going to access the table. But in this case, it seems pretty clear, it's #2.

One other point, the semi-colon is a statement terminator. Putting it front of the WITH statement is a crutch, mainly used by people posting code online so that those who are not already using the statement terminator don't get errors. Instead, just use the semi-colon as a statement terminator.

more ▼

answered Oct 29, 2012 at 12:54 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

OK thanks a lot!

Oct 29, 2012 at 01:12 PM chaitanyadabholkar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2012 at 07:45 AM

Seen: 1126 times

Last Updated: Oct 29, 2012 at 01:12 PM

Copyright 2018 Redgate Software. Privacy Policy