Howto: Transaktionssichere Eingaben

arcplan-Applikationen bieten oft die Möglichkeit, dass ein Benutzer durch Eingaben in die Applikation bzw. in die dahinter liegende Datenbank zurückschreibt. Dies ist im Besonderen bei Planungsapplikationen, aber auch vereinfacht bei Kommentareingaben der Fall.

Bei Eingaben besteht dabei immer die Gefahr, dass in einem Kontext (eine Eingabemaske mit eingaberelevanter Filterauswahl) zwei Personen gleichzeitig eine Eingabe machen und sich diese  somit gegenseitig überschreiben oder ungewollt blockieren. Problematisch ist dies gerade bei Eingabemasken, in denen sehr viele Eingabefelder vorhanden sind, bei denen die Eingabe etwas länger dauern kann, bzw. bei Masken, in denen die Eingaben aufeinander aufbauen (oft in Planungsapplikationen).

Im Folgenden soll mit dem SQL-Server als Backend beispielhaft gezeigt werden, wie Eingaben in arcplan-Applikationen transaktionssicher gemacht werden können, um gleichzeitige Eingaben innerhalb eines Kontextes zu verhindern.

Szenario

Das Szenario basiert auf einem Analysis Services Cube mit Adventure-Works-Demodaten und zeigt einen einfachen Bericht mit der zeitlichen Entwicklung von Umsätzen für verschiedene Produktkategorien. Es besteht die Möglichkeit, durch zwei Filter einen Monat bzw. eine Region auszuwählen.

Neben der Darstellung der Werte wird auch ein Kommentar in Abhängigkeit von der Filterauswahl angezeigt. Zu jedem Monat bzw. jeder Region kann ein eigener Kommentar eingegeben werden. Die Eingabe erfolgt hier über einen eigenen Bericht, der im Dialogmodus über den geöffneten Bericht wie ein Popup geöffnet wird.

Die Kommentare werden dabei in die SQL-Server-Datenbank geschrieben. Diese Datenbank wird auch für die Funktion der Transaktionssicherheit benutzt.

Die Aufgabe besteht darin, dass zu jedem Zeitpunkt nur eine Person bzw. eine Session die Möglichkeit hat, zu einer Kombination aus Monat und Region (Kontext) eine Kommentareingabe vorzunehmen. Die Eingabe wird also bewusst blockiert (Lock), sofern in einer anderen Session gerade eine Eingabe vorgenommen wird.

Sobald sich der Monat und/oder die Region unterscheiden, ist eine parallele Eingabe unkritisch, da in diesem Szenario die Eingaben getrennt gespeichert werden und somit kein Überschreiben oder ungewolltes Blockieren entstehen kann (unterschiedlicher Kontext).

Umsetzung

Die Umsetzung erfolgt in drei Schritten:

1. Schreiben eines Locks beim Öffnen der Eingabe und Löschen des Locks beim Schließen
2. Prüfen auf Locks vor dem Öffnen der Eingabe
3. Entfernen von Locks, sofern diese nicht richtig gelöscht wurden

Schreiben eines Locks beim Öffnen der Eingabe und Löschen des Locks beim Schließen

Zunächst wird eine Locktabelle im SQL-Server erstellt. Hier soll zum Zeitpunkt der Eingabe ein Datensatz geschrieben werden, der die Person, den Zeitpunkt, die Filterkombination und die Information über die Session enthält. Durch die Existenz eines solchen Datensatzes wird eine Sperre (Lock) markiert. Sobald die Eingabe beendet wird, wird der Datensatz wieder gelöscht.

Sofern Bedarf an einem Logging von Eingaben besteht oder eine solche Funktion vorhanden ist, sollte geprüft werden, ob die beiden Funktionen kombiniert werden können.

Die Tabelle hat in unserem Szenario folgende Spalten:

• kommentar_lock_id: Eindeutige ID (Primärschlüssel und Identitätsspezifikation)
• region:   Filterwert des Region-Filters
• monat:   Filterwert des Datum-Filters
• erstellung_benutzer: Der angemeldete Benutzer in der arcplan-Applikation
• erstellung_datum: Datum der Erstellung des Datensatzes
• arcplan_sid:  Session-ID von arcplan (Informationszweck)
• arcplan_prozess_id: Prozess-ID von arcplan (Informationszweck)
• spid:   Session-ID vom SQL-Server
• login_time:  Der Zeitstempel von dem Login zur spid

Notwendig in der Tabelle sind die Filterwerte, die spid und die login_time. Die weiteren Spalten werden für Informationszwecke bzw. für Ausgabetexte genutzt.

Die Tabelle ist zunächst und auch zu den meisten Zeitpunkten leer. Die Datensätze werden dann beim Öffnen der Kommentareingabe geschrieben.

