[Løst]Finne tidligere innlagt id

[lassejl]

Hei, har et stort problem her. Jeg har en tabell som heter ordre, og en som heter varer.

Tabellen ordre skal inneholde X antall av Y forskjellige varer. Derfor har jeg laget en tabell som er et bindeledd "ordre_varer". Den har ordre_id, vare_id og antall.

Tingen er når jeg skal legge det inn, oppretter jeg først ordren

INSERT INTO ordre VALUES ('','$arrangement_id','1')

Første felt her er en auto_incremental id, det andre er hvilket arrangement ordren tilhører, on den tredje er en boolean som sier om ordren er kjørt eller ikke.

 

Så langt, så greit.

 

Problemet mitt er når jeg skal legge inn ting i "ordre_varer". Her må jeg ha ordre_id til den ordren jeg nettopp lagde for at det skal stemme. Så langt har jeg:

INSERT INTO ordre_varer VALUES ('','$vare_id','$vare_mengde')

Tingen er at det første feltet skal være en ordre_id på den ordren jeg nettopp lagde, hvordan finner jeg den?

 

På forhånd takk

Endret 30. november 2009 av lassejl

[MikkelRev]

Hvilket språk og hvilken databaseserver bruker du?

Hvis du bruker PHP, har du mysql_insert_id(), som returnerer id'en til sist INSERT'ede felt. I andre språk har du sikkert tilsvarende funksjon. Sjekk manualen. I MySQL har du LAST_INSERT_ID() som gjør samme nytte. Bruker du en annen sqlserver, finnes det sikkert noe tilsvarende der. Eventuelt kan du gjøre det litt mer tungvint med SELECT id FROM ordre LIMIT 1 også hente id'en fra sql'en inn i en variabel som brukes i neste INSERT.

[zotbar1234]

Hvis du bruker PHP, har du mysql_insert_id(), som returnerer id'en til sist INSERT'ede felt.

I andre språk har du sikkert tilsvarende funksjon. Sjekk manualen. I MySQL har du LAST_INSERT_ID() som gjør samme nytte. Bruker du en annen sqlserver, finnes det sikkert noe tilsvarende der. Eventuelt kan du gjøre det litt mer tungvint med SELECT id FROM ordre LIMIT 1 også hente id'en fra sql'en inn i en variabel som brukes i neste INSERT.

 

Men ikke et eneste av disse garanterer atomisitet for operasjonene 1) hent siste id 2) insert en rad med denne id-en -- man vet jo ikke om last_insert_id() stammer fra nettopp fra den orderen som vedkommende er interessert i!

 

En mulig løsning er å bruke en sekvenstype, hente ut neste id fra den og så sette den id-en inn som neste ordre. På denne måte slipper du å bekymre deg for alle problemer knyttet til parallell oppdatering av tabellen.

[quantum]

Men ikke et eneste av disse garanterer atomisitet for operasjonene 1) hent siste id 2) insert en rad med denne id-en -- man vet jo ikke om last_insert_id() stammer fra nettopp fra den orderen som vedkommende er interessert i!

 

I såfall er det noe galt med transaksjonshåndteringen. Dersom man gjør dette i en transaksjon, OG man har nogenlunde styring på egen kode, så skal det gå bra, dette.

[quantum]

SELECT id FROM ordre LIMIT 1 også hente id'en fra sql'en inn i en variabel som brukes i neste INSERT.

Her henter du en *tilfeldig* id ...

[MikkelRev]

SELECT id FROM ordre LIMIT 1 også hente id'en fra sql'en inn i en variabel som brukes i neste INSERT.

Her henter du en *tilfeldig* id ...

Denne da: SELECT MAX(id) FROM avsnitt

[zotbar1234]

Men ikke et eneste av disse garanterer atomisitet for operasjonene 1) hent siste id 2) insert en rad med denne id-en -- man vet jo ikke om last_insert_id() stammer fra nettopp fra den orderen som vedkommende er interessert i!

 

I såfall er det noe galt med transaksjonshåndteringen. Dersom man gjør dette i en transaksjon, OG man har nogenlunde styring på egen kode, så skal det gå bra, dette.

 

Jeg uttrykket meg feil. Problemet er at oppdateringen av "ordre" og uthenting av siste innsatte id-en må foregå atomisk for at koden skulle virke etter hensikten. Denne garantien kan ikke gis (av last_insert_id()) og med 2 parallelle oppdateringer vil det være scenarioer der man oppdaterer feil kolonne i "ordre_varer" ift. id-en som er brukt i ordre (uten at det er noe galt med transaksjonshåndteringen).

[quantum]

Jeg uttrykket meg feil. Problemet er at oppdateringen av "ordre" og uthenting av siste innsatte id-en må foregå atomisk for at koden skulle virke etter hensikten. Denne garantien kan ikke gis (av last_insert_id()) og med 2 parallelle oppdateringer vil det være scenarioer der man oppdaterer feil kolonne i "ordre_varer" ift. id-en som er brukt i ordre (uten at det er noe galt med transaksjonshåndteringen).

I flg. manualen skal det gå bra så lenge de paralelle oppdateringene kommer fra ulik klient/connection? Mulig jeg ikke skjønner helt hva du mener her ...

[zotbar1234]

I flg. manualen skal det gå bra så lenge de paralelle oppdateringene kommer fra ulik klient/connection? Mulig jeg ikke skjønner helt hva du mener her ...

 

Skal vi se, la meg illustrere. T1/T2 - transaksjonene 1 og 2:

 

   T1							  T2
begin transaction
insert into ordre...
						begin transaction 
						insert into ordre...
						commit
last_insert_id()
insert into ordre_varer...
commit

 

Hvilken id henter last_insert_id() i T1? (beklager formatteringen, men jeg vet ikke helt hvordan jeg skal gå over til en stil med 2 kolonner midt i). Jeg skal ikke utelukke at T1 ikke vil se T2 sine resultater i en eller annen isolation level (for å bruke postgresterminologi), men at så er tilfellet for alle databaser og alle isolations levels? Faktisk, det er kun i "serializable" isolation level i postgres at T1 *ikke* vil se T2 sine resultater, hvis jeg tolker manualen rett. Tilsvarende situasjon finnes for MySQL.

 

Derfor er det mye lurere å bruke en SEQUENCE til å hente id-er for ordre/ordre_varer fra -- uansett hva isolation level måtte være og uansett hva en annen applikasjon måtte ha foretatt seg, er man garantert at id-ene vil være rette og unike med:

 

1) begin transaction

2) id = next_value(<suitable sequence>)

3) insert into ordre (id, ...)

4) insert into ordre_varer(id, ...)

5) commit

 

Såfremt alle applikasjoner følger 1-5, finnes det ikke en schedulering/isolation level som gir en konflikt eller medfører at id-ene i steg 3 og 4 er forskjellige.

Endret 1. desember 2009 av zotbar1234

[lassejl]

LAST_INSERT_ID() i mysql fungerte ikke, gav bare 0, men mysql_insert_id() fungerte perfekt.

 

Jeg ser problemet med flere samtidige connections, hvor man ikke kan være sikker på hvilken id man får, men det er ikke noe problem i dette tilfellet.

 

Takk for hjelpen !

Endret 2. desember 2009 av lassejl

[quantum]

@zotbar1234:

 

1) ts bruker mysql, ikke postgresql, da er det mest interessant hva som står i mysql-manualen :-)

 

2) ingen som bruker php oppretter nesta transaksjoner (min hypotese ;-), det er ihvertfall ikke nevnt i problemstillingen til ts.

 

3) mysql har ikke sequences svjv?

 

4) transaction isolation level er ikke postgres-terminologi, det er rdbms-terminologi, eget avsnitt om det i mysql-manualen og greier ...

 

5) mysql's last_insert_id() ser ut til å gi samme resultat i alle levels, dvs. det som skjer med last_insert_id() i den nestede transaksjonen er synlig i den omsluttende transaksjonen når den nestede er commitet. dette er forutsigbart og enkelt å forholde seg til i koden ved å lagre de ulike id-verdiene i variable.

 

CREATE TABLE t (
   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   name VARCHAR(10) NOT NULL
 ) engine=innodb;

set autocommit=0;

select '*** READ UNCOMMITTED ***';
set session transaction isolation level READ UNCOMMITTED;
start transaction; 
insert into t values (NULL,'arf');
select last_insert_id();
start transaction;
insert into t values (NULL,'arf');
select last_insert_id();
commit;
select last_insert_id();
select max(id) from t;
commit;


select '*** READ COMMITTED ***';
set session transaction isolation level READ COMMITTED;
start transaction; 
insert into t values (NULL,'arf');
select last_insert_id();
start transaction;
insert into t values (NULL,'arf');
select last_insert_id();
commit;
select last_insert_id();
select max(id) from t;
commit;


select '*** REPEATABLE READ ***';
set session transaction isolation level REPEATABLE READ;
start transaction; 
insert into t values (NULL,'arf');
select last_insert_id();
start transaction;
insert into t values (NULL,'arf');
select last_insert_id();
commit;
select last_insert_id();
select max(id) from t;
commit;


select '*** SERIALIZABLE ***';
set session transaction isolation level SERIALIZABLE;
start transaction; 
insert into t values (NULL,'arf');
select last_insert_id();
start transaction;
insert into t values (NULL,'arf');
select last_insert_id();
commit;
select last_insert_id();
select max(id) from t;
commit;

 

6) litt usikkerhet rundt hvordan du tolker postgresql-manualen. men postgresql støtter ikke nestede transaksjoner. man må kode seg rundt med savepoints isteden. hvis du har oversikt over hvordan savepoints funker i forhold til nestede transaksjoner ifm. ulike islolation levels, så er jeg lutter øre :-) man lærer jo stadig noe nytt. postgresql støtter forøvrig i realiteten kun read committed og serializable som isolation levels, de er iflg postgresql-manualen de eneste man trenger for å oppfylle sql-standarden.

[quantum]

LAST_INSERT_ID() i mysql fungerte ikke, gav bare 0, men mysql_insert_id() fungerte perfekt.

 

Jeg ser problemet med flere samtidige connections, hvor man ikke kan være sikker på hvilken id man får, men det er ikke noe problem i dette tilfellet.

 

Takk for hjelpen !

 

Da lurer jeg veldig på hva du mener er problemet? For det skal være fullstendig uproblematisk, last_insert_id() skal returnere siste autoincrement-id generert i den connection'en du bruker, uberørt av hva som skjer i andre connections, ref mysql-manualen.

[zotbar1234]

1) ts bruker mysql, ikke postgresql, da er det mest interessant hva som står i mysql-manualen :-)

 

Egentlig ikke. Man skal strebe etter den mest generelle løsningen. Sånn i utgangspunktet

 

2) ingen som bruker php oppretter nesta transaksjoner (min hypotese ;-), det er ihvertfall ikke nevnt i problemstillingen til ts.

 

Jeg snakket *ikke* om nestede transaksjoner -- jeg snakket om 2 *parallelle* transaksjoner som aksesserer de samme tabellene.

 

3) mysql har ikke sequences svjv?

 

Det kom som en overraskelse på meg, men heldigvis kan dette simuleres til å gi den samme effekten (helt på slutten av delkapittelet om last_insert_id() står det en oppskrift).

 

5) mysql's last_insert_id() ser ut til å gi samme resultat i alle levels, dvs. det som skjer med last_insert_id() i den nestede transaksjonen er synlig i den omsluttende transaksjonen når den nestede er commitet. dette er forutsigbart og enkelt å forholde seg til i koden ved å lagre de ulike id-verdiene i variable.

 

Nestede transaksjoner er ikke problemstillingen jeg prøvde å illustrere. I det opprinnelige eksempelet mitt er T1 og T2 to *parallelle* transaksjoner i forskjellige prosesser som aksesserer de samme tabellene *samtidig*.

 

start transaction; (...)

 

Disse er fra samme prosess -- det var ikke problemet jeg ville illustrere.

 

(...)man lærer jo stadig noe nytt. postgresql støtter forøvrig i realiteten kun read committed og serializable som isolation levels, de er iflg postgresql-manualen de eneste man trenger for å oppfylle sql-standarden.

 

Problemstillingen som dette (autonummerering) har jeg en følelse forekommer veldig ofte. Sekvenser er helt klart den rette måten å angripe dette på, noe som postgres og oracle ser ut til å støtte direkte. Jeg forstår fremdeles ikke hva MySQL gjør for å hindre det skisserte problemet med last_insert_id() (men last_insert_id() *kan* brukes på MySQL-spesifikt vis for å simulere sekvenser, og da er jo problemet løst).

 

edit: Ah, min feil. MySQL *garanterer* at connections er isolert fra hverandre mtp auto_increment/last_insert_id. Da var det jeg prøvde å illustrere et ikke-problem. Beklager forvirringen.

Endret 2. desember 2009 av zotbar1234

[quantum]

edit: Ah, min feil. MySQL *garanterer* at connections er isolert fra hverandre mtp auto_increment/last_insert_id. Da var det jeg prøvde å illustrere et ikke-problem. Beklager forvirringen.

 

og jeg trodde du snakket om nestede transaksjoner, hehe. alle disse misforståelsene har den uheldige bivirkningen at man lærer en hel masse nytt ... :o)

Endret 3. desember 2009 av quantum

[quantum]

...

6) litt usikkerhet rundt hvordan du tolker postgresql-manualen. men postgresql støtter ikke nestede transaksjoner. man må kode seg rundt med savepoints isteden. hvis du har oversikt over hvordan savepoints funker i forhold til nestede transaksjoner ifm. ulike islolation levels, så er jeg lutter øre :-) man lærer jo stadig noe nytt. postgresql støtter forøvrig i realiteten kun read committed og serializable som isolation levels, de er iflg postgresql-manualen de eneste man trenger for å oppfylle sql-standarden.

 

Bladde litt i noen ferske release-notes for ny versjon av postgresql og fant følgende:

 

"Fix premature drop of temporary files used for a cursor that is accessed within a subtransaction (Heikki)"

 

Men vi har jo funnet ut at nestede transaksjoner ikke støttes av postgresql (man må bruke savepoint), så da skjønner jeg ikke helt hva som menes med en subtransaction? Noen som har peil?

[kaffenils]

de er iflg postgresql-manualen de eneste man trenger for å oppfylle sql-standarden.

Du kan de jo like godt argumentere med at alt man trenger er sierializable, men det hadde jo blitt litt kjipt

Men fra spøk til alvor; å ikke ha repeatable read er kjipt i enkelte tilfeller. Spesielt med tanke på at man da må ta til takke med serializable og de concurrency-"problemene" det fører til.

[quantum]

de er iflg postgresql-manualen de eneste man trenger for å oppfylle sql-standarden.

Du kan de jo like godt argumentere med at alt man trenger er sierializable, men det hadde jo blitt litt kjipt

Men fra spøk til alvor; å ikke ha repeatable read er kjipt i enkelte tilfeller. Spesielt med tanke på at man da må ta til takke med serializable og de concurrency-"problemene" det fører til.

 

Kaffenils, går du på de-caf? Vi har forlatt 2009 og er i 2010 nå :-)

[kaffenils]

Kaffenils, går du på de-caf? Vi har forlatt 2009 og er i 2010 nå :-)

Begynner å bli gammel. Hukommelse og syn er ikke som det engang var