Unterschiedliches Verhalten bei Transaktionen

[DerAndereMicha]

Hallo Zusammen,

hat jemand von Euch ne Ahnung, warum folgendes Verhalten auftritt bzw. wie ich das ändern kann?

Ich verwende Delpi 2007 und bin über eine ADOConnection und ein ADODataSet mit einem MS SQL-Server 2005 verbunden.

Folgende zwei Szenarien verhalten sich unterschiedlich, warum?

I.

• a, ich beginne eine Transaktion mit ADOConnection.BeginTrans
• b, ich füge mit ADODataSet1.Insert einen neuen Datensatz hinzu
• c, ich weise einem SMALDATETIME-Feld mit ADODataSet1.FieldByName('Feldname').AsString:='01. 01.1999' zu
• d, ich schicke die Daten mittels ADODataSet1.Post zur Datenbank
• e, ich füge mit ADODataSet1.Insert einen weiteren Datensatz hinzu
• f, ich weise dem SMALDATETIME-Feld mit ADODataSet1.FieldByName('Feldname').AsString:='01. 01.2099' zu
• g, ich schicke die Daten mittels ADODataSet1.Post zur Datenbank
  -> was zu einer Exception wegen dem zu großen Jahr führt (Bereichsüberlauf)
• h, ich beende die Transaktion mit ADOConnection1.CommitTrans
  
• Ergebnis: Keiner der beiden Datensätze ist in der Datenbank vorhanden, weil bei der Exception
  anscheinend automatisch ein Rollback durchgeführt wurde.

II.

• a, ich beginne eine Transaktion mit ADOConnection.BeginTrans
• b, ich füge mit ADODataSet1.Insert einen neuen Datensatz hinzu
• c, ich weise einem VARCHAR-Feld mit Eindeutigem Index drauf mit ADODataSet1.FieldByName('Feldname').AsString:='Tes t' zu
• d, ich schicke die Daten mittels ADODataSet1.Post zur Datenbank
• e, ich füge mit ADODataSet1.Insert einen weiteren Datensatz hinzu
• f, ich weise dem VARCHAR-Feld mit ADODataSet1.FieldByName('Feldname').AsString:='Tes t' den selben Wert zu
• g, ich schicke die Daten mittels ADODataSet1.Post zur Datenbank
  -> was logischerweise zu einer Exception wegen dem eindeutigen Index führt
• h, ich beende die Transaktion mit ADOConnection1.CommitTrans
  
• Ergebnis: Der erste der beiden Datensätze ist in der Datenbank vorhanden, weil hier bei der Exception
  anscheinend kein automatisches Rollback durchgeführt wurde.

Soll bzw. muß das so sein? Und wer ist an diesem Verhalten Schuld : der SQL-Server, ADO oder Delphi ?

PS: Das Verhalten in Szenario I konnte ich übrigens nur bei SMALLDATETIME feststellen,
bei DATETIME > 31.12.9999 oder SMALLINTEGER > 32767 z.B. verhält sich das ganze genau wie in Szenario II.


Gruß
Micha

[nahpets]

Hallo,

schau mal in die Logfiles des SQL-Servers, ob der da was meldet.

Ausgehend von Deinem PS schließe ich auf einen Fehler im SQL-Server.

Stephan

[DerAndereMicha]

Hallo Stephan,

ich hab gleich mal nachgeschaut, konnte aber nichts Auffälliges erkennen. Die letzten Einträge stammen vom Hochfahren des Servers.

Gruß
Micha

[nahpets]

Hallo,

SQL-Server 2005:

Habe folgendes probiert:

SQL-Code:

/*
create table test
(
   a smalldatetime,
   b smallint,
   c VarChar(20)
)

create unique index c on test (c);

*/

begin transaction
delete from test
commit transaction

begin transaction

insert into test (a,b,c) values ('01.01.1999',32677,'test');
insert into test (a,b,c) values ('01.01.2099',32677,'test2000');
insert into test (a,b,c) values ('01.01.1999',32677,'test');
insert into test (a,b,c) values ('01.01.1999',326770,'test');
insert into test (a,b,c) values ('01.01.1999',32677,'test1');

commit transaction

select * from test

1999-01-01 00:00:00   32677   test
1999-01-01 00:00:00   32677   test1

Ergebnis sollte stimmen, bei Dir auch, dann ist's nicht die Datenbank.

Kannst Du an der Exception erkennen, wer sie genau wirft? Poste sie ggfls. mal hier.

Die ADOConnection hat ein "Fehler"-Array, gib das mal aus, eventuell gibt es da mehr Infos:

Delphi-Quellcode:

Var
    i : Integer;
begin
  for i := 0 to ADOConnection.Errors.Count - 1 do begin
    Writeln(ADOConnection.Errors.Item[i].Number);
    Writeln(ADOConnection.Errors.Item[i].Source);
    Writeln(ADOConnection.Errors.Item[i].Description);
    Writeln(ADOConnection.Errors.Item[i].SQLState);
    Writeln(ADOConnection.Errors.Item[i].NativeError);
  end;
end;

Ausgehend von Deiner Beschreibung ist für mich nicht zu erkennen, wo Du im Quelltext das Commit machst, ist sichergestellt, dass es auch bei jeder Ausnahme ausgeführt wird?

Stephan

[DerAndereMicha]

Hallo Stephan,

1. Dein SQL-Skript liefert bei mit die selben Ergebnisse
2. es wird immer ein CommitTrans ausgeführt - der Debugger kommt jedenfalls durch
3. das Fehler-Array der ADOConnection hat folgenden Inhalt:

-2147217900
Microsoft OLE DB Provider for SQL Server
smalldatetime-Überlauffehler bei der Konvertierung des datetime-Datentyps in den smalldatetime-Datentyp.
42000
298


Gruß
Micha

[nahpets]

Hallo Micha,

Wenn der Debugger immer am Commit vorbei kommt, würde dies heißen, dass die Schnittstelle entweder alle oder keine Daten durchläßt, bzw. selbst ein Rollback macht, sofern ein von ihr entdeckter Fehler auftritt.

Dies würde auch das von Dir beschriebene Verhalten erklären.

Kann man beim SQL-Server irgendwo mitgucken, welcher Client gerade was macht? Habe da keine Ahnung, bei Oracle ist's meines Wissens nach möglich.
Wenn's geht, könntest Du mal schauen, was alles von Deinem Programm an der Datenbank ankommt (von dem Du eventuell garnicht weißt, dass es von Deinem Programm kommt, das wären dann die "Selbständigkeiten" der Schnittstelle).

Leider gelingt es mir nicht anhand der Fehlermeldungen zu unterscheiden, ob sie von der Datenbank oder von der Schnittstelle kommen. Bei Google finde ich auch nichts zu dem Thema (ausser Deinem vorherigen Post (Boooooh ist Google schnell)).

Da wirst Du wohl im Programm sicherstellen müssen, dass der Datentyp in Ordnung ist.

Stephan

[DerAndereMicha]

Hallo Stephan,

hab mir mal den Verkehr zwischen meiner Testapplikation und dem SQL-Server mittels dem SQL-Profiler angeschaut.

Sehr eigenartig:

- wenn ich nem SMALLINT-Feld einen SMALLINT-Wert zuweise und dann ein Post mache, wird die Anweisung angezeigt
- wenn ich nem SMALLINT-Feld einen INTEGER-Wert zuweise und dann ein Post mache, wird diese Anweisung nicht angezeigt

--> die 1.Zeile ist danach in der Tabelle drin, die Zweite nicht


- beim SMALLDATETIME-Feld wird die Anweisung sowohl bei nem SMALLDATETIME-Wert als auch bei nem DATETIME-Wert angezeigt,

