UNIQUE CONSTRAINT spørsmål

[blackbrrd]

Jeg har en tabell med en unique constraint på felt X

 

Transaksjonsnivå er satt til Serializable

( http://www.postgresql.org/docs/7.4/static/...action-iso.html )

...When a transaction is on the serializable level, a SELECT query sees only data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. ..

 

Jeg starter transaksjon a og legger inn verdien 1 i felt X

Jeg starter transaksjon b og legger inn verdien 1 i felt X

Jeg avslutter transaksjon b

Jeg avslutter transkasjon a og får en UNIQUE CONSTRAINT exception

 

So far so good...

 

Så til spørsmålet...

Hvordan vet transaksjon a at transaksjon b har lagt inn verdi 1 i felt X?

 

Går det an å emulere denne oppførselen med triggers, eller er det noe spesiellt hardcodet inn i UNIQUE CONSTRAINT oppførselen? Utfra definisjonen av Serializeable, så ser det slik ut...

Endret 13. september 2007 av blackbrrd

[roac]

Klart det går med en after insert/update trigger, men ytelsen vil med all mulig sannsynlighet bli vesentrlig redusert.

 

Jeg regner med at PostgreSQL løser dette på samme måten som Microsoft SQL Server, ved innsetting i en unique index, og denne vil nekte innsetting av to like verdier. Hvis det er tilfellet blir transaksjonen rullet tilbake. Sånn kort fortalt.

Endret 14. september 2007 av roac

[blackbrrd]

Hmm... men transaksjon a skal jo ikke se noe av dataene til transaksjon b?

 

Jeg testet med en before insert trigger, men det fungerer ikke, relativt logisk siden transaksjon a ikke ser det som er gjort av transaksjon b. Det skal ikke spille noen rolle om det er en before/after trigger som blir kjørt?

 

(hvorfor sitere hovedinnlegget når det ikke er noen innlegg i mellom?)

Endret 14. september 2007 av blackbrrd

[roac]

Litt mer grundig forklart:

 

En after trigger skal kjøre etter at en kommando har gått ok, f eks en oppdatering. I denne kan du sjekke om det finnes duplikater, og i så fall slette denne. Om du får returnert en feil til klienten som kjører f eks en innsetting ble jeg nå litt i tvil om, men du kan i hvert fall sørge for at datene bevarer integritet. (Det kan dog forekomme i et lite tidsrom at de doble verdiene ligger der, det vil si frem til after-triggeren er ferdig med å kjøre).

 

Alternativt kan du bruke en instead of trigger, og tvinge all oppdatering til å bli gjort av denne. Dette med et transaksjonsnivå som serializable wil gjøre at man låser en tabell for lesing også, og man kan garantere at det er kun én instans av triggeren som går om gangen, og man kan sørge for at duplikate verdier ikke settes inn ved å sjekke først.

 

Av disse to triggerene er den siste den som best garanterer at det ikke på noe tidspunkt eksisterer to identiske verdier, men også den mest ressurskrevende.

Endret 14. september 2007 av roac

[kaffenils]

Dette med et transaksjonsnivå som serializable

 

Etter å ha skummet gjennom PostgreSQL "BOL" så synes jeg ikke SERIALIZABLE i PostgreSQL likner veldig på SERIALIZABLE i SQL Server. Det minner mer om en kombinasjon av SNAPSHOT og REPEATABLE READ. Nevner bl.a. ikke et ord om range locks, men det er mulig dette er noe eget SQL Server har "funnet på".

[blackbrrd]

Litt mer grundig forklart:

 

En after trigger skal kjøre etter at en kommando har gått ok, f eks en oppdatering. I denne kan du sjekke om det finnes duplikater, og i så fall slette denne. Om du får returnert en feil til klienten som kjører f eks en innsetting ble jeg nå litt i tvil om, men du kan i hvert fall sørge for at datene bevarer integritet. (Det kan dog forekomme i et lite tidsrom at de doble verdiene ligger der, det vil si frem til after-triggeren er ferdig med å kjøre).

 

Alternativt kan du bruke en instead of trigger, og tvinge all oppdatering til å bli gjort av denne. Dette med et transaksjonsnivå som serializable wil gjøre at man låser en tabell for lesing også, og man kan garantere at det er kun én instans av triggeren som går om gangen, og man kan sørge for at duplikate verdier ikke settes inn ved å sjekke først.

 

Av disse to triggerene er den siste den som best garanterer at det ikke på noe tidspunkt eksisterer to identiske verdier, men også den mest ressurskrevende.

9492616[/snapback]

 

Oki, tydligvis ikke noen av dere som er vant med Serializeable som definert i Postgres...

 

Transaksjon a vil ikke se dataene commitet av transaksjon b fordi transaksjon b startet etter transaksjon a.

 

Serializeable vil ikke si at tabellen blir låst for lesing, det vil si at hvis du kjører select * from tabell to ganger i løpet av transaksjonen vil den få samme resultat (med unntak av endringer gjort i denne transaksjonen).

 

Serializeable låser faktisk ingenting, men hvis to transaksjoner prøver å oppdatere samme rad+kolonne i en tabell vil det oppstå en exception.

[siDDis]

...When a transaction is on the serializable level, a SELECT query sees only data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. ..

 

 

Eg trur at dette har ein meining når ein kjører select og henter *gammel* data.

 

Feks (dårleg eksempel) du starter ein transaksjon som legge saman antall salg gjort av ein vare til nåværande tidspunkt.

 

VareID: Truls, antall solgt: 200 stykker til 01.01.2007.05:15:30

 

DB.LOGG:

Trans A: Hente ut tidspunktet: 01.01.2007.05:15:30
Trans B: Hente ut tidspunktet: 01.01.2007.05:15:33
Trans B: Ny VareID Solgt, totalt antall er nå 201
Trans B: Commit
Trans A: Hente ut antall varer solgt som er 200.
Trans A: Commit

 

Det er vell for sånne ting det menes med Serializable Transaksjoner. Føler at det er heilt logisk at UNIQUE CONSTRAINT fungerer 'utanfor' dette.

 

Men når du henter data som du skal putte inn i ein kolonne med unik constraint, kan du ikkje hente den direkte frå ein sequence? Då vil jo transaksjonene ikkje setja inn same verdi i ein kolonne.

[roac]

Hvis to prøver å lese ut og oppdatere data med transaksjonsnivå serializable så SKAL det (i henhold til standard) skje slik:

 

01 A: Begynn serialisert transaksjon
02 B: Begynn serialisert transaksjon
03 A: Les ut antall 'Introduction to Database Systems' på lager (100)
04 B: Les ut antall 'Introduction to Database Systems' på lager (100) -- Venter til transaksjon A er ferdig
05 A: Oppdater antall 'Introduction to Database Systems', mink med 1 (99)
06 A: Commit
07 B: Får svar 99 fra 04
08 A: Oppdater antall 'Introduction to Database Systems', mink med 1 (98)
09 B: Commit

 

Serializable betyr nemlig at resultatet skal garantert være det samme som om de to transaksjonene skjedde etter hverandre, mao må man låse også på lesing, slik at det ikke kan bli krøll. Hadde man ikke låst også for lesing i eksempelet over, så ville B fått tallet 100 tilbake fra antall, og sluttresultatet hadde blitt 99, mao ville vi endt opp med en bok for mye i "lagersystemet" vårt.

 

Hvis det viser seg at PostgreSQL ikke gjør dette riktig, så er det i mine øyne en svært kritisk feil i PostgreSQL.

[kaffenils]

Serializeable vil ikke si at tabellen blir låst for lesing, det vil si at hvis du kjører select * from tabell to ganger i løpet av transaksjonen vil den få samme resultat (med unntak av endringer gjort i denne transaksjonen).

 

Serializeable låser faktisk ingenting, men hvis to transaksjoner prøver å oppdatere samme rad+kolonne i en tabell vil det oppstå en exception.

9493415[/snapback]

 

Det har jeg heller ikke sagt

Det jeg sier er at serializable har forskjellig betydning i PostgreSQL og MS SQL Server. I SQL Server plasserer du en lås på en key range og holdes under hele transaksjonen ved å bruke serializable. Det betyr bl.a. at fantomrader ikke kan oppstå.

 

Det plasseres selvfølgelig ingen låser ved kun å starte en transaksjon, men i det du SELECT, UPDATE, INSERT, DELETEr noe så plasseres det en lås, enten shared, update eller exclusive på objekter (row, key, page, table) og isolasjonsnivået bestemmer hvor lenge låsen skal eksistere. Ikke for å være frekk, men hvis du tror at låsing kun er noe som er for å kontrollere konflikter mellom skriveoperasjoner så må du lese litt mer om emnet.

Det finnes mange flere låstyper enn de få jeg har beskrevet, f.eks intent shared, intent exclusive, schema lock osv, og det er viktig å forstå hvilke som er kompatible og ikke kompatible med hverandre.

Endret 14. september 2007 av kaffenils

[blackbrrd]

Ingen som leste linken min i første posten, dropp å kommenter hva postgres gjør/ikke gjør før det er lest, så slipper vi masse rare oppfatninger?

 

Postgres Serializeable

Dirty Read - not possible

Nonrepeatable Read - not possible

Phantom Read - not possible

 

Fortsatt ingen som egentlig har svart på hvordan i svarte UNIQUE constraints fungerer sammen med Serializeable??!?

 

Utfra hva både roac og kaffenils sier så høres det ut som de forventer akkurat det samme som vil skje i postgres ved transaksjonsnivå Serializeable.

 

Det er vel ikke uten grunn at siden jeg linker til heter transaction-iso.html

Endret 14. september 2007 av blackbrrd

[kaffenils]

Okei, så sporet det av

 

For å få klarhet i detaljene rundt problemet:

1. Du starter transaksjon a og legger 1 inn i felt X. Du får beskjed om at operasjonen er utført.

2. Du starter transaksjon b og legger 1 inn i felt X. Får du nå beskjed om at operasjonen er utført, eller "henger" SQL statementet?

Hvis det ikke "henger", men derimot gir tilbakemelding at det ble utført vellykket så virker det på meg som en bug. Statementet burde ventet på at transaksjon a enten ble committet eller rolled back. Ved commit vil statement i pkt 2 feile, mens ved rollback så vil de utføres. Slik fungere i hvert fall SQL Server. Nå påstår jeg på ingen måte at SQL Server sitter på fasit.

3. La oss si at pkt. to utføres uten å "henge". Hva hender om du nå committer transaksjon b før a? Får du noen feilmelding?

4. La oss si at pkt 3 ikke gir feilmelding, men committer. Hva skjer nå når du committer transaksjon a? Merk at jeg har byttet rekkefølge på committene i forhold til din test.

[blackbrrd]

Serializeable i postgres oppfører seg som forventet, det er ikke der spørsmålet mitt kommer inn. Spørsmålet mitt er rett og slett hvordan de har implementert UNIQUE CONSTRAINT opplegget sitt

 

Har ikke testet det første du spurte om. Tipper statement 2. i transaksjon b vil gi en exception (det henger seg aldri)

 

De som svarer på denne tråden er vel forresten sånn ca de jeg ville trodd svarte også

 

Hvis det er noen som lurer på hvorfor jeg stiller teite spørsmål rundt unique constraints og implementasjon, så er det ganske enkelt fordi jeg har noe som NESTEN passer inn i en unique constraint... men kun for nye verdier Jeg er mao ikke interessert at unique constrainten bryr seg om gamle kjipe data. Prøvde meg derfor med en trigger, men den hjelper meg pent lite, i det den faktisk bryr seg om transaksjonsnivå serializeable, mens unique constraints ikke bryr seg - på akkurat riktig måte ...

 

Mao, jeg skal egentlig ha unique constraint på eksternt ordrenr og firma på en ordre. Problemet mitt er at jeg har aaaaaltfor mange gamle ordre med samme eksterne ordrenr på samme firma.

 

Så... da lurte jeg på hvordan unique constraints fungerer siden de tydligvis ser hva samtidige transaksjoner holder på med, og om jeg kan emulere den oppførselen

 

Det blir forresten en smiley pr alkoholenhet hittil i kveld... :!:

Endret 14. september 2007 av blackbrrd

[roac]

OK. For å være litt morragretten her

 

Siden du ikke tok deg tiden med å undersøke litt rundt det jeg informerte deg om (hvordan SQL Server løser dette), så fikk jeg gjøre det for deg: Unique Indexes. En unique index vil nekte to identiske versjoner, og det har overhodet INGEN TING med transaksjonsnivå å gjøre. Dette er også grunnen til at du i Microsoft SQL Server ikke vil få opprettet et unique constraint på en kolonne hvis denne inneholder to identiske verdier, selv om du spesifiserer WITH NOCHECK.

 

Skal du løse problemstillingen din så er det ett av to alternativer som fungerer, after trigger eller instead of trigger, og jeg vet med all mulig sikkerhet at det fungerer, for jeg har vært nødt til å bruke dem selv ved et par anledninger.

[blackbrrd]

Var klar over at unique-indekser ble brukt, men utfra forklaringen om hvordan SERIALIZEBLE skal fungere, så skulle ikke transaksjon b ha sett endringene gjort av transaksjon a. Mao må unique indekser fungere halvveis utenfor transaksjonsnivåer?

 

Hvis jeg forstår deg korrekt så vil du kunne starte en transaksjon a og sette inn verdi X i unique-constraint kolonnen. Hvis denne transaksjonen så henger seg, så vil du ikke kunne sette inn verdi X i unique-constraint-kolonnen i andre transaksjoner selv om transaksjon a ikke er committet?

 

Det høres ut som du bruker ett annet transaksjonsnivå enn SERIALIZEABLE som definert i første posten?

 

Hvilket transaksjonsnivå er det du benytter?

Endret 17. september 2007 av blackbrrd

[kaffenils]

Var klar over at unique-indekser ble brukt, men utfra forklaringen om hvordan SERIALIZEBLE skal fungere, så skulle ikke transaksjon b ha sett endringene gjort av transaksjon a. Mao må unique indekser fungere halvveis utenfor transaksjonsnivåer?

 

Alt som har med opprettholdelse av datakonsistens, om det er constraints eller foreign keys, styres "dypt nede i databasemotoren". Det er ikke noe som påvirkes av isolasjonsnivå. Hadde jo vært helt på tryne om du kunne overstyre en foreign key constraint ved å velge er transaksjonsisolasjonsnivå.

 

Du må også forstå hva som menes med at SERIALIZABLE gjør at du ikke "ser" endringer gjort etter transaksjonsstart. Med "ser" menes SELECT. Hvis du SELECTer så vil du få versjonen av dataene ved transaksjonsstart. Dvs. at du som "leser" ikke blir blokkert av "skriver", og en "skriver" blir ikke blokkert av en "leser". Men det er viktig å vite at "skrivere" blokkerer andre "skrivere". Det er ikke slik at når du kjører UPDATE/DELETE/INSERT så sjekkes evt. constraints mot gamle versjoner av dataene. Det hadde jo blitt helt tullete. Skriveoperasjoner gjøres fortsatt direkte i datasidene. Derfor skjønner jeg ikke at pkt. 2 i ditt eksempel "henger" som følge av at den venter på at låsen i transaksjon a skal frigis.

[blackbrrd]

Jeg sa at transaksjon a henger, ment som at den henger av eksterne årsaker, f.eks at noen har glemt å avslutte transaksjonen. Det er slikt som kan skje... I såfall vil de henge til den timer ut.

 

Jeg vet jo at UNIQUE CONSTRAINTS fungerer som de skal og at de ikke påvirkes av transaksjonsnivå. Jeg lurte på hvordan det ble gjort og om det gikk an å emulere. Konklusjonen min hittil er den samme som din. Det gjøres dypt nede i databasemotoroen og er ikke noe som kan emuleres i en f.eks ON AFTER trigger e.l.

 

Hvis jeg tar feil om hvordan man lager en slik ON AFTER trigger, så legg gjerne ved ett eksempel... Det er jo det jeg spør etter i første posten.

 

Testet hvordan UNIQUE CONSTRAINT faktisk oppfører seg i postgres med transaksjonsnivå satt til SERIALIZEABLE:

 

Transaksjon b blir stående å vente på Transaksjon a til Transaksjon a enten committer eller failer...

 

Transaksjon a Commiter:

Transaksjon a Begin

Transaksjon a INSERT INTO ttest (tekst) VALUES ('XXX')

Transaksjon a WAITING 30 seconds

Transaksjon b Begin

Transaksjon b INSERT INTO ttest (tekst) VALUES ('XXX')

Transaksjon a Commit

Transaksjon b Failed (Unique Constraint Exception, generert av postgres)

 

Transaksjon a blir rollback-et

Transaksjon a Begin

Transaksjon a INSERT INTO ttest (tekst) VALUES ('XXX')

Transaksjon a WAITING 30 seconds

Transaksjon b Begin

Transaksjon b INSERT INTO ttest (tekst) VALUES ('XXX')

Transaksjon a Failed (Forced exception, generert av meg med vilje for å få transaksjon a til å bli rollbacket)

Transaksjon b Commit

 

Mao, transaksjon b vet om dataene i UNIQUE INDEXEN, men vet at dataene ikke er committet enda og den må vente til transaksjon a er committet eller rollbacket før den kan avgjøre om den kan sette inn raden, eller om den må aborte.

Endret 17. september 2007 av blackbrrd