SQL Hardcore: Race-Condition bei der Umkreissuche

Jürgen Auer

Jürgen Auer

Legendäres Mitglied
Seit über einem Jahr laufen auf server-daten Kundendatenbanken mit implementierter Umkreissuche und der OpenGeoDb im Hintergrund, um Nutzereingaben (Ort, PLZ) Geokoordinaten für die Suche zuzuordnen.

Heute früh schickte mir das System eine Mail:

CODE A domain error occurred. (SqlError 3623, Class 16)


Bei der Umkreissuche auf einer Kundendomain hatte jemand eine existierende PLZ eingegeben, zu dieser gab es eine Fundstelle - und die obige Fehlermeldung, der Nutzer sah diese nicht.

Der (automatisch generierte) Code ist sehr komplex: Eine gespeicherte Prozedur ruft eine Funktion auf, die eine temporäre Tabelle mit IDs zurückgibt (Filterung plus Sortierung), an diese Tabelle werden per Join die eigentlichen Daten angehängt. U.a. wird eine Spalte mit der Distanz zur Eingabe des Nutzers ausgegeben:


CODE (ACos((SIN(A.latitude_r) * SIN(@i_latitude_r)) +
(COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r))
) * 6378.137) As Distance


Das ist die allgemeine Formel zur Berechnung der Entfernung zwischen Eingabekoordinaten (hier: @i_latitude_r, @i_longitude_r im Bogenmaß) und Werten aus einer Tabelle (hier: A.latitude_r, A.longitude_r).

Der Filter- und Sortiercode in der Funktion enthält einen analogen Codeschnipsel, die Sortierung kann über verschiedenste Spalten gehen (per Case), ebenso können verschiedenste Filterkriterien (nach Nutzereingaben und / oder Entfernung) übergeben werden.

Einsicht nach längerem Rumsuchen:

(1) Klammere ich den obigen Code aus, funktioniert die Suche nach dieser PLZ. Obwohl derselbe Codeschnipsel dabei intern aufgerufen wird.
(2) Führe ich denselben Code direkt auf der Tabelle aus, dann funktioniert das über alle Datensätze.

Frage: Wo steckt der Fehler?

Hinweise: (1) Threadtitel, (2) die Fundstelle lag nahe an der Sucheingabe. Wohlgemerkt: Der Code ist in mehreren Kundendatenbanken teilweise seit über einem Jahr im Einsatz. Die Mailbenachrichtigung gibt es schon seit dem Start - dies war das erste Mal.
 
Das ist mal eine Knacknuss ;-)

Von Trigonometrie versteh ich nichts. Da die Umkreissuche schon lange läuft, geh ich davon aus, dass die Formel stimmt. Die Fehlermeldung deutet aber auf ein Problem mit den Trigonometriefunktionen hin, weil der "domain error" genau dann kommt, wenn die Eingabewerte für diese Funktionen nicht im erlaubten Bereich liegen.

Der Hinweis auf die Race Condition lässt vermuten, dass es ein Problem mit der temporären Tabelle gibt. Falls die temporäre Tabelle irgendwie gespeichert wird oder die SQL-Connection zwischen verschiedenen Benutzerzugriffen geteilt wird, könnte es zu einem Daten-Durcheinander kommen.

Was es auch gibt, sind User die doppelt auf einen Submitbutton klicken. So würde die Umkreissuche dann innert Sekundenbruchteilen zweimal gestartet, und selbst wenn die SQL-Connection streng nach Usern getrennt ist, könnte es in diesem Fall zu Überschneidungen kommen.

Ich hoffe dass meine Vermutungen in die richtige Richtung gehen, obwohl ichs nicht selber ausprobieren kann.

Griessli
Irene
 
QUOTE (hatschi1810 @ Di 29.07.2008, 15:26)Falls ja wäre interessant ob du das Statement "händisch" laufen lassen kannst.

Der 'händische Versuch' führte zu der (heute früh etwas entgeisterten) Beobachtung (2). Das lief nämlich anstandslos durch, generierte keinen Fehler und listete brav die Zeilen mit der Entfernung zum Startpunkt auf.


QUOTE (Irene @ Di 29.07.2008, 16:38)Der Hinweis auf die Race Condition lässt vermuten, dass es ein Problem mit der temporären Tabelle gibt. Falls die temporäre Tabelle irgendwie gespeichert wird oder die SQL-Connection zwischen verschiedenen Benutzerzugriffen geteilt wird, könnte es zu einem Daten-Durcheinander kommen.

Was es auch gibt, sind User die doppelt auf einen Submitbutton klicken. So würde die Umkreissuche dann innert Sekundenbruchteilen zweimal gestartet, und selbst wenn die SQL-Connection streng nach Usern getrennt ist, könnte es in diesem Fall zu Überschneidungen kommen.


Zu einem 'Datendurcheinander' sollte es eigentlich nicht kommen, weil dann der ganze Sql-Server (MS-Sql2005 - Enterprise) ja etwas doof wäre. Die temporäre Tabelle stammt nicht manuell von mir, sondern wird implizit vom SqlServer genutzt, da ich eine Tabellenwertfunktion definiert und verwendet habe.

Also Code, der eine Tabelle zurückgibt. Da deklariert man eine Tabellenvariable, kann diese (verzweigt nach Sortierung aufwärts / abwärts, das läßt sich ansonsten nicht parametrisieren) mit Daten befüllen und gibt sie zurück. Es verwenden zwar verschiedene Nutzer (sogar verschiedene Kunden) dieselben Sql-Connections, weil der Webserver nur eine Anmeldekennung hat (würde man verschiedene Sql-Server-Nutzer deklarieren, dann würden tonnenweise verschiedene Ablaufpläne generiert werden, so nutzen viele Nutzer nur ganz wenige Pläne). Aber das sind keine von mir definierten 'Create Table #Tabellenname', die beim Aufrechterhalten der Verbindung weiterexistieren würden.

Nach dem Start dieses Threads (die Lösung weiß ich seit heute früh) fiel mir auf, daß die Variante (2) crasht, falls man den Datentyp von @i_latitude_r / @i_longitude_r von Decimal(18,8) auf Decimal(18,14) verbessert.



QUOTE (Irene @ Di 29.07.2008, 16:38)Da die Umkreissuche schon lange läuft, geh ich davon aus, dass die Formel stimmt. Die Fehlermeldung deutet aber auf ein Problem mit den Trigonometriefunktionen hin, weil der "domain error" genau dann kommt, wenn die Eingabewerte für diese Funktionen nicht im erlaubten Bereich liegen.


Ersteres stimmt - und zweiteres lieferte schließlich den Gedanken zur Lösung.
 
Kleiner Nachtrag:

CODE Declare @i_latitude Decimal(18,8),
@i_longitude Decimal(18,8),
@i_latitude_r Decimal(18,8),
@i_longitude_r Decimal(18,8)

Set @i_latitude = 51.49027615464600
Set @i_longitude = 7.49839939608644
Select @i_latitude_r = Radians(@i_latitude), @i_longitude_r = Radians(@i_longitude)
Select A.AdressenId,
A.latitude, A.longitude,
A.latitude_r, A.longitude_r,
(ACos((SIN(A.latitude_r) * SIN(@i_latitude_r)) +
(COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r))
) * 6378.137) As Distance
From Adressen As A
Order By Distance


Daten der ersten Zeile:


CODE 155 51.49027615464600 7.49839939608644 0.89867485165970 0.13087175809126 0


Man sieht, das ist exakt dasselbe. Trotzdem sollte so eine Eingabe natürlich erlaubt sein.

Setzt man oben den Datentyp auf Decimal(18,14), dann crasht das.

Ergänzt man


CODE Where A.AdressenId <> 155


dann funktioniert das. Es ist genau dieser Datensatz.
 
Kenne das Problem zwar nicht (wegen MySQL
smile.gif
) aber für eine unserer Umkreissuchen haben wir einen kleinen Kniff angewendet um die Sache zu Beschleunigen.

Und zwar, wenn du nicht gerade den ganzen Globus als Quelle hast, sondern nur zB.: DE,AT,CH dann kannst du eine Annäherung machen, denn in dieser Gegend sind die Abstände der Längenkreise fast konstant. Da reicht dann also alleine der Satz des Pythagoras.

Bei uns ergab das eine max. Abweichung von ~2% aber die Abfrage war insgesamt glaube 5 mal so schnell.
 
QUOTE (Maik @ Di 29.07.2008, 18:41)Bei uns ergab das eine max. Abweichung von ~2% aber die Abfrage war insgesamt glaube 5 mal so schnell.

Die Performance ist nicht das Problem.

Die Kundendatenbanken enthalten bsp. Mitgliederdaten, das sind dann 500 oder 2000 Zeilen mit Namen, Adresse und Geodaten. Aber die Tabellen sind damit immer noch höchstens wenige MB groß. Da hat der DbServer die ganze Tabelle im Arbeitsspeicher.

Damit dauert die reine Abfrage für die Umkreissuche bsp. 0.05 Sekunden - das ist problemlos (läßt sich mit .NET-Trace gut herausfinden bzw. als Problem ausschließen). Ein extrem fragmentierter Index in einer der OpenGeoDb-Tabellen war jetzt (unter dem Gesichtspunkt der Performance) viel problematischer - da hat die Ermittlung der Geokoordinaten zu einer PLZ 'etwas arg lange' gedauert.

Da das Problem allerdings strukturell existiert und es bei 'präziseren Variablen' eher auftritt, könnte es auch bei einer mySql-Variante - allgemeiner: bei jedem Db-Backend - sichtbar werden. Das Problem trat ja in den anderen Kundendatenbanken und auch in dieser noch nie auf - es ist eine Kombination aus 'speziellen Werten in der Tabelle' plus hierzu passender Nutzereingabe.

Man benötigt allerdings eine Alarmierungsfunktion - sonst bemerkt man das ja gar nicht.
 
QUOTE Nach dem Start dieses Threads (die Lösung weiß ich seit heute früh) fiel mir auf, daß die Variante (2) crasht, falls man den Datentyp von @i_latitude_r / @i_longitude_r von Decimal(18,8) auf Decimal(18,14) verbessert.


Ist dein Lösung nun auf decimal(18,14) zu verzichten?

Wenn der händische Aufruf und die automatisch generierte Tabelle nicht sind m.E. die Datentypen in der automatischen Prozedur verschieden von dem händischne Ablauf.

Ich hatte mal den Fall eines "float" der nicht funktioniert hat, weil das bsp. 1.3434e-12 in einer Funktion weder in einen absolute Zahlenwert zurückgerechnet wurde und auch nicht weiterverarbeitet werden konnte. Hab ich die Funktion weggelassen und direkt in den code geschrieben, ging's. Warum weiss ich bis heute nicht.

Jedenfalls denke ich, dass das Datenformat im automatischen Aufruf nicht verarbeitbar ist.
In der Ergebniszeile steht:
155 51.49027615464600 7.49839939608644 0.89867485165970 0.13087175809126 0
Bei decimal(18,14) könnte m.E. wegen eines Fliesskommafehlers ein anderer Wert herauskommen, der mit dem Datentypen der Ergebnisausgabe nicht kompatibel ist. Hast du mal versucht das Ergebnis in der Ausgabe auf 14 Stellen zu runden?

Gruss
Tümmel




 
Es können auch Rundungsfehler sein die beim Umrechnen (hin und zurück) des Fixed-Point Formats so dumm enden, das ACos mit Werten ausserhalb des Funktionsbereichs gefüttert wird... zB. >1 oder <-1... Ansonsten wüßte ich auch keinen Grund mehr...
 
QUOTE (Maik @ Do 31.07.2008, 15:31)Es können auch Rundungsfehler sein die beim Umrechnen (hin und zurück) des Fixed-Point Formats so dumm enden, das ACos mit Werten ausserhalb des Funktionsbereichs gefüttert wird... zB. >1 oder <-1...

Genau dies ist das Problem gewesen.

Intern wird ja zunächst addiert:


CODE (SIN(A.latitude_r) * SIN(@i_latitude_r)) +
(COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r)


Bei zwei sehr nahe beieinanderliegenden Werten geht die Differenz


QUOTE @i_longitude_r - A.longitude_r


gegen 0, der Cosinus davon gegen 1. Wenn sich die Steigungen von Sin/Cos für Latitude/Longitude um diese beiden Punkte stark voneinander unterscheiden, dann reicht das offenbar, um zumindest einen internen Wert > 1 zu erzeugen.

Der Gesamtausdruck muß aber <= 1 bleiben, weil nur das der Definitionsbereich der ACos-Funktion ist. Der Witz war: Fügte man oben etwas wie + 0.00001 dazu, dann crashte das sofort interaktiv mit derselben Fehlermeldung. Rechnete man die Summe direkt aus, kam 1 raus. Gleichzeitig führte aber die Verzweigung


CODE Case
When Round(Abs(A.latitude_r - @i_latitude_r) + Abs(A.longitude_r - @i_longitude_r), 8) = 0 Then
Cast(0.0 As Decimal(18,14))
Else (ACos((SIN(A.latitude_r) * SIN(@i_latitude_r))
(COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r))
) * 6378.137) End As Distance


dazu, daß das Problem verschwand: Liegt die Summe der absoluten Differenzen beider Werte, gerundet auf 8 Stellen, bei 0, dann nimm gleich 0 als Entfernung, ansonsten berechne.

Nimmt man übrigens Decimal(38,36) (also so ziemlich die maximale Genauigkeit), dann funktioniert das auch wieder.

Und das Böse daran ist, daß ein solches Problem fast nicht zu erahnen ist. Ein solcher Domain-Error (fehlerhafter Eingabebereich für eine Funktion) kann mathematisch eigentlich nicht auftreten - und tritt in ganz seltenen Fällen eben doch auf. Insofern eine echte Race Condition.
 
probier einfach mal einen kleinen "Hack", vielleicht hilft's ja:

CODE
ACos(
(
(SIN(A.latitude_r) * SIN(@i_latitude_r)) +
(COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r))
) * 0.1 * 10
)



Ein Versuch ist's wert, ob's funktioniert weiß nur der Programmierer von MSSQL
smile.gif
 
QUOTE (Maik @ Do 31.07.2008, 18:26)
CODE
ACos(
 (  
   (SIN(A.latitude_r) * SIN(@i_latitude_r)) +
   (COS(A.latitude_r) * COS(@i_latitude_r) * COS(@i_longitude_r - A.longitude_r))
 ) * 0.1 * 10
)



Ein Versuch ist's wert, ob's funktioniert weiß nur der Programmierer von MSSQL
smile.gif


Nee, so etwas hilft nichts.

Wenn man sich den generierten Ausführungsplan ansieht, dann gehen 66 % auf den clustered Index Scan, 44 % auf die Sortierung nach der berechneten Spalte - und 0 % auf die Berechnung des Ausdrucks.

Ich vermute ohnehin, daß der Optimierer die Konstanten zusammenzieht.

Die obige Case - Verzweigung, die effektiv verhindert, daß der Ausdruck berechnet wird, löst das Problem vollständig.

PS:


QUOTE (Maik @ Fr 1.08.2008, 08:07)Probier es aus bevor du urteilst... in anderen Programmiersprachen geht es schließlich auch...


Natürlich habe ich das ausprobiert.
 
Probier es aus bevor du urteilst... in anderen Programmiersprachen geht es schließlich auch...
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben