Transactions og massupdate

[HDSoftware]

Heisan folkens

Jeg skal lage en masseoppdateringsrutine i vårt program og trenger litt info om dette. Tidligere har jeg jobbet mot flate filer og der har jeg bare gjort en FileLock og fyrt ivei. Så vidt jeg kan se så går ikek dette i SQL. Jeg har lest meg frem til at det er noe som heter transactions og tipper dette har mye med akkurat dette å gjøre. Hvordan fungerer dette i praksis?

Kan man starte en transaction og forvente at tabellen blir låst for andre brukere? Eller er den basert på record touch slik at det blir postene jeg leser inn som lokkes for andre brukere?

Det som i hvertfall er vesentlig for meg er at jeg må kunne angre transaksjonen. Sett at man har poster som trenger andre poster i en annen tabell, som igjen kan kreve tabeller i tredje tabell o.s.v. Det betyr at jeg f.eks. sletter fra tabell 1, som igjen sletter fra tabell 2, men siden post i tabell 3 er aktiv hos en annen bruker så må jeg avbryte, og da må ikke de foregående postene slettes alikevel.

 

Noen som vet noe om dette?

 

Takker for all hjelp

[blackbrrd]

Gjør alle oppdateringene i en transaksjon. Hvis en av oppdateringene feiler kan du rollbacke hele greia. I java ville du skrevet noe slikt som con.rollbackTransaction(). Rimelig enkelt mao.

 

Hvis oppdateringen er så stor at du skal oppdatere(update/delete) større deler av en tabell kan du skaffe deg en table locks på alle tabellene det er snakk om. Dette er kun nødvendig hvis det er andre som også skriver til de samme tabellene. Alternativt kan du forhåndslocke de radene du har tenkt å oppdatere før du begynner med oppdateringene.

 

Hvis du kun skal legge til data (insert) så trenger du ikke bry deg om locks.

 

Det er også mulig å lage checkpoints i en transaksjon - isåfall kan du rollbacket til forrige checkpoint, ikke nødvendigvis hele transaksjonen.

 

Hvilke muligheter du har kommer litt an på hvilken database du kjører på.

Endret 22. januar 2009 av blackbrrd

[kaffenils]

Det er MS SQL Server du bruker? Versjon 2005?

 

I så fall kan du begynne med å "installere" denne prosedyren. Den viser hvilke transaksjoner som er aktive, hvilken type locks som holder, hvem som blokkerer hvem osv.

Jeg har blitt helt avhengig av den.

Endret 22. januar 2009 av kaffenils

[blackbrrd]

Sitat fra siden kaffenils linket til:

If you ever tried to track down a blocking situation by running sp_who and DBCC INPUTBUFFER while the users are standing screaming at you, aba_lockinfo is the thing for you

 

hahahaha

 

Jeg kom i skade for å lage noe kode som skulle oppdatere ca 20% av radene i en tabell i en transaksjon. Dette tok kanskje ett minutt. Denne tabellen ble det gjerne skrevet til 10 ganger i minuttet. Sjansen for at transaksjonen aldri ble ferdig var mao overhengende.

 

Det er da to muligheter for å fikse på dette:

a) låse de 20% jeg skal ha tak i

b) LOCK table NOWAIT (jeg måtte oppdatere 3 tabeller, kjørte da denne kommandoen på alle tre tabellene for å unngå muligheten for "deadlock". Uten NOWAIT kommandoen kan ting bli litt morsomt)

[kaffenils]

Jeg har lest meg frem til at det er noe som heter transactions og tipper dette har mye med akkurat dette å gjøre. Hvordan fungerer dette i praksis?

Transaksjoner er akkurat det du er ute etter. Egentlig vil alle SQL statements du ekskverer kjøres i en transaksjon. Du kan starte en transaksjon eksplisitt ved å kalle BEGIN TRAN. Da vil transaksjonen være aktiv til du kaller COMMIT eller ROLLBACK, eller ved at det oppstår en feil som automatisk ruller tilbake transaksjonen.

Hvis du ikke starter en transaksjon eksplisitt så vil SQL Server kjøre hvert statement i en implisitt transaksjon. Dette for å sikre at ACID-kompabilitet. Det betyr f.eks. at hvis du i en stored procedure eller i en ad-hoc batch kjører to DELETE statements, og du ikke eksplisitt har kalt BEGIN TRAN, så vil vært DELETE statement kjøres i en egen transaksjon.

 

Kan man starte en transaction og forvente at tabellen blir låst for andre brukere? Eller er den basert på record touch slik at det blir postene jeg leser inn som lokkes for andre brukere?

Som jeg sa så vil alle statements kjøres i en transaksjon, enten eksplisitt eller implisitt. Det er fem andre ting som er viktige å forstå om transaksjoner, og dette er locking modes (S, IS, X og IX er de viktigste), isolation levels, granularity, lock compability og lock escalation.

 

Det som i hvertfall er vesentlig for meg er at jeg må kunne angre transaksjonen. Sett at man har poster som trenger andre poster i en annen tabell, som igjen kan kreve tabeller i tredje tabell o.s.v. Det betyr at jeg f.eks. sletter fra tabell 1, som igjen sletter fra tabell 2, men siden post i tabell 3 er aktiv hos en annen bruker så må jeg avbryte, og da må ikke de foregående postene slettes alikevel.

Både nei og ja. Hvis en rad i tabell3 er låst av en annen transaksjon så vil SQL Server vente på at ressursen (f.eks. raden) "frigjøres". Men du kan også spesifisere NOWAIT, som blackbrrd har nevnt, noe som vil føre til at SQL Server vil returnere en feilmelding enten umiddelbart eller et x ms, alt etter hvordan du spesifiserer NOWAIT.

[HDSoftware]

Takker for svar. Mye bra info her

[blackbrrd]

NOWAIT, eller NOWAIT med en relativt kort timeout er nødvendig hvis du skal oppdatere store deler av flere tabeller samtidig.

 

Kjapp forklaring på hvorfor det er nødvendig:

Du har to transaksjoner X og Y

X låser tabell a

Y låser tabell b

X prøver å låse tabell b, men må vente fordi tabell b er låst

Y prøver å låse tabell a, men må vente fordi tabell a er låst

Du har nå en deadlock inntil en av transaksjonene timer ut. I mellomtiden får ingen transaksjoner oppdatert tabell a eller b.

 

Det går også an å låse de spesifikke delene av tabellene du vet du kommer til å oppdatere. Dette krever mer arbeid av deg som programutvikler, men vil mest sannsynlig gi et system med høyere ytelse.

 

Personlig bruker jeg aldri LOCK TABLE statements uten NOWAIT. Det kan føre til situasjoner hvor du kan ende opp med at hele systemet ditt låser seg...

Endret 22. januar 2009 av blackbrrd

[kaffenils]

Godt poeng, men SQL Server vil detektere deadlocks automatisk, og velge den transaksjonen som startes siste som offer (deadlock victim), så det er ikke vits i å eksplisitt bruke NOWAIT for dette. Det er selvfølgelig allikvel viktig å prøve å unngå situasjonen som skaper deadlocks ved å skrive kode som unngår akkurat det du beskriver.

 

En kan bruke SQL Server Profiler til monitorere deadlock situasjoner i SQL Server slik at en skrive om de prosedyrene som forårsaker dem.

[kaffenils]

Takker for svar. Mye bra info her

Du får si i fra hvis det er spesifikke ting du lurer på.

 

Du bør teste ut aba_lockinfo prosedyren også. Den gir deg veldig mye informasjon om transaksjoner og låser slik at du faktisk ser hva som skjer, selv om de fleste transaksjoner i et driftsmiljø er så raske at du sjelden får sett dem i resultatet til prosedyren. Men den er hendig hvis det oppstå langvarige locking situasjoner. Da ser du hvem som låser og hva, og hvem som låses og hvorfor.

 

Men hvis du starter en transaksjon eksplisitt i f.eks. SSMS og kjører en del statements (select, insert, delete, update) med forskjellige isolasjonsnivåer, så vil du se hvilke låser som holdes og på hvilke objekter når du kjører prosedyren.

[blackbrrd]

Må nesten le litt, første gang på lenge dette forumet har fått spørsmål som ikke går ut på å forklare noen forskjellen på insert og update statements e.l. - og kaffenils sååå på hugget.

[kaffenils]

Må nesten le litt, første gang på lenge dette forumet har fått spørsmål som ikke går ut på å forklare noen forskjellen på insert og update statements e.l. - og kaffenils sååå på hugget.

Mobber du

Men du har rett. Jeg orker ikke lenger å engasjere meg i basic "hjelp med insert" spørsmål.

Dessuten er jo liksom SQL Server mitt "spesialfelt".

Endret 22. januar 2009 av kaffenils

[blackbrrd]

For min del er databaser et nødvendig onde etter som jeg utvikler et ordre/faktura system. Fordi systemet har noen tusen brukere som alle kjører på samme server og det lages relativt store mengder data så har jeg vel støtt borti de fleste problemer som er å oppdrive.

 

Litt komisk egentlig, har brukt postgresql hele veien og den har vokst med oppgaven. Til å begynne med trengte vi en gratis ACID compliant database, men ikke noe særlig ytelse. Det ble dekket av postgres 7.4.

 

Etterhvert vokste firmaet som bruker systemet vårt og vi oppgraderte etterhvert til 8.1 som ga et vanvittig hopp i ytelse. (Sammen med hardware oppgradering som ble gjort samtidig snakker vi om 2-3x høyere ytelse).

 

Situasjonen vi er i nå er at 6 disker i raid10 på postgres 8.1 ikke var kjappe nok. Da var postgres 8.3 akkurat kommet ut med... ja, du gjettet det, en oppdatering som økte skrive-ytelsen til databasen med 2-10x...

 

Vi gjorde en oppgradering av hardware og oppgraderte til postgres 8.3, noe som igjen ga oss en 2-3x høyere ytelse... Vi har hele tiden kjørt på linux, loaden gikk fra 1,5-2,5 til 0,25-0,5. Rimelig merkbart kan du si.

[kaffenils]

Alltid kjekt når nye versjoner ikke bare har nye features, men også bedre ytelse

 

Har selv jobbet med SQL Server fra versjon 7.0. Kjørte på 32bits systemer frem til SQL Server 2005 pga. ekstrem pris på 64 bits versjonen av SQL Server 2000. Det var deilig å kunne dytte inn 16GB RAM i et system som var utsultet på minne og som drev diskene til vannvidd

 

Kall meg sær, men jeg synes faktisk databasedesign og drift er interessant. Klarer ikke helt å se på det som et nødvendig onde. Synes det er er mer interessant enn GUI design.

 

Har du både transaksjonslogg og databasefiler på ett RAID10 volum? Jeg har alltid hatt data og logg på separate RAID10 volumer for databaser med høy skriveaktivitet.

[blackbrrd]

Neida, kjører ett volum med 2 disker i raid1 for loggfilene og 6 disker i raid10 for restene av dataene.

 

Det er forresten gjort tester av postgresql hvor forskjellen på det oppsettet og 8 disker i raid10 var minimal.

 

Vi kjører som sagt linux, så vi byttet til 64bits operativsystem så snart opteron 246 kom ut, så vi har alltid hatt optionen med å dytte i så mye ram som det var plass til i serveren.

 

Databasedesign er veldig mye mer ryddig og klart enn gui/applikasjonsdesign. Det finnes jo selvfølgelig unntak, f.eks når du finner ut at du må dobbeltlagre data for å få ting til å gå rundt.

[kaffenils]

Takker for svar. Mye bra info her

Her er en artikkel som gir deg enda mer informasjon om transaksjonsloggen. Mulig det er litt utenfor hva du spør om, men det kan være nyttig for å få en bedre forståelse av transaksjoner.