[LØST av blackbrrd] SQL lagertelling metode

[hda]

Hei

 

Jeg kunne godt tenkt meg ideer rundt hvordan en best kan foreta/hensyn ta en lagertelling i et lagersystem.

 

Scenario:

Vare lager antall

vare A inn 5

Vare A inn 3

vare A inn 2

vare A ut 3

 

noe som betyr at det er (5+3+2) =10 stykk av vare A er ført inn på lager, det er ført ut 3 stykk. noe som skulle tilsi en beholdning på 7 av vare A på lager

 

Jeg finner ut at jeg skal telle lageret slik at jeg evt får korrigert beholdningen i systemet og evt tallfestet svinn

 

videre tenker vi oss at en lagertelling avdekker at det bare er 4 stk på lager, noe som betyr at svinnet er 3 (7-4) , jeg ønsker derfor å oppdatere lagerbeholdningen til den virkelige mengden.

 

Jeg hadde tenkt å merke den nye beholdningen med "tell" slik at virkelig beholdning til enhver tid i programmet er tell-sum(ut)+sum(inn)

 

problemet oppstår når jeg gjennomfører lagertelling 2,3,4 etc, da blir min "løsning" feil.

 

Jeg må sannsynligvis ved registreing av en ny lagerbeholdning gjøre dette;

 

føre diff mellom inn og ut til en egen kode som kalles svinn, slik at jeg får nullstillt beholdningen før registrering av ny beholdning.

 

Hvordan ville du implementert lagertelling?

 

Alle meninger mottaes med takk.

Endret 1. juni 2007 av hda

[blackbrrd]

Du vil trenge en rad pr vare pr lager hvor antallet på lager står, dette feltet må kontinuerlig oppdateres hver gang det blir lagt inn en varebevegelse.

 

Du vil trenge en tabell med varebevegelser, med timestamp.

 

Du vil trenge en tabell hvor du lager antallet tellt, timestamp og hvilken vare/lager dette var.

 

Da har du følgende scenario:

Du starter med at lagerverdien for vare x er 12 kl 0700

kl 08 blir det plukket 4 av vare x

kl 09 blir det lagt inn 2 av vare x

kl 10 blir det tellt 5 av vare x

kl 11 blir det plukket 3 av vare x

 

Varetellingen blir avsluttet kl 12, du har da 5-3=2 varer på lager og dette blir lagret på varen. Du vet også at kl 10 så skulle du hatt 12-4+2=10 varer på lager, så du skal legge inn ett svinn på 5

 

Varebevegels tabellen din ser da slik ut:

Vare x -4 kl 08, plukk

Vare x +2 kl 09, innkjøp

Vare x -3 kl 11, plukk

Vare x -5 kl 12, svinn

 

Dette systemet vil gjøre at du kan telle et lager som er i bruk

Endret 31. mai 2007 av blackbrrd

[hda]

Du vil trenge en rad pr vare pr lager hvor antallet på lager står, dette feltet må kontinuerlig oppdateres hver gang det blir lagt inn en varebevegelse.

 

Du vil trenge en tabell med varebevegelser, med timestamp.

 

Du vil trenge en tabell hvor du lager antallet tellt, timestamp og hvilken vare/lager dette var.

 

Da har du følgende scenario:

Du starter med at lagerverdien for vare x er 12 kl 0700

kl 08 blir det plukket 4 av vare x

kl 09 blir det lagt inn 2 av vare x

kl 10 blir det tellt 5 av vare x

kl 11 blir det plukket 3 av vare x

 

Varetellingen blir avsluttet kl 12, du har da 5-3=2 varer på lager og dette blir lagret på varen. Du vet også at kl 10 så skulle du hatt 12-4+2=10 varer på lager, så du skal legge inn ett svinn på 5

 

Varebevegels tabellen din ser da slik ut:

Vare x -4 kl 08, plukk

Vare x +2 kl 09, innkjøp

Vare x -3 kl 11, plukk

Vare x -5 kl 12, svinn

 

Dette systemet vil gjøre at du kan telle et lager som er i bruk

8750373[/snapback]

 

Glimrende forslag, jeg går for ditt forslag med to tabeller mht lagertransaksjonene, en for fysiske transaksjoner og en for bevegelsene. Igjen, hjertlig takk for konstuktivt og gjennomarbeidet forslag.

 

Mvh

 

hda

Endret 1. juni 2007 av hda

[roac]

En liten kommentar på sidelinjen: Dette er et kremeksempel på en løsning hvor du sannsynligvis VIL denormalisere. Du ønsker ikke å bare basere deg på historikken til antall varer, det ville medføre at du må gjøre en aggregering (summering) hver eneste gang du skal ha ut antallet varer på lager. Her trenger du å føre dataene "dobbelt", med en tabell for varebeholdning og en for bevegelser. Oppdateringen av disse skla skje i en transaksjon, slik at enten begge eller ingen av tabellene blir oppdatert.

[blackbrrd]

Det var det jeg prøvde å si roac

 

Om man vil løse det ved triggers eller applikasjonslogikk er et annet interessant spørsmål...

 

Det er viktig å få lagt inn radene i varebevegelsen og oppdatering av aggregat-feltet i samme transaksjon ja, nevnte det ikke fordi det på en måte er rimelig åpenbart.

 

... skal forresten bruke forslaget i lagersystemet i firmaet jeg jobber i

[kaffenils]

For å lage systemet tamper-proof så kan endring av lagerbeholdning kun gjøres gjennom stored procedures. Du gir kun brukerene SELECT rettigheter på kolonnen som inneholder lagerbeholdning. Transaksjonstabellen har heller ingen skriverettigheter til. All oppdatering gjøres ved hjelp av tre stored procedures, en for å ta ut av lager, en for å legge inn på lager og en for å lagertelling. Hvis dere har flere lager så kan du også ha en fjerde prosedyre for å overføring mellom to lager. Brukerene har EXECUTE rettigheter til disse prosedyrene, og kun eier av prosedyrene har UPDATE rettigheter på lagerbeholdingskolonnen og INSERT i transaksjonstabellen.

Prosedyrene kjøres selvfølgelig, som roac sier, i en transaksjon. Isolasjonsnivået bør vel være REPEATABLE_READ siden du sannsynligvis skal lese endel data for å validere f.eks. om det er nok varer på lager ved uttak etc.

 

Edit: Du bør kanskje også bruke XLOCK når prosedyrene leser gjeldende lagerbeholdning ved evt. validering slik at andre som evt. prøver å lese lagerbeholdningen må vente til prosedyren er ferdig å kjøre.

 

Edit2: Hvis DBMSet støtter row-versioning (f.eks. Oracle eller SQL Server 2005) så kan dette brukes for å bedre concurrency. I SQL Server 2005 kalles det snapshot isolation.

Endret 1. juni 2007 av kaffenils

[roac]

Edit2: Hvis DBMSet støtter row-versioning (f.eks. Oracle eller SQL Server 2005) så kan dette brukes for å bedre concurrency. I SQL Server 2005 kalles det snapshot isolation.

8755555[/snapback]

Dette er i og for seg riktig, men det har også en drawback med at man må håndtere transaksjoner som feiler fordi flere bestillinger har blitt forsøkt håndtert samtidig. Bruker du snapshot isolation og to prosesser prøver å oppdatere samme rad, vil oppdatering nr 2 feile siden dataene allerede er modifisert, og dette må man da ta høyde for, ved å lese kildedatene på nytt og så forsøke å oppdatere på nytt.

 

Hvis det er concurrency man er ute etter i et lagersystem, så ville jeg heller tatt en titt på Service Broker, som er en ny feature som VIRKELIG kan forbedre dette, selv om man da må tenke litt annerledes enn man ellers gjør i en vanlig relasjonsdatabase.

[blackbrrd]

Antar at det som blir kallt "snapshot isolation" er det samme som Serializable i postgres?

 

Som du sier må man ta høyde for at oppdatering nr 2 vil feile, men det er da uendelig mye bedre enn å ha feil data i databasen!

 

Service broker som du nevner - hva er det?

[kaffenils]

Antar at det som blir kallt "snapshot isolation" er det samme som Serializable i postgres?

 

Service broker som du nevner - hva er det?

8762362[/snapback]

 

Nei, det er ikke det samme som serializable.

