Arbeta med transaktionshantering i lagrade procedurer
Förord
Att skriva en lagrad procedur är det många som kan och behärskar. Men när man precis börjat skriva dessa så kanske man inte tänker på vad som händer om något går fel och hur man skall åtgärda det på ett bra sätt. Jag tänkte förklara hur du använder SQL-serverns enorma potential att se till att du har såväl hängslen som livremmar när du arbetar.
Men vad är transaktioner egentligen?
Som vanligt skriver man sin insert, update eller delete med en sedvanlig procedur och förmodar att allting är frid och fröjd - men är det verkligen så - Har vi inte missat något här? Om du inte arbetat med transaktioner tidigare så är det hög tid att börja tänka på detta nu. En transaktion kan och går helt enkelt fel ibland och hur fångar du då detta på ett bra sätt? Troligast är kanske att du använder dig av en felhantering i kanske VB eller ASP men skadan är redan ett faktum. Då är frågan, hur skyddar vi oss mot detta?
En vanlig dag i sql-servern
Vi säger att vi skall göra en update, en update som kanske ställer till det lite för oss. Eller rättare sagt vi säger att vi ställer till det mycket - vi skall ändra valutan i vårt prisregister.UPDATE itemstock
SET price = price * 1.3
Så långt är det väl kanske inga problem trodde vi - men vad händer här? Varenda post uppdaterades och det hade vi inte tänkt oss - det var ju bara vissa poster som skulle uppdateras, de som hade valutan SEK. Det är här vi hade önskat att vi hade transaktionshanteringen för då hade vi bara behövt backa allting (rollback) och sedan lagt till vår WHERE-sats för att slippa denna tragedi.
BEGIN TRAN
Det finns två kommandon att använda som man innesluter sitt kommando med, BEGIN TRAN samt COMMIT TRAN. Begin som ni förstår berättar att nu kommer något hända så håll reda på allting som görs från nu och framåt. Med kommandot COMMIT TRAN så säger vi att allting som gjorts sedan jag skrev BEGIN TRAN är ok och kan däremot permanentas.BEGIN TRAN
UPDATE itemstock
SET price = price * 1.3
WHERE currency = 'SEK'
COMMIT TRAN
Något gick fel, jag behöver backa
Som du nu säkert listat ut så kan vi faktiskt välja att inte ens skriva COMMIT TRAN vilket kommer resultera i att ändringen körts, men kommer inte SUBMITTAS förrän sql-servern får kommandot. Då är frågan hur vi gör för att backa hela denna proceduren. Du använder kommandot ROLLBACK TRAN. Rollback är precis som det låter - man rullar tillbaks bandet och börjar från början. Detta kan du använda i stort sett på vad du vill för att försäkra dig om att inget skall gå snett. Vi tar ett exempel och med hjälp av funktionen @@ROWCOUNT som returnerar hur många poster som påverkades vid våran körning för att bestämma om vi skall backa eller inte.
Scenariot är i detta fall kanske att om det finns mer än 5 poster som påverkas så skall vi backa - i annat fall accepterar vi denna ändring och godkänner det.
BEGIN TRAN
UPDATE itemstock
SET price = price * 1.3
WHERE currency = 'SEK'
UPDATE itemstock
SET price = price * 1.1
WHERE currency = 'USD'
IF @@ROWCOUNT < 5
COMMIT TRAN
ELSE
ROLLBACK TRAN
Som du ser här nu så kan vi alltså ta hjälp av sql-serverns inbyggda hantering för att rulla tillbaks en rutin eller en sql-sats som vi felaktigt kört. Vi kan till och med kolla om vi får något fel vid en insert och ta hänsyn till det redan i sql-satsen. En insert kan ju exempelvis addera en dublett - eller kanske att man skickar in fel värden och parametrar. Med funktionen @@ERROR kan du se om det blir fel. @@ERROR returnerar 0 om allt är ok, men <> 0 om ett fel uppstår.
I detta exempel adderar jag en post där fältet id är ett unikt fält och det id jag adderar redan finns. Vi får ett fel och kontrollerar felmeddelandet genom att skicka tillbaks 10 istället för något i stil med -123897612348. Så när du i ditt program får 10 som felkod, ja då vet du vad som gäller och kan ta höjd för detta.
Create Proc TransactionDemo
AS
BEGIN TRAN
INSERT INTO [kunder]([id],
[efternamn],
[foernamn],
[telefon],
[kontaktid])
VALUES ('198321',
'Johansson',
'Pelle',
'+46 70-616 80 90',
1)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END
COMMIT TRAN
GO
Vi avslutar med ett asp-exempel
För att göra det lite tydligare vad som händer här tänkte jag skriva en liten asp-rutin som kontrollerar just felet vi får tillbaks så du förstår hur jag menar.
Set con = Server.CreateObject("adodb.connection")
con.Open Application("dsn")
On Error Resume Next
con.Execute("TransactionDemo ''198321','Pelle','Johansson','+46 70-6161 80 90','1'" )
If Err = 10 Then
Response.Write "Du får inte addera dubletter"
Else
Response.Write "Transaktionen adderad"
End If
On Error Goto 0
För att komplettera vad jag gjorde ovan så sa jag först åt asp att alla gå vidare oavsett om något fel uppstår. Därefter kollade jag om jag fick tillbaks något fel och slutligen aktiverade felhanteringen igen.
Inte bara små transaktioner
Om du tänker dig scenariot att vi skriver ett program som kallar en procedur på server 1. Den i sin tur läser data från en annan databas på server 2 och slutligen skickar en tredje transaktion över atlanten till server 3. När Server 3 svarar skall server 1 lägga in ett nytt värde på raden. Men något går fel, transaktionen på server 3 nås inte och vi har redan skrivit på de två andra servrarnas databas - klarar verkligen en ROLLBACK att lösa detta. Jadå, inga problem. Du kan skriva oerhört komplexa uppdateringar med kanske ett 20-tal updates, insert, delete, flytta data och allt - smäller det någonstans, ja då är rollback din räddning. Underskatta aldrig den rutinen.Med detta avslutar jag denna artikeln och hoppas detta gav lite klarhet i BEGIN TRAN, COMMIT TRAN samt ROLLBACK TRAN. Om du tycker något saknas eller bör kompletteras på artikeln, skicka gärna ett iMail och påpeka det.
Nils-G Nordmark
Don't try this at work! Starta en transaktion och uppdatera=lås alla poster som berörs exklusivt=ingen annan kommer åt dem! Ett lysande exempel på hur transaktioner inte får användas. Men detta är bara ett exempel, och det borde tydliggöras...