In dem Beispiel erfolgt die Eingabe mit einer Stored Procedure. An diese werden die notwendigen Parameter wie die Filterwerte, der User, die arcplan-Session- und -Prozess-ID übergeben. In dem Szenario wird der User aus der arcplan-Applikation übergeben. Dies ist notwendig, da in diesem Szenario, wie auch in vielen arcplan-Applikationen, ein technischer DB-User für den Datenbankzugriff genutzt wird. Sofern jede Session ihren eigenen DB-User hat, könnte man hier auch die DB-internen Methoden zur Ermittlung des DB-Users benutzen. In dem Szenario wird der User aus der arcplan-Applikation übergeben, da für den Zugriff auf die Metadaten-Datenbank ein technischer User benötigt wird. Dieser technische User ist für alle Benutzer gleich, sofern die SQL-Server-Funktionen zur Ermittlung des Users im Vorhinein benutzt wurden.

Innerhalb der Prozedur wird dann noch die SPID ergänzt und der Datensatz mit aktuellem Datum in die Tabelle geschrieben. Durch den Wechsel des Transaction Isolation Modes ist die Erstellung des Lock-Datensatzes dann auch innerhalb der Datenbank transaktionssicher.

Danach wird die Eingabemaske im Dialogmodus geöffnet. Aufgrund des Dialogmodus kann kein anderes Dokument aktiviert werden, bis das Dialogdokument über die Funktion SCHLIESSEN geschlossen wurde. Auf diese Weise kann nur durch ein Schließen-Schaltfeld die Eingabe beendet und der Prozess dadurch kontrolliert werden.

Dieses Schließen-Schaltfeld beinhaltet dann auch die Ausführung einer SQL-Funktion, mit der der Lock-Datensatz gelöscht wird. Hierfür wird anhand einer Funktion die aktuelle ID (Beschreibung im folgenden Schritt) ermittelt und ein dynamisches DELETE-Statement erzeugt.

Prüfen auf Locks vor dem Öffnen der Eingabe

Durch die bisherigen Schritte kann zu jedem Zeitpunkt ermittelt werden, ob eine Eingabe zu einem bestimmten Kontext aktiv ist oder nicht. Im Folgenden wird dies genutzt, um Eingaben nur dann zu ermöglichen, wenn diese nicht parallel durch eine andere Session gesperrt sind.

In dem Bericht wird beim Anklicken des Eingabe-Schaltfelds vor dem Öffnen der Eingabemaske geprüft, ob schon ein Lock-Datensatz vorhanden ist. Dies erfolgt in diesem Szenario durch eine User Defined Function:

Da die Funktion auch zum gezielten Löschen (siehe oben) verwendet wird, gibt diese die ID des gefundenen Datensatzes zurück. Wenn kein Datensatz vorhanden ist, wird eine 0 und dadurch immer ein Zahlenwert zurückgegeben.

In Abhängigkeit vom Ergebnis wird dann entweder die Kommentarmaske wie oben beschrieben geöffnet oder eine Fehlermeldung ausgegeben. Die Fehlermeldung kann dann auch durch weitere Informationen aus dem Lock-Datensatz angereichert werden. In dem Szenario werden der User und der Zeitpunkt der Erstellung des Lock-Datensatzes mit ausgegeben.

Efnternen von Locks, sofern diese nicht richtig gelöscht wurden

Die Umsetzung ist bisher grundsätzlich ausreichend. Allerdings kann es passieren, dass eine Session während der Eingabe aus unterschiedlichen Gründen beendet wird. Dann bleibt der Lock-Datensatz stehen und kann auch nicht mehr über die bisherigen Funktionalitäten entfernt werden.

Um diese Datensätze automatisiert zu entfernen, wurde eine weitere Prozedur angelegt, die immer vor dem Öffnen der Kommentarmaske ausgeführt wird. Da die Tabelle in der Regel sehr klein ist, hat das keine spürbaren Auswirkungen auf die Performance. 

Um die inaktiven Lock-Datensätze zu identifizieren, wird hier die Systemsicht sys.dm_exec_sessions benutzt. In dieser wird neben der SPID (der SQL-Server-Session-ID) auch die Login-Zeit für die Session angegeben. Diese ist notwendig, da die SPID sehr schnell wieder neu vergeben wird und so alleine nicht ausreicht, um inaktive Sessions zu identifizieren.

Fazit

In dem beschriebenen Szenario wurde gezeigt, wie man eine transaktionssichere Eingabe gestalten kann. Ziel war es, die Überlegungen hierzu sowie eine grundsätzliche Darstellung der Funktionalität aufzuzeigen.

Konkret wurde viel mit SQL-Server-Funktionalitäten gearbeitet. Dies ist allerdings nicht zwingend notwendig. Die meisten Funktionen können auch innerhalb von arcplan oder auch in anderen Datenbanken umgesetzt werden. Der Aufbau wäre dann aber relativ ähnlich.