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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 ...
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.
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.
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.
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.
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.
ASKER
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?