ms sql isolation level?

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?
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
dwkorCommented:
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.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

soozhCEOAuthor Commented:
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?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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 ...
arnoldCommented:
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.
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
TempDBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.