SQL - deadlock

Witam.
Potrzebuje nakierowania czy jestem w stanie ogarnąć kwestie deadlock na bazie SQL.

##Od początku...
Comarch ma oprogramowanie, które nazywa się Analizy BI, straszne narzędzie, kompletnie nie zoptymalizowane. Program ten wyświetla w tabelkach cyferki zysków, kosztów, strat, grupuje je według czego sobie użytkownik życzy, ogólnie kombajn ale... Cała analiza jest robiona na bazie zapytania SQL, które, mam wrażenie, jest robione na znany sposób - "byle by działało". Dodatkowo to wszystko jest wczytywane przez DevExpressowego grida... również tym samym, znanym sposobem, o którym pisałem wcześniej.
Jeśli na bazie, którą chcemy analizować, pracuje około 20 osób to analiza rzuca błędem o deadlocku

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Jest szansa, aby to działało? Czy to jest ograniczenie SQL'a, czy programu Analiz BI?

#edit
Przykładowy raport w załączniku.

PS.
SQL Server 2017 Standard, Windows Server 2012 R2, 24GB RAM, XEON E5-2420 v2 @ 2.20GHz
Postawione jako Hyper-V

Bez ingerencji w konstrukcje zapytań, która jest przyczyną powstawania deadlocków, jedyne co mi przychodzi do głowy to przebudowa/reorganizacja indeksów. To trochę takie "pudrowanie syfa" w tym przypadku, ale często pomaga.

Czy możesz fachowym okiem spojrzeć i powiedzieć, czy jest opcja, aby przykładowe zapytanie nie robiło deadlocków, ale dawało identyczny rezultat?

To nie takie hop :)
1 zapytanie nie powoduje deadlocka. Muszą być minimum 2. Do tego często dochodzą duże transakcję. Najłatwiej dojść do źródła problemu przy użyciu narzedzi typu SQL Server Profiler. Naprawa takiego problemu, to też z reguły nie jest błachy problem, bo zazwyczaj to jest wierzchołek góry lodowej. Rzadko jest to 1 zapytanie, które wystarczy poprawić i wszystko smiga :)

https://thwack.solarwinds.com/community/solarwinds-community/geek-speak/blog/2016/06/18/the-cause-of-every-deadlock-in-sql-server

Jeśli nie boli Cię aktualność danych, a zakładam, że nie (bo masz SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;) to może raporty obskocz inaczej.

1. Zrób joba typu: WYKONAJ_RAPORTY, a w ramach tego joba:
2. Utwórz snapshot bazy
3. Wykonaj raporty na snapshocie
4. Usuń snapshot

Chyba, że masz jakieś raporty na żądanie, to wtedy trzeba kombinować inaczej.

Co do samych deadlocków, to trzeba zrozumieć jak działa model lockowania obiektów w MSSQLu, zanim zacznie się wprowadzać zmiany z internetów.

Tworzenie snapshota bazy mogę zrobić podczas generowania raportu? Nie będzie to trwało dłużej niż sam raport? Biorąc pod uwagę jeszcze, że baza firmowa, z której dane są rozliczane ma ponad 10GB...

AdamWox napisał(a):

Witam.
Potrzebuje nakierowania czy jestem w stanie ogarnąć kwestie deadlock na bazie SQL.
Pfff... równie dobrze mógłbyś napisać, że potrzebujesz nakierowania aby być najlepszym łyżwiarzem figurowym na świecie.
Tyle samo wiadomo i nie wiadomo - za dużo zmiennych.

##Od początku...
Comarch ma oprogramowanie, które nazywa się Analizy BI, straszne narzędzie, kompletnie nie zoptymalizowane. Program ten wyświetla w tabelkach cyferki zysków, kosztów, strat, grupuje je według czego sobie użytkownik życzy, ogólnie kombajn ale... Cała analiza jest robiona na bazie zapytania SQL, które, mam wrażenie, jest robione na znany sposób - "byle by działało".

Jak wszystko w super-pro-rozwiązaniach firmy z Krakowa.
No może, swoje kolejny tony groszy dokładają niedouczeniu konsultanci.
Generalnie najwięcej syfu w ERP widziałem w rozwianiach Comarchu. Nie wiem, może dlatego że jest bardzo popularny?
A może dlatego, że to co tam dzieje się pod maską, to... Szkoda gadać.

Dodatkowo to wszystko jest wczytywane przez DevExpressowego grida... również tym samym, znanym sposobem, o którym pisałem wcześniej.

To nie byłyby problem, gdyby potrafiono korzystać z tego PivotGrida (np. server-mode albo i lepiej OLAP Datasource, ale to trochę inaczej niż prosty SQL i byleby działało...), ale ostatnio jak to wdziałem, to było to napisane w sposób "byleby działało"...
Tylko że dla Twojego problemu to nie ma żadnego znaczenia.

Jeśli na bazie, którą chcemy analizować, pracuje około 20 osób to analiza rzuca błędem o deadlocku

Kopiuj

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Jest szansa, aby to działało?

Tak, jest taka szansa...
Ale przecież jesteś programistą, prawda?
To może zajmij się śledzeniem problemu, odpalasz profilera i szukasz tych deadlocków.
Zakleszczenia to jest temat na niejedną książkę i nie ma na to prostych odpowiedzi.
Jeżeli takich szukasz, to nie znajdziesz, ponieważ do tematu trzeba podejść indywidualnie.
Jednakże w sieci znajdziesz na ten temat materiałów na metry.

Czy to jest ograniczenie SQL'a, czy programu Analiz BI?

Nie.

#edit
Przykładowy raport w załączniku.

Właśnie, to jest przykład na profesjonalizm osób robiących to zestawienie.
Pierwsza linia i mamy:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Co to oznacza?
Ano mniej więcej to, co napisał @czesiek, że to pudrowanie syfu.
A więc fachowiec od tego zestawienia uznał, że po co męczyć się z blokadami, skoro można ustawić poziom izolacji transakcji na nieblokujący i nie mieć problemów?
Jak pomyślał, tak zrobił - ale chyba nie ogarnął, że to problemu blokad nie rozwiąże (jak widać), a sprawi że to zapytanie będzie czytało niepotwierdzone zmiany z innych otwartych transakcji.
Taka izolacja do zestawienia, które z definicji powinno zwracać konsystentne i powtarzalne wyniki?!
Totalny fuckup, imho...

A tak poza konkursem - kto pisał to zestawienie?
Oryginalne jest od Comarcha czy to działo konsultanta?
Ich należy pytać :)

Pewnie, zawsze można przestawić bazę danych w tryb snapshot - ale to jeszcze większy puder na syf...

PS.
SQL Server 2017 Standard, Windows Server 2012 R2, 24GB RAM, XEON E5-2420 v2 @ 2.20GHz
Postawione jako Hyper-V

To w sumie bez znaczenia.

A tak poza konkursem - kto pisał to zestawienie?
Oryginalne jest od Comarcha czy to działo konsultanta?

Oryginał od Comarch w zakładce Raporty Wzorcowe, cokolwiek by to miało znaczyć ;)

Na tę chwilę jedynym rozwiązaniem jest replikacja baz danych i ustawienie Analizy na osobną instację SQL, aby ci co pracują na Optimie mogli bez problemów pracować, a ci co robią analizy mogli sobie oglądać cyferki bez deadlocków. Już jakiś czas temu chciałem poświęcić trochę czasu i spróbować zoptymalizować te zapytania, ale aż tak dobry w SQL nie jestem...
Ogólnie od początku było widać, że jest to zrobione na odpieprz. Klienci skarżą się, że jeden raport im się generuje 30 minut, w nocy, kiedy tylko on pracuje... Tragedia. Jak się zapewne domyślasz, Comarch ma w dupie sugestie, prośby i zażalenia. Całe to "partnerstwo" też nie wiadomo na czym polega, byle by się hajs zgadzał.
Trzeba przyznać, że zamysł narzędzia do analiz jest fantastyczny, ale niestety na zamyśle się skończyło, ponieważ w kwestii realizacji polegli po całości.

Mamy jedną firmę, która jest takim przypadkiem, gdzie dużo ludzi pracuje, dużo dokumentów jest generowanych i do tego, na zasmażkę, cudowne Analizy BI, które próbują z taką ilością danych cokolwiek wyświetlić. Jestem niestety też ograniczony troszkę z czasem, ponieważ klient chce to jak najszybciej, a ja potrzebuje czasu, aby pobawić się w szerloka i znaleźć rozwiązanie.

ile jestescie w stanie zaplacic za to ze wykonam prace za Comarch? ;)

wloochacz napisał(a):

Pewnie, zawsze można przestawić bazę danych w tryb snapshot - ale to jeszcze większy puder na syf...

eeee przesadzasz, snapshot jest pełnoprawnym i dobrym rozwiązaniem umożliwiającym pozbycie się współdzielonych blokad do odczytu, w sql server on azure jest domyślnie włączony, a postgresql tylko taki tryb (MVCC) jest dostępny.

neves napisał(a):

wloochacz napisał(a):

Pewnie, zawsze można przestawić bazę danych w tryb snapshot - ale to jeszcze większy puder na syf...

eeee przesadzasz, snapshot jest pełnoprawnym i dobrym rozwiązaniem umożliwiającym pozbycie się współdzielonych blokad do odczytu, w sql server on azure jest domyślnie włączony, a postgresql tylko taki tryb (MVCC) jest dostępny.

Nie, nie przesadzam.
Przeanalizuj sobie dokładnie jak ten poziom izolacji implementuje MSSQL, a jak to jest zrobione np. w Firebird albo właśni w PostgreSQL.
Ja nie mówię, że to jest złe (zresztą sam używam w moim systemie, ale z innych powodów), ale trzeba naprawdę się zastanowić jakie to będzie miało konsekwencje.
Poza tym, tu mamy do czynienia z systemem firmy trzeciej (a nie z własnym), a oni tego nie używają.
Żeby nie było, ze zaraz jakiś as powie że to działanie niezgodne z czymś tam i utrata gwarancji itd.