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
Solved

between SERIALIZABLE and SNAPSHOT

Posted on 2011-03-23
5
337 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
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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