Bereiche der Transaktionsisolationsebene

Question

Wie lauten die Scoping-Regeln für Transaktionsisolierungsebenen in SQL Server 2005? Ich weiß, was die verschiedenen Ebenen bedeuten, aber nicht, wie man sie außerhalb eines manuell ausgeführten Skripts richtig anwendet. Ich kann keinen Leitfaden für den praktischen Einsatz in produktionsfähigem Code finden.

Offensichtlich beginnt der Geltungsbereich, wenn Sie einen Befehl wie diesen verwenden:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

Aber wo endet sie? Wenn ich die Iso-Stufe in einer gespeicherten Prozedur einstelle und diese Prozedur dann eine andere aufruft, erbt die verschachtelte Prozedur sie dann? Oder noch besser: Wenn ich die Iso-Ebene innerhalb der verschachtelten Prozedur erhöhe, wird sie dann in die aufrufende Prozedur zurückgeführt? Machen Transaktionsbefehle wie BEGIN TRAN, ROLLBACK und COMMIT einen Unterschied?

Bleiben die Änderungen der Isolationsebene auf irgendeine Weise erhalten, wenn ein gespeicherter Prozess von einer Anwendung oder einem Agentenauftrag aufgerufen wird? Muss ich am Ende jedes Prozesses immer zum Standardwert READ COMMITTED zurückkehren?

Ich würde es in verschiedenen Situationen testen, aber ich weiß nicht, wie ich den aktuellen Isolationsgrad ablesen kann.

Answer 1

Führen Sie den folgenden Test durch und überzeugen Sie sich selbst:

CREATE PROCEDURE dbo.KeepsIsolation
AS
BEGIN
PRINT 'Inside sproc that does not change isolation level';
DBCC USEROPTIONS;
END
GO

CREATE PROCEDURE dbo.ChangesIsolation
AS
BEGIN
PRINT 'Inside sproc that changes isolation level';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DBCC USEROPTIONS;
END
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DBCC USEROPTIONS;
EXEC dbo.KeepsIsolation;
DBCC USEROPTIONS;
EXEC dbo.ChangesIsolation;
-- demonstrates that isolation level restored to REPEATABLE READ after exiting the procedure
    DBCC USEROPTIONS;

Answer 2

Desde MSDN

Wenn Sie SET TRANSACTION ISOLATION LEVEL in einer gespeicherten Prozedur oder Trigger, wenn das Objekt die Kontrolle über die Isolationsebene zurückgibt wird zurückgesetzt auf die Stufe zurückgesetzt, die beim Aufrufen des Objekts galt . Wenn Sie zum Beispiel Sie REPEATABLE READ in einem Batch einstellen, und der Batch dann eine gespeicherte Prozedur aufruft, die die Isolationsebene auf SERIALIZABLE setzt, wird die Isolationsebene Isolationsebene wieder auf REPEATABLE READ gesetzt, wenn die gespeicherte Prozedur die Kontrolle an den Batch zurückgibt.

Answer 3

DBCC USEROPTIONS zeigt die aktuelle Isolationsstufe zusammen mit allen anderen SET-Optionen an.

Answer 4

Von Büchern auf Linie

Nur eine der TRANSACTION ISOLATION LEVEL kann jeweils nur eine der Optionen eingestellt werden, und sie bleibt für diese Verbindung eingestellt bis sie explizit geändert wird. Diese ist das Standardverhalten, es sei denn, eine Optimierungsoption auf der Tabellenebene in der FROM-Klausel der der Anweisung angegeben wird.

Answer 5

Die Isolationsebene wird nicht mit der Transaktion zurückgesetzt.

Die Isolationsebene bleibt aktuell, auch wenn Sie Prozeduren und Funktionen aufrufen.