Locki i deadlocki w InnoDB

Witajcie. Dawno już nic nie pisałam, a wracając do tego chciałabym poruszyć temat locków bazodanowych w szczególności w MySQLu (czyli w enginie InnoDB).

Locki są "podtrzymaniem" zasobów, aby zachować integralność danych. Są używane (powstają), gdy wielu (przynajmniej dwóch) użytkowników (sesje) potrzebuje dostępu do danych jednocześnie. Czyli są one zabezpieczeniem przed uszkodzeniem lub zwróceniem niepoprawnych danych.

W MySQL-u tylko enginy InnoDB i NDB (Cluster) wspierają transakcje. Warto jednak zaznaczyć, że dla takich enginów jak MyISAM, Merge czy Memory przy każdej zmianie w tabeli, następuje jej zalockowanie (locki na poziomie całej tabeli). InnoDB wspiera lokowanie na poziomie wierszy, czyli implementuje zbiór wartości ACID:

• Atomicity - Każda transakcje wykonywana jest w całości albo w ogóle (ustawienia autocomitu, polecenia COMMIT i ROLLBACK),
• Consistency - Po wykonaniu transakcji system będzie spójny (wewnętrzne procesy InnoDB chroniące przed crash-recovery: InnoDB doublewrite buffor, InnoDB crash recovery),
• Isolation - Jeśli jakieś dwie transakcje wykonują zadania współbieżnie, to w zależności od poziomu izolacji, nie widzą zmian przez siebie wprowadzonych (ustawienia autocommitu, SET ISOLATION LEVEL),
• Durability - System potrafi zwrócić poprawne i spójne dane po każdej zatwierdzonej transakcji (realizowane przez InnoDB doublewrite buffor, konfiguracja
  innodb_flush_log_at_trx_commit, konfiguracja sync_binlog, konfiguracja innodb_file_per_table, wsparcie dla fsync() w systemie operacyjnym ...).

O transakcjach i poziomach izolacji w MySQLu i PostgreSQLu pisałam już wcześniej, a do posta możecie wrócić tutaj.

InnoDB implementuje standardowy row-level locking z dwoma typami locków:

• Shared (S) = Read
• Przykład:  SELECT ... LOCK IN READ MODE
• nie pozwala na założenie locka do pisania
• pozwala innym użytkownikom na ten sam read lock
• Exclusive (X) = Read/Write
• Przykład: SELECT ... FOR UPDATE
• nie pozwala na jakiekolwiek inne locki
• pozwala na update lub delete

Przyjrzyjmy się z bliska samej implementacji locków w MySQLu. InnoDB rozróżnia następujące typy locków:

• record lock - lock na indexie rekordu (Jeśli tabela nie ma żadnego indexu, InnoDB tworzy ukryty index (tzw. clustered index) i używa go do zalockowania rekordu).
  Przykład zapytania:
  
  SELECT * FROM test WHERE id = 25 FOR UPDATE;
  
  Zapobiega wywołaniu zamian (update, delete, insert), gdzie test.id jest równe 25.

• gap lock - lock na przerwie między indexem rekordów lub lockiem na przerwie przed pierwszym albo ostatnim indexem recordu. Mnie także, trudno było zrozumieć, o co chodzi, więc lepiej popatrzmy na jakiś przykład:
  
  SELECT * FROM test WHERE id BETWEEN 10 AND 20 FOR UPDATE;
  
  Zapytanie zapobiega wykonywania zapytań, które będą chciały wykonać zmiany na tym zakresie wartości id, a także nie pozwoli wstawić nowych wartości np. o id = 15.
  Jeśli na kolumnie id istniałby unikalny index, gap lock by nie powstał tylko rekord-lock zostałby wykorzystany do naszego zapytania.
  Tego typu locki mogą być łatwo wyłączone poprzez zmianę poziomu izolacji na READ COMMITED (przypominam, że dla MySQLa domyślnym poziomem izolacji jest REPEATABLE-READ) lub włączenie zminnej serwerowej innodb_locks_unsafe_for_binlog.
• next-key lock - jest kombinacją dwóch powyższych loków, czyli record lock i gap locku (next-key lock jest to record lock z uwzględnieniem szczeliny przed indexem rekordu). Przykład:
  
  SELECT * FROM test WHERE id > 20 FOR UPDATE;
  Na tabeli test i kolumnie id isnieje index. Zapytanie nakłada record-lock na wiersze spełniające warunek, a także nie pozwala na inserty w 'przerwach', takich jak id równe 23,27,30 i wyżej.

Wiemy, czym są same locki i jak mogą powstawać, ale dla developera bazodanowego największym zagrożeniem są deadlocki.

Co to jest deadlock?

Sytuacja gdzie dwie różne transakcje nie mogą procesować, bo każdy z nich trzyma lock, który inny potrzebuje. Ponieważ, każdy z procesów czeka na zasoby, aż będą dostępne, żadne nie zwolni locku, które trzyma.

Przykład: Proces A lockuje wiersze w tabeli o id 1,2,3,4,5, a proces B lockuje wiersze w tej samej tabeli tylko w innej kolejności 5,4,3,2,1. Proces A otrzymał już część zasobów (np. wiersze o id 1,2 i 3) i czeka na zwolnienie zasobów przez proces B (wiersze o id 5 i 4), a proces B czeka na zwolnienie zasobów, które zajął proces A. I tak mogą sobie czekać ...

Jak bazy danych radzą sobie z taką sytuacją?

W bazach danych istnieje mechanizm automatycznej detekcji deadlocków. Polega on na wykryciu deadlocku i rollbacku jednej z transakcji (victim z ang. ofiara), która bierze w deadlocku udział. InnoDB wybiera najmniejszą transakcję, gdzie rozmiar transakcji jest ustalany przez liczbę wierszy zmienionych przez operację INSERT, UPDATE lub DELETE.

Innym sposobem jest ustawienie zmiennej innodb_lock_wait_timeout (ile sekund ma czekać, aby cofnąć transakcje). Jest ona wykorzystywana, gdy lock nastapił przez polecenie LOCK TABLE lub gdy przy lockowaniu bierze udział engin inny niż InnoDB.

Jak radzić sobie z deadlockami?

Najlepszą sytuacją będzie tworzenie małych transakcji, częste commitowanie ich, aby zapobiegać możliwości wystąpienia konfliktów albo lockowania jak najmniejszej ilości wierszy/tabel. O innych sposobach poczytajcie w tym miejscu: tutaj.

Miłej nauki :-P