none
GetNextNumber

    Frage

  • Hallo Zusammen,

    ich möchte für die Datenablage auf dem SQL-Server meine bisherige Funktion GetNextNumber (für VisualFoxpro-Tabellen) anpassen.
    Diese Funktion liefert mir für Angebote, Lieferscheine, Rechnungen, ... usw. eine eindeutige und unterbrechungsfreie Nummer für die jeweilige Belegart zurück. Alle Nummern (zur Zeit 15 verschiedene) sind dabei in einer einzigen Tabelle satzweise mit Nummern-Art hinterlegt; nur der Entwickler kann diese dort auf neue Startwerte setzen.
    In der (VisualFoxpro-)Funktion arbeite ich mit RLOCK/UNLOCK um die obigen Vorgaben sicher zu erreichen.

    Ich würde auf dem SQL-Server dafür eine StoredProcedure einsetzen mit Übergabe der Nummern-Art. Optimal wäre wenn die Lösung nicht nur auf MS-SQL-Server (zb. auch auf PostgreSQL) laufen würde.

    Wie würdet Ihr hier vorgehen?

    --

    Hans-Peter Grözinger

    • Bearbeitet groezi Mittwoch, 27. Dezember 2017 13:40
    Mittwoch, 27. Dezember 2017 11:07

Antworten

  • Hallo Hans-Peter,

    "Bisher speichere ich alle Nummern (Klassifizierungen) in einer Tabelle zusammen mit vielen anderen Auswahlen"

    Da habe ich mich wohl falsch ausgedrückt. Ich meinte, dass Du Angebote, Lieferscheine und Rechnungen trennst. ist aber eigentlich für die von Dir angerissene Thematik eher irrelevant.

    Tatsächlich jedoch kommt der Speicherung in EINER Tabelle eine wesentlich höhere Bedeutung zu, wenn Du für diese "Systemtabelle" für die verschiedensten Möglichkeiten verwendest.

    "... kann ich nur ein Sequenz-Objekt je Tabelle einrichten..."

    Wenn Du die Sequenz in einer Tabelle als "DEFAULT" verwenden möchtest, ist das richtig. Du kannst die Sequence-Objekte aber auch codebasiert verwenden wie das nachfolgende Beispiel zeigt:

    CREATE SEQUENCE dbo.Rechnungen
    START WITH 1
    INCREMENT BY 1;
    GO
    
    CREATE TABLE dbo.tbl_Rechnungen
    (
    	Id	INT			NOT NULL,
    	C1	CHAR(100)	NOT NULL,
    
    	CONSTRAINT pk_Rechnungen PRIMARY KEY CLUSTERED (Id)
    );
    GO
    
    BEGIN TRANSACTION;
    GO
    	DECLARE	@NextNumber	INT = NEXT VALUE FOR dbo.Rechnungen;
    
    	INSERT INTO dbo.tbl_Lieferscheine (Id, C1)
    	VALUES (@NextNumber, 'Das ist der 1. Lieferschein');
    	GO
    
    	-- Welche Daten sind in dbo.tbl_Lieferscheine?
    	SELECT * FROM dbo.tbl_Lieferscheine;
    COMMIT TRANSACTION;
    GO

    Der - charmante - Vorteil dieser Lösung liegt darin, dass Du dennoch konkurrierende Workloads betreiben kannst. Der Nachteil jedoch ist, dass bei einem ROLLBACK die Nummer nicht wieder "ins Töpfchen" wandert und nicht mehr wiederverwendet werden kann.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 27. Dezember 2017 13:51
  • Hallo Baldur,

    Hier der aktuelle Stand meiner StoredProcedure:

    BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; DECLARE @cError Varchar (100); DECLARE @cNumber Char (70) = '1'; DECLARE @cTransactionName CHAR( 50 );

        -- Zufälligen Transaktions-Namen setzen
        SET @cTransactionName = NEWID();

    BEGIN TRY -- Wenn der Wert inkrementiert werden soll ... IF @lDontSaveNewNumber = 0 BEGIN -- Benannte Transaktion starten BEGIN TRANSACTION @cTransactionName; -- Schlüssel inkrementieren und die aktuelle in cNumber zurückgeben UPDATE dbo.Sch SET @cNumber = Sch.Sch_Wert = CAST( ( CAST( Sch.Sch_Wert AS Int ) + 1 ) AS Char( 70 ) ), Sch.Sch_BeaDat = SYSDATETIME(), Sch.Sch_BeaBen = @cUser WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = @nKeyNumber AND Sch.Sch_SprSch = @nKeyLangauge; -- Alles OK => Benannte Transaktion bestätigen und damit abschließen COMMIT TRANSACTION @cTransactionName; END; -- Andernfalls ... ELSE BEGIN -- ... holen wir nur temporär die nächste Schlüssel-Nummer für den Probedruck. -- Beim entgültigen Druck schreiben wir die nächste Schlüssel-Nummer natürlich -- wieder zurück in die Schlüssel-Tabelle -- SQL-SELECT ausführen SELECT @cNumber = CAST( ( CAST( Sch.Sch_Wert AS Int ) + 1 ) AS Char( 70 ) ) FROM dbo.Sch WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = @nKeyNumber AND Sch.Sch_SprSch = @nKeyLangauge; -- Wenn es kein Ergebnis gibt, ... IF @@ROWCOUNT = 0 BEGIN -- ... dann hängen wir einen neuen Datensatz an. -- Dieser Fall tritt nur dann ein, wenn der Entwickler eine neue Belegart hinzufügt. -- In diesem Fall ist kein Anwender auf der Datenbank sodass wir keine Transaktion -- benötigen -- Neue Schlüssel-Nummer auf 0 setzen SET @cNumber = '0'; INSERT INTO dbo.Sch ( Sch_i, Sch_ErsDat, Sch_ErsBen, Sch_SchNr, Sch_SchTxt, Sch_SprSch, Sch_Wert, Sch_AkviKZ ) VALUES ( NEWID(), SYSDATETIME(), @cUser, @nKeyNumber, @cKeyText, @nKeyLangauge, @cNumber, 4095 ); END; END; -- Ergebnis in einen INTEGER umwandeln und den Prüf-Schlüssel-Nummer setzen SET @nNumber = CAST( @cNumber AS INTEGER ); SET @nCheckNumber = IIF( @nCheckNumber >= 0, @nCheckNumber, @nNumber ); -- Wenn die Werte-Prüfung einen gültigen Wert ergibt ... IF @nNumber <> @nCheckNumber -- ... geben wir -2 als Fehlerstatus zurück -- Das bedeutet, daß der Schlüsselwert in der Zwischenzeit schon von einem anderen -- Anwender geändert wurde. Dieser Fall tritt auf wenn ein Anwender einen Probedruck -- längere Zeit prüft bevor er den entgültigen Ausdruck durchführt. SET @nNumber = -2; -- Die Schlüssel-Nummer zurückgeben RETURN @nNumber; -- Fehler-Block END TRY BEGIN CATCH -- Es ist ein Fehler aufgetreten => Rollback der benannten Transaktion durchführen ROLLBACK TRANSACTION @cTransactionName; -- Fehlermeldung für ODBC-Treiber setzen und -1 zurückgeben SET @cError = N'Fehler beim Bestimmen der nächsten Nummer von >' + @cKeyText + N'<'; RAISERROR( @cError, 16, 1 ); RETURN -1; END CATCH END







    Montag, 1. Januar 2018 18:33

