Zähler erhöhen
Micha Wienand
Wenn ich einen Zähler in einer Tabelle erhöhen möchte, muß ich dann erst den
Wert auslesen, dann erhöhen und wieder zurück schreiben, oder existiert
hierfür ein SQL-Befehl?
Vielen Dank, Micha.
Tanja Dunker
Du kannst einfach ein UPDATE-Statement verwenden, um den Zähler zu erhöhen.
z.B.:
UPDATE Tabelle SET Zähler = Zähler +1
Gruss
Tanja
"Micha Wienand" <micha-...@ngi.de> schrieb im Newsbeitrag
news:O3VNp0yh...@TK2MSFTNGP09.phx.gbl...
Jürgen Beck
falls der UPDATE-Befehl zusätzlich noch mit anderen Befehlen im selben
Kontext ausgeführt werden soll, so dass bspw. vorher oder hinterher mit
diesem Zähler gearbeitet wird, so sollte man unbedingt diese Befehle in eine
gemeinsame Transaktion mit der Isolationsstufe SERIALIZABLE einbinden. Aber
wirklich nur die notwendigen Befehle, da es sonst zu anderen
"Herausforderungen" im Bereich Sperren kommen kann. ;-)
Jürgen Beck
MCSD.NET, MCDBA, MCT
www.Juergen-Beck.de
"Tanja Dunker" <tanja....@hs.fzk.de> schrieb im Newsbeitrag
news:O2VQbHzh...@tk2msftngp13.phx.gbl...
Elmar Boye
Micha Wienand <micha-...@ngi.de> schrieb ...
> Wenn ich einen Zähler in einer Tabelle erhöhen möchte, muß ich dann
> erst den Wert auslesen, dann erhöhen und wieder zurück schreiben,
> oder existiert hierfür ein SQL-Befehl?
Hier mal eine Antwort die ich andernorts - da fehlt jetzt etwas Kontext
bezüglich Isolationsstufen - in Verbindung mit selbstzuerhöhenden
Zählern gegeben habe und ein Beispielskript unten dran.
Variante 2 wäre eine die mit einem Befehl auskäme (siehe aber
weiter im Text):
Variante 1 (spGetSequence1) ist die Standardvariante mit UPDATE, SELECT...
Sie eröffnet eine Transaktion, wenn das nicht bereits der Aufrufer getan
hat, und gibt (vereinfachte) Fehlermeldungen zurück, wenn was schief geht.
Die anderen beiden sind Variationen davon:
Variante 2 (spGetSequence2) macht sich eine proprietäre Syntax
vom SQL Server zu nutze, um eine Variable innerhalb eines UPDATE
zu setzen. Die SQL Server Entwickler raten davon ab, und für die
paar gesparten Millisekunden sollte mans IMHO auch nicht verwenden.
Variante 3 (spGetSequence3) arbeitet mit vorherigem SELECT und anschliessendem
UPDATE. Sie fügt ggf. auch eine neuen Nummernkreis ein. Vor allem interessant
wegen der erforderlichen Sperrhinweise, um die normalerweise gesetzte
Standard-Isolationstufe ReadCommitted hochzusetzen. Durchweg auf
Serializable zu arbeiten bremst den SQL Server in der Regel aus,
und proviziert auch mehr Deadlocks.
Gruss
Elmar
/* -- Cleanup
DROP PROCEDURE dbo.spGetSequence1
DROP PROCEDURE dbo.spGetSequence2
DROP PROCEDURE dbo.spGetSequence3
DROP TABLE dbo.Sequences
DROP TABLE dbo.SequencesTest
*/
CREATE TABLE dbo.Sequences
(
ID int NOT NULL
CONSTRAINT PK_Sequences PRIMARY KEY,
Value int NOT NULL
CONSTRAINT DF_Sequences_Value DEFAULT(0)
)
GO
INSERT INTO Sequences( ID ) VALUES ( 4711 )
INSERT INTO Sequences( ID, Value ) VALUES ( 4712, 1000 )
-- Test Overflow
INSERT INTO Sequences( ID, Value ) VALUES ( 4713, 2147483647 )
GO
CREATE PROCEDURE dbo.spGetSequence1
(
@ID int, -- Zähler ID
@Value int OUTPUT, -- Gelieferter Wert
@Increment int = 1 -- Inkrement
)
-- Standardvariante UPDATE ... SELECT
AS
SET NOCOUNT ON
DECLARE @InTransaction int
DECLARE @Err int
DECLARE @Rows int
IF @ID IS NULL OR @ID <= 0
BEGIN
RAISERROR('Kein gültiger Wert für ID angegeben', 16, 1)
RETURN 1
END
IF @Increment IS NULL OR @Increment = 0
BEGIN
RAISERROR('Kein gültiges Inkrement angegeben', 16, 1)
RETURN 1
END
-- Eine neue Transaktion beginnen, wenn nicht bereits eröffnet
SET @InTransaction = @@TRANCOUNT
IF @InTransaction = 0
BEGIN TRAN
UPDATE Sequences
SET Value = Value + @Increment
WHERE ID = @ID
SELECT @Rows = @@ROWCOUNT, @Err = @@Error
IF @Rows = 0 OR @Err <> 0 GOTO ErrExit
SELECT @Value = Value
FROM dbo.Sequences
WHERE ID = @ID
SELECT @Rows = @@ROWCOUNT, @Err = @@Error
IF @Rows = 0 OR @Err <> 0 GOTO ErrExit
IF @InTransaction = 0
COMMIT TRAN
RETURN 0
ErrExit:
RAISERROR('Fehler beim Abruf von Sequence %d', 16, 2, @ID)
ROLLBACK TRAN
RETURN 1
GO
CREATE PROCEDURE dbo.spGetSequence2
(
@ID int, -- Zähler ID
@Value int OUTPUT, -- Gelieferter Wert
@Increment int = 1 -- Inkrement
)
-- Die proprietäre Kurzvariante mit Variablen im UPDATE
-- Bringt kaum was ausser möglicher Inkompatibilität...
AS
SET NOCOUNT ON
DECLARE @InTransaction int
DECLARE @Err int
DECLARE @Rows int
IF @ID IS NULL OR @ID <= 0
BEGIN
RAISERROR('Kein gültiger Wert für ID angegeben', 16, 1)
RETURN 1
END
IF @Increment IS NULL OR @Increment = 0
BEGIN
RAISERROR('Kein gültiges Inkrement angegeben', 16, 1)
RETURN 1
END
-- Eine neue Transaktion beginnen, wenn nicht bereits eröffnet
SET @InTransaction = @@TRANCOUNT
IF @InTransaction = 0
BEGIN TRAN
UPDATE dbo.Sequences
SET @Value = Value + @Increment,
Value = Value + @Increment
WHERE ID = @ID
SELECT @Rows = @@ROWCOUNT, @Err = @@Error
IF @Rows = 0 OR @Err <> 0 GOTO ErrExit
IF @InTransaction = 0
COMMIT TRAN
RETURN 0
ErrExit:
RAISERROR('Fehler beim Abruf von Sequence %d', 16, 2, @ID)
ROLLBACK TRAN
RETURN 1
GO
CREATE PROCEDURE dbo.spGetSequence3
(
@ID int, -- Zähler ID
@Value int OUTPUT, -- Gelieferter Wert
@Increment int = 1 -- Inkrement
)
-- Variante mit SELECT ... UPDATE
-- Falls automatisch neue Sequenzen erzeugt werden sollen,
-- oder weitere Prüfungen notwendig sind.
AS
SET NOCOUNT ON
DECLARE @InTransaction int
DECLARE @Err int
DECLARE @Rows int
IF @ID IS NULL OR @ID <= 0
BEGIN
RAISERROR('Kein gültiger Wert für ID angegeben', 16, 1)
RETURN 1
END
IF @Increment IS NULL OR @Increment = 0
BEGIN
RAISERROR('Kein gültiges Inkrement angegeben', 16, 1)
RETURN 1
END
-- Eine neue Transaktion beginnen, wenn nicht bereits eröffnet
SET @InTransaction = @@TRANCOUNT
IF @InTransaction = 0
BEGIN TRAN
-- Erfordert HOLDLOCK (SERIALIZABLE), wenn neue Sequenzen
-- via INSERT erzeugt werden sollen, sonst reicht UPDLOCK
-- Bei nur HOLDLOCK kann es zu einem DEADLOCK (1205) kommen!
SELECT @Value = Value
FROM dbo.Sequences WITH (HOLDLOCK, UPDLOCK)
WHERE ID = @ID
SELECT @Rows = @@ROWCOUNT, @Err = @@Error
IF @Err <> 0 GOTO ErrExit
IF @Rows = 0
BEGIN
SET @Value = @Increment
INSERT INTO dbo.Sequences (ID, Value)
VALUES (@ID, @Value)
SELECT @Err = @@Error
END ELSE BEGIN
SET @Value = @Value + @Increment
SELECT @Err = @@Error
END
IF @Err <> 0 GOTO ErrExit
UPDATE dbo.Sequences
SET Value = @Value
WHERE ID = @ID
SELECT @Rows = @@ROWCOUNT, @Err = @@Error
IF @Rows = 0 OR @Err <> 0 GOTO ErrExit
IF @InTransaction = 0
COMMIT TRAN
RETURN 0
ErrExit:
RAISERROR('Fehler beim Abruf von Sequence %d', 16, 2, @ID)
ROLLBACK TRAN
RETURN 1
GO
-- Tabelle für Test
CREATE TABLE dbo.SequencesTest (
ID int NOT NULL,
Value int NOT NULL,
CONSTRAINT PK_SequencesTest PRIMARY KEY CLUSTERED (ID, value)
)
GO
/*
-- Testcode für Query Analyzer mit einer Schleife
-- in mehrere Sitzungen kopieren für Konkurrenztests
SET NOCOUNT ON
DECLARE @nIndex int
DECLARE @rc int
DECLARE @ID int
DECLARE @Value int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @ID = 4711 -- ID für Zähler
SELECT @nIndex = 0, @StartTime = GETDATE()
WHILE @nIndex < 10000
BEGIN
SELECT @nIndex = @nIndex + 1
-- Für spGetSequence3 auskommentieren zum Simulieren neuer ID's
-- SET @ID = (@nIndex % 10) + 1
EXEC @rc = dbo.spGetSequence1 @ID, @Value OUTPUT --, 3
IF @rc = 0
BEGIN
IF (@nIndex % 100) = 1 PRINT @Value
-- für Misstrauische, um zu kontrollieren ob eindeutig...
INSERT INTO SequencesTest(ID, Value) VALUES (@ID, @Value)
IF @@ERROR <> 0
BEGIN
RAISERROR('Doppelte Sequenz: %d', 16, 1, @Value)
BREAK
END
END ELSE BEGIN
BREAK
END
-- Auskommentieren für Simulation weiterer Aktionen
-- WAITFOR DELAY '00:00:00.100'
END
SET @EndTime = GETDATE()
SELECT @Value AS LastValue,
DATEDIFF(ms, @StartTime, @EndTime) AS ms
*/