PostgreSQL, a deadlock

Witam,
Napisałem procedurę składowaną w PL/pgSQL wywoływaną z poziomu JDBC, która dokonuje dwóch insertów i jeden update.

1. Kazdy watek ma wlasne polaczenie JDBC.
2. Polecenie jest wykonywane przez SELECT nazwa_procedury_skladowanej_z_parametrami(?);.

Procedura jest wywoływana przez 50 działających jednocześnie wątków, na tym samym rekordzie tabeli (update). Jest to test na deadlocki i błedy trasakcji, sytuaja malo prawdpodobna w praktyce, stworzona na potrzeby testow. Są dwa możliwe scenariusze:
a) ponieważ procedura składowana jest transakcyjna operacja daje spodziewany wynik
b) operacja konczy sie zakleszczeniem operacji: rzadko ale zawsze

CREATE OR REPLACE FUNCTION recv_product_from_user(bigint, bigint, integer, timestamp without time zone)
  RETURNS void AS
$BODY$
    DECLARE
    BEGIN
		IF $3 <= 0 THEN
			RAISE EXCEPTION 'number of products must be greater than zero';
		END IF;
	
		-- copy state of the product to the history table: product_datetime_state
		INSERT INTO product_datetime_state (id_product, num_available, prd_datetime) 
			SELECT p.id_product, p.num_available, (SELECT $4 ) FROM product p WHERE p.id_product = $2;
		-- add new delivery to product_recv
		INSERT INTO product_recv (id_product, id_user, num_recv, datetime) VALUES ($2, $1, $3, $4); 
		-- update product state (number of current items available)
		UPDATE product p SET num_available = num_available + $3 WHERE p.id_product = $2;
    END;
$BODY$

W jaki sposob zabezpieczyc sie przed deadlockiem powstajacym podczas wykonywania procedury UPDATE?

Pozdrawiam,

Padlem jak przeczytalem co napisalem:
"rzadko ale zawsze"
Mialem na mysli oczywiscie, ze rzadko podczas np. iteracji 1000 wywolan procedury (w petli for) dla kazdego z watkow. Jednak system powinien byc na to odporny.

Jak sie przed tym zabezpieczac.

ale co byś chciał zrobić - dwie różne transakcje próbują zmienić ten sam rekord - któraś się musi "wstrzymać" aż ta druga go puści. Rozwiązaniem może być zrobienie przed update select * from product WHERE p.id_product = $2 for update i zrobić commit zaraz po update. Spowoduje to, że druga transakcja będzie czekała aż się pierwsza skończy. Może to jednak zaowocować całą kolejką czekających transakcji

OK już zrozumiałem, dzięki.

FOR UPDATE to dokładnie ten lock jak był mi potrzebny

abrakadaber napisał(a):

ale co byś chciał zrobić - dwie różne transakcje próbują zmienić ten sam rekord - któraś się musi "wstrzymać" aż ta druga go puści. Rozwiązaniem może być zrobienie przed update select * from product WHERE p.id_product = $2 for update i zrobić commit zaraz po update. Spowoduje to, że druga transakcja będzie czekała aż się pierwsza skończy. Może to jednak zaowocować całą kolejką czekających transakcji

Nie znam Postgresql ale wydaje mi się dziwne, że update jednego rekordu przez kilka wątków powoduje deadlocka. Sądziłem, że Deadlock może powstać gdy różne transakcje blokują się wzajemnie (transakcja A czeka na zwolnienie blokady przez transakcję B a transakcja B czeka na zwolnienie blokady przez A). W tym przypadku taka sytuacja nie występuje.

michalp007

jak to nie występuje??? Przecież jeśli nie zaczniesz transakcji jawnie to i tak jest ona niejawna - w bazie (normalnej :)) nic (mówię o insert/update/delete) nie dzieje się poza transakcją