Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NOLOCK hint and Read Committed Snapshot Isolation Level (RCSI) Expand / Collapse
Author
Message
Posted Sunday, April 4, 2010 10:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #896427
Posted Sunday, April 4, 2010 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896431
Posted Sunday, April 4, 2010 11:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #896436
Posted Sunday, April 4, 2010 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse