Śledzenie, debugowanie i naprawianie treści Row Lock

Późno spotykałem się z wieloma kłótniami dotyczącymi blokowania wierszy. Tabela rywalizacji wydaje się być konkretną tabelą.

Tak się zwykle dzieje -

• Deweloper 1 rozpoczyna transakcję z ekranu interfejsu Oracle Forms
• Deweloper 2 rozpoczyna kolejną transakcję z innej sesji przy użyciu tego samego ekranu

Po około 5 minutach interfejs wydaje się nie reagować. Sprawdzanie sesji pokazuje rywalizację o blokadę wiersza. „Rozwiązaniem”, które wszyscy rzucają, jest zabijanie sesji: /

Jako programista baz danych

• Co można zrobić, aby wyeliminować kontrowersje związane z blokadą wierszy?
• Czy byłoby możliwe dowiedzieć się, który wiersz procedury przechowywanej powoduje te blokowania wierszy
• Jaka byłaby ogólna wytyczna w celu ograniczenia / uniknięcia / wyeliminowania problemów związanych z kodowaniem?

Jeśli to pytanie wydaje się zbyt otwarte / niewystarczające, prosimy o edycję / powiadomienie - zrobię co w mojej mocy, aby dodać dodatkowe informacje.

Tabela, o której mowa, zawiera wiele wstawek i aktualizacji. Powiedziałbym, że jest to jedna z najbardziej obciążonych tabel. SP jest dość skomplikowany - dla uproszczenia - pobiera dane z różnych tabel, zapełnia je tabelami roboczymi, wiele operacji arytmetycznych występuje na stole roboczym, a wynik tabeli roboczej jest wstawiany / aktualizowany do danej tabeli.

Wersja bazy danych to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64-bit. Przepływ logiki jest wykonywany w tej samej kolejności w obu sesjach, transakcja nie jest otwarta zbyt długo (a przynajmniej tak mi się wydaje ), a blokady pojawiają się podczas aktywnego wykonywania transakcji.

Aktualizacja: Liczba wierszy tabeli jest większa niż się spodziewałem, około 3,1 miliona wierszy. Ponadto po śledzeniu sesji stwierdziłem, że kilka instrukcji aktualizacji do tej tabeli nie korzysta z indeksu. Dlaczego tak jest - nie jestem pewien. Kolumna, do której odwołuje się klauzula where, jest indeksowana. Obecnie odbudowuję indeks.

Czy można by dowiedzieć się, który wiersz procedury przechowywanej powoduje te blokowania wierszy?

Nie do końca, ale można uzyskać instrukcję SQL powodującą blokadę, a następnie zidentyfikować powiązane linie w procedurze.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

Jakie byłyby ogólne wytyczne dotyczące ograniczenia / uniknięcia / wyeliminowania takich problemów z kodowaniem?

Sekcja „ Oracle Concepts Guide” dotycząca blokad mówi: „Wiersz jest zablokowany tylko wtedy, gdy zostanie zmodyfikowany przez pisarza”. Kolejna sesja aktualizująca ten sam wiersz będzie następnie czekać na pierwszą sesję COMMITlub ROLLBACKprzed kontynuacją. Aby wyeliminować problem, możesz serializować użytkowników, ale oto kilka rzeczy, które mogą zredukować problem do tego stopnia, że ​​nie jest problemem.

• COMMITczęściej. Każde COMMITwydanie blokuje, więc jeśli możesz wykonać aktualizacje partiami, prawdopodobieństwo kolejnej sesji wymagającej tego samego wiersza jest zmniejszone.
• Upewnij się, że nie aktualizujesz żadnych wierszy bez zmiany ich wartości. Na przykład UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1);powinien zostać przepisany jako bardziej selektywny (czytaj mniej blokad) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Oczywiście, jeśli zmiana instrukcji będzie nadal blokować większość wierszy w tabeli, wówczas zmiana przyniesie jedynie korzyści w zakresie czytelności.
• Upewnij się, że używasz sekwencji zamiast blokowania tabeli, aby dodać jedną do najwyższej bieżącej wartości.
• Upewnij się, że nie używasz funkcji, która powoduje, że indeks nie będzie używany. Jeśli funkcja jest konieczna, rozważ utworzenie indeksu opartego na funkcji.
• Myśl w zestawach. Zastanów się, czy pętla uruchamiająca blok wykonujący aktualizacje PL / SQL może zostać przepisana jako pojedyncza instrukcja aktualizacji. Jeśli nie, być może można byłoby zastosować przetwarzanie zbiorcze BULK COLLECT ... FORALL.
• Zmniejsz liczbę prac wykonywanych między pierwszym UPDATEa drugim COMMIT. Na przykład, jeśli kod wysyła wiadomość e-mail po każdej aktualizacji, rozważ umieszczenie wiadomości w kolejce i wysłanie ich po zatwierdzeniu aktualizacji.
• Zaprojektuj aplikację do obsługi oczekiwania, wykonując a SELECT ... FOR UPDATE NOWAITlub WAIT 2. Następnie możesz złapać niemożność zablokowania wiersza i poinformować użytkownika, że ​​inna sesja modyfikuje te same dane.

Odpowiem z punktu widzenia dewelopera.

Moim zdaniem, gdy napotkasz sprzeczkę między wierszami, taką jak ta, którą opisujesz, dzieje się tak dlatego, że masz błąd w aplikacji. W większości przypadków ten typ niezgodności jest oznaką podatności na utratę aktualizacji. Ten wątek na AskTom wyjaśnia koncepcję utraconej aktualizacji:

Utrata aktualizacji następuje, gdy:

sesja 1: odczytanie rekordu pracownika Toma

sesja 2: odczytanie rekordu pracownika Toma

sesja 1: zaktualizuj dane pracownika Toma

sesja 2: zaktualizuj dane pracownika Toma

Sesja 2 NAPISAĆ zmiany sesji 1, nigdy ich nie widząc, co spowoduje utratę aktualizacji.

Wystąpił jeden nieprzyjemny efekt uboczny utraconej aktualizacji: sesję 2 można zablokować, ponieważ sesja 1 jeszcze się nie rozpoczęła. Głównym problemem jest jednak to, że sesja 2 ślepo aktualizuje rekord. Załóżmy, że obie sesje wydają oświadczenie:

UPDATE table SET col1=:col1, ..., coln=:coln WHERE id = :pk

Po obu instrukcjach modyfikacje sesji 1 zostały nadpisane, a sesja2 nie została powiadomiona, że ​​wiersz został zmodyfikowany przez sesję 1.

Utracona aktualizacja (i efekt uboczny rywalizacji) nigdy nie powinna się zdarzyć, można ich w 100% uniknąć. Powinieneś użyć blokady, aby zapobiec jej za pomocą dwóch głównych metod: blokowania optymistycznego i pesymistycznego .

1) Blokowanie pesymistyczne

Chcesz zaktualizować wiersz. W tym trybie uniemożliwisz innym modyfikowanie tego wiersza, żądając blokady tego wiersza ( SELECT ... FOR UPDATE NOWAITinstrukcji). Jeśli wiersz jest już modyfikowany, pojawi się komunikat o błędzie, który możesz z wdziękiem przetłumaczyć dla użytkownika końcowego (ten wiersz jest modyfikowany przez innego użytkownika). Jeśli wiersz jest dostępny, dokonaj modyfikacji (AKTUALIZACJA), a następnie zatwierdź, gdy transakcja zostanie zakończona.

2) Optymistyczne blokowanie

Chcesz zaktualizować wiersz. Nie chcesz jednak utrzymywać blokady w tym wierszu, być może dlatego, że używasz kilku transakcji, aby zaktualizować wiersz (bezstanowa aplikacja internetowa), a może nie chcesz, aby użytkownik trzymał blokadę zbyt długo ( co może spowodować zablokowanie innych osób). W takim przypadku nie poprosisz od razu o blokadę. Użyjesz znacznika, aby upewnić się, że wiersz nie zmienił się, kiedy twoja aktualizacja zostanie wydana. Możesz buforować wartość wszystkich kolumn lub użyć kolumny znacznika czasu, która jest aktualizowana automatycznie, lub kolumny opartej na sekwencji. Niezależnie od wyboru, kiedy zamierzasz przeprowadzić aktualizację, upewnij się, że znacznik w tym wierszu nie zmienił się, wysyłając zapytanie takie jak:

SELECT <...>
  FROM table
 WHERE id = :id
   AND marker = :marker
   FOR UPDATE NOWAIT

Jeśli zapytanie zwraca wiersz, dokonaj aktualizacji. Jeśli nie, oznacza to, że ktoś zmodyfikował wiersz od ostatniego zapytania. Będziesz musiał ponownie uruchomić proces od początku.

Uwaga: Jeśli masz pełne zaufanie do wszystkich aplikacji uzyskujących dostęp do Twojej bazy danych, możesz polegać na bezpośredniej aktualizacji optymistycznego blokowania. Możesz wydać bezpośrednio:

UPDATE table
   SET <...>, 
       marker = marker + 1
 WHERE id = :id;

Jeśli instrukcja nie aktualizuje żadnego wiersza, wiesz, że ktoś zmienił ten wiersz i musisz zacząć wszystko od nowa.

Jeśli wszystkie aplikacje zgadzają się na ten schemat, nikt nigdy nie zostanie zablokowany i unikniesz ślepej aktualizacji. Jeśli jednak nie zablokujesz wcześniej wiersza, nadal będziesz podatny na blokowanie na czas nieokreślony, jeśli inna aplikacja, zadanie wsadowe lub bezpośrednia aktualizacja nie zaimplementują blokowania optymistycznego. Dlatego radzę zawsze blokować wiersz, niezależnie od wybranego schematu blokowania (działanie może być nieistotne, ponieważ podczas blokowania wiersza pobierane są wszystkie wartości, w tym rowid).

TL; DR

• Aktualizacja wiersza bez uprzedniej blokady powoduje narażenie aplikacji na potencjalne „zamrożenie”. Można tego uniknąć, jeśli wszystkie DML do DB implementują blokowanie optymistyczne lub pesymistyczne.
• Sprawdź, czy instrukcja SELECT zwraca wartości zgodne z poprzednim SELECT (aby uniknąć problemów z zagubioną aktualizacją)

Ta odpowiedź prawdopodobnie kwalifikowałaby się do wpisu w The Daily WTF.

Właśnie, po prześledzeniu sesji i przeszukaniu USER_SOURCE- wyśledziłem pierwotną przyczynę

• Nic dziwnego, że przyczyną była wadliwa logika
• Ostatnio do SP dodano aktualizację. Instrukcja aktualizacji zasadniczo zaktualizuje całą tabelę. Najwyraźniej programista, o którym mowa, zapomniał o dodaniu prawa do klauzul aktualizujących wymagane instrukcje.
• Tabela, która była aktualizowana, była jak wspomniano powyżej, jedna z najbardziej transakcyjnych tabel i miała dużą liczbę rekordów. Aktualizacja zajęłaby dużo czasu.
• W rezultacie inne sesje nie były w stanie uzyskać blokady na stole i siedziały w kłótniach z blokadą wierszy.