Frank Kalis

MS scheint seinen Zeitplan einzuhalten. :)

Microsoft hat Details zu den neuen Zertifizierungen für den SQL Server 2005 veröffentlicht. http://www.microsoft.com/learning/mcp/mcts/sql/

Im Beitrag Fälligkeiten des Bund-Futures ermitteln... haben wir uns mit dem Problem auseinandergesetzt, den jeweils 10. ten Tag eines Verfallsmonats im Bund-Future zu ermitteln. Das "einzige" Problem dieser Fragestellung war, ob der ermittelte Tag ein gültiger Börsenhandelstag ist. Ein anders gelagertes Problem erwartet uns, wenn wir uns mit Optionen auseinandersetzen.
Wir handeln fast ausschließlich mit Aktienoptionen, die an der Eurex gehandelt werden. Diese Optionen haben als Verfallstag vereinfacht gesagt den jeweils 3.ten Freitag eines Monats. Wer es genau wissen will, sollte dem Link folgen, und die Kontraktspezifikationen der Eurex selber durchlesen. Die Ermittlung diese sogenannten "Hexensabbats" könnte folgendermaßen aussehen.

Zunächst erstellen wir wieder unsere kalendarische Hilfstabelle, die wieder einmal den Großzeil der Arbeit abnimmt.

CREATE TABLE Dates 
(
 FullDate SMALLDATETIME 
 CONSTRAINT pk_Dates PRIMARY KEY(FullDate)
)

INSERT INTO Dates
 SELECT DATEADD(day, Number, '20050401')
   FROM master..spt_values
  WHERE Number BETWEEN 0 AND 256 AND Type='P'

Die entsprechende Abfrage sieht so aus:

SELECT t1.FullDate Hexensabbat
  FROM Dates t1
 WHERE YEAR(t1.FullDate)= 2005
  AND DATENAME(dw,t1.FullDate)='Friday'
  AND 
   (SELECT COUNT(*) 
      FROM Dates AS t2
     WHERE YEAR(t2.FullDate) = 2005 
      AND DATEPART(month,t2.FullDate) = DATEPART(month,t1.FullDate)
      AND DATEPART(day,t2.FullDate) <= DATEPART(day,t1.FullDate)
      AND DATENAME(dw,t2.FullDate) = 'Friday'
   ) = 3
 ORDER BY t1.FullDate

Hexensabbat                                            
------------------------------------------------------ 
2005-04-15 00:00:00
2005-05-20 00:00:00
2005-06-17 00:00:00
2005-07-15 00:00:00
2005-08-19 00:00:00
2005-09-16 00:00:00
2005-10-21 00:00:00
2005-11-18 00:00:00

(8 row(s) affected)

Was passiert?
Innerhalb der WHERE Klausel werden all Daten des uns interessierenden Zeitraumes gefiltert, bei denen der Wochentagsname "Friday" und die folgende Bedingung erfüllen:

   (SELECT COUNT(*) 
      FROM Dates AS t2
     WHERE YEAR(t2.FullDate) = 2005 
      AND DATEPART(month,t2.FullDate) = DATEPART(month,t1.FullDate)
      AND DATEPART(day,t2.FullDate) <= DATEPART(day,t1.FullDate)
      AND DATENAME(dw,t2.FullDate) = 'Friday'
   ) = 3

Ist COUNT(*) = 3 erfüllt, ist das entsprechende Datum (im Zusammenhang mit den anderen Bedingungen) der 3.te Freitag eines Monats.

Zunächst einmal ein ganz, ganz wenig Hintergrundwissen. Der Bund-Futures ist ein Zinsderivat mit einer Laufzeit von 3 Monaten oder einem Vielfachen davon. Die Fälligkeit eines Kontraktes fällt in die Monate März, Juni, September und Dezember. Ein tatsächlich reger Handel findet aber nur in dem Kontrakt mit der nächsten Fälligkeit statt. Durchaus üblich ist aber auch ein sog. "Roll-over", wenn man von einem Kontrakt in einen Kontrakt mit späterer Fälligkeit wechselt. Sei es, um die Absicherungsmaßnahme zu verlängern; sei es, um zu versuchen, doch noch einen positiven Saldo aus sämtlichen Kontrakten zu verbuchen, wenn die Position gegen Einen gelaufen ist.
Angenommen, wir haben jetzt zur Absicherung unseres Festzinsportfolios eine Postition im Juni Bund-Futures eröffnet und sind nun daran interessiert, zu erfahren, wann denn die Fälligkeitstermine bis Jahresende 2005 sind. Wie kann das mit T-SQL gelöst werden?

