Solved

ms sql isolation level?

Posted on 2012-03-23
8
418 Views
Last Modified: 2012-03-28
I have a MS SQL 2008 database which has a number of tables.

I have two applications. One writes to a couple of tables.  The other reads these tables.

The applications run as windows services.

I have discovered that the reader application can read data from the database whilst the writer is still writing it ( there is more than one row of data written in one table. The read sometimes does not get all the rows).

How can i "protect" the written data until it has been written completly?  Can i use a combination of transacations and setting the isolation level to the correct value?
0
Comment
Question by:soozh
8 Comments
 
LVL 77

Assisted Solution

by:arnold
arnold earned 167 total points
ID: 37760474
You would have to do the writing as a transaction block. The writer will see the changes, but the reader will only see them when the transaction is committed.
http://stackoverflow.com/questions/506602/best-way-to-work-with-transactions-in-ms-sql-server-management-studio

This way the reader will not reflect partial information which is likely what raised this issue for you.
0
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 167 total points
ID: 37760561
Here is the link http://aboutsqlserver.com/2011/09/28/locking-in-microsoft-sql-server-table-of-content/ to the series of posts that explain how SQL Server is dealing with locking and transactions. Hope it would be useful for you.
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 166 total points
ID: 37760602
if you "OK" to have "Dirty" reads - useNOLOCK hint on table or  
TRANSACTION ISOLATION LEVEL -READ UNCOMMITTED, when  query can read values modified by other transactions before they are commited
--

you may like to try the
SNAPSHOT isolation level does not apply a lock while reading rows.

SET TRANSACTION ISOLATION LEVEL
http://msdn.microsoft.com/en-us/library/ms173763(v=sql.100).aspx
more
http://beyondrelational.com/modules/2/blogs/28/posts/10468/sql-server-transaction-isolation-level-snapshot.aspx
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:soozh
ID: 37760717
if i put all my updates in a transaction does this mean that the reader will not see any of the changes until the transaction is committed?

I mean when SQL Server commits the transaction it still must update a number of tables.. so can i be sure the reader will not only see part of the changes?

Can i be sure the SQL Server commits all the changes at exactly the same instance?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 37760950
depends what you needs
with READ UNCOMMITTED or MOLOCK hint - you will see
all will be commited . if you set commit transaction.. you said  1 for update 2nd transaction for select ...
0
 
LVL 77

Expert Comment

by:arnold
ID: 37760972
Yes, the transaction block  inserts, updates are only visible to the process that initiated the transaction block and no other. Does the writer have a transactional modification I.e. user request, or does it process data on a schedule?
Beside transactions, other means such as table, row, column, locks might have to be used.
There is no information included in the question to narrow down the need.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37765925
One guarantee that you have from SQL Server is that a reader that is using the default isolation level - READ_COMMITTED will only see those transactions that are committed.

The big difference here is whether or not a Reader is blocked while there is a transaction going on in the table that it is reading from.  

If the Reader is using (NOLOCK) or READ_UNCOMMITTED, then you have no guarantee that the data that is being written will not be seen by a Reader with this isolation level. (Dirty reads as was stated above).

If you do not want blocking while a Reader is reading the table of the writer, then you can use READ_COMMITTED_SNAPSHOT isolation which says, give me a picture of the data as it is committed and let the writer behind me do it's thing, but I only want to see COMMITTED data. This way the blocking is much less severe in these cases.

Hope that makes sense.  Let me know if it doesn't.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37771675
Or you should be holding a lock on the table because when you insert, the lock is escalated to either row level or page level.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

770 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