[es] - select for update

djoka_l
Beograd

Član broj: 56075
Poruke: 3497

Jabber: djoka_l

+1482 Profil

^{27.10.2008. u 14:43 - pre 193 meseci}

Postoje 2 tipa locka: shared i exclusive. Poenta je da ako je neki proces dobio exclusive lock nad redom tabele, tada drugi proces ne može da dobije exclusive lock. Exclusive lock dobijaju UPDATE (kao i select for update), DELETE i INSERT komande, dok shared lock dobija SELECT.
Dalje, Select naredba može da dobije shared lock nad redom tabele koji ima exclusive lock.

Poenta je, svako ko hoće može da čita iz tabele, a samo jedan proces može da menja red koji je zaključao. Upravo SELECT FOR UPDATE služi da se promeni default ponašanje SELECT naredbe, tako da redove zaključava u exclusive režimu, pa kasnije može da se sa sigurnošću promeni vrednost u redu (UPDATE ... WHERE CURRENT OF ...)

Odgovor na temu

ndbosna
Nadir Zlatar

Član broj: 115931
Poruke: 19
*.bstelecom.ba.

Profil

Re: select for update

^{28.10.2008. u 07:49 - pre 193 meseci}

Hvala na odgovoru, sada mi je dosta jasnije.

Trenutno imam problem sa koristenjem SELECT FOR UPDATE naredbe sa REF cursorom. Da li je uopste moguce ovo uraditi sa REF cursorom. Uradim OPEN cursor FOR SELECT FOR UPDATE, ali kasnije imam problema sa updateom tabele iz kursora, javlja gresku kao da ne vidi taj cursor.

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3497

Jabber: djoka_l

+1482 Profil

Re: select for update

^{28.10.2008. u 09:06 - pre 193 meseci}

Code:

drop table test;
create table test ( t number);
insert into test (t) values (1);
insert into test (t) values (2);
insert into test (t) values (3);

create or replace package test_pkg as
  procedure testiraj;
end test_pkg;
/

create or replace package body test_pkg as

  type test_cur is ref cursor return test%rowtype;

  procedure test_upd( c in SYS_REFCURSOR) is

  begin
    update test set t=t+1 where t>1;
  end;

  procedure testiraj is

    c test_cur;
  begin
    open c for select t from test where t>1 for update of t;
    test_upd(c);
    close c;
  end;
end test_pkg;
/
begin test_pkg.testiraj; end;
/
select t from test;
drop table test;
drop package test_pkg;

Evo primera gde se vidi da u okviru iste transakcije može da se otvori kurzor sa FOR UPDATE klauzulom, preda funkciji, koja ažurira LOCKovane slogove.

Koju grešku prijavljuje SQL? Verovatno si uptrebio KURZORSKU VARIJABLU u kontekstu u kojem da se upotrebi samo KURZOR.

Na primer možeš da napišeš konstrukciju

for i in (select * from dual) loop

ali ne možeš da napišeš

for i in CURR_VARR loop

gde je CURR_VARR deklarisana kao ref cursor.

Evo svih restrikcija (preneto iz PL/SQL User's Guide and Reference

Restrictions on Cursor Variables

Currently, cursor variables are subject to the following restrictions:

*

You cannot declare cursor variables in a package specification, as illustrated in Example 6-34.
*

If you bind a host cursor variable into PL/SQL from an OCI client, you cannot fetch from it on the server side unless you also open it there on the same server call.
*

You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
*

Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.
*

You cannot store cursor variables in an associative array, nested table, or varray.
*

Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop.

Odgovor na temu

ndbosna
Nadir Zlatar

Član broj: 115931
Poruke: 19
*.bstelecom.ba.

Profil

Re: select for update

^{28.10.2008. u 12:05 - pre 193 meseci}

Uspio sam kompajlirati proceduru ali nisam uspio rijesiti i problem. Naime stvar je u tome da kada uradimm COMMIT transakcije taj REF cursor u koji sam uradio SELECT FOR UPDATE ostane bez podataka u njemu. REF kursor i imam iz razloga jer te redove moram proslijediti aplikaciji. Cijeli problem koji imam je sljedeci. Imam dvije aplikacije koje kupe podatke iz iste tabele, pokupe 100 slogova i obradjuju ih. To rade tako sto pokupe slogove i azuriraju kolunu sa vremenom kada su uzele podatke tako sto je uvecaju za 3 minute. U uslovu za SELECT ima uslov kojim provjerava da li se ti podaci smiju pokupiti (tj. da li je vec druga transakcija povecala vrijeme za TIMENOW+3 minute). Medjutim, problem je sto se prvo izvrsi taj select pa tek onda UPDATE tog vremena. Moguce je da druga aplikacija pokupi iste slogove prije nego sto je prva (pozivom procedure na bazi) uspjela azurirati ta vremena (i uraditi COMMIT).

Mislio sam da je SELECT FOR UPDATE rjesenje, jer bi njega radio na pocetku transakcije i isti slogovi se ne bi mogli ponovo pokupiti sa SELECT FOR UPDATE do kraja transakcije. Medjutim problem mi je sto nakon COMMITA izgubim podake u kursoru i samim tim ne mogu da ih dohvatim u aplikaciji.

Jedino rjesenje po meni je (nisam nikakav expert za Oracle) da uradim SELECT INTO FOR UPDATE u neku strukturu (to moze jer sam probao), a zatim da nekako iz nje te podatke prebacim u REF kursor.

Code:

TYPE Prep_Tab IS TABLE OF Prepared_SMS_Msgs%ROWTYPE INDEX BY PLS_INTEGER;
Temp_Table Prep_Tab;

SELECT INTO BULK COLLECTION Temp_Table
FROM Prepared_SMS_Msgs
WHERE (MS  <> 0 OR  OpID  <> 1)
        AND  ID  > BEGIN_ID
     AND  ROUND(( (SYSDATE)  -  NVL(LAST_TRY_TIME, (SYSDATE)) ) *24*60*60)  >= 0--*
     AND ROWNUM  <= NO_OF_RECS AND REFNUM IN (select REFNUM FROM (SELECT REFNUM, TOTALNUM, COUNT(REFNUM) FROM PREPARED_SMS_MSGS
   GROUP BY REFNUM, TOTALNUM
   HAVING COUNT(REFNUM) = TOTALNUM)))
FOR UPDATE NOWAIT);

Interesuje me kako je moguce prebaciti sada podatke iz ovog Temp_Table u REF cursor.

Mozda ima i neko bolje rjesenje od SELECT FOR UPDATE, ali ga ja ne znam.

Hvala

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3497

Jabber: djoka_l

+1482 Profil

Re: select for update

^{28.10.2008. u 16:12 - pre 193 meseci}

Ovde mi nije najjasnije šta se gde radi - šta u aplikaciji, a šta u proceduri koja radi SELECT.

Ovo što si opisao je normalno ponašanje SELECT FOR UPDATE komande. Posle COMMIT ili ROLLBACK lockovi se puštaju, a kurzor postaje nedefinisan. Možda aplikacija nije dobro osmišljena ili je negde na drugom mestu problem.

Treba pogledati dokumentaciju, možda je ovde potrebno koristiti PIPE i PIPELINED funkcije.

Odgovor na temu

ndbosna
Nadir Zlatar

Član broj: 115931
Poruke: 19
*.bstelecom.ba.

Profil

Re: select for update

^{30.10.2008. u 11:54 - pre 193 meseci}

Pozdrav,

nakon mnogobrojnih pokusaja sa SELECT FOR UPDATE na kraju sam i odusta od istog i odlucio da na pocetku procedure odmah uradim UPDATE cime bih trebao onemoguciti pristup slogu naredne 3 minute. Nakon UPDATE-a se odmah uradi i COMMIT tako da ostale sesije vide ovu promjenu. Kod izgleda ovako:

Code:

CREATE OR REPLACE procedure PROCEDURA1(
  NO_OF_RECS IN  INTEGER  DEFAULT 100,
BEGIN_ID IN  NUMBER  DEFAULT 0,
MINUTES_1 IN  SMALLINT DEFAULT 2,
MINUTES_2 IN  SMALLINT DEFAULT 15,
APPID in number default -1,
RCT1  IN OUT GLOBALPKG.RCT1
)

AS
  dateTimeForThisTry DATE;
  statusFlagForTry CHAR(1);
  minutesToActivate1 INT;
  minutesToActivate2 INT;
  noOfAttempts1  INT;
  noOfAttempts2  INT;
  loopControl   INT;
  myRowCount   INT;
  Error    INT;
ReccCount   INT;
  strOA VARCHAR2(1024);
  strDA VARCHAR2(1024);
  timelasttry DATE;
  timesystime DATE;
  tmpDate date;

BEGIN

   ReccCount := 0;
  dateTimeForThisTry := (SYSDATE);
  statusFlagForTry := 'S';
  minutesToActivate1 := MINUTES_1;
  minutesToActivate2 := MINUTES_2;
  noOfAttempts1  := 3;
  noOfAttempts2  := 6;
  loopControl   := 0;
    myRowCount   := 0;
    Error := 0;
  GLOBALPKG.TRANCOUNT := GLOBALPKG.TRANCOUNT + 1;
  timesystime := (SYSDATE);
tmpDate := dateTimeForThisTry + TRUNC(minutesToActivate1) /24/60;

begin

update tabela1
set last_try_time = tmpDate, app_id = appid
where ID in(
   SELECT
   ID
   FROM Tabela1
   WHERE (MS  <> 0 OR  OpID  <> 1)
        AND  ID  > BEGIN_ID
        AND  ROUND(( (SYSDATE)  -  NVL(LAST_TRY_TIME, (SYSDATE)) ) *24*60*60)  >= 0--*
        AND ROWNUM  <= NO_OF_RECS AND REFNUM IN (select REFNUM FROM (SELECT REFNUM, TOTALNUM, COUNT(REFNUM) FROM Tabela1
   GROUP BY REFNUM, TOTALNUM
   HAVING COUNT(REFNUM) = TOTALNUM)));

COMMIT;
end;

begin
open RCT1 FOR
   SELECT
   ID,
   OpID,
   ST,
   SMI,
   DA,
    OA,
    NA,
    NT,
   PID,
    DCS,
    VP,
    DDT,
   SCTS,
    DT,
    RP,
    UDL,
    UD,
   SRR,
   MS,
   MR,
   MMS,
   REPLACE,
    TRY_COUNT,
    STATUS_FLAG,
    LAST_TRY_TIME,
    PRIORITY,
    CDRNUM,
    GetSubscriberProfile(trim(OA)) || GetGroupProfile(trim(OA)) as OAProfile,
    GetSubscriberProfile(trim(DA)) || GetGroupProfile(trim(DA)) as DAProfile,
    GetProfileAutoReply(trim(OA)) as OAAutoReply,
    GetProfileAutoReply(trim(DA)) as DAAutoReply,
    LAST_TRY_TIME_TEST,
    SYSTEM_TIME_TEST
   FROM Tabela1
  WHERE app_id = appid and last_try_time = tmpDate;

COMMIT;

   GLOBALPKG.TRANCOUNT := GLOBALPKG.TRANCOUNT - 1;
   COMMIT;

        EXCEPTION
   WHEN OTHERS THEN
   BEGIN
    GLOBALPKG.TRANCOUNT := 0;
open RCT1 for select null from dual;
END;
end;

end PROCEDURA1;

Uveo sam novu kolonu app_id u koju se pri updateu upise koja aplikacija je zakljucala slog tako da u nastavku procedure (iz iste procedure) mogu i pokupiti taj slog da bih ga jos i selektovao u REF cursor.

Medjutim ponovo imam isti problem, da dvije aplikacije pokupe iste slogove i duplo ih obrade. Kao da imam problem sa osnovnom konkuretnosti na nivou ORACLA prilikom pristupa slogu. Ne znam da li postoji neko podesenje u bazi koje bi moglo uticati na ovakvo nesto.

Takodjer ne znam postoji li neki drugi mehanizam da se SELECTovani slogovi "zakljucaju" za SELECT od strane druge aplikacije.

