Zakleszczenie instrukcji Usuń

Dostaję impas, gdy uruchomione jest zadanie SQL Server. Zakleszczenie występuje na prostej instrukcji DELETE. Myślałem, że musiałoby być uruchomione zapytanie SELECT / UPDATE, aby spowodować impas? Ale wygląda na to, że jest to impas DELETE / DELETE ...

To, czego szukam, to dlaczego mam zakleszczenie DELETE / DELETE. O ile mi wiadomo, przekazuje różne parametry.

Jakieś pomysły? Dzięki.

deadlock-list
2014-05-20 07:30:09.66 spid25s      deadlock victim=process409048
2014-05-20 07:30:09.66 spid25s       process-list
2014-05-20 07:30:09.66 spid25s        process id=process409048 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127294 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x397219620 lockMode=U schedulerid=5 kpid=3792 status=suspended spid=150 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)     
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown     
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process432e08 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=2648 ownerId=629859744 transactionname=DELETE lasttranstarted=2014-05-20T07:30:04.833 XDES=0x4c3426b50 lockMode=U schedulerid=6 kpid=5988 status=suspended spid=146 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:04.833 lastbatchcompleted=2014-05-20T07:30:04.820 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629859744 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)     
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown     
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process39ea562c8 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x13e0e4b50 lockMode=U schedulerid=2 kpid=7124 status=suspended spid=150 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2

sql-server deadlock

— K09
źródło

Nie, impas może wystąpić w innych scenariuszach poza SELECT / UPDATE. Wszystko, czego naprawdę potrzebujesz, to dwa procesy, z których każdy potrzebuje zasobu, który posiada drugi. (1) Czy instrukcje DELETE są częścią większej transakcji? (2) Czy umiesz gdzieś zakleszczyć XML, zamiast gównianego tekstu dziennika błędów?

— Aaron Bertrand

Czy możesz zamieścić schemat tabeli dbo.UserDetailsDataobejmujący wszystkie indeksy? Czy wiesz również, czy te instrukcje są wywoływane z tymi samymi parametrami? Biorąc pod uwagę, że oba mają zerowy dziennik, zastanawiam się, czy wszystko, co musisz zrobić, to serializować połączenia, ponieważ nadepną na siebie.

— Jon Seigel

Jak uzyskać XML? Mam błąd z dzienników błędów programu SQL Server. Instrukcje są wywoływane z różnymi parametrami. Niedawno dodaliśmy szereg odfiltrowanych indeksów, które filtrują w polu Data użytkownika.

— K09

Złap zdarzenie wykresu zakleszczenia w programie Profiler. Następnie, po złapaniu, kliknij prawym przyciskiem myszy wiersz -> wyodrębnij dane zdarzenia -> zapisz gdzieś jako .xdl i opublikuj jego zawartość (to jest xml) na Pastebin (lub gdzieś podobnym).

— Marian

Cześć, XML tutaj opublikowany ... mam nadzieję, że to pomoże! dl.dropboxusercontent.com/u/16953128/DeadlockTest.xdl

— K09

To, czego szukam, to dlaczego mam zakleszczenie DELETE / DELETE.

Wygląda na to, że impas występuje, ponieważ:

spid 54 ecid 0uzyskuje Ublokadę strony update ( )PAGE: 12:1:5147422
spid 166 ecid 3żąda Ublokady strony aktualizacji ( ) na tej samej stronie i jest blokowany
spid 54 ecid 2żąda Ublokady strony aktualizacji ( ) na tej samej stronie ...

Strony są wstępnie pobierane dla zapytania, z blokadami aktualizacji uzyskanymi przez ecid 0. To jest krok 1 powyżej. W kroku 3 wątek potomny tego samego zapytania równoległego ( ecid 2) żąda tej samej blokady. Zwykle nie stanowiłoby to problemu. SQL Server zna ecid 0i ecid 2jest wątkiem tego samego procesu nadrzędnego. Niestety przeszkadza w tym krok 2 i następuje impas.

To powiedziawszy, nie powinieneś zbytnio przejmować się tym, dlaczego pojawia się impas, ważne pytanie, jak go uniknąć. Odpowiedzią jest zapewnienie efektywnej ścieżki dostępu dla DELETE. Instrukcja musi znajdować wiersze WHERE Username = @P1 AND UserDate = @P2, dlatego w tych kolumnach powinien znajdować się indeks.

I oczywiście masz taki indeks. Prawdziwym pytaniem jest, dlaczego problemy zaczęły występujący po dodany filtrowane indeksy.

Odpowiedź na to, że dodatkowe informacje o kolumnach są potrzebne do zlokalizowania odfiltrowanych wierszy indeksu do usunięcia (i sprawdzenia ich predykatów). Jeśli kwerenda korzysta z planu wykonania wąskiego / na wiersz , silnik wykonawczy nie może pobrać dodatkowych kolumn w operatorze usuwania klastrowanego indeksu, podobnie jak w planie szerokim / na indeks.

Możesz znaleźć więcej szczegółów na ten temat oraz sprawdzony przykład w tym poście na blogu .

W takim przypadku informacje o kolumnie muszą pochodzić z części planu po prawej stronie usuwania indeksu klastrowanego, dlatego używane jest równoległe skanowanie indeksu klastrowanego, a użytkownik otrzymuje wolne zapytanie o wysokim potencjale impasu.

Odpowiedź brzmi: wykonaj jedną z następujących czynności:

Usuń przefiltrowane indeksy
Dodaj filtrowane kolumny indeksu klucz / włącz / predykat do istniejącego indeksu nazwa / data
Wymuś szeroki plan aktualizacji (brak obsługiwanego sposobu, aby to zrobić)
Uruchom zapytanie w izolacji migawki (nie RCSI)

Opcja 2 byłaby moim zdecydowanym wyborem.

Opcja 4 (dzięki Jack Douglas) ma tę zaletę, że usuwa zakleszczenia i nie powinna powodować żadnych „konfliktów aktualizacji”, biorąc pod uwagę rozłączny charakter zmian, ale wymaga włączenia izolacji migawki na poziomie bazy danych, jawnie zmieniając poziom izolacji, i nie rozwiąże podstawowego problemu : nadal skończysz marnotrawstwem równoległego skanowania tabeli, w którym dobre wyszukiwanie indeksu jest tym, czego naprawdę chcesz.

— Paul White 9
źródło