SQL Smarties: Tip 1 WITH (NOLOCK) HINT

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 :

  1. 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.

 

  1. 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.
  1. 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.

 

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s