Takodjer pokusao sam i sa EXPLICIT lockom na tabeli, i ponovo sam imao isti problem. kao i sa set transaction
isolation level read committed; i nije mi uspjelo.

Ne znam da li postoji jos neki nacin da se "zakljuca" kriticna sekcija u PL/SQL proceduri tj. da proceduru moze pozivati samo jedna aplikacija u jednom trenutku.

Hvala.

Odgovor na temu

brzak

Član broj: 66407
Poruke: 126

+5 Profil

Re: select for update

^{30.10.2008. u 12:38 - pre 193 meseci}

Ne pratim te bas najbolje, (bolje receno uopste), ali ako dobro razumem, dovoljno ti je da potreban broj puta radis select for update nowait. To bi ti bila onda jedna procedura, koju pozivas po potrebi
U exception delu uhvatis gresku -54, resource busy, i javis neku smislenu poruku
Mada mi u firmu radimo 100 godina u oracle-u i nikada se ne bavimo tim stvarima, pretpostavljam da imas bas specifican problem, kada moras eksplicitno da resavas zakljucavanja
Izvini ako ovo nije odgovor na pitanje,
Pozdrav.

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3497

Jabber: djoka_l

+1482 Profil

Re: select for update

^{31.10.2008. u 08:22 - pre 193 meseci}

Bio sam poslednja dva dana na putu, pa nisam mogao da pogledam, ali evo me sada opet ovde.

Kada sam napisao da mi nije baš najjasnije šta koji deo radi, hteo sam da kažem da koncept aplikacije nije baš najbolji. Ovo već radi bolje, ali rečenica da Oracle ima problema sa sinhronizacijom ili konkurentošću pristupa podacima prosto nije tačna, već ti nisu dovoljno poznati Oracle mehanizmi.

Postoji mogućnost da se kritičnom delu koda zabrani pristup iz više aplikacija istovremeno i to je realizovano LOCK mehanizmom kroz skup funkcija iz paketa SYS.DBMS_LOCK. Ono što je pokušano u tvom primeru naprosto nije dobro. Uradi se UPDATE, pa COMMIT. Onda se otvori kurzor za SELECT pa COMMIT pa se onda ažurira varijabla iz paketa, pa opet COMMIT. Poslednja dva COMMITa su potpuno nepotrebno, a iz primera se ne vidi da li se kurzor RCT1 uopšte i zatvara.

Opet mi nije jasna logika.
Šta ako obrada u aplikaciji tih 100 slogova traje duže od tajm auta. Onda je jasno da će se neki slogovi koje je baferisala jedna aplikacija i još nije završila obradu do isteka zadatog vremena naći i u drugoj aplikaciji.
Zašto je AppID koji je sada postao mehanizam za označavanje seta podataka za aplikaciju definisan da ima default vrednost. Šta ako ni jedna ni druga aplikacije ne pošalju AppID, nego obe pokušaju da dohvate one slogove u kojima stoji AppID = -1?
Ako problem sa sinhronizacijom postoji na nivou dohvatanja 100 slogova, zašto ne probati da se smanji broj slogova koji se dohvataju u jednom prolazu, na 10 ili 15.
Zašto se COMMIT posle UPDATE komande ne uradi POSLE završetka obrade slogova od strane aplikaciju, umesto što se radi odmah posle UPDATE?

Ako je već poznato da će dve aplikacije pristupati podacima, a već imaš problema da podeliš podatke između njih, ne napraviš da jedna pristupa samo onim slogovoima sa neparnim ID, a druga onima sa parnim ID?

_{[Ovu poruku je menjao djoka_l dana 31.10.2008. u 09:33 GMT+1]}

Odgovor na temu

ndbosna
Nadir Zlatar

Član broj: 115931
Poruke: 19
*.bstelecom.ba.

Profil

Re: select for update

^{31.10.2008. u 09:39 - pre 193 meseci}

Pozdrav,