--> im Nachhinein ist allerdings keiner der beiden Werte in der Tabelle


Bei beiden Test ist aber auch kein zusätzliches Rollback zusehen, allerdings sehe ich auch das "begin transaction" nicht ???

PS: Wenn ich das mit dem Integer mache, steht auch gar nichts im Error-Record drin !!! So als ob Delphi das Post gar nicht erst ausführen würde und schon eine Exception auslösen tut, bevor es die Daten überhaupt zum SQL-Server schickt. Würde erklären, daß hier kein Rollback ausgeführt wird, da so am SQL-Server ja auch kein Fehler vorliegt.


Gruß
Micha

[nahpets]

Hallo Micha,

sorry, aber das kommt mir jetzt spanisch vor:

Zitat:

- wenn ich nem SMALLINT-Feld einen SMALLINT-Wert zuweise und dann ein Post mache, wird die Anweisung angezeigt

Die kommt dann wohl bei der Datenbank an.

Zitat:

- wenn ich nem SMALLINT-Feld einen INTEGER-Wert zuweise und dann ein Post mache, wird diese Anweisung nicht angezeigt

Da tippe ich dann auf die Schnittstelle, die scheint wohl die Sinnhaftigkeit zu prüfen und die Daten dann nicht an die Datenbank weiter zu geben (nach welchen Kriterien mag sie prüfen? Ist aus Entwícklersicht wohl 'ne Blackbox).

Zitat:

--> die 1.Zeile ist danach in der Tabelle drin, die Zweite nicht

Dies wäre ja auch okay, wenn die zweite Anweisung in einer eigenen Transaktion bei der Datenbank ankommt.

Zitat:

- beim SMALLDATETIME-Feld wird die Anweisung sowohl bei nem SMALLDATETIME-Wert als auch bei nem DATETIME-Wert angezeigt,

Hier scheint die Schnittstelle nicht zu prüfen, sondern gibt's an die Datenbank weiter.

Zitat:

Bei beiden Test ist aber auch kein zusätzliches Rollback zusehen, allerdings sehe ich auch das "begin transaction" nicht ???

Gibt es andere Situationen, in denen "begin transaction" und "Commit" bzw. "Rollback" angezeigt werden oder werden die nie angezeigt?

Habe das bei mir mal ausprobiert, kann im SQL-Profiler (wusste bis eben garnicht, dass es sowas gibt ) die Transaktionen sehen. Allerdings habe ich in den Eigenschaften der Ablaufverfolgung die Checkbox "Alle Ereignisse" anzeigen aktiviert und dann im Zweig "Transactions" alles aktiviert.

Wieso ist hier nachher ein Datensatz in der Datenbank?

SQL-Code:

begin transaction 

insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
insert into test (a,b,c) values ('01.01.2099',32677,'test2000'); 
--insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
--insert into test (a,b,c) values ('01.01.1999',326770,'test'); 
--insert into test (a,b,c) values ('01.01.1999',32677,'test1'); 

commit transaction

wenn das in einer Transaktion abläuft, hätte ich hier keinen erwartet. Das ist meiner Meinung nach ein Fehler der Datenbank, entweder alles aus einer Transaktionsklammer oder nichts.

Hier das gleiche Verhalten:

SQL-Code:

begin transaction 

insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
insert into test (a,b,c) values ('01.01.1999',326770,'test'); 

commit transaction

Auch das ist meiner Meinung nach nicht korrekt.

Korrekt wäre:

SQL-Code:

begin transaction 
insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
commit transaction 

begin transaction
insert into test (a,b,c) values ('01.01.1999',326770,'test'); 
commit transaction

und anschließend ist der erste Datensatz in der Datenbank, aber das weicht jetzt vom Thema ab, allerdings liegt für meine Begriffe die Vermutung nahe, dass Dein Problem mit diesem Verhalten der Datenbank im Zusammenhang steht.
Das von Dir geschilderte Problem wird mir in diesem Zusammenhang nicht klarer bzw. habe ich momentan keine Ahnung, wie Du um Dein Problem herumkommen kannst (von richtig lösen mag ich garnicht sprechen).

Wie soll man konsistente Daten sicherstellen, wenn sich die Datenbank nicht "erwartungskonform" verhält und zwischen der eigenen Applikation und der Datenbank noch eine Schnittstelle steckt, zu deren "Verhalten im Fehlerfalle" keine Informationen vorliegen?

Habe keine Idee mehr, die Dir bei der Problemlösung helfen könnte.

Zitat:

PS: Wenn ich das mit dem Integer mache, steht auch gar nichts im Error-Record drin !!! So als ob Delphi das Post gar nicht erst ausführen würde und schon eine Exception auslösen tut, bevor es die Daten überhaupt zum SQL-Server schickt. Würde erklären, daß hier kein Rollback ausgeführt wird, da so am SQL-Server ja auch kein Fehler vorliegt.

Der Integer kann "programmintern" schon zu groß sein und deshalb eine Exception auslösen, nicht's im Error-Record = kein Datenbankfehler. Versuch mal im Try Except-Block die Datenbankfehler separat zu behandeln um zwischen Delphi-Fehlern und Datenbankfehlern unterscheiden zu können und mache im Fehlerblock für die Datenbankfehler immer ein Rollback (EDatabaseError wird bei Fehlern der ADO-Komponenten nicht angesprochen, aber EOleException definiert in ComObj).

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
Var
    i : Integer;

begin
  ADOConnection1.Open;
  ADOQuery1.SQL.Clear;
  Memo1.Lines.Clear;
  Try
    ADOQuery1.SQL.Text := memo2.Text;
    ADOConnection1.BeginTrans;
    ADOQuery1.ExecSQL;
    ADOConnection1.CommitTrans;
  Except
    on e : EOleException do begin
      ADOConnection1.RollbackTrans;
      for i := 0 to ADOConnection1.Errors.Count - 1 do begin
        Memo1.Lines.Add(IntToStr(ADOConnection1.Errors.Item[i].Number));
        Memo1.Lines.Add(ADOConnection1.Errors.Item[i].Source);
        Memo1.Lines.Add(ADOConnection1.Errors.Item[i].Description);
        Memo1.Lines.Add((ADOConnection1.Errors.Item[i].SQLState));
        Memo1.Lines.Add(IntToStr(ADOConnection1.Errors.Item[i].NativeError));
      end;
    end;
    on e : Exception Do begin
      ADOConnection1.RollbackTrans;
      ShowMessage(e.Message);
    end;
  end;
  ADOConnection1.Close;
end;

mit dem SQL:

SQL-Code:

delete from test ;
insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
insert into test (a,b,c) values ('01.01.2099',32677,'test2000'); 
insert into test (a,b,c) values ('01.01.1999',32677,'test'); 
insert into test (a,b,c) values ('01.01.1999',326770,'test'); 
insert into test (a,b,c) values ('01.01.1999',32677,'test1');

In diesem Fall habe ich anschließend zwei Datensätze in der Datenbank.

Offensichtlich hast nicht Du ein Problem, sondern alle haben ein Problem

Kannst Du das Ganze gegen eine andere Datenbank testen (nicht SQL-Server)?

Stephan

[DerAndereMicha]

Hallo Stephan,

vielen Dank für Deine Bemühungen. Ich werde jetzt aus allen SMALLDATETIME-Feldern DATETIME-Felder machen, daß wenigstens bei allen Feldern das selbe vorhersehbare Verhalten im Fehlerfall auftritt. Ob's nun richtig ist oder falsch ...

Einen anderen SQL-Server hab ich momentan nicht zur Verfügung und leider auch keine Zeit mir eine weitere Testumgebung zu schaffen.

Vielen Danken nochmal.

Gruß
Micha