S-Lock auf gesamter Tabelle beim Lesen durch MS-Access

[grgushi]

Hallo,

ich hoffe ich kann mein Problem korrekt schildern:

Ich habe seit einiger Zeit eine MS-SQL-Server-2000 (SP3) Datenbank auf die ich mittels ODBC aus einem MS-Access2000 (mdb -- kein Projekt) zugreife.

Seit ein paar Wochen (der genaue Zeitpunkt ist leider unbekannt) kann ich in einer Tabelle keine Datensätze mehr anlegen, editieren oder löschen. Bei genauerer Kontrolle ist mir aufgefallen, daß auf dieser Tabelle ausschliesslich Tabllen s-Lock's eingetragen sind, während auf allen anderen Tabellen jeweils nur eine ID, bzw. eine Seite mit S versehen ist, die TAB selbst jeweils nur mit IS.

Auf der Suche nach dem Grund dafür habe ich die DB erst einmal auf einen Testserver importiert (Backup eingespielt) und habe das Problem dort 1:1 wiedergefunden. Dann habe ich ein neues MS-Access Frontend erstellt, daß nichts tut ausser per ODBC auf die eine Tabelle zuzugreifen. (also Tabelle verknüpft und sonst nichts eingestellt.) Beim Öffnen der Tabelle in Access sehe ich sofort wieder das TAB - S - Lock, ohne, daß ich auch nur versucht hätte irgendwas zu editieren.
Ich habe als nächstes die Tabelle mittels dem Enterprisemanager kopiert. (Wodurch ja alle Trigger, Constraints etc. "verloren" gehen.) Der Effekt bleibt weiterhin derselbe: Öffnen der kopierten Tabelle: sofort S-Lock auf TAB.

Ich bin echt ratlos, woran das liegen könnte - jede andere Tabelle (und deren Kopien) kann ich ganz normal aufmachen und bekomme eine ID/Seitensperre S und auf der Tab lediglich ein IS -- nur diese eine Tabelle sperrt sich dauernd wieder.

Ich bin der Meinung, daß es nicht an irgendwelchen Versionen liegen kann, da sich ja alle Tabellen bis auf diese eine ganz normal verhalten und keine Version verändert wurde meines Wissens. Server identisch, Access identisch, ODBC identisch. Der Fehler tritt auch mit verschiedenen Versionen auf. (Access 2000, Access 2003, Windows Server 2000(DB), Windows Server 2003(DB))

Es handelt sich imho nicht um diese automatische Sperreneskalation des SQL-Servers, das habe ich bereits mittels den Angaben eines MS-Knowledge-base Artikels geprüft (Lock:Escalation im Profiler gesucht)

Kann man irgendwo einstellen, daß eine Tabelle sofort ein Tab-S-Lock bekommt? Kann man im Access sowas irgendwo einstellen?

Vielen Dank schon mal, falls jemand was weiss,

Frank

[Andreas Kosch]

Hallo,

Kann man irgendwo einstellen, daß eine Tabelle sofort ein Tab-S-Lock bekommt?

Ja - denn der Isolationsgrad einer Transaktion legt automatisch fest, wie lange die Lesesperren (S-Locks) gehalten werden. Wenn ein Client im Isolationsgrand REPEATABLE READ oder SERIALIZABLE alle Datensätze der Tabelle einliest, bleibt das S-Lock für die komplette Zeitdauer der Transaktion bestehen. Im Gegensatz dazu wirkt beim Isolationsgrad READ COMMITTED der S-Lock nur sehr kurzzeitig für die Zeitdauer des physischen Zugriffs auf die Daten aktiv, aber nicht für die ganze Transaktionsdauer.

Der Isolationsgrad kann unter anderem über die folgenden Wege definiert werden:

• T-SQL Anweisung: SET TRANSACTION ISOLATION LEVEL
• ADO: IsolationLevel-Eigenschaft des Connection-Objekts.
• ADO.NET: BeginTransaction-Methode der SqlConnection-Klasse.
• COM+: Eigenschaft TRANSAKTIONSISOLATIONSEBENE auf der Registerseite TRANSAKTION des Eigenschaftsdialogs des COM+ Objekts (alias .NET Enterprise Service). Die Änderung ist erst ab COM+ 1.5 (Windows 2003 Server beziehungsweise Windows XP Service Pack 1) möglich.

Kann man im Access sowas irgendwo einstellen?

Da bin ich überfragt. Ich kann das Problem mit dem ODBC-Treiber des MS SQL Server und ACCESS 2003 nicht reproduzieren. Gibt es eventuell zusätzliche VBA-Routinen in der mdb-Datei, die auf die MS SQL Server-Datenbank zugreifen?

[Bernhard Geyer]

Evtl. mal die Access DB reparieren bzw. komprimieren. Ich könnte mir Vorstellen das Access auch in neueren Versionen immer noch zu selbstzerstörung neigt.

[grgushi]

vielen Dank erst mal für die schnellen Antworten.

Mein Problem läßt sich damit leider nicht beheben. Die Access DB habe ich (natürlich) bereits komprimiert und repariert. Ich hab auch eine komplett neue, leere Access DB erstellt, um auszuschliessen, daß es an der DB bzw. irgendwelchen darin enthaltenen Dingen liegt. Ich habe nun eine vollkommen leere mdb, in der nur diese eine Problem-Tabelle verbunden ist. Zusätzlich ist in dieser DB noch eine zweite Verknüpfung auf eine "funktionierende" Tabelle die in derselben DB auf demselben Server liegt.

Ich kann diesen Effekt leider auch nicht reproduzieren. Es ist lediglich bei dieser einen Tabelle. (Dann aber auch, wenn ich sie kopiere, umbenenne usw.) Kann sowas am Inhalt einer Tabelle liegen? Sie enthält u.a. ein ntext Feld und ist unschön breit mit 48 Spalten (Datetime und ntext(30)).

Grüße,

Frank

[grgushi]

PS: Zur Programmierung verwende ich VBA, DAO. ADO hat bisher noch keinen Einzug in die Anwendung gehalten. Da das Problem aber bereits bei einer verknüpften Tabelle entsteht, weiss ich nicht, wo ich da den Isolation Level setzen könnte. Kann man das evtl. im ODBC-connection String machen?

Frank

[tinof]

Hallo und guten Tag hier im Forum,

da dies mein erster Post hier ist, eine kurze Vorstellung:
Ich bin freiberuflicher Softwareentwickler und programmiere (inzwischen so an die 14 Jahre) hautpsächlich in Delphi (alle Versionen, bisher noch ohne .net), MS - SQL - Server / Pervasive.SQL sowie in Access / VBA, neuerdings auch PHP. Ich habe schon hin und wieder passiv hier mitgelesen und durfte auch Herrn Kosch schon zu verschiedenen EKons kennenlernen (**einschleim**).

Ok, ich bin auch in das Problem von grgushi involviert und möchte deshalb folgende Erkenntnisse (und Fragen) hinzufügen.

ich habe :
- alle ntext - Felder der Tabelle erstmal ersatzlos gelöscht -> keine Änderung

- Tabelle vollständig geleert -> jetzt keine Sperren mehr beim Öffnen (gar keine ?!)
-> manuell einige Sätze eingefügt - alles geht fein. Wohlgemerkt, keine Änderungen an den Constrainsts / Triggern usw. - mit 2-3 Datensätzen geht alles
-> per Select .. Into viele (vorher genauso 'gesicherte') Datensätze hinzugefügt, dabei die Anzahl der Sätze schrittweise erhöht : ab ca. 950 Datensätze tritt das Problem wieder auf - TAB/S - Sperre wieder da.
Bis zu dieser Datensatzanzahl klappt alles problemlos.

Die Anwendung lief bereits mehrere Jahre klaglos, zeitweise mit weit mehr als 50 konkurrierenden Benutzern, es gab nie solche Probleme.

