Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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?
0
soozh
Asked:
soozh
3 Solutions
 
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.
0
 
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.
0
 
Eugene ZCommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
soozhAuthor 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?
0
 
Eugene ZCommented:
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
 
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.
0
 
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.
0
 
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now