May 24, 2009 at 2:03 am
Hi,
Is it possible to get Locks even though I select data with no locks hint?
Sorry for the poor information but that's what I have now.
Thanks,
Ran.
May 24, 2009 at 2:21 am
If it's a select, you'll still get schema stability locks. If it's any form of modification, the nolock will be ignored. Data modifications have to lock.
Why nolock?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2009 at 4:21 am
Hey Ran,
The short answer is yes. The long answer follows.
You say that you are SELECTing data with the NOLOCK hint, so I am going to limit what I say to SELECTs.
The NOLOCK hint is a table-level hint, so if you have other tables in the same query they will still use whatever isolation is in effect. By default this will be read committed, or read committed snapshot if you have turned that on at the database level. You change the isolation in effect using the SET TRANSACTION ISOLATION LEVEL statement.
NOLOCK is, in my view, a poor choice of name. NOLOCK is synonymous with READUNCOMMITTED, a term I much prefer. READUNCOMMITTED does not mean that no locks are taken. It just means that dirty reads are allowed: no shared locks are taken to prevent other transactions from modifying data read by the current transaction and the current transaction ignores exclusive locks held by other transactions. Therefore it is possible to read data which has not been committed yet - or may be rolled back. This is what is called a 'dirty read'.
READUNCOMMITTED only applies to data locks: schema stability (Sch-S) locks are still acquired during compilation and query execution to ensure that no schema changes take place while the query is compiling or executing. This is important because you can imagine the chaos that would ensue if, for example, a column or index was dropped while your query was reading data from it. For this reason, transactions reading data with NOLOCK/READUNCOMMITTED block transactions performing DDL on referenced objects, and vice-versa.
Finally, if you trace the locks acquired by a transaction running at READUNCOMMITTED, you will see that it does still acquire Shared (S) locks on associated objects like sys.allocation_units and sys.partitions. The key being locked is in the ObjectID2 column of the trace data.
Paul
P.S. Whilst strictly not a lock as such, SQL Server also has to acquire latches (a sort of lightweight internal lock) in order to safely read/write the database pages in memory. There is no way to avoid latches as they are required to ensure that physical data corruption does not occur.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply