Solved

ms sql isolation level?

Posted on 2012-03-23
8
421 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
[X]
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
8 Comments
 
LVL 78

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 43

Accepted Solution

by:
Eugene Z 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 78

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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