Solved

between SERIALIZABLE and SNAPSHOT

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

778 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