Frage Warum sollten Sie den TSQL-Anweisungsblock einfügen, wenn die Transaktionsisolationsstufe für eine andere Transaktion mit einem nicht konfligierenden Filter serialisiert werden kann?


Serialisierbare Transaktionsisolationsstufen vermeiden das Problem von Phantom-Lesevorgängen, indem alle Einfügungen in eine Tabelle in einer Transaktion blockiert werden, die mit ausgewählten Anweisungen in anderen Transaktionen in Konflikt stehen. Ich versuche, es mit einem Beispiel zu verstehen, aber es blockiert einfügen, auch wenn der Filter in der SELECT-Anweisung nicht widersprüchlich ist. Ich würde mich über jede Erklärung darüber, warum sie sich so verhält, freuen.

Tabellenskript

CREATE TABLE [dbo].[dummy](
    [firstname] [char](20) NULL,
    [lastname] [char](20) NULL
) ON [PRIMARY]

GO

Session 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from dummy where firstname = 'abc'

Sitzung - 2

insert into dummy values('lmn', 'lmn') -- Why this blocks?

5
2017-07-09 10:06


Ursprung


Antworten:


Das erste Problem in Ihrem Testszenario besteht darin, dass die Tabelle keinen nützlichen Index enthält firstname. Die zweite ist, dass die Tabelle leer ist.

Von Key-Range-Verriegelung in BOL

Bevor die Schlüsselbereichssperre auftreten kann,   Die folgenden Bedingungen müssen erfüllt sein   zufrieden:

  • Die Transaktionsisolationsstufe muss auf festgelegt werden SERIALIZABLE.

  • Der Abfrageprozessor muss einen Index verwenden, um den Bereichsfilter zu implementieren   Prädikat. Zum Beispiel, die WHERE   Klausel in a SELECT Aussage könnte   stelle damit eine Bereichsbedingung auf   Prädikat: ColumnX BETWEEN N'AAA' AND N'CZZ'. Eine Schlüsselbereichssperre kann nur sein   erworben, wenn ColumnX von einem abgedeckt ist   Indexschlüssel.

Es gibt keinen geeigneten Index RangeS-S Sperrt auf, um serialisierbare Semantik zu gewährleisten SQL Server muss die gesamte Tabelle sperren.

Wenn Sie versuchen, einen gruppierten Index für die Tabelle in der Spalte für den ersten Namen wie folgt hinzuzufügen, und wiederholen Sie den Test ...

CREATE CLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)

... wirst du feststellen, dass du immer noch blockiert bist!

Trotz der Tatsache, dass jetzt ein passender Index existiert und der Ausführungsplan zeigt, dass es angefragt wird, um die Anfrage zu erfüllen.

Sie können sehen, warum, indem Sie Folgendes ausführen

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM   dummy
WHERE  firstname = 'abc'

SELECT resource_type,
       resource_description, 
       request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID

COMMIT 

Kehrt zurück

+---------------+----------------------+--------------+
| resource_type | resource_description | request_mode |
+---------------+----------------------+--------------+
| DATABASE      |                      | S            |
| OBJECT        |                      | IS           |
| PAGE          | 1:198                | IS           |
| KEY           | (ffffffffffff)       | RangeS-S     |
+---------------+----------------------+--------------+

SQL Server entfernt nicht nur eine Bereichssperre für genau den Bereich, den Sie in Ihrer Abfrage angeben.

Für ein Gleichheitsprädikat auf einem eindeutigen Index, wenn es einen übereinstimmenden Schlüssel gibt, wird nur eine reguläre Sperre und keine Art von Bereichssperre verwendet.

Bei einem nicht eindeutigen Suchprädikat werden Sperren für alle übereinstimmenden Schlüssel innerhalb des Bereichs plus dem "nächsten" am Ende des Bereichs (oder an ffffffffffff um Unendlichkeit darzustellen, wenn keine "nächste" Taste existiert). Sogar gelöschte "Geister" -Datensätze In diesem Bereich kann die Schlüsselverriegelung verwendet werden.

Wie hier beschrieben für ein Gleichheitsprädikat für einen eindeutigen oder nicht eindeutigen Index

Wenn der Schlüssel nicht vorhanden ist, wird die Sperre 'Bereich' auf der   'Next' Taste für eindeutigen und nicht eindeutigen Index. Wenn die 'nächste' Taste   existiert nicht, dann wird eine Bereichssperre für den 'unendlich'-Wert genommen.

Also mit einem leeren Tisch SELECT endet immer noch das Sperren des gesamten Index. Sie müssten zuvor auch eine Zeile dazwischen eingefügt haben abc und lmn und dann würde Ihr Einfügen erfolgreich sein.

insert into dummy values('def', 'def')

10
2017-07-11 23:57



Von http://msdn.microsoft.com/en-us/library/ms173763.aspx

SERIALIZIERBAR Gibt Folgendes an:

Anweisungen können keine Daten lesen, die von anderen Transaktionen geändert, aber noch nicht festgelegt wurden.

Keine anderen Transaktionen können Daten ändern, die von der aktuellen Transaktion gelesen wurden, bis die aktuelle Transaktion abgeschlossen ist.

Wie ich es verstehe, wird Ihr Einfügevorgang blockiert, da die Transaktion, unter der Ihr SELECT ausgeführt wird, nicht abgeschlossen wurde.


0
2017-07-09 15:48