Solved

between SERIALIZABLE and SNAPSHOT

Posted on 2011-03-23
5
335 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 142

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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

919 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now