Aujourd’hui
j’avoue que je reste très sceptique face à l’emploi du with NOLOCK. Je dirais même que dans ma pratique actuelle d’audit de performances il reste un symptôme caractéristique que “Something is rotten in the state of Denmark.” C’est un sparadrap..et parfois même plus tristement une simple mauvaise habitude ou tradition Dernièrement lors d’un audit on m’a rétorqué que ca avait fait sur base de la proposition d’un audit précédent par un autre expert. Au vu de leur code c’était en effet le plus rapide. Comme du fond de teint épais sur une peau acnéique.Le traitement dermatologique adéquat est plus long: FORMATION!!!!
WITH(NOLOCK) HINT
What does it mean?
Using the “NOLOCK” hint inform SQL Server to not issue shared locks and not honor exclusive locks on a table.
When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled backed.
This hint only applies to the SELECT statement.
Why NOT use them?
Two main reasons :
- Dirty reads are possible.
Because the statement does not issue any locks against the tables being read, some “dirty,” uncommitted data could potentially be read. A “dirty” read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems.
The following example shows how NOLOCK works and how dirty reads can occur.
In the script below, a transaction inserts a record in the SalesHistory table.
BEGIN TRANSACTION
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
(‘PoolTable’, GETDATE(), 500)
The transaction is still open, which means that the record that was inserted into the table still has locks issued against it.
In a new query window, the following script runs, this uses the NOLOCK table hint and returns the number of records in the SalesHistory table.
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
The number of records returned is 301.
Since the transaction that entered the record into the SalesHistory table has not been committed, it can be cancelled. The transaction is rolled back by issuing the following statement:
ROLLBACK TRANSACTION
This statement removes the record from the SalesHistory table that was previously inserted.
Now the same SELECT statement that I ran earlier:
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
This time the record count returned is 300. The first query read a record that was not yet committed — this is a dirty read.
- With (NOLOCK) hint can lead to application side problems.
When you run a query with the NOLOCK hint, and a concurrent user is deleting or updating rows from the table in the range you are reading, the query will fail with following message “Could not continue scan with NOLOCK due to data movement”.
That means data that your query is using has changed while your query was running.
Your connection will be broken, no data will be returned.
- Wich solution for locking contention problems ? In a future tip I will explain how to reduce the concurrency in a SQL2000 and a SQL 2005 environment.
DatAtypical 