ReadCommited vs SnapShot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ReadCommited vs SnapShot

Author

Topic

amitbarkai
Starting Member

19 Posts

Posted - 2010-08-15 : 08:36:10

Hi all

first i will tell you about my problem
there are transactions on our system the run more than 10 min
these transactions updates huge amount of data .
Simultaneously we read the writen data

http://technet.microsoft.com/es-es/library/ms173763.aspx
i hope someone could clear for me the difference between the 2
for now we are using the read commited option

is there a better option for this problem
which one is the better ?
read commited or snapshot ?

thanks for the help

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-15 : 12:29:13

is there a better option for this problem
which one is the better ?
read commited or snapshot ?

It is depended on your business requirement.

If you don't mind, just post your sp/fn ... or scripts of the transaction that is running more than 10 minutes. People have more info to help you.

amitbarkai
Starting Member

19 Posts

Posted - 2010-08-17 : 03:04:35

thanks for the answer
the script is not important in this metter , believe me you dont want to see it .. :)
there is a huge ammout of data been processed in the transaction few milions rows

from what i understood the snapshot option "Takes" your data and put on the tempDB for processing
if there will be any change on the REAL DB you will get worng data from the query
BUT since it a tempDB you'll have the fastest processing and commiting of your queries

in the other hand you have the READ UNCOMMITED which in my opinion seem to good to be ture.
you may have some "dirty data" but you do have a real time data
which is grate

thanks

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 04:24:19

"but you do have a real time data"

You do? What about the data you never see, or the data you see twice? Very dangerous method of operation except in certain, clearly defined and understood, circumstances. For example, we have a DBA report on logging tables that runs in READ UNCOMMITED mode so that it has zero impact on Writes to the logging tables. But the DBAs know in what ways the data is unreliable. A business manager would not understand that, and would be likely to take business decisions on data from READ UNCOMMITTED which could be disastrous.

If you have Reads blocking Writes then you could/should consider using READ_COMMITTED_SNAPSHOT and then you can use the (default) Read Committed isolation without it blocking writes.

"the script is not important in this metter"

Its normally important to people here. General questions don't get much air time because folk here know all to well that "it depends" and that they will waste time making suggestions which turn our to be wrong once the guts of the problem is, eventually, described. Of course posting huge scripts will mean that people with limited time may not be able to help. No doubt some balance in there somewhere. If you are going to post scripts make sure they are well formatted - wading though long scripts that are poorly formatted is gonna reduce the size of the audience too I'm afraid. So ... well crafed questions are more likely to get interest in the community and useful responses - no rocket science there then eh?!

amitbarkai
Starting Member

19 Posts

Posted - 2010-08-17 : 07:38:39

thanks for the answer
the reason i said "the script is not important in this metter" is becouse it's an historical procedure
which in not close to be well formatted like you said and i didnt think it would do any good to put it here

all i wanted to know the differance between to 2 and which one is better
so thanks for clearing it for me

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 07:48:11

"which one is better "

That's the "it depends" issue ... can't tell without knowing in some detail what problem you are needing to solve

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-17 : 14:58:01

from what i understood the snapshot option "Takes" your data and put on the tempDB for processing
if there will be any change on the REAL DB you will get worng data from the query
BUT since it a tempDB you'll have the fastest processing and commiting of your queries

in the other hand you have the READ UNCOMMITED which in my opinion seem to good to be ture.
you may have some "dirty data" but you do have a real time data
which is grate

there is a huge ammout of data been processed in the transaction few milions rows

I am a fan of READ UNCOMMITED, however in your case, it is not suitable at all as Kristen described.

Data returning from a READ UNCOMMITED should NOT be input for other processes. It will mess the whole thing up. The most importance is that it rarely happens in development enviroment. So developers do not recognize the potential bomb in the DB.

Take out READ UNCOMMITED, you have other transaction levels to pick up:
1 - READ_COMMITTED
2 - READ_COMMITTED_SNAPSHOT
3 - SNAPSHOT
4 - REPEATABLE
5 - SERIALIZABLE

Each is designed for particular business requirement. For example, do you allow other processes to select/modify the source while your main process are running? The answer "yes", "no", "does'nt matter" decides which transaction level you should use. And many other factors may involve...

If you still insist on the question: "which one is the better ? read commited or snapshot ?", the answere may be: "all transaction levels are created equal."

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 05:10:52

"I am a fan of READ UNCOMMITED"

I'd be interested to know of situations where you use it as its pretty much banned here! Maybe I could "downgrade" some of the things we are doing.

For example, Country Code Lookup. How often is a new country added? Is the risk of a page split so low that I could use READ UNCOMMITED? or introduce a [more complicated] process for adding a new Country Code that would lock-out READ UNCOMMITED in some way that was safe?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-08-18 : 11:51:20

quote:
Originally posted by amitbarkai

from what i understood the snapshot option "Takes" your data and put on the tempDB for processing
if there will be any change on the REAL DB you will get worng data from the query
BUT since it a tempDB you'll have the fastest processing and commiting of your queries

I would not call it 'wrong' data. Data is always changing. Say you want the sum of a column from a big table and it will take 1 min. During that one minute the data could change (or there could be an attempt to change the data). With both Read Committed (Snapshot Off) and Snapshot, you will get the same answer. However, with Snapshot, during that minute SQL Server will allow the Data to change in the meantime, with Read Committed (Snapshot Off), these Transactions will be blocked and will have to wait until the Select statement has completed. If you call it 'wrong' because the data is not up to date, that would suggest if there are any changes to the data while you are executing the minute long query to sum a column, you are implying SQL Server should start over to get the 'right' answer.

quote:

in the other hand you have the READ UNCOMMITED which in my opinion seem to good to be ture.
you may have some "dirty data" but you do have a real time data
which is grate

thanks

I guess it is too good to be true. What if you Selected data that got rolled back rather than committed? Not exactly great real-time data. We actually use READ UNCOMMITTED a lot partly because we still have clients on 2000. Supposedly we haven't encountered a single known problem while using it. But I'm not advocating it, I think it is dangerous.

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 12:07:16

"What if you got data that got rolled back rather than committed?"

Not just that ... your query might miss part of a page that is being split. So your query will omit some of the records. But if you rerun the query they will be there ...

Or a page might be split as your query is being processed - so your query will include (the second) half of the records in that page twice.

I think it is more than "dangerous", I think that unless the APP specifically handles the "dirty data" it shouldn't be used at all. Agreed, back in SQL 2000 there were fewer options than SQL 2005 onwards.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-18 : 12:20:23

quote:
Originally posted by denis_the_thief

I would not call it 'wrong' data. Data is always changing. Say you want the sum of a column from a big table and it will take 1 min. During that one minute the data could change (or there could be an attempt to change the data). With both Read Committed (Snapshot Off) and Snapshot, you will get the same answer. However, with Snapshot, during that minute SQL Server will allow the Data to change in the meantime, with Read Committed (Snapshot Off), these Transactions will be blocked and will have to wait until the Select statement has completed. If you call it 'wrong' because the data is not up to date, that would suggest if there are any changes to the data while you are executing the minute long query to sum a column, you are implying SQL Server should start over to get the 'right' answer.

Also be aware that, although rare, READ COMMITTED will mis-count rows if they are updated durring your query. Mostly if your query is scanning the table.

Here is an article that describes the situation:
http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

EDIT: Removed the bit about phantom rows. My brain took a break. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources