Procedura w oracle - jak skutecznie zablokować dostęp do wierszy?

[PiotreK]

Cześć,

I've got an PL/SQL (Oracle) problem.
Mam do napisania procedurę, która wyszukuje dostępny rekord (np. z
polem TAKEN=0), który spełnia także pewne dodatkowe kryteria. Po kolei
sprawdzanych jest 5 różnych zestawów warunków. Jeżeli rekord spełni
warunki - wyciągany jest z niego identyfikator grupy (np.
REC_GROUP_ID). Następnie wszystkie rekordy z tym REC_GROUP_ID są
nadpisywane (UPDATE table_name SET TAKEN=1 WHERE REC_GROUP_ID =
znalezione_rec_group_id) i ładowane do kursora zwracanego aplikacji.

Po testach wydajnościowych wykonanych grinderem wyszło, że te same
grupy rekordów (z tym samym REC_GROUP_ID) są pobierane przez różne
wątki (czyli użytkowników), czemu miała przeciwdziałać flaga TAKEN.

Jak zabezpieczyć dane przed jednoczesnym pobraniem (czyli jak
sprawdzać bieżące dane aktualizowane przez równolegle uruchomioną
procedurę)? Blokada na całej tabeli odpada ze względu na wymagania
wydajnościowe.


Z góry dzięki,
PiotreK

[Lucyna Witkowska]

Post by PiotreK
I've got an PL/SQL (Oracle) problem.
Mam do napisania procedurę, która wyszukuje dostępny rekord (np. z
polem TAKEN=0), który spełnia także pewne dodatkowe kryteria. Po kolei
sprawdzanych jest 5 różnych zestawów warunków. Jeżeli rekord spełni
warunki - wyciągany jest z niego identyfikator grupy (np.
REC_GROUP_ID). Następnie wszystkie rekordy z tym REC_GROUP_ID są
nadpisywane (UPDATE table_name SET TAKEN=1 WHERE REC_GROUP_ID =
znalezione_rec_group_id) i ładowane do kursora zwracanego aplikacji.
Po testach wydajnościowych wykonanych grinderem wyszło, że te same
grupy rekordów (z tym samym REC_GROUP_ID) są pobierane przez różne
wątki (czyli użytkowników), czemu miała przeciwdziałać flaga TAKEN.

Musiałby byc Commit po Update - zeby inni widzieli już zmianę TAKEN.

Post by PiotreK
Jak zabezpieczyć dane przed jednoczesnym pobraniem (czyli jak
sprawdzać bieżące dane aktualizowane przez równolegle uruchomioną
procedurę)? Blokada na całej tabeli odpada ze względu na wymagania
wydajnościowe.

Jesli te pobierane rekordy mogą być modyfikowane w aplikacji można
wykorzystać blokady Oracle i zrobić SELECT ... FOR UPDATE
z dodatkowymi klauzulami NOWAIT lub WAIT n.
Wtedy inne transakcje żądające tych samych wierszy dostaną wyjątek
ORA-00054.

Pozdrowienia,

[PiotreK]

Post by Lucyna Witkowska
Jesli te pobierane rekordy mogą być modyfikowane w aplikacji można
wykorzystać blokady Oracle i zrobić SELECT ... FOR UPDATE
z dodatkowymi klauzulami NOWAIT lub WAIT n.
Wtedy inne transakcje żądające tych samych wierszy dostaną wyjątek
ORA-00054.

Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ile to
możliwe. Zastosowałem zatem coś takiego:


--################################################################

--user_id ustawione.
znalezione_rec_group_id := NULL;

--warunek 1
BEGIN
EXECUTE IMMEDIATE
'SELECT REC_GROUP_ID FROM table_name WHERE ROWNUM = 1 AND
TAKEN=0 AND' || some_conditions_string
INTO
znalezione_rec_group_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN chain_id := NULL;
END;

IF ( znalezione_rec_group_id IS NOT NULL ) THEN
EXECUTE IMMEDIATE
'UPDATE table_name SET TAKEN = 1, USER_ID = :1 WHERE
REC_GROUP_ID = :2'
USING
user_id, znalezione_rec_group_id;
COMMIT;
END IF;

IF ( znalezione_rec_group_id IS NULL )
--warunek 2 (analogicznie do warunek 1, tylko some_conditions_string
się zmienia)
END IF;

--analogicznie następne warunki

--finał
IF ( znalezione_rec_group_id IS NOT NULL ) THEN
ukradzionoGrupe := 1;
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID <> :
1'
USING
user_id;

IF ( ukradzionoGrupe = 0 ) THEN
--laduje kursor
OPEN moj_kursor FOR 'SELECT * FROM table_name WHERE REC_GROUP_ID
= ' || TO_CHAR(znalezione_rec_group_id);

--ponowne sprawdzenie
ukradzionoGrupe := 1;
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID
<> :1'
USING
user_id;

IF ( ukradzionoGrupe = 0 ) THEN
--zwracam kursor
RETURN;
ELSE
CLOSE moj_kursor;
--brak return każe zrobić kolejny obrót pętli, która w
rzeczywistości jest założona na całości i jest wykonywana 3 razy.
END IF;


END IF;
ELSE
-- nie znaleziono żadnego REC_GROUP_ID spełniającego którykolwiek z
warunków.
RETURN;
END IF;

--################################################################

Tylko przy testach wydajnościowych, pomimo zastosowanych warunków
zdarza się, że ta sama grupa zostanie pobrana przez dwóch różnych
użytkowników. Nie mam pomysłu czemu.



Z góry dzięki za odpowiedź,
Pozdrawiam,
PiotreK

[Sławomir Szyszło]

Post by PiotreK
Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ile to

Dlaczego nie używać blokad?
--finał

Post by PiotreK
IF ( znalezione_rec_group_id IS NOT NULL ) THEN
ukradzionoGrupe := 1;
EXECUTE IMMEDIATE
1'
USING
user_id;

A gdzie INTO?

Post by PiotreK
IF ( ukradzionoGrupe = 0 ) THEN
--laduje kursor
OPEN moj_kursor FOR 'SELECT * FROM table_name WHERE REC_GROUP_ID
= ' || TO_CHAR(znalezione_rec_group_id);
--ponowne sprawdzenie
ukradzionoGrupe := 1;
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID
<> :1'
USING
user_id;

Tu znowu brak INTO.

[PiotreK]

Post by Sławomir Szyszło

Post by PiotreK
Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ile to

Dlaczego nie używać blokad?
--finał

Post by PiotreK
IF ( znalezione_rec_group_id IS NOT NULL ) THEN
  ukradzionoGrupe := 1;
  EXECUTE IMMEDIATE
1'
  USING
     user_id;

A gdzie INTO?

Post by PiotreK
  IF ( ukradzionoGrupe = 0 ) THEN
     --laduje kursor
     OPEN moj_kursor FOR 'SELECT * FROM table_name WHERE REC_GROUP_ID
= ' || TO_CHAR(znalezione_rec_group_id);
     --ponowne sprawdzenie
     ukradzionoGrupe := 1;
     EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM table_name WHERE TAKEN=1 AND USER_ID
<> :1'
     USING
        user_id;

Tu znowu brak INTO.
--
Sławomir Szyszło
Primus inter FAQires & Grand Inquisitor no.0 of pl.comp.bazy-danych
FAQ pl.comp.bazy-danychhttp://www.dbf.pl/faq/
Archiwumhttp://groups.google.com/groups?group=pl.comp.bazy-danych

Przepraszam za brak INTO, pisałem skróconą wersję z pamięci,
oczywiście powinno tam być.
Brak blokad? Chwilowa krótkowzroczność projektanta bazy
przyzwyczajonego do wcześniej stosowanych metod i obawa przed
blokowaniem całej tabeli. Jak nie będzie się dało inaczej, to wpłynę
na człowieka ;)

Pozdrawiam,
PiotreK

[Sławomir Szyszło]

Post by PiotreK
Przepraszam za brak INTO, pisałem skróconą wersję z pamięci,
oczywiście powinno tam być.
Brak blokad? Chwilowa krótkowzroczność projektanta bazy
przyzwyczajonego do wcześniej stosowanych metod i obawa przed
blokowaniem całej tabeli. Jak nie będzie się dało inaczej, to wpłynę
na człowieka ;)

Po to są blokady na rekord. :)
Select ... for update nowait;

Albo pakiet DBMS_LOCK:
http://www.unix.com.ua/orelly/oracle/bipack/ch04_01.htm

[Marcin Wróblewski]

om>

Niestety, decyzja na razie jest taka, =BFeby nie u=BFywa=E6 blokad, o il=

e to

Dlaczego nie u=BFywa=E6 blokad?
--fina=B3

IF ( znalezione_rec_group_id IS NOT NULL ) THEN
=A0 ukradzionoGrupe :=3D 1;
=A0 EXECUTE IMMEDIATE
=A0 =A0 =A0'SELECT COUNT(*) FROM table_name WHERE TAKEN=3D1 AND USER_ID=
1'
=A0 USING
=A0 =A0 =A0user_id;

A gdzie INTO?

=A0 IF ( ukradzionoGrupe =3D 0 ) THEN
=A0 =A0 =A0--laduje kursor
=A0 =A0 =A0OPEN moj_kursor FOR 'SELECT * FROM table_name WHERE REC_GROU=

P_ID

=3D ' || TO_CHAR(znalezione_rec_group_id);
=A0 =A0 =A0--ponowne sprawdzenie
=A0 =A0 =A0ukradzionoGrupe :=3D 1;
=A0 =A0 =A0EXECUTE IMMEDIATE
=A0 =A0 =A0 =A0 'SELECT COUNT(*) FROM table_name WHERE TAKEN=3D1 AND US=

ER_ID

<> :1'
=A0 =A0 =A0USING
=A0 =A0 =A0 =A0 user_id;

Tu znowu brak INTO.
--
S=B3awomir Szysz=B3o
Primus inter FAQires & Grand Inquisitor no.0 of pl.comp.bazy-danych
FAQ pl.comp.bazy-danychhttp://www.dbf.pl/faq/
Archiwumhttp://groups.google.com/groups?group=3Dpl.comp.bazy-danych

Przepraszam za brak INTO, pisa=B3em skr=F3con=B1 wersj=EA z pami=EAci,
oczywi=B6cie powinno tam by=E6.
Brak blokad? Chwilowa kr=F3tkowzroczno=B6=E6 projektanta bazy
przyzwyczajonego do wcze=B6niej stosowanych metod i obawa przed
blokowaniem ca=B3ej tabeli. Jak nie b=EAdzie si=EA da=B3o inaczej, to wp=B3=
yn=EA
na cz=B3owieka ;)
Pozdrawiam,
PiotreK

Ale chcac uniknac blokad i tak zakladasz blokady - robiac UPDATE. UPDATE (w
ORACLE) nie blokuje calej tabeli, tylko te wiersze, ktorych dotyczy. Poczytaj
dokumentacje, przygotuj argumenty merytoryczne i pogadaj z projektantem :)

Pewnie najlepszym podejsciem bedzie to, co pisala Lucyna Witkowska (SELECT ..
FOR UPDATE NOWAIT lub WAIT n)

A przy okazji: czemu wszedzie w tej procedurze uzywasz EXECUTE IMMEDIATE
zamiast zwyklych SQLi?

[PiotreK]

Post by Marcin Wróblewski
Pewnie najlepszym podejsciem bedzie to, co pisala Lucyna Witkowska (SELECT ..
FOR UPDATE NOWAIT lub WAIT n)

Zastosowałem. Ze względu na mnogość warunków umieściłem w
podprocedurze, gdzie sprawnie wyłapuję zdefioniowany przez siebie
EXCEPTION (ORA-00054). Wszystko działa dobrze, nawet testy
wydajnościowe (które na szczęście nie wiedzą 'jak ma być' ;) Dziękuję.

Post by Marcin Wróblewski
A przy okazji: czemu wszedzie w tej procedurze uzywasz EXECUTE IMMEDIATE
zamiast zwyklych SQLi?

Nazwa tabeli jest przekazywana jako argument.

Przy okazji zauważyłem, że jest pewna sztywność w definiowaniu
zmiennej ROWTYPE. Jeżeli korzystam z kursora zdefiniowanego jako REF
CURSOR i zapełniam go w ciele procedury (podaje skonstruowany string
zapytania), to nie mogę (a przynajmniej się nie udało) po tym
zdefiniować zmiennej o odpowiednim ROWTYPE (albo wcześniej jakoś
ogólnie, a potem uścićlić, jak w przypadku kursora), a potrzebuję jej
do wykonania FETCH, bo to chyba jedyny sposób, żeby sprawdzić, czy w
kursorze coś jest... Kwestię rozwiązałem wykonując FETCH do zestawu
zmiennych, ale niesmak pozostał.

Pozdrawiam,
PiotreK

[Lucyna Witkowska]

Post by PiotreK
Przy okazji zauważyłem, że jest pewna sztywność w definiowaniu
zmiennej ROWTYPE. Jeżeli korzystam z kursora zdefiniowanego jako REF
CURSOR i zapełniam go w ciele procedury (podaje skonstruowany string
zapytania), to nie mogę (a przynajmniej się nie udało) po tym
zdefiniować zmiennej o odpowiednim ROWTYPE (albo wcześniej jakoś
ogólnie, a potem uścićlić, jak w przypadku kursora), a potrzebuję jej
do wykonania FETCH, bo to chyba jedyny sposób, żeby sprawdzić, czy w
kursorze coś jest... Kwestię rozwiązałem wykonując FETCH do zestawu
zmiennych, ale niesmak pozostał.

Typ ROWTYPE musi byc znany na etapie kompilacji.
Tzn. musi opierac sie na istniejacej tabeli lub silnym kursorze.
Wtedy PL/SQL wie dla jakiego typu zmiennych ma rezerwować miejsce.
W przypadku kursora, ktory tak naprawde generowany jest w czasie wykonania
- tego nie wiadomo. Dlatego trzeba samemu podawać typy zmiennych.

W przypadku bardziej zaawansowanego od EXECUTE IMMEDIATE pakietu dbms_sql
możliwe jest dynamiczne sprawdzanie pobieranych typów, ale i tak zmienne
dla nich muszą być wcześniej deklarowane explicite.

Pozdrowienia,

[Lucyna Witkowska]

Post by PiotreK
Niestety, decyzja na razie jest taka, żeby nie używać blokad, o ile to

To znaczy nie stosowac blokad Oracle, ale własne.

Post by PiotreK
--################################################################
--user_id ustawione.
znalezione_rec_group_id := NULL;
--warunek 1
BEGIN
EXECUTE IMMEDIATE
'SELECT REC_GROUP_ID FROM table_name WHERE ROWNUM = 1 AND
TAKEN=0 AND' || some_conditions_string
INTO
znalezione_rec_group_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN chain_id := NULL;
END;
IF ( znalezione_rec_group_id IS NOT NULL ) THEN
EXECUTE IMMEDIATE
'UPDATE table_name SET TAKEN = 1, USER_ID = :1 WHERE
REC_GROUP_ID = :2'
USING
user_id, znalezione_rec_group_id;
COMMIT;
END IF;

(...)

Post by PiotreK
-- nie znaleziono żadnego REC_GROUP_ID spełniającego którykolwiek z
warunków.
RETURN;
END IF;
--################################################################
Tylko przy testach wydajnościowych, pomimo zastosowanych warunków
zdarza się, że ta sama grupa zostanie pobrana przez dwóch różnych
użytkowników. Nie mam pomysłu czemu.

Bo to rozwiązanie zakłada, ze żadne dwie sesje nie zrobią pierwszego
SELECT równocześnie. A testy wydajnościowe o tym nie wiedzą :-)

Przyjmijmy, ze dwoch uzytkownikow zaczyna prace z aplikacją. Obaj dostaną
tą samą grupę w pierwszym SELECT, co więcej obaj będą mogli zmienić TAKEN
w UPDATE na 1 (bo nie ma w nim juz warunku z SELECT). Dopiero użytkownik
pracujący po nich nie dostanie tej samej grupy.

Pozdrowienia,