| Author |
Topic |
|
juicyapple
Posting Yak Master
176 Posts |
 Posted - 2008-04-08 : 10:52:08
|
| Hi all,I just read this article. The kind of select is called dirty read. So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..http://www.4guysfromrolla.com/webtech/101100-1.2.shtmlNOLOCKUsing NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk. For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-08 : 10:53:38
|
As always it depends on what type of table or system you are quering.If tables are updated once per week, and you have several millions select per hour, I would say use NOLOCK.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-08 : 10:54:12
|
| Yes, it will read only committed rows. What exactly do you want to know? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 10:55:15
|
| Thats right. If you need to pull Sensitive information like A/C no,Bank information then donot use nolock |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-08 : 10:58:08
|
quote:
Originally posted by sodeep
Thats right. If you need to pull Sensitive information like A/C no,Bank information then donot use nolock
Thats not exactly true, Bank account information should not be a problem, financial information such as stock market prices would be a problem as these are updated on a far more regular basis. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 11:05:19
|
| I mean Sensitive information. |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-04-08 : 11:06:36
|
| Hi RickD, it should be read all the committed and uncommitted rows. If I select the data with nolock (filter by record date time), and insert it into another table, will the uncommitted rows also be included?One more thing, the tables are updated every few seconds, and data non stop pump in...so would it be suitable to use nolock in the select statement?? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-08 : 11:07:04
|
What Rick said is that AccountNumber is sensitive but not updated often. You can use WITH (NOLOCK) to read AccountNumber.But other data, such as transactions that has higher probability to change should not be used with NOLOCK.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-08 : 11:21:14
|
quote:
Originally posted by juicyapple
Hi RickD, it should be read all the committed and uncommitted rows. If I select the data with nolock (filter by record date time), and insert it into another table, will the uncommitted rows also be included?One more thing, the tables are updated every few seconds, and data non stop pump in...so would it be suitable to use nolock in the select statement??
Quite simply no, you need to use a read committed transaction level.Peso - Thanks for the clarification. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-04-08 : 11:42:02
|
| That article is fairly old, and deals with SQL Server 2000.You should look at the new features of SQL Server 2005:SET TRANSACTION ISOLATION LEVEL SNAPSHOT with the ALLOW_SNAPSHOT_ISOLATION database option set to ON. Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.orSET TRANSACTION ISOLATION LEVEL READ COMMITTED with the READ_COMMITTED_SNAPSHOT database option set to ON. If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.These will allow you to have good concurrency with an active database. You should read SQL Server 2005 Books Online in detail to see the tradeoffs and how to use them.CODO ERGO SUM |
|
|
|
|
select with nolock has calculation risk??