Alle Antworten

  • Ganz genauso;-).
    Der Unterschied ist lediglich, dass du für die Vorgänge Transaktionen verwenden musst.
    Transaktionen sperren automatisch die Daten vor weiteren Veränderungen und zusätzlich kannst du die Unterbrechungsfreiheit garantieren.
    Z.B. wird beim Druck des Lieferscheins die Transaktion eröffnet, alle Daten des Lieferscheines werden vervollständigt und eine LS-Nummer vergeben.
    Ist alles korrekt, wird die Transaktion committed und der nächste LS enthält die nächste Nummer.
    Tritt irgendwo ein Problem auf, machst du einen Rollback und auch die Vergabe der LS-Nummer wird zurückgesetzt als hätte sie nie stattgefunden.

    Während der Transaktion kann keine weitere LS-Nummer vergeben werden, da sie ja noch nicht abgeschlossen ist.

    PostgreSQL sollte dies allerdings ebenso bereits unterstützen.

    Mittwoch, 27. Dezember 2017 11:18
  • Hallo Groezi,

    ich vermute, dass alle Klassifizierungen in verschiedenen Tabellen gespeichert werden. So gibt es z. B. eine Tabelle für

    • Angebote -> AngebotsDetails
    • Lieferscheine -> LieferscheinDetails
    • Rechnungen -> RechnungsDetails

    Grundsätzlich steht Dir IDENTITY als fortlaufende Nummerierung automatisch zur Verfügung. Die Definition einer solchen Tabelle sieht wie folgt aus:

    -- Verwendung von Lieferscheinen
    CREATE TABLE dbo.tbl_Lieferscheine
    (
    	Id INT	NOT NULL	DEFAULT IDENTITY (1, 1),
    	C1 CHAR(100)	NOT NULL	DEFAULT 'Irgendein Test',
    
    	CONSTRAINT pk_Lieferscheine PRIMARY KEY CLUSTERED (Id)
    );

    Wenn Du jedoch die Nummerierung "kontrollieren" möchtest, gibt es seit SQL Server 2012 die Möglichkeit, mit Sequenzen zu arbeiten. Hierbei handelt es sich um ein ähnliches Verhalten. Der besondere Unterschied besteht jedoch darin, dass Du Sequenzen individuell "konfigurieren" und "zurücksetzen" kannst. Das folgende Beispiel zeigt die Verwendung von Sequenzen in der Tabelle dbo.tbl_Rechnungen

    -- Laufende Nummern für Rechnungen
    CREATE SEQUENCE dbo.Rechnungen
    AS INT
    START WITH 1
    INCREMENT BY 1;
    GO
    
    CREATE TABLE dbo.tbl_Rechnungen
    (
    	Id	INT			NOT NULL	DEFAULT NEXT VALUE FOR dbo.Rechnungen,
    	C1	CHAR(100)	NOT NULL	DEFAULT ('Noch ein Test'),
    
    	CONSTRAINT pk_Rechnungen PRIMARY KEY CLUSTERED (Id)
    );
    GO

    Beiden Varianten geht jedoch die Besonderheit voraus, dass eine - innerhalb einer Transaktion - generierte Nummer nicht mehr verwendet werden kann. Wird also die Transaktion zurückgerollt, ist die Nummer "verloren".

    Ich kenne aus vielen Fragen/Anregungen, dass dieses Verhalten nicht gewünscht ist. Ich persönlich halte es jedoch so, dass diese Nummer in der Regel ein Surrogatschlüssel ist und der Wert für die eigentliche Bewertung vollkommen irrelevant sein sollte.

    Solltest Du also tatsächlich auf eine "fortlaufende" Nummerierung bestehen (und damit das Konkurrenzverhalten deutlich einschränken), bleibt Dir nur die Möglichkeit, die Nummerierung innerhalb einer Transaktion zu ermitteln und die Tabelle dann während der gesamten Transaktion zu sperren. Es wäre nicht meine Wahl aber grundsätzlich ist das mit Hilfe von granularen Sperren ohne Weiteres möglich:

    CREATE TABLE dbo.ApplicationNumbers
    (
    	ApplicationType	NVARCHAR(64)	NOT NULL,
    	CurrentValue	INT				NOT NULL,
    
    	CONSTRAINT pk_ApplicationNumbers PRIMARY KEY CLUSTERED (ApplicationType)
    );
    GO
    
    INSERT INTO dbo.ApplicationNumbers
    VALUES (N'Rechnungen', 0), (N'Lieferscheine', 0);
    
    -- Verwendung von Lieferscheinen
    CREATE TABLE dbo.tbl_Lieferscheine
    (
    	Id	INT			NOT NULL,
    	C1	CHAR(100)	NOT NULL,
    
    	CONSTRAINT pk_Lieferscheine PRIMARY KEY CLUSTERED (Id)
    );
    
    CREATE TABLE dbo.tbl_Rechnungen
    (
    	Id	INT			NOT NULL,
    	C1	CHAR(100)	NOT NULL,
    
    	CONSTRAINT pk_Rechnungen PRIMARY KEY CLUSTERED (Id)
    );
    GO

    Das obige Skript erzeugt ein Beispielszenario, bei dem die fortlaufenden Nummern in einer Systemtabelle verwaltet werden. Wenn Du nun die Aktualisierungen durchführst, musst Du dich gut in den verschiedenen Isolationsstufen sowie den Sperrhinweisen auskenne, um einen funktionierenden Workload hinzubekommen.

    Das folgende Beispiel zeigt, wie innerhalb einer Transaktion die Nummer hochgezählt wird. Wichtig dabei ist jedoch, dass alles in EINER "User-Transaktion" abgewickelt wird (die Sperren werden bis zur Beendigung der Transaktion gehalten!

    BEGIN TRANSACTION;
    GO
    	DECLARE	@NextNumber	INT = 0;
    
    	-- Nächste Nummer für Rechnungen erhalten
    	UPDATE	dbo.ApplicationNumbers
    	SET		CurrentValue = CurrentValue + 1
    	WHERE	ApplicationType = N'Lieferscheine';
    
    	SELECT	@NextNumber = CurrentValue
    	FROM	dbo.ApplicationNumbers
    	WHERE	ApplicationType = N'Lieferscheine';
    
    	INSERT INTO dbo.tbl_Lieferscheine (Id, C1)
    	VALUES (@NextNumber, 'Das ist der 1. Lieferschein');
    	GO
    
    	-- Welche Ressourcen sind blockiert?
    	SELECT	CASE WHEN resource_type = N'OBJECT'
    				 THEN OBJECT_NAME(resource_associated_entity_id)
    				 ELSE CAST(resource_associated_entity_id AS NVARCHAR(100))
    			END,
    			*
    	FROM	sys.dm_tran_locks WHERE request_session_id = @@SPID;
    	GO
    
    	-- Welche Daten sind in dbo.tbl_Lieferscheine?
    	SELECT * FROM dbo.tbl_Lieferscheine;
    ROLLBACK TRANSACTION;
    GO

    Soweit sieht das alles ganz gut aus. Der Versuch einer weiteren Transaktion, die Daten aus der Verwaltungstabelle zu lesen, wird scheitern, da ein X-Lock nicht kompatibel ist mit einem S-Lock. Problematisch wird das Ganze jedoch dann, wenn sich der Kunde beschwert, dass die Auswahl von Daten so lange dauert. Dann gibt es zwei Möglichkeiten, diesen Problem zu beheben:

    • READ COMMITTED SNAPSHOT ISOLATION
    • READ UNCOMMITTED (NO LOCK)

    Leider wird - zu häufig - mit der Option NO LOCK gearbeitet. Genau dann fangen die Probleme an. Bei RCSI werden die VOR dem Beginn der Transaktion vorhandenen Werte in der Systemtabelle gelesen während NO LOCK den - noch nicht bestätigten - neuen Wert lesen kann. Beides ist ...!!!

    Die einzige Möglichkeit, hier überhaupt Abhilfe zu schaffen, wäre ein Lesen der Daten mit der Option (UPLOCK), da diese Sperre nicht kompatibel ist mit einem X-Lock. Mit RCSI kann ein S-Lock gesetzt werden, da ja die Daten aus dem Version Store gelesen werden. An NOLOCK würde ich erst gar nicht denken :)

    Hier mal ein paar Informationen, die Du dir in Ruhe zu Gemüte führen solltest.

    Mein - persönlicher - Vorschlag: Vergiss die durchgängige Nummerierung, wenn sie für die Applikation nicht von Bedeutung ist; es ist nur eine Zahl :)


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 27. Dezember 2017 12:57
  • Hallo Baldur,

    "Während der Transaktion kann keine weitere LS-Nummer vergeben werden, da sie ja noch nicht abgeschlossen ist."

    Ich wette dagegen! Wenn sich alle Systeme an einer Isolationsstufe halten, wird das klappen. Aber was passiert, wenn ein zweiter Prozess mit READ UNCOMMITTED aus der Systemtabelle liest? Was passiert, wenn mit Hilfe von RCSI gearbeitet wird, und die Reihenfolge (Systemtabelle aktualisieren -> Neuen Wert auslesen) in einem zweiten Prozess verdreht wird?

    Bei READ UNCOMMITTED wird der - noch nicht bestätigte - Wert gelesen, um 1 inkrementieren und der neue Datensatz wird eingetragen. Wenn dann T1 einen ROLLBACK macht, ist ein "Bruch" in der Nummerierung.

    Noch bescheidener wird es, wenn RCSI zum Einsatz kommt. Dann liest T2 den - vorherigen - Wert, zählt 1 hinzu und der INSERT schlägt mit einer Schlüsselverletzung fehl oder man hat die gleiche ID zwei Mal im System!


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 27. Dezember 2017 13:05
  • Hallo Uwe,

    erstmal Danke für die sehr ausführlichen Infos :-)

    > ich vermute, dass alle Klassifizierungen in verschiedenen Tabellen gespeichert werden.

    Bisher speichere ich alle Nummern (Klassifizierungen) in einer Tabelle zusammen mit vielen anderen Auswahlen wie Anrede, Kunden-Art, usw.. Der Grund dafür ist, dass ich für ein neues Merkmal keine weitere (Auswahl-)Tabelle anlegen muss. Ich bezeichne diese Tabelle als Schlüssel-Tabelle.

    > Wenn Du jedoch die Nummerierung "kontrollieren" möchtest, gibt es seit SQL Server 2012 die Möglichkeit, mit Sequenzen zu arbeiten.

    Das Sequenz-Objekt beim SQL-Server (auch vorhanden beim PostgreSQL) hatte ich als Möglichkeit in Betracht gezogen, jedoch nicht mehr weiter verfolgt. Nach meinem (bisherigen) Verständnis kann ich nur ein Sequenz-Objekt je Tabelle einrichten; das hilft mir deshalb für die Schlüssel-Tabelle leider nur für eine einzige Nummern-Art.

    > Solltest Du also tatsächlich auf eine "fortlaufende" Nummerierung bestehen ...

    Auch wenn im Umsatzsteueranwendungserlass 2010 steht:
    Eine lückenlose Abfolge der ausgestellten Rechnungsnummern ist nicht zwingend."

    möchte ich das für meine Programme zumindest für die Rechnungs-Nummern sicherstellen.

    Deine Schluss-Informationen werde ich mir "zu Gemüte" führen.

    --

    Hans-Peter Grözinger

    • Bearbeitet groezi Mittwoch, 27. Dezember 2017 13:32
    Mittwoch, 27. Dezember 2017 13:32
  • Hallo Hans-Peter,

    "Bisher speichere ich alle Nummern (Klassifizierungen) in einer Tabelle zusammen mit vielen anderen Auswahlen"

    Da habe ich mich wohl falsch ausgedrückt. Ich meinte, dass Du Angebote, Lieferscheine und Rechnungen trennst. ist aber eigentlich für die von Dir angerissene Thematik eher irrelevant.

    Tatsächlich jedoch kommt der Speicherung in EINER Tabelle eine wesentlich höhere Bedeutung zu, wenn Du für diese "Systemtabelle" für die verschiedensten Möglichkeiten verwendest.

    "... kann ich nur ein Sequenz-Objekt je Tabelle einrichten..."

    Wenn Du die Sequenz in einer Tabelle als "DEFAULT" verwenden möchtest, ist das richtig. Du kannst die Sequence-Objekte aber auch codebasiert verwenden wie das nachfolgende Beispiel zeigt:

    CREATE SEQUENCE dbo.Rechnungen
    START WITH 1
    INCREMENT BY 1;
    GO
    
    CREATE TABLE dbo.tbl_Rechnungen
    (
    	Id	INT			NOT NULL,
    	C1	CHAR(100)	NOT NULL,
    
    	CONSTRAINT pk_Rechnungen PRIMARY KEY CLUSTERED (Id)
    );
    GO
    
    BEGIN TRANSACTION;
    GO
    	DECLARE	@NextNumber	INT = NEXT VALUE FOR dbo.Rechnungen;
    
    	INSERT INTO dbo.tbl_Lieferscheine (Id, C1)
    	VALUES (@NextNumber, 'Das ist der 1. Lieferschein');
    	GO
    
    	-- Welche Daten sind in dbo.tbl_Lieferscheine?
    	SELECT * FROM dbo.tbl_Lieferscheine;
    COMMIT TRANSACTION;
    GO

    Der - charmante - Vorteil dieser Lösung liegt darin, dass Du dennoch konkurrierende Workloads betreiben kannst. Der Nachteil jedoch ist, dass bei einem ROLLBACK die Nummer nicht wieder "ins Töpfchen" wandert und nicht mehr wiederverwendet werden kann.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 27. Dezember 2017 13:51
  • Dies wiederspäche jeder Datenbanklogik und wäre ein schwerer Fehler.

    Ich kann zwar mittels Read uncommitted, Snapshot o.ä. einen ungültigen Wert lesen, aber durch die Updatesperre der offenen Transaktion darf die Datenbank keine weitere Änderung zulassen.
    Wenn sie es denn täte, könnte man sich auf keine einzige Transaktion und die Sicherheit derselben mehr verlassen.

    Das Verwenden von Transaktionen und den gegenseitigen Schutz (Datenkonsistenz) wäre in diesem Falle vollkommen unnütz.

    Auch wenn mit Snapshots gearbeitet wird, darf es nicht zu konkurierenden Updates kommen.
    Eine von beiden konkurierenden Updates müsste beim Commit auf jeden Fall auf einen Fehler laufen, wobei die ältere Transaktion, die einen Wert zuerst geändert hat, gewinnen müsste.

    Ich kenne dies auch von anderen Datenbanken, die Satzversionen unterstützen, dass beim Update einer geänderten Information dieser abgelehnt wird bzw. spätestens beim Commit ein Konflikt gemeldet wird. Der Commit kann nicht abgeschlossen werden und ich muss einen Rollback machen, da ich sonst an die neuen Informationen ja nicht drankomme.

    Bei Unique-Contraints habe ich noch nicht erlebt, unabhängig vom Isolation-Level, dass ich tatsächlich 2 identische Schlüssel bekommen habe.

    Was die ganzen Sperrmimiken angeht, so sollte man sich i.d.R. auf die Schutzmechanismen der Datenbank verlassen. Wenn ich also eine Rechnungsnummer geholt habe, so wird nur dieser Satz gesperrt, eine Nummer aus anderen Nummernkreisen sollte man trotzdem jederzeit holen können.

    Vorraussetzung ist natürlich das Zeilenweise sperren von Datensätzen.
    Hier kann ich diesbezüglich auch nichts gegenteiliges entdecken:
    https://msdn.microsoft.com/de-de/library/jj856598%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396#Lock_Basics

    Zusätzlich gilt ja immer die Regel, dass Transaktionen möglichst kurz und nahezu unterhalb der Wahrnehmungsschwelle des Users liegen sollten.

    Und was die sog. "Schmutzdaten" angeht, so ist das schon immer so gewesen, und das hat mit Transaktionen gar nichts zu tun, dass ich in der Anwendungsprogrammierung immer damit rechnen muss, dass die gerade angezeigten Daten ggf. schon nicht mehr aktuell sind. Deshalb gibt es ja Refresh-Funktionen z.B. für Bestandsabfragen, Auftragsübersichten u.s.w.

    Mittwoch, 27. Dezember 2017 13:57
  • Hallo Baldur,

    "Ich kann zwar mittels Read uncommitted, Snapshot o.ä. einen ungültigen Wert lesen, aber durch die Updatesperre der offenen Transaktion darf die Datenbank keine weitere Änderung zulassen.
    Wenn sie es denn täte, könnte man sich auf keine einzige Transaktion und die Sicherheit derselben mehr verlassen."

    Du hast natürlich vollkommen Recht, dass T2 keine Aktualisierung durchführen kann, solange ein X-Lock auf der Tabelle liegt. Dennoch ist - bei unsauberer Programmierung - diese Situation relativ leicht erreicht:

    Transaktion 1 (Ich habe zum Verständnis Zeiten eingetragen!):

    BEGIN TRANSACTION;
    -- 10:00:00
    	UPDATE	dbo.ApplicationNumbers
    	SET		CurrentValue = CurrentValue + 1
    	WHERE	ApplicationType = N'Rechnungen';
    
    -- 10:00:02
    	SELECT	@nextval = CurrentValue
    	FROM	dbo.ApplicationNumbers
    	WHERE	ApplicationType = N'Rechnungen';
    
    -- 10:00:04
    	INSERT INTO dbo.tbl_Rechnungen VALUES ('Test');
    ROLLBACK TRANSACTION
    GO

    Transaktion 2:

    BEGIN TRANSACTION;
    GO
    -- 10:00:01
    	SELECT	@nextval = CurrentValue + 1
    	FROM	dbo.ApplicationNumbers WITH (NOLOCK)
    	WHERE	AplicationType = N'Rechnungen'
    
    -- 10:00:03
    	INSERT INTO dbo.tbl_Rechnungen VALUES ('Test');
    
    -- 10:00:05
    	UPDATE	dbo.ApplicationNumbers
    	SET	CurrentValue = @nextval
    	WHERE	ApplicationType = N'Rechnungen';
    COMMIT TRANSACTION;
    GO

    Bei diesem Beispiel wird T1 um 10:00:00 den Wert um 1 erhöhen (Bsp: 5 + 1 = 6). Die Zeile ist - wegen des X-Lock gesperrt und in READ COMMITTED nicht von T2 gelesen werden. Der Programmierer von T2 hat aber mal gegoogled und findet NOLOCK richtig g..l :)

    Also kann T2 um 10:00:01 den - not Committed - Wert 6 ebenfalls lesen.

    Um 10:00:02 liest T1 diesen Wert aus (6) und um 10:00:03 trägt T2 den - unbestätigten Wert - in dbo.Rechnungen ein. Das ist möglich, da diese Tabelle ja weder exklusiv gesperrt ist oder bereits ein Datensatz mit ID = 6 vorhanden ist.

    Um 10:00:04 möchte T1 den Wert ebenfalls eintragen und wird mit einer Schlüsselverletzung abgebrochen. Der Wert in der Systemtabelle wird also wieder auf 5 zurück gesetzt.

    Was macht der nächste Versuch, einen Datensatz in dbo.Rechnungen einzutragen?

    Problem bei RCSI.

    Der Unterschied besteht darin, dass T2 kein "NOLOCK" verwenden muss und somit den "vorherigen" Committed value (5) liest. und 1 dazu zählt. Somit hat auch T2 den Wert 6 für die ID ermittelt und möchte sie abspeichern.

    T2 muss natürlich mit dem UPDATE auf die Systemtabelle warten, bis T1 die Sperre wieder aufgehoben hat!

    Hierbei wird T1 das Opfer. Der Unterschied besteht lediglich darin, dass T2 erfolgreich die aktuelle Nummer eintragen kann.

    Worauf ich hinaus will, ist, dass man bei solchen "Konkurrenzmodellen" höllisch aufpassen muss. Wenn Entwickler A die Daten in einem anderen Workflow speichert als Entwickler B, ist das System kritisch.

    Möge jemand sagen, dass es so etwas nicht gibt; ich habe das schon sehr häufig gesehen!

    Und wer Lack, Leder und Peitsche mag, der macht das Ganze auch noch ohne "Explizite Transaktion" :)


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)


    Mittwoch, 27. Dezember 2017 14:19
  • Dass man korrekt programmieren muss um solche Szenarien zu verhindern, habe ich schon vor der Datenbankprogrammierung gelernt. Sowas setze ich (i.d.R.) voraus;-).

    Eine (in meinen Augen) normale Anwendungsprogrammierung arbeitet grundsätzlich mit Read committed und Zeilensperren. Updates blocken den Datensatz und Transaktionen sollten kurz sein.

    Das Thema Deadlock zwischen konkurierenden Transaktionen/Updates gab es übrigens schon vor den SQL-Datenbanken.

    Mittwoch, 27. Dezember 2017 14:32
  • Hallo Baldur,

    sehe ich genau so wie Du - nur leider sieht die Realität (immer wieder mal) ganz anders aus :)

    "Das Thema Deadlock zwischen konkurrierenden Transaktionen/Updates gab es übrigens schon vor den SQL-Datenbanken."

    Mein Beispiel ist keine Deadlock-Situation. Zu der kommt es beim INSERT in meinem Beispiel nie. Würde ein UPDATE auf dbo.Rechnungen stattfinden, wäre das ein ganz übler Kandidat für einen Deadlock.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 27. Dezember 2017 14:37
  • Hallo Baldur und Uwe,

    > Wenn Entwickler A die Daten in einem anderen Workflow speichert als Entwickler B, ist das System kritisch.

    Da ich als einziger Entwickler sowohl die Anwendung als auch die Datenbank entwickle/betreue, dürfte dieses Szenario in diesem Falle niemals eintreten :-)

    Für die Anzeige/Vorschau/Probeausdruck eines Beleges wird eine temporäre nächste Nummer geholt. Verwirft der Anwender den Auftrag/Beleg, so werden alle Positionen und der Auftrags-Kopf verworfen. Erst beim "entgültigen Druck" wird die nächste Nummer aus der Schlüssel-Tabelle geholt und inkrementiert. Dieser Vorgang sollte im 100 ms-Bereich liegen und selbst bei konkurrierendem Zugriff für den/die weiteren Anwender unter der Wahrnehmbarkeits-Schwelle liegen.

    Ich versuche eure weiteren Hinweise aufzunehmen um die Nummern-Vergabe für meinen Einsatz-Fall sicher umzusetzen.

    --

    Hans-Peter Grözinger

    • Bearbeitet groezi Mittwoch, 27. Dezember 2017 15:15
    Mittwoch, 27. Dezember 2017 14:54
  • "Dieser Vorgang sollte im 100 ms-Bereich liegen..."
    Warum so lange;-)?
    Mittwoch, 27. Dezember 2017 15:23
  • Hallo Baldur,

    > > "Dieser Vorgang sollte im 100 ms-Bereich liegen..."
    > Warum so lange;-)?

    1 ms liegt auch im 100 ms-Bereich ...

    --

    Hans-Peter Grözinger

    Mittwoch, 27. Dezember 2017 15:51
  • Hallo Uwe und Baldur,

    So habe ich es jetzt umgesetzt:

    USE [ConfigIndiGO]
    GO
    /****** Object:  StoredProcedure [dbo].[GetNextKeyNumber]    Script Date: 31.12.2017 16:37:32 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =================================================================================================
    -- Author:		Hans-Peter Grözinger
    -- Create date: 30.12.2017
    -- Description:	Holt die nächste laufende Nummer für eine Tabelle aus der Schlüsseltabelle
    -- =================================================================================================
    -- CREATE PROCEDURE [dbo].[GetNextKeyNumber]
    ALTER PROCEDURE [dbo].[GetNextKeyNumber]
    (	
    	@cKeyText Varchar(70) = '   ', 
    	@nKeyNumber Int = 760,
    	@nKeyLangauge Int = 0,
    	@lDontSaveNewNumber Bit = 0,
    	@nCheckNumber Int,
    	@cUser Varchar(10) = '   ', 
    	@nNumber Int OUTPUT
    )
    AS
    
    /* Holt die nächste laufende Nummer für eine Tabelle aus der Schlüsseltabelle
    Parameters:
    @cKeyText:				Schlüsseltext, hier zb. 'REC', 'AUF', usw..
    @nKeyNumber:			Schlüsselnummer, wenn diese nicht übergeben wird setzen wir diese auf 760.
    @nKeyLangauge:			Sprache des Schlüssels, default 0 für deutsch.
    @lDontSaveNewNumber:	Kennzeichen, wenn gesetzt wird die Schlüsselnummer nicht inkrementiert (für Probedruck).
    @nCheckNumber:			Vergleichs-Nummer.
    Rückgabe:				Die nächste Schlüsselnummer bzw. 
    						 - 1:	Der Datensatz mit der laufenden Nummer konnte nicht gesperrt werden.
    						 - 2:	Der Wert der laufenden Nummer entspricht nicht dem Vergleichswert. Deshalb 
    								wird die laufende Nummer in der Schlüsseltabelle nicht erhöht.
    */
    
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @cError Varchar (100);
    	DECLARE @cNumber Varchar (20);
    	DECLARE @cNumberOut Varchar (20);
    
    	BEGIN TRY
    
    		BEGIN TRANSACTION;
    		
    		-- SQL-SELECT ausführen
    		SELECT @cNumberOut = Sch.Sch_Wert FROM dbo.Sch WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = TRIM( STR( @nKeyNumber ) )
    			AND Sch.Sch_SprSch = LTRIM( STR( @nKeyLangauge ) );
    
    		-- Wenn es kein Ergebnis gibt, ...
    		IF @@ROWCOUNT = 0
    
    			BEGIN
    			-- ... dann hängen wir einen neuen Datensatz an
    			-- Neue Startnummer auf 0 setzen
    			SET @cNumber = '0';
    			INSERT INTO dbo.Sch ( Sch_i, Sch_ErsDat, Sch_ErsBen, Sch_SchNr, Sch_SchTxt, Sch_SprSch, Sch_Wert, Sch_AkviKZ )
    				VALUES ( NEWID(), SYSDATETIME(), @cUser, @nKeyNumber, @cKeyText, @nKeyLangauge, @cNumber, 4095 );
    			END;
    
    		-- Die Schlüssel-Nummer in eine Zahl umwandeln, inkrementieren und den Parameter prüfen
    		SET @nNumber = CAST( @cNumberOut AS Int );
    		SET @nNumber = @nNumber + 1;
    		SET @nCheckNumber = IIF( @nCheckNumber >= 0, @nCheckNumber, @nNumber );
    
    		-- Wenn der Wert inkrementiert werden soll ...
    		IF @lDontSaveNewNumber = 0
    
    			-- ... und die Werte-Prüfung einen gültigen Wert ergibt
    			IF @nNumber = @nCheckNumber
    			
    				BEGIN
    				-- Nummer in Text umwandeln
    				SET @cNumber = LTRIM( STR( @nNumber ) );
    
    				-- Neuen Schlüsseltext (laufende Nummer), die Bearbeitungszeit, den Bearbeiter, den Schlüssel-Text,
    				-- die Schlüsselnummer und den Sprach-Schlüssel setzen
    				UPDATE dbo.sch SET Sch.Sch_Wert = @cNumber, Sch.Sch_BeaDat = SYSDATETIME(), Sch.Sch_BeaBen = @cUser
    					 WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = LTRIM( STR( @nKeyNumber ) ) 
    					 AND Sch.Sch_SprSch = LTRIM( STR( @nKeyLangauge ) );
    				END;
    
    			-- Andernfalls ...
    			ELSE
    
    				BEGIN
    				-- ... geben wir -2 als Fehlerstatus zurück
    				-- Das bedeutet, daß der Schlüsselwert in der Zwischenzeit schon von einem anderen
    				-- Anwender geändert wurde.
    				SET @nNumber = -2;
    				END;
    
    		-- Alles OK => Transaktion bestätigen und damit abschließen
    		COMMIT TRANSACTION;
    			
    		-- Die Schlüssel-Nummer zurückgeben
    		RETURN @nNumber;
    
    	-- Fehler-Block
    	END TRY
    	
    	BEGIN CATCH
    
    		-- Es ist ein Fehler aufgetreten => Rollback durchführen
    		ROLLBACK TRANSACTION;
    
    		-- Fehlermeldung für ODBC-Treiber setzen und -1 zurückgeben
    		SET @cError = N'Fehler beim Bestimmen der nächsten Nummer von >' + @cKeyText + N'<';
    		RAISERROR( @cError, 16, 1 ); 
    		RETURN -1;
    	END CATCH   
    END
    

    Bei den Tests konnte ich folgende Ausführzeit auf der Client-Seite vom Aufruf bis zur Rückgabe der nächsten Nummer messen:

    • Lokal: 1 - 2 ms
    • Netz: 2 - 3 ms
    • Netz Vollauslastung: ca. 60 ms

    Eine Frage hätte ich noch dazu für SQLServer 2017 und ODBC-Treiber 10.00.16299.15.

    Sollten tatsächlich wirklich nahezu zeitgleiche Anfragen kommen sperrt die Transaktion den Zugriff bzw. den Update auf die Schlüsselnummer für die zweite Abfrage.

    Muß die zweite Anwendung dann nochmals die Abfrage stellen oder Cached bzw. wiederholt der ODBC-Treiber bzw. die SQLServer-Eingangs-Queue die Abfrage bis zum Ablauf des Timeouts?

    --

    Hans-Peter Grözinger

    Sonntag, 31. Dezember 2017 18:23
  • Die Sperre hält die Ausführung an bis die Sperre aufgehoben wird (Commit/Rollback) oder ein Lock-Timeout zuschlägt.

    Die Gefahr besteht allerdings in deinem Transaktionshandling!
    Besteht bereits eine Transaktion, wird der Begin ignoriert.
    Dein Commit oder Rollback allerdings nicht!

    Wenn also der Aufruf innerhalb einer bestehenden Transaktion stattfindet, so wird diese entweder unerwartet vorzeitig abgeschlossen oder zurückgedreht.
    Dies gilt es zu vermeiden.
    Dies passiert durch die Benennung der Transaktion:
    https://docs.microsoft.com/de-de/sql/t-sql/language-elements/begin-transaction-transact-sql
    wodurch eine Schachtelung erfolgen kann.

    Durch die parallele Ausführung kann es aber zu Problemen in der Zeit zwischen Select und Update/Insert kommen.
    D.h., dass zwischen Select und nachfolgendem Insert, der Insert in einer 2. parallelen Abfrage bereits erfolgt ist und somit fehlschlägt.
    Das selbe gilt ebenso auch für einen Update.

    Schau dir diesbezüglich mal den Merge-Befehl an, der Update/Insert in einem zusammenfasst.
    https://docs.microsoft.com/de-de/sql/t-sql/statements/merge-transact-sql

    Hinzukommt, dass per Output-Klausel das Ergebnis des Insert/Update in einem Rutsch zurückgegeben werden kann. M.a.W.: der Befehl läuft quasi atomar (ohne Unterbrechung) ab.

    Insgesamt wird deine Prozedur da dann übrigens auch erheblich kürzer und übersichtlicher.

    Sonntag, 31. Dezember 2017 21:50
  • Hallo Baldur,

    Danke für deine Infos.

    Der SELECT/INSERT kommt normalerweise nie vor, es sei denn der Entwickler (also ich) legt eine neue Belegart fest die eine laufende Nummer benötigt. Und dann ist es eine einmalige Geschichte in der ein neuer Satz in der Schlüssel-Tabelle für die laufende Nummer angelegt wird.

    Die benannten Transaktionen schaue ich mir an.

    Montag, 1. Januar 2018 09:02
  • Das Problem gilt auch für Select/Update, da der Select keine Sperre setzt.
    Dann verwende gleich den Update mit Output um das Ergebnis zu erhalten.
    Das ist allemal sicherer, wenn dir der Merge nicht zusagt.
    Montag, 1. Januar 2018 11:57
  • Hallo Baldur,

    Hier der aktuelle Stand meiner StoredProcedure:

    BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; DECLARE @cError Varchar (100); DECLARE @cNumber Char (70) = '1'; DECLARE @cTransactionName CHAR( 50 );

        -- Zufälligen Transaktions-Namen setzen
        SET @cTransactionName = NEWID();

    BEGIN TRY -- Wenn der Wert inkrementiert werden soll ... IF @lDontSaveNewNumber = 0 BEGIN -- Benannte Transaktion starten BEGIN TRANSACTION @cTransactionName; -- Schlüssel inkrementieren und die aktuelle in cNumber zurückgeben UPDATE dbo.Sch SET @cNumber = Sch.Sch_Wert = CAST( ( CAST( Sch.Sch_Wert AS Int ) + 1 ) AS Char( 70 ) ), Sch.Sch_BeaDat = SYSDATETIME(), Sch.Sch_BeaBen = @cUser WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = @nKeyNumber AND Sch.Sch_SprSch = @nKeyLangauge; -- Alles OK => Benannte Transaktion bestätigen und damit abschließen COMMIT TRANSACTION @cTransactionName; END; -- Andernfalls ... ELSE BEGIN -- ... holen wir nur temporär die nächste Schlüssel-Nummer für den Probedruck. -- Beim entgültigen Druck schreiben wir die nächste Schlüssel-Nummer natürlich -- wieder zurück in die Schlüssel-Tabelle -- SQL-SELECT ausführen SELECT @cNumber = CAST( ( CAST( Sch.Sch_Wert AS Int ) + 1 ) AS Char( 70 ) ) FROM dbo.Sch WHERE Sch.Sch_SchTxt = @cKeyText AND Sch.Sch_SchNr = @nKeyNumber AND Sch.Sch_SprSch = @nKeyLangauge; -- Wenn es kein Ergebnis gibt, ... IF @@ROWCOUNT = 0 BEGIN -- ... dann hängen wir einen neuen Datensatz an. -- Dieser Fall tritt nur dann ein, wenn der Entwickler eine neue Belegart hinzufügt. -- In diesem Fall ist kein Anwender auf der Datenbank sodass wir keine Transaktion -- benötigen -- Neue Schlüssel-Nummer auf 0 setzen SET @cNumber = '0'; INSERT INTO dbo.Sch ( Sch_i, Sch_ErsDat, Sch_ErsBen, Sch_SchNr, Sch_SchTxt, Sch_SprSch, Sch_Wert, Sch_AkviKZ ) VALUES ( NEWID(), SYSDATETIME(), @cUser, @nKeyNumber, @cKeyText, @nKeyLangauge, @cNumber, 4095 ); END; END; -- Ergebnis in einen INTEGER umwandeln und den Prüf-Schlüssel-Nummer setzen SET @nNumber = CAST( @cNumber AS INTEGER ); SET @nCheckNumber = IIF( @nCheckNumber >= 0, @nCheckNumber, @nNumber ); -- Wenn die Werte-Prüfung einen gültigen Wert ergibt ... IF @nNumber <> @nCheckNumber -- ... geben wir -2 als Fehlerstatus zurück -- Das bedeutet, daß der Schlüsselwert in der Zwischenzeit schon von einem anderen -- Anwender geändert wurde. Dieser Fall tritt auf wenn ein Anwender einen Probedruck -- längere Zeit prüft bevor er den entgültigen Ausdruck durchführt. SET @nNumber = -2; -- Die Schlüssel-Nummer zurückgeben RETURN @nNumber; -- Fehler-Block END TRY BEGIN CATCH -- Es ist ein Fehler aufgetreten => Rollback der benannten Transaktion durchführen ROLLBACK TRANSACTION @cTransactionName; -- Fehlermeldung für ODBC-Treiber setzen und -1 zurückgeben SET @cError = N'Fehler beim Bestimmen der nächsten Nummer von >' + @cKeyText + N'<'; RAISERROR( @cError, 16, 1 ); RETURN -1; END CATCH END







    Montag, 1. Januar 2018 18:33
  • Hallo Zusammen,

    ich habe jetzt einen Test auf zwei PCs laufen lassen die 1000 x eine neue Nummer holen wobei auf einem PC ein Sleep von 11 ms und beim anderm von 12 ms eingebaut ist.

    Es gab weder gleiche Nummern noch Lücken.

    Montag, 1. Januar 2018 20:35
  • Wenn ich mir die SQL's so ansehe frage ich mich, warum du da ständig casten musst an Stelle die Variablen, Parameter und Tabellenfelder passend zueinander zu definieren.

    Machst du N x Probedruck parallel, erhältst du ebenso N x dieselbe Nummer. Wenn das so gewollt ist....;-)
    Denn beim anschließenden Echtdruck ist dann die Wahrscheinlichkeit der selben Nummer wie beim Probedruck nicht unbedingt gegeben.

    Es ist zwar nicht so performancerelevant, da die Tabelle eher klein ist, aber unschön ist es doch.

    Dienstag, 2. Januar 2018 00:04
  • Hallo Hans-Peter,

    Dein Konstrukt wird im Rahmen einer Konkurrenzsituation nicht fehlerfrei laufen. Der erste Fehler besteht bereits darin, dass Du innerhalb Deiner Transaktion lediglich einen SELECT auf die Lookup-Tabelle machst. Damit wird zwar eine Nummer gelesen aber der Datensatz wird nicht vor anderen Prozessen geschützt, die parallel Daten eintragen möchten!

    Liest Deine Transaktion die Nummer, kann T2 gleichzeitig ebenfalls die Nummer lesen - somit hast Du doppelte Nummern oder aber Deine Transaktion und T2 möchten einen neuen Datensatz in die Lookup-Tabelle eintragen. T2 wird dann mit einer Schlüsselverletzung abbrechen!

    Besser ist der Ansatz, im ersten Schritt eine neue Transaktionsnummer zu erhalten, indem Du einen UPDATE auf die Lookup-Tabelle durchführst!

    CREATE TABLE dbo.foo
    (
    	Id			INT		NOT NULL	IDENTITY (1, 1),
    	Name		CHAR(3)	NOT NULL,
    	LastValue	INT		NOT NULL,
    
    	CONSTRAINT pk_foo_Id PRIMARY KEY CLUSTERED (Id)
    );
    GO
    
    CREATE UNIQUE INDEX unix_foo_Name ON dbo.foo(Name);
    GO
    
    INSERT INTO dbo.foo (Name, LastValue) VALUES ('REC', 0);
    GO
    
    CREATE TABLE dbo.demo_table
    (
    	Id		INT			NOT NULL	IDENTITY (1, 1),
    	Type	CHAR(3)		NOT NULL,
    	No		INT			NOT NULL,
    	C1		CHAR(100)	NOT NULL
    );
    GO
    
    BEGIN TRANSACTION
    GO
    	DECLARE	@NextNo	INT = 0;
    
    	UPDATE	dbo.foo
    	SET		LastValue = LastValue + 1
    	WHERE	Name = 'REC'
    
    	IF @@ROWCOUNT = 0
    	BEGIN
    		INSERT INTO dbo.foo (Name, LastValue)
    		VALUES ('REC', 1)
    
    		SET	@NextNo = 1;
    	END
    	ELSE
    		SELECT	@NextNo = LastValue FROM dbo.foo WHERE name = 'REC';
    
    	SELECT	*
    	FROM	sys.dm_tran_locks
    	WHERE	request_session_id = @@SPID;
    
    	INSERT INTO dbo.demo_table (Type, No, C1)
    	VALUES
    	('REC', @NextNo, 'Test');
    
    	SELECT * FROM dbo.demo_table;
    COMMIT TRANSACTION;
    GO

    Das Beispiel zeigt eine Demo-Tabelle und eine Nachschlagetabelle (dbo.foo). Innerhalb der Transaktion wird zunächst ein UPDATE auf die Nachschlagetabelle durchgeführt. Damit erreichst Du, dass die Tabelle mit einem X-Lock gesperrt wird.

    Wenn Du erst einen SELECT machst, wird die Sperre unmittelbar nach dem SELECT wieder aufgehoben und T2 KANN den gleichen Wert lesen. Einzige Möglickeit, diesen Konflikt zu entzerren, wäre bereits beim SELECT einen UPDLOCK, HOLDLOCK zu verwenden!

    BEGIN TRANSACTION
    GO
    	DECLARE	@NextNo	INT = 0;
    
    	SELECT	@NextNo = LastValue + 1
    	FROM	dbo.foo WITH (HOLDLOCK, UPDLOCK)
    	WHERE	name = 'REC';
    


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Dienstag, 2. Januar 2018 06:09
  • Hallo Baldur,

    Wenn ich mir die SQL's so ansehe frage ich mich, warum du da ständig casten musst an Stelle die Variablen, Parameter und Tabellenfelder passend zueinander zu definieren.

    Das liegt daran dass die Schlüsseltabelle eine Universal-Tabelle für alle Schlüssel, Auswahl, Lookups usw. ist.

    Die laufenden Nummern stehen deshalb in einem Text-Feld da hier auch in einer anderen Zeile der Tabelle 'Sehr geehrte Damen und Herren' usw. steht.


    Machst du N x Probedruck parallel, erhältst du ebenso N x dieselbe Nummer. Wenn das so gewollt ist....;-)

    Das macht nichts aus.

    --

    Hans-Peter Grözinger



    • Bearbeitet groezi Mittwoch, 3. Januar 2018 23:42
    Mittwoch, 3. Januar 2018 21:35
  • Hallo Uwe,

    Dein Konstrukt wird im Rahmen einer Konkurrenzsituation nicht fehlerfrei laufen. Der erste Fehler besteht bereits darin, dass Du innerhalb Deiner Transaktion lediglich einen SELECT auf die Lookup-Tabelle machst. Damit wird zwar eine Nummer gelesen aber der Datensatz wird nicht vor anderen Prozessen geschützt, die parallel Daten eintragen möchten!

    Auf den Rat von Baldur führe ich in einem Befehl einen Update mit Inkrementierung des Schlüsselwertes aus der von einer Transaktion "geschützt" ist. Wo siehst du da einen SELECT?

    Der SELECT für einen Probedruck sowie der sehr unwahrscheinliche Fall des Anlegens einer "Laufenden Nummer" für eine neue Belegart muss nicht von einer Transaktion geschützt sein.

    Bei einem Probedruck macht es nichts aus wenn es bei einer Belegart mehrere gleiche Nummern gibt. Für den entgültigen Druck mit Verbuchung wird eine eindeutige und laufende Nummer gegebenenfalls nochmals geholt.

    Eine neue Belegart lege immer ich an und da ist dann kein Anwender mit dem Server verbunden.

    --

    Hans-Peter Grözinger

    • Bearbeitet groezi Mittwoch, 3. Januar 2018 21:54
    Mittwoch, 3. Januar 2018 21:54
  • Guten Morgen Hans-Peter,

    Du hast Recht - der SELECT ist im ELSE-Zweig (Probedruck); da habe ich etwas überlesen.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Donnerstag, 4. Januar 2018 06:21
  • Wobei ich beim Probedruck auf den Zugriff verzichten würde und eine Pseudonummer (99999999) ausgeben würde, damit auch keiner in die Verdrückung kommt, den Probedruck als Echt anzunehmen.
    Donnerstag, 4. Januar 2018 09:40