Nun läßt sich das Problem plötzlich auf allen Test- und Produktivrechnern reproduzieren, ich habe z.B. eine Maschine mit Windows XP SQL Server 2000 und SP4, Clients Windows XP und Windows 2000.

Kann es sein, das durch eines der letzten Windows - Updates etwas in den MS DAC - Bibliotheken verändert wurde (MDAC 2.8 SP1 auf winXP bzw. MDAC 2.7 Refresh auf Win2000) ? Oder in server - relevanten Bibliothen ?

Vielen Dank für Ideen und Hinweise !

Tino

[tinof]

Hallo nochmal,

ich habe die gleiche DB inzwischen mal in ein Access - Projekt (==ADO) eingebunden. Dort tritt das Problem nicht mehr auf.

Deshalb jetzt wieder mal eine konkrete Frage (sorry für die etwas konfusen Erkläuterungen oben!) :

Wie kann man das Sperrverhalten in einer Access -> Jet -> ODBC -> SQL - Server - Umgebung generell beeinflussen ?

Welche Auswirkungen haben die Access - Optionen ("DB mit Sperrung auf Datensatzebene öffnen") usw in dieser Konfiguration ?

Gibt es weitere 'Stellmöglichkeiten' im ODBC - Treiber / in der Registry, die sích auf das Sperrverhalten auswirken ?

Nochmal Danke
Tino

[Andreas Kosch]

Hallo tinof,

willkommen im Forum.

..ab ca. 950 Datensätze tritt das Problem wieder auf - TAB/S - Sperre wieder da.

Anscheinend sorgt der lokale Datenpuffer der Treiberschicht dafür, dass ab einer bestimmten Datenmenge ein S-Lock auf die Tabelle gesetzt wird, um den lokalen Pufferinhalt garantiert auf den gleichen Stand zu halten wie die Datenbanktabelle.

ich habe die gleiche DB inzwischen mal in ein Access - Projekt (==ADO) eingebunden. Dort tritt das Problem nicht mehr auf.

Weil beim RecordSet von ADO der lokale Datenpuffer über die Eigenschaft CursorLocation abgeschaltet werden kann.

Gibt es weitere 'Stellmöglichkeiten' im ODBC - Treiber / in der Registry, die sích auf das Sperrverhalten auswirken ?

Bei der SELECT-Abfrage könnte der Table-Hint WITH (NOLOCK) verwendet werden.

[tinof]

Danke für die Antwort.

Komischerweise tritt dieses Verhalten nur bei einer einzigen von ca. 100 Tabellen auf. Alle anderen verhalten sich 'normal', obwohl da auch recht große Tabellen dabei sind. Auch haben wir das Problem erst seit 3 Wochen, vorher ging alles (wie immer :-) ), es gab auch keine Änderungen am Programm.

Die ganze Anwendung verwendet DAO.Recordsets, deshalb klappt der (NOLOCK) - Tip leider nicht. Zumindest erhalte ich immer Syntaxfehler.

Wir bauen ein Auf ADO basierendes 'Not' - Frontend, um erstmal weiter arbeitsfähig zu sein. Vielleicht ergibt sich ja doch noch eine Lösung..

Also, danke nochmal und schöne Woche.

Grüße
Tino

[tinof]

Hallo nochmal,

ich muss nochmal Danke sagen ! Angeregt durch den Post von Andreas Kosch habe ich nochmal über das NOLOCK nachgedacht :

CREATE VIEW V_[MeineTabelle]
AS SELECT * FROM [MeineTabelle] (NOLOCK)

-> und dann diese View statt der Tabelle einbinden.

Das scheint zu gehen, ohne das wir an der Anwendung etwas machen müssen (außer der anderen Verknüpfung).
Mal sehen, wie das in der Praxis ausschaut. Im 'Labor' jedenfalls vielversprechend.

Also, DANKE,
Tino

P.S. Trotzdem ist das Ganze ziemlich "schwammig" :-<