NOLOCK

Was ist NOLOCK?

NOLOCK ist ein sogenannter Abfragehinweis, mit dem ihr das Standardverhalten des SQL Server für eure Abfrage überschreiben oder verändern könnt. NOLOCK ist ein Hinweis, der das Isolationslevel für die entsprechende Abfrage überschreibt. Das funktioniert nur für lesende Abfragen, ihr könnt den NOLOCK-Hint also nicht in UPDATE, DELETE oder INSERT-Statements verwenden. Der NOLOCK-Hinweis ist dabei synonym mit dem READUNCOMMITED-Hinweis, er ändert das Isolationslevel eurer Abfrage also dahingehend, dass „Dirty Reads“ möglich werden, die Abfrage also Daten zurück liefern kann, die am Ende durch einen ROLLBACK gar nicht in der Tabelle landen.

Verwendung von NOLOCK

Um NOLOCK ranken sich einige Mythen, gleichzeitig ist es vermutlich im SQL Server-Universum am häufigsten verwendete Abfragehinweis. In den folgenden Abfragen seht ihr die Effekte und Verwendung von NOLOCK etwas näher erklärt. Um diese Abfragen nachvollziehen zu können, müsst ihr üblicherweise mit zwei Sessions gleichzeitig arbeiten, ihr braucht also mehrere offene Abfrage-Tabs in eurem Azure Data Studio oder SSMS oder anderen Abfragetool eurer Wahl.
Im ersten Tab startet ihr nun eine Transaktion und macht in dieser Transaktion ein UPDATE auf einer Tabelle:

BEGIN TRANSACTION

UPDATE [Warehouse].[StockItemTransactions]
SET [TransactionTypeID] = 13 WHERE [TransactionTypeID] = 10

Beachtet, dass die Transaktion nicht beendet wird. Das bedeutet, wenn ihr nun in einer zweiten Abfrage die Tabelle abfragt, dann erhaltet ihr kein Ergebnis zurück. Öffnet also eine zweite Abfrage und führt dort folgende Abfrage aus:

SELECT [StockItemTransactionID]
    ,[StockItemID]
    ,[TransactionTypeID]
    ,[CustomerID]
    ,[InvoiceID]
    ,[SupplierID]
    ,[PurchaseOrderID]
FROM [Warehouse].[StockItemTransactions] 
WHERE [TransactionTypeID] = 10

Hier könnt ihr lange warten, werdet jedoch keine Resultate sehen. Das ist natürlich so, weil im SQL Server READ COMMITTED das standardmäßig ausgewählte Isolationslevel ist und ihr bei einer offenen Transaktion auf der Tabelle mit eurem Lesevorgang warten müsst. Das seht ihr zum Beispiel an den Ergebnissen der folgenden Abfrage:

DECLARE @who TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @who EXEC sp_who2

SELECT [SPID]
    ,[Status]
    ,[LOGIN]
    ,[BlkBy]
    ,[DBName]
    ,[Command] 
FROM @who 
WHERE DBName = 'WideWorldImporters'

Hier seht ihr 2 Transaktionen, eine im Status „sleeping“ mit dem Kommando „AWAITING COMMAND“, das ist die noch offene Transaktion in der das UPDATE bereits gelaufen ist, und eine mit dem Status „SUSPENDED“ mit dem Kommando SELECT, bei der in der Spalte „BlkBy“ die SPID der ersten Transaktion steht, die also von dieser geblockt wird.
Brecht ihr nun die SELECT-Abfrage ab und fügt hinter den Tabellennamen den NOLOCK-Hinweis hinzu, dann erhaltet ihr sofort Ergebnisse (bzw. in diesem Fall keine Ergebnisse, da ihr ja in der Transaktion den Typen 10 in 13 geändert hattet):

SELECT [StockItemTransactionID]
    ,[StockItemID]
    ,[TransactionTypeID]
    ,[CustomerID]
    ,[InvoiceID]
    ,[SupplierID]
    ,[PurchaseOrderID]
FROM [Warehouse].[StockItemTransactions] (NOLOCK)
WHERE [TransactionTypeID] = 10

Führt ihr nun im ersten Fenster ein ROLLBACK aus und führt dann die Abfrage auf die Tabelle erneut aus, dann erhaltet ihr Ergebnisse, da die Transaktionen mit Typ 10 nun ja wieder zurückgesetzt wurden.
Hier erkennt ihr schon den ersten Effekt, den NOLOCK hat: ihr habt eine UNCOMMITTED Transaktion gelesen und damit Daten, die so nie in der Datenbank persistiert wurden.
Trotzdem ist NOLOCK der mit Abstand am häufigsten verwendete Abfragehinweise im T-SQL Universum. Das liegt daran, dass sich hierum einige Mythen gebildet haben:

1. Abfragen mit NOLOCK können nicht durch andere Operationen blockiert werden.
   Das ist falsch. Um das zu sehen, führt die obige NOLOCK-Abfrage aus und während sie läuft gleichzeitig in einem anderen Tab sp_lock. Ihr seht dann Einträge für einen Schema-Lock auf der abgefragten Tabelle und für einen Shared-Lock auf der Datenbank. Das heißt eine Operation die das Schema der Tabelle ändert (beispielsweise eine Index-Erstellung oder ein ALTER TABLE Kommando) kann eine NOLOCK-Tabelle durchaus blockieren.
2. Abfragen mit NOLOCK blockieren keine anderen Operationen.
   Das stimmt definitiv nicht. Durch den oben gezeigten Schema-Lock auf die Tabelle kann eine NOLOCK-Abfrage natürlich auch eine INDEX-Erstellung blockieren.
3. Wenn alle Abfragen mit NOLOCK laufen, wird alles schneller.
   Das entspricht so nicht ganz der Wahrheit. Natürlich müssen Abfragen, die mit NOLOCK laufen keine Daten ins Transaktions-Log legen und benötigen daher weniger Speicher und sind schneller. Trotzdem ist es wenig sinnvoll, auf Abfrageebene das Isolationslevel für jede Abfrage zu ändern, denn einerseits erhaltet ihr Resultate wie UNCOMMITED READS, die vom DBA nicht vorgesehen sind (sonst hätte die Datenbank ja global ein anderes ISOLATION LEVEL).
4. Mach einfach NOLOCK, dann ist alles gut.
   Wenn ihr das Standardverhalten des SQL Servers überschreibt, dann sollte euch bewusst sein, was ihr da tut und warum. Wenn ihr NOLOCK standardmäßig nutzt, macht euch bewusst warum und was die Seiteneffekte sein könnten.

Referenzen

• Offizielle Dokumentation von Microsoft
• Erklärungen auf mssqltips