Ändringshantering i SQL Server
Förord
En vanlig fråga man ser på forum och diskussionsgrupper om SQL Server handlar om hur man gör för att hantera att data ändrats i en tabell. Ibland vill man bara kunna konstatera att det har förändrats, medan man en annan gång kanske dessutom vill kunna avgöra när den ändrades, hur många gånger det skett eller annat. Denna artikel ska ta upp några sätt att hantera detta. Artikeln finns även publicerad på Hedgate.NET tillsammans med andra SQL Server-relaterade artiklarInnehåll
»»
»
»
»
»
»
Behov och funktionalitet
Först och främst måste man bestämma vilket behov man har. Vilken datatyp man ska välja och vilket sätt man använder denna på beror på behovet, samt hur man vill bygga sin funktionalitet. Det enklaste, och förmodligen vanligaste, behovet är att man vill veta om datan har förändrats sedan man senast kontrollerade den. På så sätt kan man se till att olika användare inte skriver över varandras information. Script 1 visar ett exempel på hur det kan bli fel om man inte hanterar detta. Man kan förstås lösa detta genom att arbeta med s k pessimistic concurrency (dvs datan låses så länge någon läser den och lämnas inte åter förrän de uppdaterat eller 'släppt' den), men ofta vill man inte låsa data på det sättet utan istället arbeta med optimistic concurrency (dvs man låser ej datan utan kontrollerar istället när den ska uppdateras att den ej ändrats av någon annan). Detta går att göra på flera olika sätt, och det enklaste sättet att göra det på är också det mest automatiska. Det tar vi till sist. Istället börjar vi med lite mer manuella varianter.
-- Script 1
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'Messages')
BEGIN
DROP TABLE Messages
END
CREATE TABLE Messages
(
MessageId int NOT NULL
, Title varchar(50) NOT NULL
, Body varchar(255) NOT NULL
)
GO
INSERT INTO Messages (MessageId, Title, Body) VALUES (1,null Ett meddelande',null En meddelandetext')
-- Användare A hämtar ut datan
DECLARE @bodyA varchar(255)
SELECT @bodyA = Body FROM Messages WHERE MessageId = 1
-- Användare B hämtar också ut datan
DECLARE @bodyB varchar(255)
SELECT @bodyB = Body FROM Messages WHERE MessageId = 1
-- Efter att ha ändrat texten lite uppdaterar användare A med sin nya data
SET @bodyA = 'En annan text, skriven av användare A'
UPDATE Messages SET Body = @bodyA
-- Strax efter sparar användare B sin ändrade version av datan,
-- och skriver därmed över den ändring som användare A gjort
SET @bodyB = 'En tredje text, skriven av användare B'
UPDATE Messages SET Body = @bodyB
SELECT * FROM Messages
SET NOCOUNT OFF
Senast ändrad
En vanlig variant som man ofta ser är att man använder sig av ett datumfält, i vilket man hela tiden lagrar tidpunkten då datan på en rad ändras (med ändras menar jag både INSERT- och UPDATE-satser). Antingen sköter man det manuellt i koden i den applikation som uppdaterar databasen, eller kan man sköta det genom triggers i databasen. Script 2 visar ett exempel på det senare eftersom jag själv i de flesta situationer skulle välja det, huvudsakligen för att jag som DBA alltid vill se till att informationen i databasen är korrekt och uppdaterad. Låter man den uppdaterande applikationen ta hand om att ändra datumfältet kan man inte vara säker på att andra applikationer som kan komma att använda databasen hanterar detta.
-- Script 2: Demonstration av trigger för att uppdatera senast ändrat-kolumn
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'Messages')
BEGIN
DROP TABLE Messages
END
CREATE TABLE Messages
(
MessageId int NOT NULL CONSTRAINT pkMessages PRIMARY KEY CLUSTERED
, Title varchar(50) NOT NULL
, Body varchar(255) NOT NULL
, ChangeDate datetime NOT NULL DEFAULT GETDATE()
)
GO
CREATE TRIGGER TrU_Messages
ON Messages
FOR UPDATE
AS
BEGIN
UPDATE Messages SET ChangeDate = GETDATE()
FROM Messages m
INNER JOIN INSERTED i
ON m.MessageId = i.MessageId
END
GO
SET NOCOUNT ON
INSERT INTO Messages (MessageId, Title, Body) VALUES (1,null Ett meddelande',null En meddelandetext')
SELECT * FROM Messages
GO
PRINT 'Väntar 10 sekunder...'
GO
WAITFOR DELAY '00:00:10'
UPDATE Messages SET Body = 'En annan text' WHERE MessageId = 1
SELECT * FROM Messages
SET NOCOUNT OFF
Timestamp
Med triggern i Script 2 kan vi nu hålla koll på när en rad senast ändrades. Med den informationen kan vi nu se till att användare B inte kan skriva över den information som användare A har ändrat. Det kan antingen göras manuellt i applikationen som ändringarna görs i, eller så kan vi skapa en procedur för att köra uppdateringarna genom vilken testar det åt användaren automatiskt. Det är också ett bra sätt att som DBA hålla koll på sin data: låt aldrig användarna ändra direkt i tabellerna utan 'styr' dem genom att endast ge dem rättighet till de procedurer du tillhandahåller för dem. Script 3 skapar en procedur för att uppdatera Messages, men den uppdaterar endast om datan ej förändrats sedan man hämtade ut den. Denna teknik kallas för timestamp (datumstämpel). Genom att man alltid hämtar ut den datumstämpel som datan har då man gör en SELECT på den kan man när man försöker uppdatera den kontrollera om datan har ändrats sedan man hämtade den, genom att se om den har en ny datumstämpel. Om raden har en annan datumstämpel än den hade då man hämtade ut den får man hämta datan på nytt (inkl den nya datumstämpel som gäller nu), göra om ändringen om man fortfarande vill det samt försöka uppdatera på nytt.
-- Script 3
CREATE PROCEDURE MessagesUpd
@messageId int
, @body varchar(255)
, @retrieveDate datetime
AS
BEGIN
SET NOCOUNT ON
UPDATE Messages SET Body = @body
WHERE MessageId = @messageId
AND ChangeDate = @retrieveDate
IF (@@rowcount = 0)
BEGIN
PRINT 'Ett fel har inträffat! Datan som ska uppdateras har ändrats efter att den hämtades ut.'
END
SET NOCOUNT OFF
END
DECLARE @body varchar(255), @retrieveDate datetime, @otherDate datetime
SELECT @body = Body, @retrieveDate = ChangeDate
FROM Messages
WHERE MessageId = 1
SET @otherDate = DATEADD(d, 1, @retrieveDate)
SET @body = 'En ny text'
-- Detta kommer ej fungera
EXEC MessagesUpd 1, @body, @otherDate
SELECT * FROM Messages
-- Detta fungerar
EXEC MessagesUpd 1, @body, @retrieveDate
SELECT * FROM Messages
En enklare stämpel
Om man inte har behov av att se när datan senast ändrades utan istället hur många gånger den förändrats (versionshantering), samt fortfarande vill skydda datan från att skrivas över av olika användare kan man använda sig av en vanlig integer som stämpel. När datan först läggs till får den värdet 0 eller 1, och därefter ökar man detta värde med 1 i triggern varje gång datan uppdateras. För att uppdatera datan måste man skicka in den versionsstämpel som raden hade när man hämtade ut den, och i proceduren kontrolleras att den ej fått en ny versionsstämpel efter det. Effekten blir densamma som med en datumstämpel ovan, men man får en annan extra effekt i att man kan versionshantera datan istället för att hålla koll på när den senast ändrats. Det är bara frågan om vilket behov man har. Är man riktigt noggrann kan man även tänka på att en integer tar mindre plats att lagra än en datetime och ger därmed en minimal fördel prestandamässigt.
Rowversion
Ofta har man dock ej varken behov av att veta när datan senast ändrades eller hur många gånger den förändrats. Det enda behov man har är istället att se till att användarna inte skriver över varandras data. Det enklaste sättet att hantera detta är att utnyttja den funktionalitet som SQL Server tillhandahåller för detta, nämligen datatypen rowversion. Denna har tidigare tyvärr kallats just timestamp i SQL Server, vilket är olyckligt då den ej är alls samma sak som den timestamp som beskrivs i SQL-standarden. Att den ändå hette timestamp beror på att den fanns i SQL Server innan man definierade den i standarden. För bakåtkompatibilitetens skull kan man fortfarande använda namnet timestamp på den, men precis som alla andra saker som endast finns kvar pga bakåtkompatibilitet rekommenderar Microsoft att man använder namnet rowversion istället då definitionen av timestamp kan komma att ändras till ANSI-definitionen (vilket för övrigt är samma sak som en datetime i SQL Server) i senare versioner. En rowversion i SQL Server är egentligen en binary(8) (om den ej tillåter null, varbinary(8) om den gör det), vilken automatiskt får ett nytt värde varje gång en rad i den tabell man skapat den för uppdateras (inkl läggs till). Det kan endast finnas en kolumn av typen rowversion per tabell, och man kan aldrig manuellt uppdatera den med ett specifikt värde utan den uppdaters som sagt automatiskt av SQL Server.
Precis som med en datumstämpel eller ett siffervärde hämtar man i sin SELECT-sats ut den rowversion som finns på raden när man läser datan. När man sedan ska uppdatera jämför man den rowversion som finns på raden just då med den man tidigare hämtat ut. Överensstämmer de ej måste användaren hämta datan igen och ändra på nytt.
Med en rowversion i en tabell kan man även använda SQL Servers inbyggda funktionalitet för optimistic concurrency. Det innebär att man kan läsa ut data till ett recordset i en klientapplikation och hantera detta lokalt i exempelvis en datagrid. När man sedan utfört de förändringar man vill göra och ska uppdatera så kopplar man recordset-objektet till databasen och kör Update eller UpdateBatch. Då kontroller SQL Server automatiskt om raden/-erna har ändrats genom att jämföra rowversion-kolumnen. Man kan dock arbeta på detta sätt även utan en rowversion-kolumn, men då måste SQL Server jämföra alla kolumnerna på raden jämfört med de originalvärden man hade recordset-objektet innan man ändrade för att kontrollera att man inte skriver över någon annans förändringar.
0 Kommentarer