June 9, 2009 at 2:01 pm
1. We want to insert million records to a table. Will these many inserts cause table lock? I know delete/update would, but not sure about inserts. We don't have any indexes on this table.
2. If I use select ______ from _____with (NoLock) where ____, does that mean this select won't place shared locks on those records or the table?
3. Am I right that select ______ from _____with (NoLock) read uncommitted data too?
Thank you in advance.
June 9, 2009 at 3:05 pm
jungnaja (6/9/2009)
1. We want to insert million records to a table. Will these many inserts cause table lock? I know delete/update would, but not sure about inserts. We don't have any indexes on this table.
Yes, it will cause locks.
2. If I use select ______ from _____with (NoLock) where ____, does that mean this select won't place shared locks on those records or the table?
It won't lock the records being selected.
3. Am I right that select ______ from _____with (NoLock) read uncommitted data too?
No, that's "Read Uncommitted". "NoLock" can cause you to have other problems. For example, if a it reads data from a page, and then the page is split by an insert/update, it can possibly read the same data again. It can also read data that is in the process of being changed or deleted, and it can miss data that's inserted while it's running. This means two of the same select, run at the same time, may not get the same results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2009 at 2:02 am
3. Am I right that select ______ from _____with (NoLock) read uncommitted data too?
Yes it will read uncommitted data
From BOL
NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply