Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOLOCK hint and Read Committed Snapshot Isolation Level (RCSI)


NOLOCK hint and Read Committed Snapshot Isolation Level (RCSI)

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search