READ UNCOMMITTED - SNAPSHOT

Hi All,
I am having to run a "batch update" on a LARGE table that is used by a repor
t.
i.e.
BEGIN TRANSACTION
EXEC 5_minute_Procedure_On_Large_Table
COMMIT TRANSACTION
This batch update takes 5 minutes to run and during this time the LARGE
table is locked and prevents anyone from accessing the report since it uses
the LARGE table that is being updated in 5_minute_Procedure_On_Large_Table.
- this is not good
My "Sorta" Solution is:
READ UNCOMMITTED is "almost" the right solution - however it does not return
a SNAPSHOT of the table data as it was before the begining of the
5_minute_Procedure_On_Large_Table. Instead it shows the records as they are
being updated. - This gives distorted results
My question is - is there something like READ ONLYCOMMITED records?
If not - any suggestions?
Best Regards,
Mekim
P.S. For those interested in SQL 2005 - this would seem to resolve my issue
- but that's obviously not an option
http://msdn.microsoft.com/sql/defau...hotisola_topic6Mekim
In SQL 2000, there is no way to get a shapshot of the data before your proc
began. It doesn't exist anymore. So you have 3 choices:
1. Wait for the transaction modifying the data to finish. This is the
default behavior.
2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
new values of data that has changed. As you noticed, it does not guarantee
transactional consistency.
3. Use the READPAST hint. This will only show you data that is not locked.
It causes SQL Server to READ PAST locked rows. So it will show you only
COMMITTED data, as you asked for, but it won't show you ALL the data.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mekim" <mekim@.discussions.microsoft.com> wrote in message
news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
> Hi All,
> I am having to run a "batch update" on a LARGE table that is used by a
> report.
> i.e.
> BEGIN TRANSACTION
> EXEC 5_minute_Procedure_On_Large_Table
> COMMIT TRANSACTION
> This batch update takes 5 minutes to run and during this time the LARGE
> table is locked and prevents anyone from accessing the report since it
> uses
> the LARGE table that is being updated in
> 5_minute_Procedure_On_Large_Table.
> - this is not good
> My "Sorta" Solution is:
> READ UNCOMMITTED is "almost" the right solution - however it does not
> return
> a SNAPSHOT of the table data as it was before the begining of the
> 5_minute_Procedure_On_Large_Table. Instead it shows the records as they
> are
> being updated. - This gives distorted results
> My question is - is there something like READ ONLYCOMMITED records?
> If not - any suggestions?
> Best Regards,
> Mekim
> P.S. For those interested in SQL 2005 - this would seem to resolve my
> issue
> - but that's obviously not an option
> http://msdn.microsoft.com/sql/defau...hotisola_topic6
>|||Hi Kalen,
Thank you for your suggestions and it therefore looks like ReadUncommited
(or Read Past) are the best (and only) options
Best Regards,
Mekim
"Kalen Delaney" wrote:

> Mekim
> In SQL 2000, there is no way to get a shapshot of the data before your pro
c
> began. It doesn't exist anymore. So you have 3 choices:
> 1. Wait for the transaction modifying the data to finish. This is the
> default behavior.
> 2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
> new values of data that has changed. As you noticed, it does not guarantee
> transactional consistency.
> 3. Use the READPAST hint. This will only show you data that is not locked.
> It causes SQL Server to READ PAST locked rows. So it will show you only
> COMMITTED data, as you asked for, but it won't show you ALL the data.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "mekim" <mekim@.discussions.microsoft.com> wrote in message
> news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
>
>