?
Solved

ms sql isolation level?

Posted on 2012-03-23
8
Medium Priority
?
431 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 82

Assisted Solution

by:arnold
arnold earned 668 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 668 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 43

Accepted Solution

by:
Eugene Z earned 664 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

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 43

Expert Comment

by:Eugene Z
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 82

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 25

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

568 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