Die wahrscheinlich einfachste Lösung, besteht in der Verwendung einer kalendarischen Hilfstabelle, die, wie bereits in anderen Beiträgen angedeutet, für eine Vielzahl von Fällen eine immense Hilfe und Erleichterung bedeutet.

CREATE TABLE Dates 
(
 FullDate SMALLDATETIME 
 CONSTRAINT pk_Dates PRIMARY KEY(FullDate)
)

INSERT INTO Dates
 SELECT DATEADD(day, Number, '20050401')
   FROM master..spt_values
  WHERE Number BETWEEN 0 AND 256 AND Type='P'

Das obige Beispiel verwendet master..spt_values. Dies ist eine interne Hilfstabelle des SQL Servers, die diverse Systemprozeduren verwenden. Im SQL Server 2000 liegen nur die Zahlen von 0 bis 256 in fortlaufender Reihenfolge vor, weshalb wir unseren Zeitraum auf den 01.04.2005 bis irgendwann Mitte Dezember 2005 beschränken. Für längere Zeiträume oder der Erstellung solcher Tabelle im Produktionscode sollte man vielleicht besser auf eine Schleifenkonstruktion zurückgreifen. Für diese Zwecke hier, reicht es aber vollkommen aus.

So, zurück zur Ausgangslage. Da wir eine Position im Juni Kontrakt eingegangen sind, interessiert uns in erster Linie der entsprechende Tag im Juni. Zur Übersicht wollen wir aber auch die beiden anderen Fälligkeitstermine bis Jahresende 2005 wissen. Also, die September Fälligkeit und die Dezember Fälligkeit.

SET DATEFIRST 1 
SELECT 
 CASE DATEPART(dw,t1.FullDate) 
  WHEN 6 THEN DATEADD(day, 2, t1.FullDate)
  WHEN 7 THEN DATEADD(day, 1, t1.FullDate)
  ELSE t1.FullDate
 END Bund_Futures_Fälligkeit
  FROM Dates t1
 WHERE YEAR(t1.FullDate)= 2005
  AND DAY(t1.FullDate)= 10
  AND MONTH(t1.FullDate) % 3 = 0
 ORDER BY t1.FullDate

Bund_Futures_Fälligkeit                                
------------------------------------------------------ 
2005-06-10 00:00:00
2005-09-12 00:00:00
2005-12-12 00:00:00

(3 row(s) affected)

Was passiert hier?
Ein Blick auf die WHERE Klausel zeigt, daß alle 10.ten Tage jeden Monats des Jahres 2005 zurückgegeben werden, bei denen MONTH(t1.FullDate) % 3 = 0 ist. Diese Bedingung erfüllen nur die Monats März, Juni, Spetember und Dezember.

Nachdem nun diese Zeilen ausgefiltert worden sind, schaut der CASE Ausdruck nach, ob das jeweilige Datum auch auf einen gültigen Wochentag fällt, sprich ein Börsenhandelstag ist.

SET DATEFIRST 1

stellt dabei sicher, daß Montag der Beginn der Woche ist und die Ermittelung nächsten Börsentages auch richtige Werte liefert. Fällt der zurückgegebene Wert auf ein Wochende, wird die entsprechende Anzahl an Tagen hinzuaddiert, um den darauffolgenden Montag zurückzugeben. Der aufmerksame Leser mag sich jetzt vielleicht fragen, was denn wäre, wenn dieser Montag ebenfalls kein Börsenhandelstag ist? Nun, in diesem Fall wäre der nächste Börsenhandelstag der Fälligkeitstermin. Also wahrscheinlich der darauffolgende Dienstag. Diese Fingerübung überlasse ich dem geneigten Leser. Für den Fall des Bund-Futures mit seinen Fälligkeitsterminen ist es nicht wahrscheinlich, daß der darauffolgende Montag ein Feiertag ist, deswegen benötige ich diesen Sicherheitscheck nicht (hoffentlich!).

Man mag darüber denken, wie man will. Man mag sich auch streiten, ob dieses Thema in die Datenbank gehört oder nicht doch besser in die Präsentationsschicht. Ein bißchen ist das wie die Frage, was zuerst da war: Das Huhn oder das Ei. Beide Seiten haben irgendwie Recht und doch gleichzeitig wiederum auch nicht. Von daher überlasse ich es dem jeweiligen Benutzer, für welchen Ansatz er sich entscheidet.

Genug der einleitenden Worte...
Das Problem an sich kennt wahrscheinlich jeder. Man hat eine numerische Spalte in die durch gewisse Logik ein monoton steigender Wert eingegeben wird (oder werden soll). Verändern sich jedoch im Laufe der Zeit die Daten, d.h. Daten werden gelöscht und hinzugefügt, entstehen auf diese Art und Weise Löcher in der Sequenz. Wenn man diese Löcher direkt bei Eingabe stopfen will, kann man folgendes machen:

CREATE TABLE t
(
 k1 INT NOT NULL
 , c1 CHAR NOT NULL
 CONSTRAINT pk_t PRIMARY KEY(k1)
)
GO

ist unsere Augangssituation. Wir wollen direkt beim INSERT über eine DEFAULT Einschränkung entweder die Sequenz fortführen oder aber, die Löcher stopfen, sofern vorhanden. Dazu schreiben wir folgende UDF:

CREATE FUNCTION dbo.CloseMyGaps() RETURNS INT
AS
BEGIN
 RETURN
  CASE
   WHEN EXISTS
        (SELECT * 
           FROM t 
          WHERE k1 = 1)
   THEN (SELECT MIN(t1.k1) + 1
           FROM t t1 
            LEFT OUTER JOIN t t2
            ON t1.k1 = t2.k1 - 1
          WHERE t2.k1 IS NULL)
   ELSE 1
  END
END
GO

Und definieren diese Funktion als DEFAULT für die Spalte k1 in der Tabelle.

ALTER TABLE t ADD CONSTRAINT d_k1 DEFAULT dbo.CloseMyGaps() FOR k1
GO

Jetzt können wir ein bißchen mit Eingaben spielen

INSERT INTO t(c1) VALUES('a')
INSERT INTO t(c1) VALUES('b')
INSERT INTO t(c1) VALUES('c')
INSERT INTO t(c1) VALUES('d')

SELECT * 
  FROM t

k1          c1   
----------- ---- 
1           a
2           b
3           c
4           d

(4 row(s) affected)

Wie man sieht, funktionieren INSERTs tadellos. Aber schließlich haben wir auch bisher nicht die bestehende Sequenz unterbrochen. Was passiert nun bei DELETEs?

DELETE FROM t WHERE k1 IN (2,3)
INSERT INTO t(c1) VALUES('d')
INSERT INTO t(c1) VALUES('d')
DELETE FROM t WHERE k1 =1 
INSERT INTO t(c1) VALUES('f')

k1          c1   
----------- ---- 
1           f
2           d
3           d
4           d

(4 row(s) affected)

Man sieht, die Sequenz bleibt einwandfrei.
Inwieweit sich die UDF auf die Performance auswirkt, muß man im Einzelfall testen. Für ein ausgelastetes System mit vielen Datenänderungen dürfte es eher nicht geeignet sein.

Beliebt sind (oder besser gesagt, waren) diese Informationen bei Kreditangeboten aller Art. Einen monatlichen Zinssatz durch die Multiplikation * 12 in einen jährlichen umzurechnen, ist zur gleichen Zeit richtig und doch nicht. Auf diese Weise erhält man nur den Nominalzins. Der sogenannte Zinseszinseffekt kann aber für eine in der Regel weniger erfreuliche Überraschung sorgen. Berücksichtigt man diesen Effekt erhält man den Effektivzins. Dieser liegt umso höher, je mehr Zinszeitpunkte in einer Periode eintreten. Wie man jetzt genau von Nominalzins zum Effektivzins gelangt, ist zu einem guten Teil auch der Kreativität der Mathematiker überlassen. Da gibt es viele verschiedene Methoden, die z.B. mit der exakten Anzahl der Tage rechnen, oder vereinfachend mit 30/360er Regeln und, und... Ferner muß man überlegen, ob und inwieweit Bearbeitungskosten und sonstige Nebenkosten eingerechnet werden oder nicht. All dies interessiert aber hier an dieser Stelle nicht. Wir betrachtet hier einen einfachen Fall.

Beispiel: Die Firma "Wir nehmen's nicht so genau mit der Angabenpflicht unser Kreditangebote GmbH & Co. KG" wirbt mit dem Angebot für nur 1,55% Zinsen pro Monat all die kleinen Konsumwünsche zu erfüllen, auf die man sonst evtl. verzichten müßte. Ferner steht im Angebot eine Angabe zum jährlichen Zins iHv. 18,6%. Da man heutzutage (meint ;-) ) immer mehr repräsentieren zu müssen, um nicht ins gesellschaftliche Abseits, besuchen wir das Büro dieser Firma um einen Kreditvertrag über eine Summe von 10.000 € abzuschließen. Als es dann zur Unterschrift geht, haben wir das Geld zwar schon mental ausgegeben, zum Glück aber nichts an den Augen, als wir über eine Rückzahlungssumme von insgesamt 12.027,05 € in einem Jahr stolpern. Unserer Meinung nach sollte da ein Betrag ihV. 11.860 € stehen. Also über 167 € weniger oder etwas mehr als 1%. Wir verlassen empört das Büro und bauen uns folgendes SQL Statement, um nie wieder auf soetwas herein zufallen.

DECLARE @apr FLOAT 
DECLARE @frequency FLOAT

SELECT @apr = 18.6, @frequency = 12
SELECT 100 * (POWER((1 + ((@apr/100)/@frequency)), @frequency)-1) AS EAR

EAR                                                   
----------------------------------------------------- 
20.270504548765487

(1 row(s) affected)

Als Input wird der jährliche Nominalzins und die Anzahl der Zinszeitpunkte pro Periode angegeben. Da wir einen monatlichen Zins unterstellen, fallen also 12 Zinszeitpunkte in einem Jahr an. Wie bereits schon oben erwähnt, ist dieses Beispiel sehr einfach und kann beliebig variiert und kompliziert werden. Es sollte aber recht gut den Unterschied zwischen beiden Zinsangaben zeigen.

Angenommen, wir haben folgendes Portfolio:

Anlageklasse	Anteil am Portfolio
Aktien	70%
Renten	20%
Cash	10%

Für Aktien erwarten wir einen Return von 3%, für Renten 5% (jaja, wir haben "High-Yield" Bonds :-) ), und Cash 1,5%. Was ist nun der durchschnittliche Return über das gesamte Portfolio?

Per Hand würde man (0,03 * 0,7)+(0,05 * 0,2) + (0,015 * 0,1) = 0,0325 = 3,25% errechnen.

In Excel würde man einfach alles in drei Spalten untereinander schreiben und dann per SUMMENPRODUKT ein identisches Ergebnis ausrechnen und mit T-SQL?

CREATE TABLE #t
 (expected_return FLOAT NOT NULL
 , weight FLOAT NOT NULL
 )
INSERT INTO #t SELECT 0.03, 0.7
UNION ALL
SELECT 0.05, 0.2
UNION ALL 
SELECT 0.015, 0.1

SELECT SUM(expected_return*weight)
  FROM #t

DROP TABLE #t
                                                      
----------------------------------------------------- 
3.2500000000000001E-2

(1 row(s) affected)

Deadlocking tritt dann auf, wenn zwei Benutzerprozesse einen Lock auf ein Datenbankobjekt halten und versuchen, inkompatible Locks auf das jeweils andere Objekt zu erhalten. In dies der Fall, beendet SQL Server automatisch einen der beiden Prozesse, beendet damit den Deadlock und erlaubt dem anderen Prozeß fortzufahren. Die beendete Transaktion wird zurückgerollt und eine entsprechende Fehlermeldung wird an den Client gesendet. Im allgemeine wird derjenige Prozeß abgebrochen, dessen Zurückrollen den geringsten Overhead verursacht.

Wie man sich unschwer vorstellen kann, verschwenden Deadlock unnötig Resourcen, speziell CPU.

Die meisten gutdurchdachten Applikationen werden versuchen, die Transaktion erneut durchzuführen, nachdem sie die Deadlock Meldung erhalten haben. Diese neue Transaktion wird nun sehr wahrscheinlich erfolgreich durchlaufen. Kommt dieses Procedere häufig auf einem Server vor, kann es die Performance in den Keller ziehen. Kann die Applikation nicht mit der Deadlockmeldung umgehen, versucht also nicht automatisch die Transaktion durchzuführen, kann dies durchaus zu Verwirrung des Anwenders führen, wenn dieser die Deadlock Fehlermeldung am Bildschirm erhält.

Hier sind einige Tips, wie man Deadlocking im SQL Server vermeiden kann:

• Sicherstellen, das die Datenbank normalisiert ist.
• Sicherstellen, das die Applikation jedesmal auf die Serverobjekte in der gleichen Reihenfolge zurückgreift.
• Während einer Transaktion keine Benutzereingaben erlauben. Also, alle notwendigen Informationen vorher einsammeln.
• Cursor vermeiden.
• Die Transaktionen so kurz als möglich zu halten. Ein Weg dazu ist, die Anzahl von Roundtrips zwischen der Applikation und dem SQL Server durch den Einsatz von Gespeicherten Prozeduren zu minimieren bzw. die Transaktion in einem einzelnen Batch zu halten. Ein weiterer Weg zur Reduzierung der Dauer einer Transaktion ist es, sicherzustellen, das man nicht ständig die gleichen Daten lesen muß. Muß man Daten mehr als einmal lesen, sollte man diese versuchen durch den Einsatz von Variablen zu cachen und anschließend die Variablen auszulesen.
• Sperrzeiten minimieren. Versuchen, die Applikation so zu entwickeln, daß Sperren erst so spät wie möglich gesetzt werden und so früh wie möglich wieder aufgehoben werden.
• Falls angebracht, Sperren durch Einsatz von ROWLOCK or PAGLOCK vermindern.
• Den NOLOCK hint in Betracht ziehen, falls die Daten, die gelockt werden, nicht häufig modifiziert werden.
• Den niedrigsten Isolation Level in Betracht ziehen, mit dem man sein Ziel erreichen kann, d.h. die Transaktion erfolgreich beenden kann.
• Den Einsatz von "Bound Connections" in Betracht ziehen.

 *****

Tritt ein Deadlock auf wählt SQL Server standardmäßig den Prozeß als Victim aus, dessen Rollback den geringsten Overhead bedeutet und gibt den die Fehlermeldung 1205 zurück.

Was aber nun wenn man dieses Standardverhalten nicht mag? Kann man dies ändern? Ja, man kann. Indem man folgenden Befehl verwendet:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

wobei:

LOW dem SQL Server mitteilt, daß die aktuelle Session das bevorzugte Deadlock Victim sein soll und nicht die Session deren Rollback den geringsten Overhead bedeutet. Die Standardmeldung 1205 wird zurückgegeben.

NORMAL teil dem SQL Server mit, daß das Standardverhalten angewendet werden soll.

@deadlock_var ist eine Zeichenfolgenvariable, die angibt, welche Deadlock Methode angewendet werden soll. "3" bedeutet LOW, "6" bedeutet NORMAL.

Dieser Befehl wird zur Laufzeit für jeweils eine Connection angewendet.

 

 *****

Um Deadlock Probleme zu lösen, sollte man den Einsatz eines Serverbasierten Trace in Betracht ziehen. Der Overhead für den Trace ist minimal.

DBCC TRACEON (3605,1204,-1)

Nachdem man dies ausgeführt hat, werden alle Aktivitäten, die mit dem Deadlock zusammenhängen, in das SQL Server Error Log geschrieben. Die -1 als letzter Parameter ist optional. Läßt man sie weg, gilt das Trace Flag nur für die aktuelle Connection. Setzt man hingegen die -1 wird das Flag für alle Connections gesetzt.

 *****

Um Tabellen oder Gespeicherten Prozeduren, die Deadlock Probleme verursachen, auf die Spur zu kommen, kann man die Trace Flags 1204 oder 1205 verwenden. 1204 gibt grundlegende Tracing Daten zurück, 1205 gibt detailiertere Tracing Daten zurück. Man sollte sicherstellen, daß man die Trace Flag wieder abschalten, nachdem man sein Problem gelöst hat. Obwohl Traces einen geringen Overhead verursachen, tragen sie auf der anderen Seite auch nicht zur Verbesserung der Performance des Servers bei, da sie unnötigerweise Resourcen binden, nachdem das Deadlocking Problem beseitigt ist.

 *****

Idealerweise sollten Deadloch in der Applikation ausgeschlossen werden. Kann man jedoch aus diesem oder jenem Grund nicht all Deadlock aus der Applikation ausschließen, sollte man sicherstellen, daß die Applikation anwenderfreundlich für den Umgang mit Deadlocks gerüstet ist.

Angenommen man hat zwei Transaktionen, die sich deadlocken und SQL Server beendet eine der beiden Transaktionen. In diesem Fall sendet SQL Server eine Fehlermeldung, auf die die Applikation reagieren muß. Meistens möchte man in einem solchen Zeit, eine bestimmte (zufällig gewählte) Zeit warten, bevor man die beendete Transaktion erneut an den Server sendet.

Daß eine zufällig gewählte Zeit gewartet wird, ist deshalb wichtig, da es möglich sein kann, daß eine weitere konkurrierende Transaktion ebenfalls wartet und man schließlich nicht möchte das diese beiden Transaktionen nun die gleicht Zeit warten, bevor sie erneut versuchen, die Transaktion durchzuführen und einen erneuten Deadlock verursachen.

Ist die Fehlerbehandlung ausgefeilt, kriegt der Benutzer im Idealfall nicht mit, daß überhaupt ein Deadlock Problem existiert hat.

 *****

Ein Weg, um Deadlocks zu verhindern, ist der Einsatz des UPDLOCK Query Hints. Dieser Hinweis zwingt SQL Server einen Update Lock anstelle eines Shared Locks zu verwenden.

Ein Shared Lock tritt auf, wenn eine Abfrage Daten lesen will, diese aber nicht verändern will. Shared Lock können friedlich koexistieren mit anderen Shared Locks. Das bedeutet, daß mehrere Shared Locks für eine Zeile, Seite oder Tabelle vorhanden sein können. Ein Shared Lock verhindert, daß die gesperrte Resource einen exklusiven Lock erhält. Falls also ein weiterer Benutzer eine Abfrage ausführt, die eine Zeile updatet, kann dieses UPDATE erst dann durchgeführt werden, nachdem all Shared Locks aufgelöst worden sind. Shared Locks werden unverzüglich aufgelöst, sobald sie nicht mehr benötigt werden.

Ein Update Lock ist ein Zwischending zwischen einem Shared Lock und einem Exklusive Lock. Update Lock werden benötigt, wenn eine Abfrage eine oder mehrere Zeilen in einer Tabelle aktualisieren soll. Solange aber die WHERE Klausel des UPDATE Statements nicht komplett abgearbeitet wurde, steht noch nicht fest, welche Zeilen von der Aktion betroffen sind. Anstelle nun eine Exklusive Locks auf alle Zeilen zu setzen (was Concurrency und Performance der Applikation beeinträchtigen würde), wird ein Shared Lock gesetzt und erst dann, wenn die WHERE Klausel abgearbeitet wurde, wird ein Exklusive Lock auf die betroffenen Zeilen gesetzt. Die Shared Locks, die von einem Update Lock erzeugt wurden, werden gehalten bis die WHERE Klausel abgearbeitet wurden, werden aber im Gegensatz zu "normalen" Shared Locks nicht unverzüglich wieder aufgelöst.

Wie kann nun ein Update Lock bei der Vermeidung von Deadlock helfen? Erzwingt man den Einsatz eines Update Locks anstelle eines Shared Locks während die Tabellen gelesen werden, hält der Update Lock die Lock bis das Statement oder die Transaktion beendet wird. Dies ermöglicht es, Daten zu lesen, ohne andere Benutzer zu blockieren und die Daten zu modifizieren, in der Gewißheit, daß sich diese nicht verändert haben, seit man sie zuletzt eingelesen hat. Dies verhindert Deadlocks, da andere Benutzer sich nicht einschmuggeln können und eine Zeile sperren können, die man benötigt bevor man sie benötigt.

 *****