April 4, 2010 at 10:01 am
The database code in our system is littered with NOLOCK hints, and we are considering switching to Read Committed Snapshot Isolation (RCSI) in the near future.
Once RCSI is in place, will it override the NOLOCK hints in the code? In other words, will SELECTs read committed data, even with the NOLOCK still in place?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 4, 2010 at 10:36 am
Marios Philippopoulos (4/4/2010)
The database code in our system is littered with NOLOCK hints, and we are considering switching to Read Committed Snapshot Isolation (RCSI) in the near future. Once RCSI is in place, will it override the NOLOCK hints in the code? In other words, will SELECTs read committed data, even with the NOLOCK still in place?
No, READ_COMMITTED_SNAPSHOT only applies to operations performed at the (default) READ_COMMITTED isolation level.
NOLOCK hints explicitly request READ UNCOMMITTED semantics, and will continue to function as before.
Just one more reason that NOLOCK hints are evil, I am afraid. Setting the isolation level required at connection time, or by using the SET TRANSACTION ISOLATION LEVEL statement, makes maintenance much easier, as I am sure you well appreciate! Every developer responsible for adding the NOLOCK hints should be tasked with removing them 😉
When changing to RCSI, be sure to fully test any trigger code you have, and add READCOMMITTEDLOCK hints where required. Also, be aware that certain operations, like checking foreign key values, are always performed using READ_COMMITTED semantics, regardless of the RCSI setting.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 11:23 am
Paul White NZ (4/4/2010)
Marios Philippopoulos (4/4/2010)
The database code in our system is littered with NOLOCK hints, and we are considering switching to Read Committed Snapshot Isolation (RCSI) in the near future. Once RCSI is in place, will it override the NOLOCK hints in the code? In other words, will SELECTs read committed data, even with the NOLOCK still in place?No, READ_COMMITTED_SNAPSHOT only applies to operations performed at the (default) READ_COMMITTED isolation level.
NOLOCK hints explicitly request READ UNCOMMITTED semantics, and will continue to function as before.
Just one more reason that NOLOCK hints are evil, I am afraid. Setting the isolation level required at connection time, or by using the SET TRANSACTION ISOLATION LEVEL statement, makes maintenance much easier, as I am sure you well appreciate! Every developer responsible for adding the NOLOCK hints should be tasked with removing them 😉
When changing to RCSI, be sure to fully test any trigger code you have, and add READCOMMITTEDLOCK hints where required. Also, be aware that certain operations, like checking foreign key values, are always performed using READ_COMMITTED semantics, regardless of the RCSI setting.
Paul
Thank you for the suggestions.
Yes, I was aware of the trigger code issue; in our environment we use DML triggers to archive previous versions of changed records in history tables. I will need to check whether that trigger code references base tables (other than the inserted and deleted pseudotables) and add the READCOMMITTEDLOCK hint with any of the base tables.
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?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 4, 2010 at 12:13 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply