Jak ograniczyć procedurę przechowywaną SQL, która może być uruchamiana przez jedną osobę naraz?

Mam procedurę składowaną, która zasadniczo wybiera wartości z jednej tabeli i wstawia je do drugiej, co jest rodzajem archiwizacji. Chcę uniknąć robienia tego przez wiele osób jednocześnie.

Podczas gdy ta procedura jest uruchomiona, nie chcę, aby ktokolwiek mógł ją uruchomić, jednak nie chcę serializacji, druga osoba uruchomi procedurę po jej zakończeniu.

Chcę, aby druga osoba próbująca go uruchomić, otrzymywała błąd podczas wykonywania procedury.

Próbowałem z użyciem sp_getapplock, jednak nie jestem w stanie całkowicie zatrzymać tej osoby przed uruchomieniem procedury.

Próbowałem również znaleźć procedurę z sys.dm_exec_requests i zablokować procedurę, chociaż to działa, myślę, że nie jest optymalne, ponieważ na niektórych serwerach nie mam uprawnień do uruchamiania sys.dm_exec_sql_text (uchwyt_sql).

Jak najlepiej to zrobić?

Aby dodać do odpowiedzi @ Tibor-Karaszi, ustawienie limitu czasu blokady nie powoduje w rzeczywistości błędu (przesłałem PR przeciwko dokumentom). sp_getapplock zwraca tylko -1, więc musisz sprawdzić zwracaną wartość. Więc tak:

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
   begin
      throw 50001, 'There is already an instance of this procedure running.', 10
   end

  --do stuff
  waitfor delay '00:00:20'


commit transaction
end

Użyj sp_getapplock na początku proc i ustaw limit czasu blokady na bardzo niską wartość. W ten sposób pojawia się błąd, gdy jesteś zablokowany.

Inną opcją jest zbudowanie tabeli kontrolującej dostęp do procedury. poniższy przykład pokazuje możliwą tabelę, a także procedurę, która mogłaby z niej skorzystać.

CREATE TABLE dbo.ProcedureLock
    (
    ProcedureLockID INT NOT NULL IDENTITY(1,1)
    , ProcedureName SYSNAME NOT NULL
    , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
    , UserSPID INT NULL
    , DateLockTaken DATETIME2(7) NULL
    , DateLockExpires DATETIME2(7) NULL
    , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
    )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
    ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
    (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

    /** Take Lock */
    UPDATE dbo.ProcedureLock
    SET IsLocked = 1
        , UserSPID = @@SPID
        , DateLockTaken = SYSDATETIME()
        , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
    WHERE ProcedureName = 'dbo.DoSomeWork'
        AND (IsLocked = 0
            OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
            )

    IF COALESCE(@@ROWCOUNT, 0) = 0
    BEGIN
        ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
    END

    /** DO WHATEVER NEEDS TO BE DONE */

    /** Release the lock */
    UPDATE dbo.ProcedureLock
    SET IsLocked = 0
        , UserSPID = NULL
        , DateLockTaken = NULL
        , DateLockExpires = NULL
    WHERE ProcedureName = 'dbo.DoSomeWork'

END

Myślę, że próbujesz rozwiązać problem w niewłaściwy sposób. To, czego chcesz, to najwyższa ochrona spójności bazy danych. Jeśli dwie osoby jednocześnie uruchamiają procedurę składowaną, spójność bazy danych może zostać naruszona.

Aby zabezpieczyć się przed różnego rodzaju niespójnościami w bazie danych, standard SQL ma cztery poziomy izolacji transakcji:

• PRZECZYTAJ NIEDOPUSZCZONE, gdy transakcje zasadniczo tracą na wartości, a inne transakcje zawierają brudne dane. Nie używaj tego!
• PRZECZYTAJ ZOBOWIĄZANE, gdy transakcje widzą tylko zatwierdzone dane, ale mogą występować niespójności, w których dwie transakcje mogą przekraczać się nawzajem
• POWTARZALNE CZYTANIE, w którym rozwiązany jest jeden rodzaj niespójności, powtarzanie odczytu
• SERIALIZABLE, który gwarantuje, że istnieje pewne wirtualne zamówienie, w którym wykonanie transakcji doprowadziłoby do rezultatów, których rezultatem była ich realizacja

Jednak standard SQL ma podejście oparte na blokowaniu tych niespójności z bazami danych, a ze względu na wydajność wiele baz danych stosuje podejście oparte na izolacji migawek, które zasadniczo ma następujące poziomy:

• PRZECZYTAJ ZAKOŃCZONO, czyli to samo, co w blokowanych bazach danych
• SNAPSHOT ISOLATION, gdzie baza danych widzi migawkę wszystkich danych, a jeśli spróbuje zaktualizować wiersz, który został zaktualizowany przez inną transakcję, zostanie anulowana, ale istnieją pewne dobrze znane anomalie, które mogą mieć miejsce
• SERIALIZABLE, który jest taki sam, jak w bazach danych opartych na blokowaniu, ale tym razem zaimplementowany w inny sposób, nie poprzez podejmowanie blokad, ale przez zapewnienie, że nie występują naruszenia serializacji, a jeśli takie naruszenie zostanie wykryte, anulowanie transakcji

Anulowanie transakcji w tych bazach danych opartych na izolacji migawek może wydawać się niepokojące, ale znowu każda baza danych anuluje transakcję z powodu impasu, więc każda rozsądna aplikacja i tak musi móc ponownie spróbować przeprowadzić transakcję.

Co chcesz jest SERIALIZABLE poziom izolacji: zapewnia, że jeśli transakcje wykonywane niezależnie jeden po drugim wynikiem w stanie dobrym, każda równoległa realizacja transakcji powoduje również stan dobry. Na szczęście Michael Cahill w swojej rozprawie doktorskiej dowiedział się, w jaki sposób SERIALIZABLE poziom izolacji może być obsługiwany przez migawkowe izolowane bazy danych przy niewielkim wysiłku.

Jeśli używasz poziomu izolacji SERIALIZABLE w izolowanej bazie danych migawek, jeśli dwie osoby spróbują uruchomić procedurę składowaną jednocześnie i staną sobie na palcach, jedna z transakcji zostanie anulowana.

Czy SQL Server rzeczywiście obsługuje poziom izolacji SERIALIZABLE (zamiast maskaradowej izolacji migawki za słowem kluczowym SERIALIZABLE )? Szczerze mówiąc, nie wiem: jedyną znaną bazą danych, która ją obsługuje, jest PostgreSQL.

Mimo że nie udzieliłem porady SQL Serverowi, nadal zamieszczam tę odpowiedź, ponieważ użytkownicy PostgreSQL i użytkownicy innych baz danych, którzy mogą rozważyć przejście na PostgreSQL, mogą skorzystać z mojej odpowiedzi. Ponadto użytkownicy baz danych innych niż PostgreSQL, którzy nie mogą przełączyć się na PostgreSQL, mogą wywierać presję na swojego ulubionego dostawcę bazy danych, aby zaoferował autentyczny SERIALIZABLE poziom izolacji.

Zdaję sobie sprawę, że „prawdziwy” problem może być bardziej złożony.

W przeciwnym razie: jeśli archiwizujesz za pomocą wyzwalaczy wstawiania i / lub aktualizacji, możesz uniknąć problemu, który próbujesz rozwiązać.

Mam nadzieję, że to pomaga,
Chris C.