Snapshot Isolation er isolasjonsnivå som gir deg row versioning funksjonalitet (Oracle har hatt det lenge). For å forklare det enkelt så gir det funksjonalitet hvor kun writes blokkerer for andre writes. En write vil m.a.o. ikke blokkere for en readoperasjon. Dette er mulig fordi SQL Server lagrer eldre versjoner av rader i TempDB, og readoperasjoner leser herfra hvis originaldataene har en XLock på seg. SQL Server har faktisk to typer rowversioning isolasjonsnivåer; SNAPSHOT og READ_COMMITTED (når READ_COMMITTED_SNAPSHOT er aktivert for databasen). De to nivåene fungerer ganske likt, men det er en viktig forskjell i hvilken versjon av radene som leses. Du kan lese mer her http://msdn2.microsoft.com/en-us/library/ms173763.aspx

 

Service Broker er enkelt forklart en meldingssystem som gjør det mulig å sende meldinger frem og tilbake asynkront mellom service broker tjenester på SQL Server, enten på samme server eller mellom forskjellige servere.

Endret 2. juni 2007 av kaffenils

[kaffenils]

Dette er i og for seg riktig, men det har også en drawback med at man må håndtere transaksjoner som feiler fordi flere bestillinger har blitt forsøkt håndtert samtidig. Bruker du snapshot isolation og to prosesser prøver å oppdatere samme rad, vil oppdatering nr 2 feile siden dataene allerede er modifisert, og dette må man da ta høyde for, ved å lese kildedatene på nytt og så forsøke å oppdatere på nytt.

 

Enig i det. Alternativet er å ikke bruke row versioning og at andre skriveoperasjoner må vente til din prosess er ferdig. Det er fordeler og ulemper med begge alternativene. Med rowversioning så blokkerer du iallefall ikke andre leseoperasjoner mens du skriver, men som du selv sier så kan du støte på problemet med at ventende skriveoperasjoner feiler fordi radene har blitt oppdatert til en annen verdi en det versjonen som din transaksjon har tilgang til gjennom versjonslageret.

[blackbrrd]

Antar at det som blir kallt "snapshot isolation" er det samme som Serializable i postgres?

 

Service broker som du nevner - hva er det?

8762362[/snapback]

 

Nei, det er ikke det samme som serializable.

Snapshot Isolation er isolasjonsnivå som gir deg row versioning funksjonalitet (Oracle har hatt det lenge). For å forklare det enkelt så gir det funksjonalitet hvor kun writes blokkerer for andre writes. En write vil m.a.o. ikke blokkere for en readoperasjon. Dette er mulig fordi SQL Server lagrer eldre versjoner av rader i TempDB, og readoperasjoner leser herfra hvis originaldataene har en XLock på seg. SQL Server har faktisk to typer rowversioning isolasjonsnivåer; SNAPSHOT og READ_COMMITTED (når READ_COMMITTED_SNAPSHOT er aktivert for databasen). De to nivåene fungerer ganske likt, men det er en viktig forskjell i hvilken versjon av radene som leses. Du kan lese mer her http://msdn2.microsoft.com/en-us/library/ms173763.aspx

 

Service Broker er enkelt forklart en meldingssystem som gjør det mulig å sende meldinger frem og tilbake asynkront mellom service broker tjenester på SQL Server, enten på samme server eller mellom forskjellige servere.

8763553[/snapback]

 

Det jeg har uthevet i bold beskriver serializable i Postgres såvidt jeg kan se. Det kalles MVCC såvidt jeg husker, multi version control ett-eller-annet. Dvs at hvis du overskriver en rad så blir ikke den gamle raden borte, men blir brukt av transaksjoner som kjører før din transaksjon er ferdig. Poenget er at 99,9% av tiden så oppleves databasen som om alt skjer etterhverandre, mens det egentlig skjer parallellt

 

Postgres sin MVCC kan du lese om her: http://linuxgazette.net/issue68/mitchell.html og postgres har hatt det så lenge jeg har brukt Postgres, dvs i ihvertfall 8 år.

Endret 2. juni 2007 av blackbrrd

[roac]

Det jeg har uthevet i bold beskriver serializable i Postgres såvidt jeg kan se. Det kalles MVCC såvidt jeg husker, multi version control ett-eller-annet. Dvs at hvis du overskriver en rad så blir ikke den gamle raden borte, men blir brukt av transaksjoner som kjører før din transaksjon er ferdig. Poenget er at 99,9% av tiden så oppleves databasen som om alt skjer etterhverandre, mens det egentlig skjer parallellt

8764123[/snapback]

Serializable er et standard transaksjonsnivå som også PostgreSQL støtter. Jeg tror du bør ta en liten titt på PostgreSQL Transactions. Serializable har IKKE noe med row versioning å gjøre.

[Frank2004]

Det jeg har uthevet i bold beskriver serializable i Postgres såvidt jeg kan se. Det kalles MVCC såvidt jeg husker, multi version control ett-eller-annet. Dvs at hvis du overskriver en rad så blir ikke den gamle raden borte, men blir brukt av transaksjoner som kjører før din transaksjon er ferdig. Poenget er at 99,9% av tiden så oppleves databasen som om alt skjer etterhverandre, mens det egentlig skjer parallellt

8764123[/snapback]

Serializable er et standard transaksjonsnivå som også PostgreSQL støtter. Jeg tror du bør ta en liten titt på PostgreSQL Transactions. Serializable har IKKE noe med row versioning å gjøre.

8772523[/snapback]

Han nevner vel serializable fordi isolasjonsnivået vil gi feilmelding/rollback ved samtidige oppdateringer av en rad, altså oppførselen som er beskrevet tidligere i tråden.

 

Han ordlegger seg kanskje litt upresist når han sier "beskriver serializable" i stedet for "beskriver oppførselen [til postgres] under/med/over/whatever serializable", men..

[kaffenils]

Det jeg har uthevet i bold beskriver serializable i Postgres såvidt jeg kan se. Det kalles MVCC såvidt jeg husker, multi version control ett-eller-annet. Dvs at hvis du overskriver en rad så blir ikke den gamle raden borte, men blir brukt av transaksjoner som kjører før din transaksjon er ferdig. Poenget er at 99,9% av tiden så oppleves databasen som om alt skjer etterhverandre, mens det egentlig skjer parallellt

 

Postgres sin MVCC kan du lese om her: http://linuxgazette.net/issue68/mitchell.html og postgres har hatt det så lenge jeg har brukt Postgres, dvs i ihvertfall 8 år.

8764123[/snapback]

 

Det ser ut til å være det samme.

At PostgreSQL også har denne funksjonaliteten får meg til å undres enda mer hvorfor det er MySQL som får all oppmerksomheten.

[blackbrrd]

MySql har vesentlig bedre lese-ytelse enn Postgres, så det er nok ikke så rart. Dessuten er windows-versjonen av postgres relativt ny. Postgres har hatt mye bedre transaksjonsstøtte enn mysql ganske lenge.

 

Grunnen til at jeg nevnte Serializeable er at følgende tabell fra postgres brukermanualen:

 

Isolation Level        Dirty Read    Nonrepeatable Read  Phantom Read  
Read uncommitted  Possible        Possible                   Possible  
Read committed     Not possible   Possible                   Possible  
Repeatable read     Not possible  Not possible              Possible  
Serializable            Not possible  Not possible              Not possible  

[Frank2004]

MySql har vesentlig bedre lese-ytelse enn Postgres

Jasså? Lyst til å utdype/kvantifisere den litt?

 

Høyere IO throughput?

 

Eller raskere svar på enkle testspørringer som gjerne finnes i query cache og ved bruk av ikke-persistente connections?

[blackbrrd]

Hmm, faktisk så har testene jeg sett tidligere (som jeg ikke fant nå) konkludert med at MySql er kjappere med en prosessor, og hovedsakelig selects.

 

http://tweakers.net/reviews/657/2

 

Viser at joda, Mysql 5 vs Postgres 8.2 leder med 250 vs 175 med en prosessor. Dvs 42% raskere.

 

Med to dual core prosessorer så endrer det seg litt, nå vinner Postgres med 575 vs 475, Dvs 21% raskere.

 

Med en dual core prosessor så vinner Mysql med 400 vs 325, dvs 23% raskere.

 

Mao, i denne testen skalerer Postgres bedre enn mysql, og når du kommer opp i 4+ kjerner så er faktisk Postgres kjappere

 

Det er kanskje interessant å se hvordan de skalerer iforhold til seg selv når du øker antall prosessorer: http://tweakers.net/reviews/657/5

 

Databasetesten er beskrevet her: http://tweakers.net/reviews/646/9

Det er kort sagt databasen til websiden deres som er brukt til å teste.

 

Som du nevnte, dette er bare en test.

 

PS: jeg har som nevnt ovenfor brukt postgres i 8 år, aldri mysql

Endret 4. juni 2007 av blackbrrd