Freitag, 7. Juni 2013

Dynamische Standardwerte für Attribute einer Relation

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];

Das obige Beispiel besitzt auf dem Attribut [col1] einen DEFAULT, der immer dann eingetragen wird, wenn der Anwender selbst für dieses Attribut beim Erfassen eines neuen Datensatzes keinen Wert übergibt. Solche Konstruktionen werden gerne gebildet, wenn das Attribut selbst nicht NULL sein darf und sicher gestellt werden soll, dass immer ein Wert eingetragen wurde. Wie man am Script erkennen kann, wurde für [col2] kein Standardwert vorgegeben. Da das Attribut die Einschränkung NOT NULL besitzt, ist der Anwender gezwungen, explizit einen Wert für [col2] beim Eintragen eines neuen Datensatzes zu übergeben.

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');

    transaction_blocks_01

    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:

    Workflow

    • 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');

    transaction_blocks_02

    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.

    transaction_blocks_03

    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!

    Technorati-Markierungen: ,,,
  • Keine Kommentare :

    Kommentar veröffentlichen