Hvala vam na trudu da mi pomognete. Sto se tice mog nepoznavanja ORACLE mehanizama tu si u pravu, kao sto sam rekao nisam nikakav ORACLE ekspert, i ovo mi je prilika da neke stvari naucim. Rekao sam da se meni cini kao da je problem sa konkurentoscu na nivou sloga, ne mislim da je greska na ORACLU i da on to ne odradjuje dobro, samo sam razmisljao da mozda ne postoji neko podesenje o kojem ja ne znam. Sto se tice COMMITA apsolutno si u pravu to sam promjenio, i ponovo cu testirati sa drugom procedurom.

U sustini problem sam uspio rijesiti sa jednom starijom procedurom koju ja nisam pisao tako sto sam dodao Explicit lock na tabelu na pocetku procedure (medjutim ta procedura radi sa nekim pomocnim tabelama i po meni ima viska koda). Nisam bas pretjerano odusevljen rjesenjem i u medjuvremenu cu pokusati smisliti nesto drugo (popraviti gore napisnu proceduru). Sto se tice vremena obrade, tesko da se moze desiti da se tih 100 slogova obradjuje duze od 3 minute, a i ako se desi mislim da ce se oni izbrisati iz ove tabele i upisati u novu tabelu Rejected (ovo je regulisano drugom procedurom).

UPDATE bi trebao uradiri explicit lock na nivou reda, ako se ne varam i taj lock bi trebao postojati sve do commita. Na ovaj nacin druga aplikacija koja poziva istu proceduru ne bi trebala moci raditi update jer vec postoji explicit lock na redovima.

Pozdrav

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3497

Jabber: djoka_l

+1482 Profil

Re: select for update

^{31.10.2008. u 13:10 - pre 193 meseci}

Evo jednog predloga kako da unaprediš aplikaciju:

U aplikaciji koju je napravila naša firma, postoje masovne obrade koje se izvršavaju paralelno. Postoji jedna glavna obrada koja kontroliše broj paralelenih procesa koji se izvršavaju nad istim setom podataka, ali ta glavna obrada deli taj set na sitnije komade koje šalje na AQ, a paralelne obrade dohvataju iz AQ, pa koja prva stigne. Na taj način u QUEUE postoje delići obrada koji čekaju na prvi slobodan proces koji će ih obraditi, a pojedinačne "niti" obrade ne uzimaju unapred predefinisani broj slogova koji se u tvom slučaju limitira na 100 ne vodeći računa da je obrada nekih 100 slogova duža ili kraća od obrade drugih 100 slogova.
Na ovaj način, paralelni procesi uzimaju novi set u onom momentu kada završe prethodni i nema čekanja da sve obrade završe svoj posao da bi se pripremio sledeći paket.

Odgovor na temu

ndbosna
Nadir Zlatar

Član broj: 115931
Poruke: 19
*.bstelecom.ba.

Profil

Re: select for update

^{31.10.2008. u 15:14 - pre 193 meseci}

Uspio sam sa manje-vise slicnom procedurom koju sam postavio gore. Problem je bio sto se prvo obradi subquery pa tek onda update koji ce zakljucati slogove, drugi update (poziv procedure od strane druge aplikacije) je zaista cekao da se obradi prvi ali problem je bio sto je subquery prije toga pokupio iste podatke (u slucaju istovremenog pristupa). Nakon COMMITA, kada se oslobode podaci on ide ponovo u update sa istim podacima). Rijesio sam problem time sto sam postavio dodatni uslov u WHERE od update-a, u sustini isti onaj iz subqueryija.

Ova aplikacija radi prosljedjivanje SMS poruka. Samo poruke koje se sastoje iz vise dijelova se upisuju u bazu da bi se proslijedile tek kada su sve na okupu. To zaista i provjerava onaj subquery. U sustini duzine tih slogova su uvijek iste. I ovdje postoji thread koji svake 3 sekunde cita iz baze blokove poruka (ta duzina dolazi kao parametar proceduri, za primjer sam govorio da je ona 100). Problem je bio sa duplim isporukama, jer su dvije aplikacije citale iste slogove zato jer se prvo radio SELECT, pa kasnije update i tek na kraju procedure COMMIT. Dvije aplikacije su mogle selectovati iste podatke jer se tek kasnije radio update sloga gdje kazemo naredne 3 minute niko taj slog ne smije kupiti iz baze.

Odgovor na temu