(ms)sql: Select / update racecontition

[petterg]

Jeg trenger å få utført følgende to query samtidig:

Select * from myTable where status=0

Update myTable set status=1 where status=0

 

Altså, jeg trenger å få hentet ut radene som endres, samtidig som de endres. Risikoen her er at en annen prosess kan finne på å legge til nye rader med status=0 mellom select og update. Hvordan kan jeg skrive query slik at det er garantert at det er de samme radene som returneres av select og oppdateres av update?

[quantum]

spørringen blir akkurat som du har skrevet, men du må kjøre den i en transaksjon med isolation-level serializable.

 

https://en.wikipedia.org/wiki/Isolation_%28database_systems%29

[terjeelde]

Quantum har selvsagt helt rett, og det er vanskelig å si særlig mer uten å vite litt mer om hva du egentlig gjør.

 

Hvis det er mange rader, så er gjerne den løsningen best. Hvis det er få rader du skal oppdatere, så kan du kanskje endre WHERE i UPDATE til å gå mot ID på datene fra den første spørringen.

 

Da påvirker du bare radene du hentet ut, og slipper å endre isolation level.

[quantum]

Å bruke id'ene fra den første spørringen er ikke så dumt, men det kan være begrensninger i implementasjonen om man bruker update blablabla where id in (1,2,3,4,5 ....) - altså begrenset hvor mange id'er man kan putte i in-klausulen.

 

Et annet alternativ kan være "select .... for update"; se f.eks. her http://stackoverflow.com/questions/10935850/when-to-use-select-for-update.

 

Som terjeelde sier - det blir et spørsmål om hva du egentlig gjør. Hva er f.eks. hensikten med select først?

[terjeelde]

SELECT FOR UPDATE låser radene du SELECTer, men gjør ikke noe for ny-innsatte rader.

 

Hvis radene kan slettes mellom SELECT og UPDATE, så må du kombinere min tanke om å bruke id, med quantums tanke om FOR UPDATE.

 

Tenker det burde være en grei løsning om antall rader ikke skaper problemer, men igjen, vanskelig å si mer uten å vite mer.

[quantum]

SELECT FOR UPDATE låser radene du SELECTer, men gjør ikke noe for ny-innsatte rader.

Ja, det er da man må vite hva man vil, skal evt. ny-insatte ignoreres, eller oppdateres de også?

[petterg]

Databasen er en slags logg som stadig får nye rader fra en prosess (ca 500 om dagen)

Jeg har lagt til en ekstra kolonne med status default 0, slik at alle rader prosessen skriver får status 0.

 

Planen er at jeg skal skrive et slags loganalyseverktøy som kjører med jevne mellomrom, leser ut alle (evt limit X) som har status=0, endrer status på disse til 1, og gjør noe med logmeldingene (f.eks sende mail om det er mange av en type)

 

Tanken med status feltet er å holde styr på hvilke rader analyseverktøyet har behandlet og ikke. Bruk av timestamp eller Id kan ikke brukes fordi prosessen som skriver radene skriver noen typer direkte, og andre med en forsinkelse.

 

Hvilken metode høres ut som er mest fornuftig for dette?

[quantum]

Iom. at ikke log'entryene nødvendigvis skrives umiddelbart så vil du få «overlapp» i analyse-batchene, det kan du ikke unngå vha. isolationlevels og «for update»., medmindre du vet max. forsinkelse og sørger for at log-analysatoren alltid ligger på «etterskudd» større enn max forsinkelse.

 

For meg ser det ut som det eneste problemet ditt er at du tenker select og update i feil rekkefølge, hva med

update logtable set status = status +1; -- status initielt = 0;
select * from logtable where stautus = 1;

(du kan gjøre dette mer effektivt men logisk sett blir det ekvivalent). Dette blir ikke så veldig annerledes enn å bruke timestamp eller id, men du må nesten forklare hva som gjør at dette ikke vil funke for deg. (jadda, er muligens litt treig i nøtta ...)

 

Edit: Spørsmålet er egentlig om det er nok å garantere at analyseverktøyet ikke teller den samme pannekaka to ganger, eller om det også må garanteres at det ikke er overlapp på tidsintervallene over entries som hver enkelt analyse-runde behandler.

Endret 14. juni 2013 av quantum

[petterg]

select før update medfører at man kan risikere å oppdatere flere rader enn man hadde med i select

update før select medfører at man får med rader som allerede er behandlet i tidligere.

update - select - update bør kunne fungere.

update tabell set status = 1 where status = 0
select * from tabell where status=1
update tabell set status = 2 where status = 1

Da betyr status=0 ubehandlet, status=1 utvalgt for behandling, status=2 innlest av behandlingsprosess

[quantum]

update før select medfører at man får med rader som allerede er behandlet i tidligere.

neida, men det er som nevnt mer effektivt å gjøre som du foreslår.