Sleeping SPID blokuje inne transakcje

Naprawdę mam problemy ze śledzeniem blokowania, którego doświadczamy.

Rdzeń blokujący status SPID to „spanie”, cmd to „OCZEKUJĄCE POLECENIE” i tak sqltextjest SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Kiedy przeglądam raport Najważniejsze transakcje według liczby zablokowanych transakcji, blokująca instrukcja SQL ma wartość „-”.

Przeprowadziłem śledzenie SQL, a kiedy następuje blokowanie, śledzenie SPID blokującego rootowanie, ale tak naprawdę nigdzie mnie to nie doprowadziło. Ostatnia instrukcja śledzenia jest taka sama jak sqltextpowyżej SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Sprawdziłem wszystkie powiązane procedury przechowywane, które mogę znaleźć, aby upewnić się, że mają TRY / CATCH BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN (używamy procedur przechowywanych do wszystkiego, więc nie są uruchamiane samodzielne instrukcje). Ten problem zaczął się pojawiać w ciągu ostatnich 24 godzin i nikt nie twierdzi, że wprowadził jakiekolwiek zmiany w systemie.

Rozwiązanie: jedna z naszych rzadko używanych procedur przechowywanych miała błąd z wstawką (liczba kolumn nie pasowała), ale nadal jesteśmy zdezorientowani, co dokładnie się dzieje.

Przeglądając wszystkie informacje śledzenia, instrukcja EXEC dla tej procedury składowanej była wymieniona czasami, ale NIGDY nie przed wystąpieniem BLOKU na blokującym SPID. Wydawało się, że kiedy zaczyna się blokować, ślad nie rejestruje jego wykonania (ani żadnej z zawartych w nim instrukcji). Jednak zdarzają się inne czasy, gdy ślad zarejestrował jego wykonanie i nie nastąpiło blokowanie.

Raport o błędzie procedury składowanej pochodzi od użytkownika, a ja byłem w stanie znaleźć wiele instrukcji EXEC w plikach śledzenia i uruchomić je w SSMS. Nie miałem czasu, kiedy je prowadziłem, czy zdarzyło się, że nastąpiło jakieś zablokowanie, czy też się zawiesiły. Działały zgodnie z oczekiwaniami (blok catch został uruchomiony i wycofał transakcję po błędzie). Po rozwiązaniu problemu z procedurą przechowywaną problem nie pojawił się ponownie.

Z komentarzy domyślam się, że upłynął limit czasu poleceń po stronie klienta, który przerwał zapytanie SQL. Nie powoduje to wycofania transakcji, ponieważ połączenie pozostaje otwarte na serwerze SQL Server ze względu na pule połączeń.

Musisz więc użyć SET XACT_ABORT ON lub dodać kod wycofania klienta

Zobacz Limit czasu transakcji SQL Server dla wszystkich krwawych szczegółów

Użyj most_recent_sql_handle w sys.dm_exec_connections, aby zobaczyć ostatnią wykonaną instrukcję.

SELECT  t.text,
        QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
        + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
        c.connect_time,
        s.last_request_start_time,
        s.last_request_end_time,
        s.status
FROM    sys.dm_exec_connections c
JOIN    sys.dm_exec_sessions s
        ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE   c.session_id = 72;--your blocking spid

Sprawdź także, czy istnieją otwarte transakcje dla tego spid

SELECT  st.transaction_id,
        at.name,
        at.transaction_begin_time,
        at.transaction_state,
        at.transaction_status
FROM    sys.dm_tran_session_transactions st
JOIN    sys.dm_tran_active_transactions at
        ON st.transaction_id = at.transaction_id
WHERE   st.session_id = 72;--your blocking spid

Czy próbowałeś używać sp_whoisactive Adama Machanica ? Istnieje opcja uzyskania zewnętrznego polecenia, aby sprawdzić, czy naprawdę znajduje się w proc. Możliwe, że aplikacja wstrzymuje otwartą transakcję zamiast jej zatwierdzenia. Spróbuj także spojrzeć na DBCC OPENTRAN .