Dirty Reads

Question

Dirty Reads

jarretg

SSCommitted

Points: 1706
More actions
December 30, 2003 at 7:37 am

#161282

Hello everyone,
I was given the task to research dirty reads for my database. Performance is a major factor for my boss as I am sure it is for everyone's. I explained that it would help performance, but she wants to know if maybe I was missing something.
After digging around, it seems to me that it can be summed up like this:
Pro:
Could be faster to read data from DB (depending on contention for data).
Con:
Could potentially read data that might be rolled back.
Can anyone shed any more light on this and maybe let me know if you allow dirty reads at your company? Also, if you do allow dirty reads, can you give me some situations of when I would not want to?
Any feedback would help my cause. Thanks!!!
Jarret

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

lakusha SSCommitted Points: 1857 More actions · Answer 1

It depends on what you are doing with the data you are reading. If it's only for display (e.g. a monitoring tool) it's not too bad. Don't base an action on the data you are fetching (you can possibly see garbage data in a field). So re-fetch a particular row (in standard read committed isolation) before making a decision or editing.

If you can afford to skip locked rows, use the READPAST locking hint instead.

Yukon is suppose to have a new snapshot isolation level that will fit nicely with your needs if you can wait for it.

jxflagg Hall of Fame Points: 3817 More actions · Answer 2

quote:
Can anyone shed any more light on this and maybe let me know if you allow dirty reads at your company?

I've never had a reason to perform dirty reads. Lakusa's point about monitoring notwithstanding, I've always found that with proper clustering, tuning of queries and giving the server enough RAM, locking has not been a problem on SQL Server 2000. (I've had plenty of locking problems on Sybase.)

But maybe I've been lucky.

sholliday SSCrazy Points: 2100 More actions · Answer 3

I use "dirty reads" for reports. Display purposes, as already suggested.

If you understand what they are, then you can use them effectively. But as a general rule, I use them in a "read only" method.

sholliday SSCrazy Points: 2100 More actions · Answer 4

Here is an example:

table1 has some data. and a primary_key (t1.primary_key)

table2 has a foreign key to the t1.primary key (t2.primary_key_fk)

Then, your business logic is that you do something like

"select table1.* from table1 with (NOLOCK)"

"select table2.* from table2 with (NOLOCK)"

and then you use maybe a dataset (or whatever your flavor is) that joins up the 2 tables.

If you have a large transaction that populates both table1 and table2...

But the T (transaction) is such that table1 gets populated way way before table2.

what I'm trying to get at is that if T hasn't completed yet, then you'll have some records in table1 that don't have corresponding records in table2.

this will happen because you are reading (dirty) from table1 and table2.

if the "WITH (NOLOCK)" is omitted, then its not a dirty read, and you wont' get these orphan rows (tuples).

if you do an inner join, then you'll be ok. (inner join table1 and table2). but keep in mind, that .NET datasets are moving toward less inner joins, and putting the data into 2 dataset_tables.

anyway. there's some food for thought.

covitz Old Hand Points: 322 More actions · Answer 5

Our policy about "dirty reads" (or uncommitted dependencies) is to do it only when necessary, in read only, but notify the user (different color screen) that it is not final.

jarretg SSCommitted Points: 1706 More actions · Answer 6

quote:
I've always found that with proper clustering, tuning of queries and giving the server enough RAM, locking has not been a problem on SQL Server 2000.

I've got plenty of RAM in my server and the server has plenty of processing power so that's not the problem. There are many transactions taking place in the database, I just don't want locking to be a factor in increasing the call duration for search queries. With READ COMMITTED being the default transaction isolation level, the queries can't take place until the locks are released on the table. Some transactions do many operations before they are committed, meanwhile, all of the locks on all of those tables are held until the commit is executed, which also locks out any reads on that data.

Some of these search queries join in many large tables to each other, it is taking too long and the component is timing out on the user. Many of these tables are also part of the transactions that I mention above.

I know that READ UNCOMMITTED will read the current value on the records, but there is a chance that the value could be rolled back. This would only be for inquiries, not for editing a record.

Just in case I have missed anything, I am trying to gather as much information as possible.