ADO + Mehrbenutzerbetrieb + Transaktionen + SQL Server

[sega84]

Hallo zusammen,
habe ein Projekt mit etwa 120 Tabellen zu Betreuen.
Wir verbinden uns via ADO auf die Datenbank.
Da im Programm Transaktionen verwenden, kommt es mitlerweile zu sehr vielen Timeouts, da verschiedenste Locks auf den Datensätzen liegen.
Mit der Software arbeiten momentan maximal gleichzeitig 30 Leute auf einer Datenbank (Entweder SQL Server 2000 oder 2005, teilweise schon 2008).

Nun ist es mein Ziel, die Timeouts zu entfernen.
Der ADO-Provider ist:
Microsoft OLE DB Provider for SQL Server (dieser darf nicht geändert werden, er muss so bleiben!!!)

Momentan verwende ich folgende Einstellungen für die ADOConnection:
Code:

Delphi-Quellcode:

TAdoConnection(DB).Attributes := [xaCommitRetaining, xaAbortRetaining];
TAdoConnection(DB).IsolationLevel := ilReadUncommitted;
TAdoConnection(DB).LoginPrompt := False;
TAdoConnection(DB).ConnectionTimeout := 30;
TAdoConnection(DB).CommandTimeOut := 0;
TAdoConnection(DB).Connected := True;

Die Transaktionen werden wie folgt gestartet:

Delphi-Quellcode:

TAdoConnection(DB).BeginTrans;

Sie werden so commited:

Delphi-Quellcode:

TAdoConnection(DB).CommitTrans;

So läuft der Rollback:

Delphi-Quellcode:

TAdoConnection(DB).RollbackTrans;

So läuft die Überprüfung, ob eine Transaktion gestartet ist

Delphi-Quellcode:

result := TAdoConnection(DB).InTransaction;

Die Transaktionen werden immer so kurz wie möglich und so lang wie nötig offen gehalten. Leider kann es aber vorkommen, dass eine Nutzer halt einen Kaffee trinken gehen und somit eine Transaktion geöffnet bleibt (Das alte Leiden halt).

Nun meine Frage:
Welche Einstellungen, sollten für einen Mehrbenutzer-Betrieb einer SQL Server Datenbank mit Transaktionsnutzung, für die ADO-Connnection gelten? Wie sollten die Transaktionen gestartet und beendet werden? Was ist allgemein im Mehrbenutzerbetrieb mit ADO zu beachten? Es gibt viele Indizies auf den einzelnen Tabellen, haben diese damit was zu tun?

Vielen Dank für eure Mithilfe.

[mjustin]

Delphi-Quellcode:

TAdoConnection(DB).Attributes := [xaCommitRetaining, xaAbortRetaining];
[b]TAdoConnection(DB).IsolationLevel := ilReadUncommitted;[/b]
TAdoConnection(DB).LoginPrompt := False;
TAdoConnection(DB).ConnectionTimeout := 30;
TAdoConnection(DB).CommandTimeOut := 0;
TAdoConnection(DB).Connected := True;

"Read Uncommitted" als Transaktionslevel ist eigenartig:

Zitat:

When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.

http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx

[sega84]

Zitat:

"Read Uncommitted" als Transaktionslevel ist eigenartig:

Das stimmt leider. Das Problem liegt daran, dass trotz einer geöffneten Transaktion ein anderer Benutzer auf den Datensatz zugreifen muss. Natürlich kenne ich bereits das Dirty-Read Problem, nur kann ich es leider nicht umgehen.
Wäre ein anderes Transaktionslevel sinnvoller?

[mjustin]

Zitat:

Zitat von sega84

Zitat:

"Read Uncommitted" als Transaktionslevel ist eigenartig:

Das stimmt leider. Das Problem liegt daran, dass trotz einer geöffneten Transaktion ein anderer Benutzer auf den Datensatz zugreifen muss. Natürlich kenne ich bereits das Dirty-Read Problem, nur kann ich es leider nicht umgehen.
Wäre ein anderes Transaktionslevel sinnvoller?

Wenn Read Uncommitted verwendet merden muss, kann man natürlich nichst machen. Wir verwenden in einer ähnlichen Umgebung Read Committed. Auch damit kann man ja auf Datensätze zugreifen, die in einer anderen Transaktion verwendet werden. (Man sieht aber im Unterschied zu Read Uncommitted nur die Datenfeldwerte, die committed wurden.)

[Bernhard Geyer]

AFAIK unterstützen neuere MS SQL Sersionen das Multiversionskonzept so das Deadlocks bei weiten weniger häufiger Auftreten.

[sega84]

Zitat:

Zitat von mjustin

Wir verwenden in einer ähnlichen Umgebung Read Committed. Auch damit kann man ja auf Datensätze zugreifen, die in einer anderen Transaktion verwendet werden. (Man sieht aber im Unterschied zu Read Uncommitted nur die Datenfeldwerte, die committed wurden.)

Werde es mal ausprobieren. Wie arbeitet Ihr mit Transaktionen? Wann und wie startet ihr diese?

[iKilledKenny]

Was mich verwundert, ist die Länge einer "offenen Transaktion". Ich weiss ja nun nicht wie genau die Architektur deiner Anwendung ist, jedoch ists bei uns so, dass für jede Aktion (Insert, Update, Delete eines Records oder einer Liste von Records) eine eigene Transaktion gestartet wird, die Transaktion also nur für den tatsächlichen Datenbankzugriff offen ist und danach committed wird oder im Fehlerfall eben ge-rollback-t wird.

Deshalb haben wir kein "ReadUncommitted", eine Userzahl von ca. 600-1000 Anwender weltweit auf einem DB-Server und ein verteilte Architektur mit MiddleWare-Server.

[sega84]

Kleines Beispiel:
Nutzer 1:
Wir starten eine Fakturierung zur Rechnungserzeugung (StartTransaction).
Diese Fakturierung erzeugt die Rechnung, die Rechnungspostionen, etc.
Dieser Lauf dauert bei uns etwa 5 - 30 Minuten. Nun kann der Anwender zwischendurch die Sache komplett abbrechen.
Beim Abbruch sollte somit ein Rollback ausgeführt werden. Wenn nicht ein Commit.
Da wir in der Transaktion "Select"-Abfragen auf andere Tabellen fahren (z.B. um Artikel, Faktor und Texte zu ermitteln) kommt es somit zu Speeren auf anderen Tabellen (z.b. Tabelle Artikel).

Nutzer 2:
Wenn nun ein anderer Nutzer allerdings z.b. Einen Druck aller Arikel durchführt bekommt dieser eine Speeranforderung vom SQL Server, da alle Datensätze durch die geöffnete Transaktion von Nutzer 1 gesppert sind.

Wie kann ich dieses besser umgehen?

[hoika]

Hallo,

ein Hoch auf MGA bei Firebird ... ;)

So, nun im Ernst.

Verwende so oft wie möglich With NoLock
NoLock

Ab wann es das beim MS-SQL gibt, musst du noch herausfinden.


Heiko

[sega84]

With No Lock gibt es unterm SQL Server.
Allerdings müssen dann ca. 5000 Querys von mir geändert werden... :wall: Da freue ich mich jetzt schon drauf...
Naja gibt es sonst vielleicht eine andere Möglichkeit die Sperren zu verhindern?