Access sperrt SQL-Server-Tabellen
Lutz Uhlmann
Hoffe hier lesen noch welche ;)
Habe folgendes Problem mit einer Anwendung, bei dem die Auswirkungen
zwar auf dem SQL-Server zu spüren sind, die Ursachen aber evtl im Access
liegen.
Ich greife mit Access 2003 auf einen SQL-Server 2005 zu.
In meinem FE habe ich SQl-Server-Tabellen und -Sichten per ODBC
verknüpft. Seit einigen Tagen passiert es, daß wenn ich einem Formular
einen SELECT zuweise, welcher auf eine verknüpfte Sicht zugreift, daß
dann Tabellen im Server blockiert werden.
Dabei spielt wahrscheinlich folgendes Verhalten eine Rolle.
Das Endlosformular läd die Abfrage. Die scheint insgesamt so
kompliziert, daß Sperren auf dem Server angelegt werden. Offensichtlich
werden es soviele, daß eine LockEscalation eintritt und er gleich die
ganze Tabelle sperrt.
Da Access seinerseits nur einen Teil der Daten auf einmal läd (soviele
wie anzuzeigen sind) bleibt die Verbindung und damit auch die Sperrung
erhalten. Springe ich mit den Navigationspfeilen ans Ende des
Endlosfomulares wird die Sperrung aufgehoben - vermutlich, da nun alle
Daten gelesen sind.
Wir versuchen derzeit verschiedene Maßnahmen auf dem SQL-Server:
- Vereinfachung der Abfragen
- Verbesserung der Indizierung
Was kann ich evtl Access-seitig noch tun?
- Index auf die verknüpfte Sicht??
Haben wir probiert, ist allerdings nicht so leicht, da man keinen
eindeutigen PK in der Abfrage hat. Bei einem Versuch war die Sperrung
weg, allerdings wurde die Anzeige des Formulars langsam und das Formular
fing ständig an die Anzeige zu refreshen (als würde jemadn ständig auf
Reload drücken ;)
- kann man irgendwie veranlassen, daß Access alle Daten in einem Rutsch läd?
- umgehen von ODBC?
- anderer Aufruf?
Bzw wie kann ich im SQL-Server den Anforderungen von Access noch
entgegenkommen?
Sind ein bißchen ratlos, da wir derzeit die Fehlerursache nicht 100%
identifizieren können bzw unser Gegenmaßnahmen nur bedingt fruchten.
Die Software lief über ein Jahr ohne Probleme - bis Mittwoch ;)
Lutz
P.S. Ein Beispiel
Formularquelle:
SELECT * FROM a_stoerung9 WHERE ID > 0 AND AUFNAHME IN (1009,1027,2110)
ORDER BY MELDUNGAM DESC, PRIO
a_stoerung9 ist eine verknüpfte SQL-Server-Sicht:
SELECT schAnl.DB_PASSWD.NUTZ AS AUFTRAGVON,
schAnl.a_auftrag_kz_hand.NDATE AS AUFTRAGAM,
schAnl.a_auftrag_kz_hand.AUFUSER AS AUFNAHME,
schStoer.DB_STOERUNG.MELDUNGVON, schStoer.DB_STOERUNG.MELDUNGAM,
schStoer.DB_STOERUNG.MANGEL, schAnl.DB_ANGEBOT.NR,
schAnl.DB_ANGEBOT.STO_ID, schAnl.DB_ANGEBOT.ANL_TYP,
schAnl.DB_ANGEBOT.KON_ID, schAnl.DB_ANGEBOT.BEMERKUNG,
schAnl.DB_ANGEBOT.MASS_ID, schAnl.DB_ANGEBOT.ZUSTAND,
schAnl.DB_ANGEBOT.AENDE, CAST(schStoer.DB_STOERUNG.KENNER AS NVARCHAR) +
CAST(schStoer.DB_STOERUNG.ZAEHLER AS NVARCHAR) +
CAST(schAnl.DB_ANGEBOT.NR AS NVARCHAR) AS NUMMER,
schAnl.a_auftrag_kz_hand.PRIO, schAnl.a_auftrag_kz_hand.AUFKZ, 0 AS
ERLSTO, schAnl.DB_TERMARC.AUF_ID, schAnl.DB_TERMARC.PRUSCHL_ID,
schAnl.DB_TERMARC.FIRMA, schAnl.a_anlagen_alle.ID,
schAnl.a_anlagen_alle.TYP, schAnl.a_anlagen_alle.NUMMER AS SCHL,
schAnl.a_anlagen_alle.BEZ, schAnl.a_anlagen_alle.KEN,
schAnl.a_anlagen_alle.STANDORT, schAnl.a_anlagen_alle.ORT,
schAnl.a_anlagen_alle.B10, schAnl.a_anlagen_alle.STAMMNR,
schAnl.a_anlagen_alle.WPA
FROM schStoer.DB_STOERUNG INNER JOIN
schAnl.DB_ANGEBOT ON schStoer.DB_STOERUNG.ID =
schAnl.DB_ANGEBOT.STO_ID INNER JOIN
schAnl.a_auftrag_kz_hand ON schAnl.DB_ANGEBOT.AUF_ID =
schAnl.a_auftrag_kz_hand.ID INNER JOIN
schAnl.DB_TERMARC ON schAnl.a_auftrag_kz_hand.ID =
schAnl.DB_TERMARC.AUF_ID INNER JOIN
schAnl.a_anlagen_alle ON schAnl.DB_TERMARC.ID =
schAnl.a_anlagen_alle.ID LEFT OUTER JOIN
schAnl.DB_PASSWD ON schAnl.a_auftrag_kz_hand.AUFUSER =
schAnl.DB_PASSWD.ID
WHERE (schAnl.DB_ANGEBOT.AENDE > DATEADD(month, - 2, GETDATE()))
Josef Poetzl
Lutz Uhlmann schrieb:
> Hoffe hier lesen noch welche ;)
Du könntest als Alternative de.comp.datenbanken.ms-access verwenden.
> In meinem FE habe ich SQl-Server-Tabellen und -Sichten per ODBC
> verknüpft. Seit einigen Tagen passiert es, daß wenn ich einem Formular
> einen SELECT zuweise, welcher auf eine verknüpfte Sicht zugreift, daß
> dann Tabellen im Server blockiert werden.
Ich hatte das Problem auch einmal bei einer einzigen Sicht oder Prozedur
(bin mir nicht mehr sicher). Es war aber auch eine relativ umfangreiche
SQL-Anweisung mit vielen Tabellen.
Sobald diese Sicht in einem Access-Formular (nur lesend) geöffnet wurde,
wurden die Datensätze aus einer in der Sicht verwendeten Tabelle
gesperrt.
Meine Problemumgehung war ein /WITH (NOLOCK)/ bei der einen Tabelle in
der From-Teil der SQL-Anweisung.
SELECT
...
FROM
TABELLE WITH (NOLOCK)
INNER JOIN
TABELLE2
ON ...
mfg
Josef
--
Code-Bibliothek für Access-Entwickler: http://access-codelib.net/
Access-FAQ von Karl Donaubauer: http://www.donkarl.com/
Peter Doering
Lutz Uhlmann wrote:
> Hoffe hier lesen noch welche ;)
Aber hallo ;-)
> In meinem FE habe ich SQl-Server-Tabellen und -Sichten per ODBC
> verknüpft. Seit einigen Tagen passiert es, daß wenn ich einem Formular
> einen SELECT zuweise, welcher auf eine verknüpfte Sicht zugreift, daß
> dann Tabellen im Server blockiert werden.
> [...]
Was passiert, wenn du die Abfrage als Snapshot ausfuehrst? Entweder in der
Abfrage selbst (Abfrage-Editor - Eigenschaften - Recorsettyp) oder im
Formular.
> Formularquelle:
> SELECT * FROM [...]
Zu Select * brauche ich dir wohl nichts sagen, oder? ;-)
Gruss - Peter
--
Mitglied im http://www.dbdev.org
FAQ: http://www.donkarl.com
Lutz Uhlmann
> Hallo!
>
> Lutz Uhlmann schrieb:
>> Hoffe hier lesen noch welche ;)
>
> Du könntest als Alternative de.comp.datenbanken.ms-access verwenden.
Stimmt ... aber irgendwie hab ich noch mehr zutrauen!
> Ich hatte das Problem auch einmal bei einer einzigen Sicht oder Prozedur
> (bin mir nicht mehr sicher). Es war aber auch eine relativ umfangreiche
> SQL-Anweisung mit vielen Tabellen.
> Sobald diese Sicht in einem Access-Formular (nur lesend) geöffnet wurde,
> wurden die Datensätze aus einer in der Sicht verwendeten Tabelle
> gesperrt.
> Meine Problemumgehung war ein /WITH (NOLOCK)/ bei der einen Tabelle in
> der From-Teil der SQL-Anweisung.
>
> SELECT
> ...
> FROM
> TABELLE WITH (NOLOCK)
> INNER JOIN
> TABELLE2
> ON ...
Ich habe jetzt in der a_auftrag_kz_hand die gesperrte DB_AUFTRAG-Tabelle
mal um dein WITH (NOLOCK) erweitert.
Ergebnis ist, daß sich Access zwar weiter so verhält (nur teilwieses
Laden, offene Verbindung mit Sperren) diese Sperren aber wohl einen
anderen Typ haben und damit die Tabelle nicht mehr blockieren.
Das Thema mit dem nur lesend fällt mir auch auf.
Die Abfrage ist kompliziert und dient nur der Info. Das heißt ich muß
nix ändern. Trotzdem sind Daten in der Abfrage änderbar, weswegen der
SQL-Server wohl auch entsprechende Sperren setzt.
Mich würde interessieren ob ich solche Abfragen auch explizit als
ReadOnly markieren könnte.
@Peter
> Was passiert, wenn du die Abfrage als Snapshot ausfuehrst? Entweder
> in der Abfrage selbst (Abfrage-Editor - Eigenschaften - Recorsettyp)
> oder im Formular.
Das hatten wir getestet ... hat glaub ich nichts gebracht.
>> Formularquelle:
>> SELECT * FROM [...]
>
> Zu Select * brauche ich dir wohl nichts sagen, oder? ;-)
Naja ... in der Quellabfrage sind nur die Felder drin die auch im
Formular verwendet werden. Von daher reine Faulheit ;)
Oder hat das auch direkte Nachteile???
Lutz
Bernd Gilles
Lutz Uhlmann schrieb:
> Ich habe jetzt in der a_auftrag_kz_hand die gesperrte DB_AUFTRAG-Tabelle
> mal um dein WITH (NOLOCK) erweitert.
> Ergebnis ist, daß sich Access zwar weiter so verhält (nur teilwieses
> Laden, offene Verbindung mit Sperren) diese Sperren aber wohl einen
> anderen Typ haben und damit die Tabelle nicht mehr blockieren.
Hast Du noch JOIN's drin?
Die brauchen dann auch (NOLOCK):
SELECT A.Feld1, A.Feld2, B.Feld1
FROM Tabelle1 A [WITH] (NOLOCK)
INNER JOIN Tabelle2 B [WITH] (NOLOCK) ON B.ID = A.ID
(das WITH kann ggf. weggelassen werden - hängt von der Server-Version ab)
Diese "dirty reads" laufen übrigens nicht nur ohne Sperren, sondern auch deutlich performanter.
Allerdings liest man damit auch noch nicht commitete Änderungen, also Daten, die zwar in der Tabelle
stehen, deren Transaktion aber noch nicht abgeschlossen wurde.
Wird die Transaktion später per rollback verworfen, gibt's die gelesenen Daten nicht mehr.
Sollte man bei der Verwendung von (NOLOCK) zumindest im Hinterkopf behalten.
--
Gruß, Bernd
---
Access goes Subversion - http://oasis.dev2dev.de
Lutz Uhlmann
> Hallo Lutz,
>
> Lutz Uhlmann schrieb:
>> Ich habe jetzt in der a_auftrag_kz_hand die gesperrte
>> DB_AUFTRAG-Tabelle mal um dein WITH (NOLOCK) erweitert.
>> Ergebnis ist, daß sich Access zwar weiter so verhält (nur teilwieses
>> Laden, offene Verbindung mit Sperren) diese Sperren aber wohl einen
>> anderen Typ haben und damit die Tabelle nicht mehr blockieren.
>
> Hast Du noch JOIN's drin?
> Die brauchen dann auch (NOLOCK):
Ja! Warum auch bei den anderen?
> SELECT A.Feld1, A.Feld2, B.Feld1
> FROM Tabelle1 A [WITH] (NOLOCK)
> INNER JOIN Tabelle2 B [WITH] (NOLOCK) ON B.ID = A.ID
>
> (das WITH kann ggf. weggelassen werden - hängt von der Server-Version ab)
Die Sperre ist bisher nur bei Tabelle1 aufgetrteten. Muß ich trotzdem
auch die anderen Tabellen so behandeln?
Die nächste Frage ... funktioniert NOLOCK auch auf Abfragen?
FROM Abfrage1 WITH NOLOCK?
Und falls ja ... wie wirkt das dann?
> Diese "dirty reads" laufen übrigens nicht nur ohne Sperren, sondern auch
> deutlich performanter.
> Allerdings liest man damit auch noch nicht commitete Änderungen, also
> Daten, die zwar in der Tabelle stehen, deren Transaktion aber noch nicht
> abgeschlossen wurde.
> Wird die Transaktion später per rollback verworfen, gibt's die gelesenen
> Daten nicht mehr.
> Sollte man bei der Verwendung von (NOLOCK) zumindest im Hinterkopf
> behalten.
Ja, das hatte ich bereits rausgelesen.
Diese Sache kann in dem Anwendungsfall allerdings vernachlässigt werden.
Peter Doering
Lutz Uhlmann wrote:
> @Peter
>
> >> Formularquelle:
> >> SELECT * FROM [...]
> >
> > Zu Select * brauche ich dir wohl nichts sagen, oder? ;-)
>
> Naja ... in der Quellabfrage sind nur die Felder drin die auch im
> Formular verwendet werden. Von daher reine Faulheit ;)
> Oder hat das auch direkte Nachteile???
Eine Frage des Standpunkts. Als direkten Nachteil sehe ich, wenn mehr
Felder als noetig gelesen werden. Bei dir waere das dann der Fall, wenn du
Felder zur Abfrage aber nicht im Formular hinzufuegst.
Zum Thema Locks: hast du mal versucht, die Abfrage als PT auszufuehren?
Tritt das Problem dann auch auf?
Bernd Gilles
> Ja! Warum auch bei den anderen?
>
>> SELECT A.Feld1, A.Feld2, B.Feld1
>> FROM Tabelle1 A [WITH] (NOLOCK)
>> INNER JOIN Tabelle2 B [WITH] (NOLOCK) ON B.ID = A.ID
>>
> auch die anderen Tabellen so behandeln?
das sollte keinen Effekt auf Tabelle1 haben.
Ich tippe also eher auf die Einstellungen der Verbindung (Snapshot?, ReadOnly?)
Ich würde mal Peters Vorschlag mit den PassThrough's versuchen
> Die nächste Frage ... funktioniert NOLOCK auch auf Abfragen?
> FROM Abfrage1 WITH NOLOCK?
> Und falls ja ... wie wirkt das dann?
Du meinst einen existierenden View auf dem Server?
Ja - funzt!
Wir verwenden in der Firma in allen SELECT's für reine Lesezugriffe NOLOCK.
Eigentlich sogar für die Schreibzugriffe, da unsere Anwendung als 3-Tier mit COM+ an der Ecke sowiso
statuslos arbeitet - aber das ist ein anderes Thema.
Lutz Uhlmann
>>
>> Naja ... in der Quellabfrage sind nur die Felder drin die auch im
>> Formular verwendet werden. Von daher reine Faulheit ;)
>> Oder hat das auch direkte Nachteile???
>
> Eine Frage des Standpunkts. Als direkten Nachteil sehe ich, wenn mehr
> Felder als noetig gelesen werden. Bei dir waere das dann der Fall, wenn du
> Felder zur Abfrage aber nicht im Formular hinzufuegst.
Das stimmt natürlich. Diese Abfrage ist speziell für dieses Formular
zusammengestellt. Im Moment passt das also!
> Zum Thema Locks: hast du mal versucht, die Abfrage als PT auszufuehren?
> Tritt das Problem dann auch auf?
Nein noch nicht ...
Hilf mir mal schnell auf die Sprünge, so viel habe ich mit PT noch nicht
gemacht.
Lutz
Lutz Uhlmann
> Lutz Uhlmann schrieb:
>> Ja! Warum auch bei den anderen?
>>
>>> SELECT A.Feld1, A.Feld2, B.Feld1
>>> FROM Tabelle1 A [WITH] (NOLOCK)
>>> INNER JOIN Tabelle2 B [WITH] (NOLOCK) ON B.ID = A.ID
>>>
>> auch die anderen Tabellen so behandeln?
>
> das sollte keinen Effekt auf Tabelle1 haben.
> Ich tippe also eher auf die Einstellungen der Verbindung (Snapshot?,
> ReadOnly?)
Steh grad auf dem Schlauch ...
Was sollte keinen Effekt auf Tabelle1 haben? Der eine NOLOCK hat auf
jedenfall einen Effekt - die Tabelle wird nicht mehr gesperrt.
Und was meinst du mit den Einstellungen?
>> Die n�chste Frage ... funktioniert NOLOCK auch auf Abfragen?
>> FROM Abfrage1 WITH NOLOCK?
>> Und falls ja ... wie wirkt das dann?
>
> Du meinst einen existierenden View auf dem Server?
> Ja - funzt!
Und wie wird das dann verarbeitet?
Wird das NOLOCK von der View quasi auf die enthaltenen Tabellen vererbt?
> Wir verwenden in der Firma in allen SELECT's f�r reine Lesezugriffe NOLOCK.
> Eigentlich sogar f�r die Schreibzugriffe, da unsere Anwendung als 3-Tier
> mit COM+ an der Ecke sowiso statuslos arbeitet - aber das ist ein
> anderes Thema.
Ich �berlege das �hnlich zu machen. Um �hnlichen Problemen bereits jetzt
vorzubeugen.
Lutz
Bernd Gilles
>>> Die Sperre ist bisher nur bei Tabelle1 aufgetrteten. Muß ich trotzdem
>>> auch die anderen Tabellen so behandeln?
>>
>> das sollte keinen Effekt auf Tabelle1 haben.
>> Ich tippe also eher auf die Einstellungen der Verbindung (Snapshot?,
>> ReadOnly?)
>
> Steh grad auf dem Schlauch ...
> Was sollte keinen Effekt auf Tabelle1 haben? Der eine NOLOCK hat auf
> jedenfall einen Effekt - die Tabelle wird nicht mehr gesperrt.
> Und was meinst du mit den Einstellungen?
Naja, dass die Tabelle nicht mehr gesperrrt wird ist ja nur die halbe Miete, solange die Daten nur
teilweise geladen werden.
Mit den Einstellungen meine ich, wie Deine Verbindung zum Server geöffnet wird.
OpenRecordset() z.B. kann ja ausser dem Statement noch einiges an Parametern empfangen - die sollten
natürlich zum Zweck passen.
Beim reinen Lesen also dbOpenSnapshot und dbReadOnly
CurrentDb.OpenRecordset("SELECT ...", dbOpenSnapshot, dbReadOnly)
Eventuell würde ich auch mal versuchen, das Ganze mit ADO durchzuführen oder die Anwendung auf ADP
umzustellen.
>>> Die nächste Frage ... funktioniert NOLOCK auch auf Abfragen?
>>> FROM Abfrage1 WITH NOLOCK?
>>> Und falls ja ... wie wirkt das dann?
>>
>> Du meinst einen existierenden View auf dem Server?
>> Ja - funzt!
> Und wie wird das dann verarbeitet?
> Wird das NOLOCK von der View quasi auf die enthaltenen Tabellen vererbt?
Meine Aussage bezog sich darauf, dass das Sprachkonstrukt "SELECT * FROM viewMeinView WITH (NOLOCK)"
als solches funktioniert und keinen Fehler wirft.
In den verwendeten Views müssen die Tabellen natürlich auch mit (NOLOCK) gelesen werden, damit der
Effekt auch zum Tragen kommt.
Peter Doering
Lutz Uhlmann wrote:
>
>> Zum Thema Locks: hast du mal versucht, die Abfrage als PT auszufuehren?
>> Tritt das Problem dann auch auf?
>
> Nein noch nicht ...
> Hilf mir mal schnell auf die Sprünge, so viel habe ich mit PT noch nicht
> gemacht.
DAO-Beispiel (Luftcode):
Public Function PT(strSQL As String, strConnect As String) _
As DAO.Recordset
'Fehlerbehandlung nicht vergessen
Dim Dbs As DAO.Database
Set Dbs = DBEngine.Workspaces(0).OpenDatabase("", _
False, False, strConnect)
Set PT = Dbs.OpenRecordset(strSQL, dbOpenSnapshot, _
dbSQLPassThrough)
End Function
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim strConnect As String
strConnect = "ODBC;Driver=SQL Server;"& _
"Server=Server1;Database=DeineDB;Uid=Lutz;Pwd=geheim;"
strSQL = "SELECT * FROM a_stoerung9 WHERE ID > 0 AND AUFNAHME IN " & _
" (1009,1027,2110) ORDER BY MELDUNGAM DESC, PRIO"
Set Me.Recordset = GetSQL(strSQL, strConnect)
End Sub