February 22, 2010 at 9:36 am
could some explain me what is the effect of the following two statements; Which one better one? both same?
update i set i.name= 'MR Y' from inventory i with (NOLOCK) where ID>100 and ID <=200
update inventory set name= 'MR X' where ID>100 and ID <=200
Thanks in advance.
February 22, 2010 at 9:56 am
From BOL:
READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all). For more information about dirty reads, nonrepeatable reads, and phantom reads, see Concurrency Effects.
READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2010 at 10:41 pm
Joe-420121 (2/22/2010)
could some explain me what is the effect of the following two statements; Which one better one? both same?update i set i.name= 'MR Y' from inventory i with (NOLOCK) where ID>100 and ID <=200
update inventory set name= 'MR X' where ID>100 and ID <=200
Thanks in advance.
No effect.
both same.
February 22, 2010 at 11:14 pm
Suresh B. (2/22/2010)
Joe-420121 (2/22/2010)
could some explain me what is the effect of the following two statements; Which one better one? both same?update i set i.name= 'MR Y' from inventory i with (NOLOCK) where ID>100 and ID <=200
update inventory set name= 'MR X' where ID>100 and ID <=200
Thanks in advance.
No effect.
both same.
You should avoid the use of the nolock hint in an update statement (or any other statement for that matter).
According to msdn it is supposed to be ignored by the query optimizer.
http://msdn.microsoft.com/en-us/library/ms187373.aspx
Due to the data consistency problems that nolock causes, you should evaluate the usage of nolock. I would certainly remove it from the update statements.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2010 at 3:16 pm
And for (future) reference-- looking at the execution plan should show they are identical.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply