Jednoczesne wywołania tej samej funkcji: w jaki sposób występują zakleszczenia?

Moja funkcja new_customerjest wywoływana kilka razy na sekundę (ale tylko raz na sesję) przez aplikację internetową. Pierwszą rzeczą, jaką robi, jest zablokowanie customertabeli (wykonanie „wstaw, jeśli nie istnieje” - prosty wariant upsert).

Rozumiem te dokumenty, że inne wywołania new_customerpowinny po prostu stać w kolejce, aż wszystkie poprzednie połączenia zakończą się:

LOCK TABLE uzyskuje blokadę na poziomie tabeli, czekając w razie potrzeby na zwolnienie wszelkich sprzecznych blokad.

Dlaczego zamiast tego czasami się zakleszcza?

definicja:

create function new_customer(secret bytea) returns integer language sql 
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex') 
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

błąd z dziennika:

2015-07-28 08:02:58 SZCZEGÓŁY BST: Proces 12380 czeka na ExclusiveLock w relacji 16438 bazy danych 12141; zablokowane przez proces 12379.
        Proces 12379 czeka na ExclusiveLock na relacji 16438 bazy danych 12141; zablokowane przez proces 12380.
        Przetwarzaj 12380: wybierz nowego klienta (dekoduj (1 $ :: tekst, „hex”))
        Proces 12379: wybierz nowego klienta (dekoduj (1 $ :: tekst, „hex”))
2015-07-28 08:02:58 BST WSKAZÓWKA: szczegółowe informacje dotyczące zapytań znajdują się w dzienniku serwera.
2015-07-28 08:02:58 KONTEKST BST: Funkcja SQL „new_customer” instrukcja 1
2015-07-28 08:02:58 OŚWIADCZENIE BST: wybierz nowego klienta (dekoduj (1 $ :: tekst, „hex”))

relacja:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

edytować:

Udało mi się uzyskać prosty, powtarzalny przypadek testowy. Dla mnie wygląda to na błąd z powodu warunków wyścigu.

schemat:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

skrypt bash działa jednocześnie w dwóch sesjach bash:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

dziennik błędów (zwykle garść zakleszczeń na 1000 połączeń):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edycja 2:

@ypercube zasugerował wariant z lock tablefunkcją zewnętrzną:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

co ciekawe, eliminuje to impasy.

Wysłałem to do pgsql-bugs, a odpowiedź tamta Tom Lane wskazuje, że jest to problem eskalacji blokady, ukryty pod mechaniką sposobu przetwarzania funkcji języka SQL. Zasadniczo blokada wygenerowana przez insertjest uzyskiwana przed wyłączną blokadą na stole :

Uważam, że problem polega na tym, że funkcja SQL wykona parsowanie (a może również planuje; nie chcę teraz sprawdzać kodu) dla całego ciała funkcji jednocześnie. Oznacza to, że dzięki komendzie INSERT użytkownik nabywa RowExclusiveLock w tabeli „test” podczas analizowania treści funkcji, zanim komenda LOCK faktycznie się uruchomi. LOCK reprezentuje więc próbę eskalacji blokady i należy się spodziewać zakleszczenia.

Ta technika kodowania byłaby bezpieczna w plpgsql, ale nie w funkcji języka SQL.

Dyskutowano o ponownym wdrożeniu funkcji języka SQL, aby parsowanie odbywało się pojedynczo, ale nie wstrzymuj oddechu na coś, co dzieje się w tym kierunku; to nie wydaje się być priorytetem dla nikogo.

pozdrowienia, Tom Lane

To wyjaśnia również, dlaczego zablokowanie tabeli poza funkcją w zawijanym bloku plpgsql (jak sugeruje @ypercube) zapobiega zakleszczeniom.

Zakładając, że uruchamiasz inne instrukcje przed wywołaniem new_customer, a te uzyskują blokadę, która jest w konflikcie EXCLUSIVE(zasadniczo każda modyfikacja danych w tabeli klientów), wyjaśnienie jest bardzo proste.

Problem można odtworzyć na prostym przykładzie (nawet bez funkcji):

CREATE TABLE test(id INTEGER);

Pierwsza sesja:

BEGIN;

INSERT INTO test VALUES(1);

2. sesja

BEGIN;
INSERT INTO test VALUES(1);
LOCK TABLE test IN EXCLUSIVE MODE;

1. sesja

LOCK TABLE test IN EXCLUSIVE MODE;

Kiedy pierwsza sesja wykonuje wstawkę, uzyskuje ROW EXCLUSIVEblokadę na stole. Tymczasem sesja 2 próbuje również uzyskać ROW EXCLUSIVEblokadę i próbuje uzyskać EXCLUSIVEblokadę. W tym momencie musi czekać na pierwszą sesję, ponieważ EXCLUSIVEblokada powoduje konflikt ROW EXCLUSIVE. W końcu pierwsza sesja skacze rekinom i próbuje zdobyć EXCLUSIVEzamek, ale ponieważ zamki są zdobywane w kolejności, kolejkuje po drugiej sesji. To z kolei czeka na 1., powodując impas:

DETAIL:  Process 28514 waits for ExclusiveLock on relation 58331454 of database 44697822; blocked by process 28084.
Process 28084 waits for ExclusiveLock on relation 58331454 of database 44697822; blocked by process 28514

Rozwiązaniem tego problemu jest uzyskanie blokad tak wcześnie, jak to możliwe, zwykle jako pierwsza rzecz w transakcji. Z drugiej strony obciążenie PostgreSQL wymaga blokad tylko w niektórych bardzo rzadkich przypadkach, dlatego sugeruję przemyślenie sposobu, w jaki robisz upsert (spójrz na ten artykuł http://www.depesz.com/2012/06/10 / dlaczego-jest-upsert-tak skomplikowane / ).