Der nachfolgende Artikel beschreibt einen Lösungsansatz für einer Anfrage aus den Microsoft Foren zu Microsoft SQL Server. Die Anforderung besteht darin, für ein Attribut einer Relation die DEFAULT-Eigenschaft zu variieren ohne dabei die Einschränkung selbst mit ALTER TABLE… zu ändern. Der hier beschriebene Lösungsansatz basiert auf der Verwendung einer Relation für die Speicherung von Standardvorgaben für Attribute. Diese Informationen werden dann mittels Funktion (die Basis der DEFAULT-Einschränkung ist) als Standardvorgaben verwendet. Der nachfolgende Artikel geht sehr detailliert auf die Vor- und Nachteile verschiedener Lösungsansätze ein.

Der Threadersteller hatte die Anforderung zu lösen, dass Benutzer der Datenbank die Standardtexte für Attribute einer Relation eigenständig anpassen können. Ein DEFAULT ist die Eigenschaft eines Attributs einer Relation, die ein “normaler” User nicht ändern sollte. Da eine Änderung dieser Eigenschaft immer eine Schema-Änderung ist, muss der betreffende Anwender die ALTER-Berechtigung für die Relation erhalten. Das Problem einer solchen Berechtigung ist jedoch, dass mit der Berechtigung eine Vielzahl weiterer Möglichkeiten einher gehen, die der Entwickler nicht vorgesehen hat (siehe dazu: http://msdn.microsoft.com/de-de/library/ms190273.aspx) oder nicht wünscht. Es muss also eine Lösung gefunden werden, die nur die “Datenmanipulation” in den Relationen selbst gewährleistet (SELECT, INSERT, UPDATE, DELETE); nur so kann sicher gestellt werden, dass keine Manipulationen am Schema selbst vorgenommen werden.

DEFAULT-Einschränkung

Wenn eine neue Zeile in eine Relation mit einer DEFAULT-Definition für ein Attribut gespeichert werden soll, verwendet Microsoft SQL Server einen Standardwert in dieses Attribut, wenn kein Wert angegeben wurde. Die Definition eines solchen Standardwertes wird entweder beim Erstellen der Relation oder kann nachträglich hinzugefügt werden.

CREATE TABLE dbo.tbl_Demo
(
    Id    int          NOT NULL  IDENTITY (1, 1),
    col1  varchar(200) NOT NULL,
    col2  varchar(200) NOT NULL,

    CONSTRAINT pk_tbl_Demo_Id PRIMARY KEY CLUSTERED(Id)
);

ALTER TABLE dbo.tbl_Demo ADD CONSTRAINT tbl_Demo_def_col1  DEFAULT ('Das ist ein Standardwert') FOR [col1];

Gleichwohl wird nun auch das Problem klar, das sich bei einer solchen Konstruktion ergibt. Was muss gemacht werden, wenn der Standardwert im obigen Beispiel geändert werden soll? Hierzu sind zwei Schritte erforderlich:

• Löschen der bestehenden Einschränkung
• Erstellen der neuen Einschränkung

ALTER TABLE dbo.tbl_Demo DROP CONSTRAINT tbl_Demo_def_col1;
ALTER TABLE dbo.tbl_Demo ADD CONSTRAINT tbl_Demo_def_col1 DEFAULT ('Neuer Standardwert') FOR [col1];

Bei dieser Variante treten zwei eklatante Probleme auf, die es zu lösen gibt:

1. Der ausführende Datenbankbenutzer muss die Berechtigung ALTER für die Relation dbo.tbl_Demo besitzen (sehr kritisch!)
2. Während einer Schemaänderung wird die Relation mit einer Sperre belegt!

Die Bedenken zu Punkt 1 wurde bereits oben behandelt. Jedoch ist Punkt 2 ein äußerst kritischer Punkt, der insbesondere bei größeren Relationen berücksichtigt werden muss- exklusive Sperre der Relation! Das Ändern der DEFAULT-Einschränkung habe ich zu Demonstrationszwecken in eine Transaktion implementiert. Das Ergebnis der Sperren, die während dieser Transaktion gesetzt werden, zeigt die nachfolgende Abbildung.

SELECT  resource_type,
        resource_description,
        resource_associated_entity_id,
        request_mode,
        request_type,
        request_status,
        request_owner_type
FROM    sys.dm_tran_locks
WHERE   request_session_id = 55 AND
        request_mode IN ('X', 'SCH-M');

Man kann aus der Abbildung sehr deutlich erkennen, dass Sperren auf Grund von Schema-Modifikationen [SCH-M] für das Objekt gesetzt sind. Sperren des Typs [Sch-M] werden von Microsoft SQL Server verwendet, wenn für eine Relation ein DDL-Vorgang ausgeführt wird. Während die [SCH-M]-Sperre besteht, werden gleichzeitige Zugriffe auf die Tabelle verhindert.

Lösungsansatz

Als Lösung wurde das nachfolgende Modell entwickelt:

• Es gibt eine Relation, in der alle Default-Werte gespeichert werden.
• Eine zusätzliche Funktion wird erstellt, die – basierend auf dem Wert der Variablen @config_flag – den Standardwert aus der Konfigurationstabelle ausliest
• Die Funktion wird – anstelle eines konstanten Wertes – als DEFAULT für die Relation verwendet

dbo.sysconfiguration

Diese Relation besitzt zwei Attribute; das Attribut [config_flag] ist der Clustered Key der Relation

CREATE TABLE dbo.sysconfiguration
(
    config_flag   varchar(50)     NOT NULL,
    config_value  nvarchar(4000)  NOT NULL,

    CONSTRAINT pk_sysconfiguration_config_flag PRIMARY KEY CLUSTERED (config_flag)
);
GO

-- Ein Demoeintrag
INSERT INTO dbo.sysconfiguration (config_flag, config_value) VALUES ('default_foo_col1', 'Das ist der Standardwert');

dbo.fn_GetDefaultValue

Um einen Wert aus einer referenzierenden Relation als DEFAULT-Einschränkung verwenden zu können, kann man nur eine Funktion verwenden. Nebenbei hat diese Art des Zugriffs noch einen anderen – wichtigen – Vorteil; man muss den Anwendern KEINE Berechtigungen auf die Relation dbo.sysconfiguration gewähren. Es reicht, den schreibenden Zugriff für die Relation auf wenige Anwender zu beschränken, die für die Konfiguration der Standardwerte zuständig sind.

CREATE FUNCTION dbo.fn_GetDefaultValue(@config_flag varchar(50)) RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @ReturnValue    nvarchar(4000);

    SELECT  @ReturnValue = config_value
    FROM    dbo.sysconfiguration
    WHERE   config_flag  = @config_flag;

    RETURN  @ReturnValue;
END
GO

Einbindung der Funktion als DEFAULT-Einschränkung

Zu guter Letzt bleibt nur noch die Einbindung der Funktion als DEFAULT-Einschränkung in die zuvor erstellte Relation und schon ist das System implementiert.

ALTER TABLE dbo.tbl_Demo DROP CONSTRAINT tbl_Demo_def_col1;
ALTER TABLE dbo.tbl_Demo ADD CONSTRAINT tbl_Demo_def_col1 DEFAULT (dbo.fn_GetDefaultValue('default_foo_col1')) FOR [col1];

Funktionstests und Sperrverhalten

Nachdem die Funktionalität implementiert wurde, bleibt noch ein abschließender Test. Bei diesem Test ist es vor allen Dingen wichtig, zu ermitteln, welche Sperren Microsoft SQL Server beim Eintragen eines neuen Datensatzes verwendet. Um das zu protokollieren, wird die Eintragung eines neuen Datensatzes in eine dedizierte Transaktion eingebettet:

BEGIN TRANSACTION
INSERT INTO dbo.tbl_Demo (col1, col2) VALUES (DEFAULT, 'Uwe Ricken');

Perfekt – es wird ausschließlich eine exklusive Sperre für den IDENTITY-Wert gesetzt. Auf die Relation dbo.sysconfiguration werden keine Sperren angewendet. So sollte es auch sein! Nähere Informationen zum Sperrverhalten bei IDENTITY-Werten kann man in dem von mir geschriebenen Artikel “IDENTITY-Werte…-warum wird der Wert…” nachlesen.

Ein letzter Test soll zeigen, wie sich das Einfügen eines neuen Datensatzes in die Relation dbo.tbl_Demo gestaltet, wenn gleichzeitig eine Aktualisierung des Standardtextes durchgeführt wird.

In einem neuen Abfragefenster in SSMS wird das folgende Update der Relation dbo.sysconfiguration durchgeführt:

BEGIN TRANSACTION
UPDATE dbo.sysconfiguration
SET    config_value = 'Neuer Standard'
WHERE  config_flag  = 'default_foo_col1'

Während diese Transaktion noch nicht abgeschlossen ist (es fehlt ja das COMMIT TRANSACTION) wird in einem zweiten Abfragefenster ein neuer Datensatz in die Relation dbo.tbl_Demo eingetragen.

BEGIN TRANSACTION INSERT INTO dbo.tbl_Demo (col1, col2) VALUES (DEFAULT, 'Beate Ricken');

Die Situation stellt sich aktuell wie folgt dar:

Ein Anwender möchte den Standardwert für das Attribut [col1] auf den Text “Neuer Standard” aktualisieren. Dazu führt er die obige Aktualisierungsabfrage aus. Gleichzeitig möchte ein weiterer Anwender die die Relation dbo.tbl_Demo einen neuen Datensatz eintragen. Da der Standardwert aus der Relation dbo.sysconfiguration entnommen wird, kommt es zu einem Konkurrenzverhalten, dass man durch einen Blick in die Transaktionssperren erkennen kann.

Die Abbildung zeigt klar, dass die Session 55 (das ist in meinem Beispiel die Session, die einen neuen Wert in die Relation dbo.tbl_Demo einträgt) versucht, einen [S]hared Lock zu setzen aber warten muss. Dieser Shared Lock soll auf den Datensatz mit dem Schlüsselattribut [KEY] gesetzt werden, der sich im OBJECT [tbl_Demo] befindet. Aufgerufen wurde diese Sperranforderung durch das OBJECT [fn_GetDefaultValue]. Momentan befindet sich der Aufruf in einer Sackgasse weil…

eine [X]klusive Sperre auf dem Schlüsselattribut [KEY] liegt. Diese Sperre wird von der Session 57 erzeugt; diese Session ist in meinem Beispiel der Anwender, der eine Aktualisierung des Standardtextes vornimmt. Diese Sperren werden so lange aufrecht erhalten, bis der Initiator [Session 57] die Ressourcen wieder frei gibt. Das kann er entweder durch ein COMMIT TRANSACTION (speichern) oder ROLLBACK TRANSACTION (verwerfen). Im Beispiel wird der neue Eintrag gespeichert! Nachdem der Eintrag gespeichert wurde, wird die Ressource wieder freigegeben und die Session 55 kann die nun freigewordene Ressource verwenden. Das fertige Ergebnis aller durchgeführten Beispiele sind zwei Datensätze in der Relation dbo.tbl_Demo mit jeweils unterschiedlichen Standardeintragungen.

Fazit

Die von mir vorgeschlagene Lösung ist dynamischem SQL oder weitreichenden Berechtigungen für Standardbenutzer auf jeden Fall vorzuziehen. Jedoch gilt bei der Implementierung solcher Lösungen auch immer ein Blick “über den Tellerrand”. So charmant diese Lösung auch sein mag – eines sollte dabei nicht unberücksichtigt bleiben: Diese Lösung ist nicht unbedingt für OLTP-Systeme geeignet, in denen es darauf ankommt, Datensätze so schnell wie möglich zu speichern. In einem solchen Fall kann eine Aktualisierung der Standardwerte zu Verzögerungen führen, wenn die Ressourcen nicht schnell genug wieder freigegeben werden.

Herzlichen Dank fürs Lesen!