Solved

between SERIALIZABLE and SNAPSHOT

Posted on 2011-03-23
5
340 Views
Last Modified: 2012-05-11
Hey experts, I've read about
 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
 http://msdn.microsoft.com/es-es/library/ms173763 (v = SQL.100). aspx
but i do not understand
 I would like to know the difference between SERIALIZABLE and SNAPSHOT
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 125 total points
ID: 35197300
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 35197310
SNAPSHOT will take a snaphshot of the table(s) involved for the transaction, so you will keep to get the same information, whatever the other sessions are doing. the other sessions are not blocked whatsoever
SERIALIZABLE will block/delay all the others to do anything on the same objects you lock until your transaction is completed. this is a very strong locking, and needs to be used carefully
0
 

Author Comment

by:enrique_aeo
ID: 35197462
angelIII... the SNAPSHOT need more space to do their SNAPSHOT?
 and when you delete that copy?. Could you explain a little more about SNAPSHOT
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 35197474
the snapshot is only "virtual", means that there is no snapshot data unless you try to access tables which are getting modified (by other processes). in short, the snaphot is only a list of data blocks that have been modified since you started the "snapshot transaction", so as long as the transaction is short, there will be almost no "changes" stored for "your snapshot".
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 125 total points
ID: 35197520
In Snapshot Isolation level updated row versions for each transaction are maintained in TempDB.
After transaction has begun, it ignores all the newer rows inserted or updated in your table.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Substring works but need to tweak it 14 35
When to use a Unique Index? A Clustered Index? 5 76
calculate running total 8 51
How do I partition this table on date? 5 54
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question