Limit czasu transakcji programu SQL Server

Czy w SQL Server 2008 R2 istnieje sposób, aby spowodować przekroczenie limitu czasu dla modyfikacji bazy danych dotyczącej transakcji? Mamy scenariusz, w którym nasz kod aplikacji zawiesza się lub zgłasza wyjątek i nie wykonuje wycofania ani zatwierdzenia. Spowoduje to następnie zawieszenie innych sesji i oczekiwanie na zakończenie transakcji.

Rozszerzanie odpowiedzi Marka ...

Gdy wystąpi limit czasu klienta (na przykład .net CommandTimeout), klient wysyła komunikat „ABORT” do programu SQL Server. Następnie SQL Server po prostu porzuca przetwarzanie zapytania. Żadna transakcja nie jest wycofywana, żadne blokady nie są zwalniane.

Teraz połączenie jest zwracane do puli połączeń, więc nie jest zamykane na SQL Server. Jeśli to się kiedykolwiek zdarzy (poprzez KILL lub restart klienta itp.), Transakcje i blokady zostaną usunięte. Zauważ, że sp_reset_connection ich nie wyczyści lub nie wyczyści, nawet jeśli jest to reklamowane

Ten szczegół po przerwaniu zablokuje inne procesy.

Sposobem na wyczyszczenie transakcji SQL Server + blokowanie limitu czasu klienta (ściśle, zdarzenia ABORT) jest użycie ustawienia SET XACT_ABORT ON.

Możesz to sprawdzić otwierając 2 okna zapytań w SSMS:

Okno 1:

W menu Zapytanie .. Opcje zapytania ustaw limit czasu na 5 sekund, a następnie uruchom go

BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout

Okno 2, będzie czekać wiecznie (lub przekroczy limit czasu)

SELECT * FROM sometable

ZESTAW XACT_ABORT ON ma również interesujące efekty uboczne:

• @@ TRANCOUNT jest ustawiany na zero przy niejawnym wycofaniu, ale błąd 266 jest pomijany (dzieje się tak, jeśli @@ TRANCOUNT jest inny przy wejściu i wyjściu z zapisanego proc)
• XACT_STATE będzie wynosić -1 (jest „skazany”)

Kombinacja tego oznacza, że ​​nie można użyć SAVEPOINTS (chociaż nie pamiętam dokładnego zachowania) do częściowych zatwierdzeń / wycofań. Który mi odpowiada

SO linki na SET XACT_ABORT:

• https://stackoverflow.com/a/1539686/27535 (ja)
• https://stackoverflow.com/a/919279/27535 (ja)
• https://stackoverflow.com/a/1150350/27535 (Remus Rusanu)

W zagnieżdżonych przechowywanych procesach:

• https://stackoverflow.com/a/2074139/27535 (ja)

Na sp_reset_connection:

• https://stackoverflow.com/a/2924456/27535

Odpowiadam na to z wahaniem, ponieważ w opisie problemu nie ma wystarczających informacji, aby mieć 100% pewność, że to najlepsza rada. „Zawiesza się lub zgłasza wyjątek” sugeruje, że źródło problemu nie jest właściwie zrozumiane, więc postępuj ostrożnie.

Prawdopodobnie najprostsze rozwiązanie tego problemu SET XACT_ABORT ON.

XACT_ABORTokreśla, czy SQL Server cofnie transakcję w przypadku błędu w czasie wykonywania. Domyślnie SET XACT_ABORT OFFwycofuje tylko instrukcję, która spowodowała błąd, pozostawiając otwartą każdą transakcję nadrzędną.

Efektem ubocznym „gotcha” ustawienia domyślnego jest to, że przekroczenie limitu czasu może spowodować dokładnie ten sam problem, otwartą transakcję, którą klienci muszą obsługiwać i wycofywać. Jeśli klient nie spróbuje / catch / rollback, transakcja pozostanie otwarta, dopóki nie zajmie się (i cytuję @gbn) ultra-przemocą KILL <spid>.

Często cytowane artykuły Erlanda Sommarskoga na temat obsługi błędów w SQL Server zawierają całe zaplecze i strategię potrzebne do radzenia sobie z tymi scenariuszami i nie tylko.

Edytuj (następujący komentarz): Aby zidentyfikować otwarte transakcje, sp_whoisactive jest prawdopodobnie najbardziej kompletną funkcją.