TL; DR: Poniższe pytanie sprowadza się do: Podczas wstawiania wiersza istnieje okno możliwości między wygenerowaniem nowej Identitywartości a zablokowaniem odpowiedniego klucza wiersza w indeksie klastrowym, w którym zewnętrzny obserwator mógłby zobaczyć nowszą Identity wartość wprowadzona przez równoległą transakcję? (W SQL Server.)

Wersja szczegółowa

Mam tabelę programu SQL Server z Identitykolumną o nazwie CheckpointSequence, która jest kluczem indeksu klastrowanego tabeli (który ma również szereg dodatkowych indeksów nieklastrowanych). Wiersze są wstawiane do tabeli przez kilka równoległych procesów i wątków (na poziomie izolacji READ COMMITTEDi bez IDENTITY_INSERT). Jednocześnie istnieją procesy okresowo odczytujące wiersze z indeksu klastrowego, uporządkowane według tej CheckpointSequencekolumny (również na poziomie izolacji READ COMMITTED, z READ COMMITTED SNAPSHOTwyłączoną opcją).

Obecnie polegam na tym, że procesy odczytu nigdy nie mogą „pominąć” punktu kontrolnego. Moje pytanie brzmi: czy mogę polegać na tej właściwości? A jeśli nie, co mogę zrobić, aby było to prawdą?

Przykład: po wstawieniu wierszy o wartościach tożsamości 1, 2, 3, 4 i 5 czytelnik nie może zobaczyć wiersza o wartości 5 przed zobaczeniem wiersza o wartości 4. Testy pokazują, że zapytanie zawierające ORDER BY CheckpointSequenceklauzulę ( i aWHERE CheckpointSequence > -1 klauzula), niezawodnie blokuje za każdym razem, gdy wiersz 4 ma zostać odczytany, ale jeszcze nie zatwierdzony, nawet jeśli wiersz 5 został już zatwierdzony.

Uważam, że przynajmniej teoretycznie mogą istnieć warunki rasowe, które mogą spowodować załamanie tego założenia. Niestety, dokumentacja Identitynie mówi wiele o tym, jak Identitydziała w kontekście wielu równoczesnych transakcji, mówi tylko: „Każda nowa wartość jest generowana na podstawie bieżącego ziarna i przyrostu”. i „Każda nowa wartość dla określonej transakcji różni się od innych równoczesnych transakcji w tabeli”. ( MSDN )

Moje rozumowanie jest takie, że musi działać jakoś tak:

1. Transakcja jest uruchamiana (jawnie lub niejawnie).
2. Generowana jest wartość tożsamości (X).
3. Odpowiednia blokada wiersza jest pobierana na indeks klastrowany na podstawie wartości tożsamości (chyba że uruchomi się eskalacja blokady, w którym to przypadku cała tabela jest blokowana).
4. Wiersz został wstawiony.
5. Transakcja zostaje zatwierdzona (być może sporo czasu później), więc blokada jest ponownie usuwana.

Myślę, że pomiędzy krokiem 2 a 3 jest bardzo małe okno, w którym

• równoczesna sesja może wygenerować następną wartość tożsamości (X + 1) i wykonać wszystkie pozostałe kroki,
• w ten sposób pozwalając czytelnikowi przyjść dokładnie w tym momencie, aby odczytać wartość X + 1, pomijając wartość X.

Oczywiście prawdopodobieństwo tego wydaje się bardzo niskie; ale nadal - może się zdarzyć. A może to?

(Jeśli interesuje Cię kontekst: jest to implementacja SQL Persistence Engine NEventStore. NEventStore implementuje magazyn zdarzeń tylko do dołączania, w którym każde zdarzenie otrzymuje nowy, rosnący numer sekwencji kontrolnej. Klienci odczytują zdarzenia z magazynu zdarzeń uporządkowane według punktu kontrolnego aby wykonać wszelkiego rodzaju obliczenia. Po przetworzeniu zdarzenia z punktem kontrolnym X klienci biorą pod uwagę tylko „nowsze” zdarzenia, tj. zdarzenia z punktem kontrolnym X + 1 i wyższym. Dlatego ważne jest, aby zdarzenia nigdy nie były pomijane, ponieważ nigdy więcej nie będą brane pod uwagę. Obecnie próbuję ustalić, czy Identityimplementacja punktu kontrolnego na podstawie tego warunku spełnia ten wymóg. Są to dokładnie używane instrukcje SQL : Schemat , zapytanie Writer ,Zapytanie czytelnika ).

Jeśli mam rację i może się zdarzyć sytuacja opisana powyżej, widzę tylko dwie opcje radzenia sobie z nimi, obie są niezadowalające:

• Gdy zobaczysz wartość sekwencji punktu kontrolnego X + 1 przed zobaczeniem X, odrzuć X + 1 i spróbuj ponownie później. Ponieważ jednak Identitymoże oczywiście tworzyć luki (np. Przy wycofywaniu transakcji), X może nigdy nie nadejść.
• To samo podejście, ale zaakceptuj przerwę po n milisekundach. Jaką wartość n należy jednak przyjąć?

Jakieś lepsze pomysły?

Czy podczas wstawiania wiersza istnieje szansa między wygenerowaniem nowej wartości tożsamości a zablokowaniem odpowiedniego klucza wiersza w indeksie klastrowym, w którym zewnętrzny obserwator może zobaczyć nowszą wartość tożsamości wstawioną przez równoległą transakcję?

Tak.

Alokacja wartości tożsamości jest niezależna od zawierającym transakcji użytkownika . Jest to jeden z powodów, dla których wartości tożsamości są konsumowane, nawet jeśli transakcja zostanie wycofana. Sama operacja przyrostowa jest chroniona przez zatrzask, aby zapobiec uszkodzeniu, ale taki jest zakres zabezpieczeń.

W szczególnych okolicznościach implementacji alokacja tożsamości (wezwanie do CMEDSeqGen::GenerateNewValue) jest wykonywana przed aktywacją transakcji użytkownika dla wstawki (a więc przed podjęciem jakichkolwiek blokad).

Uruchamiając jednocześnie dwie wstawki z dołączonym debugerem, aby umożliwić mi zawieszenie jednego wątku tuż po zwiększeniu i przydzieleniu wartości tożsamości, byłem w stanie odtworzyć scenariusz, w którym:

1. Sesja 1 uzyskuje wartość tożsamości (3)
2. Sesja 2 uzyskuje wartość tożsamości (4)
3. Sesja 2 wykonuje wstawianie i zatwierdza (więc wiersz 4 jest w pełni widoczny)
4. Sesja 1 wykonuje wstawianie i zatwierdza (wiersz 3)

Po kroku 3 zapytanie wykorzystujące numer wiersza w ramach zablokowanego odczytu zatwierdzonego zwróciło:

W Twojej implementacji spowodowałoby to niepoprawne pominięcie Checkpoint ID 3.

Okno niewłaściwej szansy jest stosunkowo małe, ale istnieje. Aby dać bardziej realistyczny scenariusz niż dołączenie debuggera: Wykonujący wątek zapytania może dać harmonogram po kroku 1 powyżej. Umożliwia to drugiemu wątkowi przydzielenie wartości tożsamości, wstawienie i zatwierdzenie, zanim oryginalny wątek wznowi wykonywanie jego wstawiania.

Dla jasności nie ma żadnych blokad ani innych obiektów synchronizujących chroniących wartość tożsamości po jej przydzieleniu i przed użyciem. Na przykład po kroku 1 powyżej w transakcji współbieżnej można zobaczyć nową wartość tożsamości za pomocą funkcji T-SQL, tak jak IDENT_CURRENTzanim wiersz istnieje w tabeli (nawet niezaangażowany).

Zasadniczo nie ma więcej gwarancji dotyczących wartości tożsamości niż udokumentowane :

• Każda nowa wartość jest generowana na podstawie bieżącego ziarna i przyrostu.
• Każda nowa wartość dla konkretnej transakcji różni się od innych równoczesnych transakcji w tabeli.

To jest naprawdę to.

Jeśli wymagane jest ścisłe transakcyjne przetwarzanie FIFO, prawdopodobnie nie masz innego wyboru niż ręczna serializacja. Jeśli aplikacja ma mniejsze wymagania, masz więcej opcji. Pytanie nie jest w 100% jasne w tym względzie. Niemniej jednak możesz znaleźć przydatne informacje w artykule Remusa Rusanu Używanie tabel jako kolejek .

Ponieważ Paul White odpowiedział absolutnie poprawnie, istnieje możliwość tymczasowego „pominięcia” wierszy tożsamości. Oto tylko mały fragment kodu do odtworzenia tego przypadku na własny użytek.

Utwórz bazę danych i tabelę testową:

create database IdentityTest
go
use IdentityTest
go
create table dbo.IdentityTest (ID int identity, c1 char(10))
create clustered index CI_dbo_IdentityTest_ID on dbo.IdentityTest(ID)

Wykonuj współbieżne operacje wstawiania i wybierania w tej tabeli w programie konsoli C #:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading;

namespace IdentityTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var insertThreads = new List<Thread>();
            var selectThreads = new List<Thread>();

            //start threads for infinite inserts
            for (var i = 0; i < 100; i++)
            {
                insertThreads.Add(new Thread(InfiniteInsert));
                insertThreads[i].Start();
            }

            //start threads for infinite selects
            for (var i = 0; i < 10; i++)
            {
                selectThreads.Add(new Thread(InfiniteSelectAndCheck));
                selectThreads[i].Start();
            }
        }

        private static void InfiniteSelectAndCheck()
        {
            //infinite loop
            while (true)
            {
                //read top 2 IDs
                var cmd = new SqlCommand("select top(2) ID from dbo.IdentityTest order by ID desc")
                {
                    Connection = new SqlConnection("Server=localhost;Database=IdentityTest;Integrated Security=SSPI;Application Name=IdentityTest")
                };

                try
                {
                    cmd.Connection.Open();
                    var dr = cmd.ExecuteReader();

                    //read first row
                    dr.Read();
                    var row1 = int.Parse(dr["ID"].ToString());

                    //read second row
                    dr.Read();
                    var row2 = int.Parse(dr["ID"].ToString());

                    //write line if row1 and row are not consecutive
                    if (row1 - 1 != row2)
                    {
                        Console.WriteLine("row1=" + row1 + ", row2=" + row2);
                    }
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        private static void InfiniteInsert()
        {
            //infinite loop
            while (true)
            {
                var cmd = new SqlCommand("insert into dbo.IdentityTest (c1) values('a')")
                {
                    Connection = new SqlConnection("Server=localhost;Database=IdentityTest;Integrated Security=SSPI;Application Name=IdentityTest")
                };

                try
                {
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }
    }
}

Ta konsola wypisuje wiersz dla każdego przypadku, gdy jeden z czytanych wątków „pomija” wpis.

Najlepiej nie oczekiwać, że tożsamość będzie następować po sobie, ponieważ istnieje wiele scenariuszy, które mogą pozostawić luki. Lepiej jest traktować tożsamość jak liczbę abstrakcyjną i nie przypisywać do niej żadnego znaczenia biznesowego.

Zasadniczo luki mogą się zdarzyć, jeśli cofniesz operacje INSERT (lub jawnie usuniesz wiersze), a duplikaty mogą wystąpić, jeśli ustawisz właściwość tabeli IDENTITY_INSERT na ON.

Luki mogą wystąpić, gdy:

1. Rejestry są usuwane.
2. Wystąpił błąd podczas próby wstawienia nowego rekordu (wycofano)
3. Aktualizacja / wstawka z jawną wartością (opcja tożsamość_wstaw).
4. Wartość przyrostowa jest większa niż 1.
5. Transakcja wycofuje się.

Właściwość tożsamości w kolumnie nigdy nie gwarantowała:

• wyjątkowość

• Kolejne wartości w ramach transakcji. Jeśli wartości muszą być następujące po sobie, transakcja powinna korzystać z wyłącznej blokady tabeli lub poziomu izolowania SERIALIZABLE.

• Kolejne wartości po ponownym uruchomieniu serwera.

• Ponowne wykorzystanie wartości.

Jeśli z tego powodu nie możesz użyć wartości tożsamości, utwórz osobną tabelę z aktualną wartością i zarządzaj dostępem do tabeli i przypisywania numerów w swojej aplikacji. Może to mieć wpływ na wydajność.

https://msdn.microsoft.com/en-us/library/ms186775(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110) .aspx

Podejrzewam, że czasami może to prowadzić do problemów, które nasilają się, gdy serwer jest obciążony. Rozważ dwie transakcje:

1. T1: wstaw do T ... - powiedzmy, że 5 wstawia
2. T2: wstaw do T ... - powiedzmy 6 wstaw się
3. T2: zatwierdzenie
4. Czytelnik widzi 6, ale nie 5
5. T1: zatwierdzenie

W powyższym scenariuszu Twój LAST_READ_ID wyniesie 6, więc 5 nigdy nie zostanie odczytany.

Uruchamianie tego skryptu:

BEGIN TRAN;
INSERT INTO dbo.Example DEFAULT VALUES;
COMMIT;

Poniżej znajdują się blokady, które widzę, że zostały nabyte i zwolnione podczas przechwytywania przez sesję zdarzenia rozszerzonego:

name            timestamp                   associated_object_id    mode    object_id   resource_type   session_id  resource_description
lock_acquired   2016-03-29 06:37:28.9968693 1585440722              IX      1585440722  OBJECT          51          
lock_acquired   2016-03-29 06:37:28.9969268 7205759890195415040     IX      0           PAGE            51          1:1235
lock_acquired   2016-03-29 06:37:28.9969306 7205759890195415040     RI_NL   0           KEY             51          (ffffffffffff)
lock_acquired   2016-03-29 06:37:28.9969330 7205759890195415040     X       0           KEY             51          (29cf3326f583)
lock_released   2016-03-29 06:37:28.9969579 7205759890195415040     X       0           KEY             51          (29cf3326f583)
lock_released   2016-03-29 06:37:28.9969598 7205759890195415040     IX      0           PAGE            51          1:1235
lock_released   2016-03-29 06:37:28.9969607 1585440722              IX      1585440722  OBJECT          51      

Zwróć uwagę na blokadę RI_N KEY uzyskaną bezpośrednio przed blokadą X dla nowego tworzonego wiersza. Ta krótkotrwała blokada zakresu zapobiegnie uzyskaniu przez współbieżną wstawkę kolejnej blokady RI_N KEY, ponieważ blokady RI_N są niezgodne. Okno wspomniane między krokami 2 i 3 nie stanowi problemu, ponieważ blokada zakresu jest uzyskiwana przed blokadą wiersza na nowo wygenerowanym kluczu.

Tak długo, jak SELECT...ORDER BYrozpoczyna się skanowanie przed pożądanymi nowo wstawionymi wierszami, oczekiwałbym pożądanego zachowania na domyślnym READ COMMITTEDpoziomie izolacji, o ile READ_COMMITTED_SNAPSHOTopcja bazy danych jest wyłączona.

Z mojego zrozumienia programu SQL Server domyślnym zachowaniem jest to, że drugie zapytanie nie wyświetla żadnych wyników, dopóki pierwsze zapytanie nie zostanie zatwierdzone. Jeśli pierwsze zapytanie wykonuje ROLLBACK zamiast COMMIT, wówczas w kolumnie będzie brakować identyfikatora.

Podstawowa konfiguracja

Tabela bazy danych

Utworzyłem tabelę bazy danych o następującej strukturze:

CREATE TABLE identity_rc_test (
    ID4VALUE INT IDENTITY (1,1), 
    TEXTVALUE NVARCHAR(20),
    CONSTRAINT PK_ID4_VALUE_CLUSTERED 
        PRIMARY KEY CLUSTERED (ID4VALUE, TEXTVALUE)
)

Poziom izolacji bazy danych

Sprawdziłem poziom izolacji mojej bazy danych za pomocą następującej instrukcji:

SELECT snapshot_isolation_state, 
       snapshot_isolation_state_desc, 
       is_read_committed_snapshot_on
FROM sys.databases WHERE NAME = 'mydatabase'

Które zwróciło następujący wynik dla mojej bazy danych:

snapshot_isolation_state    snapshot_isolation_state_desc   is_read_committed_snapshot_on
0                           OFF                             0

(Jest to ustawienie domyślne dla bazy danych w SQL Server 2012)

Skrypty testowe

Poniższe skrypty zostały wykonane przy użyciu standardowych ustawień klienta SQL Server SSMS i standardowych ustawień SQL Server.

Ustawienia połączeń klienckich

Klient został skonfigurowany do używania poziomu izolacji transakcji READ COMMITTEDzgodnie z opcjami zapytania w SSMS.

Zapytanie 1

Następujące zapytanie zostało wykonane w oknie zapytania o numerze SPID 57

SELECT * FROM dbo.identity_rc_test
BEGIN TRANSACTION [FIRST_QUERY]
INSERT INTO dbo.identity_rc_test (TEXTVALUE) VALUES ('Nine')
/* Commit is commented out to prevent the INSERT from being commited
--COMMIT TRANSACTION [FIRST_QUERY]
--ROLLBACK TRANSACTION [FIRST_QUERY]
*/

Zapytanie 2

Następujące zapytanie zostało wykonane w oknie zapytania o numerze SPID 58

BEGIN TRANSACTION [SECOND_QUERY]
INSERT INTO dbo.identity_rc_test (TEXTVALUE) VALUES ('Ten')
COMMIT TRANSACTION [SECOND_QUERY]
SELECT * FROM dbo.identity_rc_test

Zapytanie nie zostało zakończone i czeka na zwolnienie blokady eXclusive na STRONIE.

Skrypt określający blokowanie

Ten skrypt wyświetla blokowanie występujące na obiektach bazy danych dla dwóch transakcji:

SELECT request_session_id, resource_type,
       resource_description, 
       resource_associated_entity_id,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_session_id IN (57, 58)

A oto wyniki:

58  DATABASE                    0                   S   GRANT
57  DATABASE                    0                   S   GRANT
58  PAGE            1:79        72057594040549300   IS  GRANT
57  PAGE            1:79        72057594040549300   IX  GRANT
57  KEY         (a0aba7857f1b)  72057594040549300   X   GRANT
58  KEY         (a0aba7857f1b)  72057594040549300   S   WAIT
58  OBJECT                      245575913           IS  GRANT
57  OBJECT                      245575913           IX  GRANT

Wyniki pokazują, że okno zapytania nr 1 (SPID 57) ma blokadę współdzieloną (S) w bazie danych, blokadę zamierzonego eXlusive (IX) w OBJECT, blokadę zamierzonego eXlusive (IX) na STRONIE, do której chce wstawić, oraz wyłączność blokada (X) na KLUCZIE, do którego jest włożony, ale jeszcze nie zatwierdzony.

Z powodu nieprzydzielonych danych drugie zapytanie (SPID 58) ma blokadę współdzieloną (S) na poziomie DATABASE, blokadę zamierzonego współdzielenia (IS) w OBJECT, blokadę zamierzonego współdzielenia (IS) na stronie udostępnionej (S ) zablokuj KLUCZ ze statusem żądania CZEKAJ.

Podsumowanie

Zapytanie w pierwszym oknie zapytania jest wykonywane bez zatwierdzania. Ponieważ drugie zapytanie może zawierać tylko READ COMMITTEDdane, czeka albo na przekroczenie limitu czasu, albo na zatwierdzenie transakcji w pierwszym zapytaniu.

Z mojego zrozumienia wynika, że ​​domyślne zachowanie Microsoft SQL Server.

Należy zauważyć, że identyfikator jest rzeczywiście w sekwencji dla kolejnych odczytów instrukcji SELECT, jeśli pierwsza instrukcja COMMITs.

Jeśli pierwsza instrukcja wykonuje ROLLBACK, w sekwencji znajdzie się brakujący identyfikator, ale nadal z identyfikatorem w kolejności rosnącej (pod warunkiem, że utworzono INDEKS z domyślną opcją ASC lub w kolumnie ID).

Aktualizacja:

(Tępo) Tak, możesz polegać na tym, że kolumna tożsamości działa poprawnie, dopóki nie napotkasz problemu. Jest tylko jeden HOTFIX dotyczący SQL Server 2000 i kolumny tożsamości w witrynie Microsoft.

Jeśli nie możesz polegać na poprawnej aktualizacji kolumny tożsamości, myślę, że w witrynie Microsoft byłoby więcej poprawek lub poprawek.

Jeśli masz umowę pomocy technicznej Microsoft, zawsze możesz otworzyć skrzynkę doradczą i poprosić o dodatkowe informacje.