Marios Philippopoulos (4/4/2010)
One thing I find a little disconcerting with RCSI is the risk of non-repeatable reads, ie. the possibility that 2 SELECTs involving the same table within a transaction may return different result sets (since reading committed data holds at the statement level). I guess the READCOMMITTEDLOCK hint would be a remedy for this situation as well?
Fully repeatable reads are only guaranteed at the REPEATABLE_READ, SERIALIZABLE, and SNAPSHOT (SI) isolation levels. RCSI does provide statement-level repeatable reads.
The READCOMMITTEDLOCK hint simply ensures that read operation behave as though the isolation level is READ_COMMITTED and RCSI is OFF. Reads take shared locks, which are most often taken and released as the read operation progresses.
This is the minimum requirement to ensure that only committed data is read - it does not provide the REPEATABLE_READ behaviour, where shared locks are taken and held to the end of the transaction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi