March 17, 2004 at 8:50 pm
I have the following query that seems to cause locking issues on the joined tables. What is the correct usage of the NOLOCK directive for joins?
SELECT DEALS.*, COMPANIES.COMPANYNAME, ROOMS.ROOMCODE,
USERS.FULLNAME AS AGENT, USERS_1.FULLNAME AS VERIFIER, USERS_2.FULLNAME AS PROVISIONER
FROM DEALS WITH (NOLOCK) INNER JOIN COMPANIES ON DEALS.COMPANYID = COMPANIES.COMPANYID
INNER JOIN ROOMS ON DEALS.ROOMID = ROOMS.ROOMID
INNER JOIN USERS ON DEALS.USERID = USERS.USERID
LEFT JOIN USERS AS USERS_1 ON Deals.VERIFIERID = USERS_1.USERID
LEFT JOIN USERS AS USERS_2 ON Deals.PROVISIONERID = USERS_2.USERID
WHERE (DEALID = 0)
March 18, 2004 at 12:57 am
put nolock after all table name. Or set isolation level to read uncommitted.
March 19, 2004 at 2:22 pm
If you want speed, you should use nolock. If you absolutely must have the most up to date data, don't use nolock. The difference being that a Select without nolock will wait for any uncommmited transactions to clear before selecting the data. With nolock it will give you whatever is commited to the database.
cl
Signature is NULL
March 19, 2004 at 2:47 pm
Much simpler to begin your sp with Set Transaction Isolation Level Read Uncommitted. This means that any selects in the batch/sp will allow dirty reads, but will not hold locks, essentially adding with(nolock) after every table name in your select(s).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2004 at 4:01 pm
I aggree
Set Transaction Isolation Level Read Uncommitted
is easier than putting (NOLOCK) (without the WITH) after each referenced table in the SELECT STATEMENT.
But always be aware if a tansaction fails or is purposely rolled back you may have read the data that is no longer there or not intended to be part of your output. Be sure your ok with this before you do. The performance gains may be great but if the output is off high importance on accuracy you may bite yourself.
March 20, 2004 at 6:36 am
Thanks to all that replied. You've been very helpful!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply