Wybierz do aktualizacji za pomocą SQL Server

Mam podobny problem, chcę zablokować tylko 1 wiersz. O ile wiem, z opcją UPDLOCK, SQLServer blokuje wszystkie wiersze, które musi przeczytać, aby uzyskać wiersz. Tak więc, jeśli nie zdefiniujesz indeksu w celu bezpośredniego dostępu do wiersza, wszystkie poprzedzające wiersze zostaną zablokowane. W twoim przykładzie:

Ponieważ masz tabelę o nazwie TBL z polem id. Chcesz zablokować rząd za pomocą id=10. Musisz zdefiniować indeks dla identyfikatora pola (lub innych pól, które są w tobie zaangażowane Wybierz):

CREATE INDEX TBLINDEX ON TBL ( id )

A następnie Twoje zapytanie, aby zablokować tylko wiersze, które przeczytałeś, to:

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

Jeśli nie używasz opcji INDEX(TBLINDEX), SQLSERVER musi odczytać wszystkie wiersze od początku tabeli, aby znaleźć wiersz z id=10, więc te wiersze zostaną zablokowane.

Nie można mieć jednocześnie izolacji migawek i blokowania odczytów. Celem izolacji migawek jest zapobieganie blokowaniu odczytów.

Try (updlock, rowlock)

Pełna odpowiedź może zagłębić się w wewnętrzne systemy DBMS. To zależy od tego, jak działa silnik zapytań (który wykonuje plan zapytań wygenerowany przez optymalizator SQL).

Jednakże, jednym z możliwych wyjaśnień (dotyczy przynajmniej niektórych wersji niektórych DBMS - niekoniecznie MS SQL Server) jest to, że w kolumnie ID nie ma indeksu, więc każdy proces próbujący wykonać zapytanie z 'WHERE id = ? ' kończy się wykonaniem sekwencyjnego skanowania tabeli i że sekwencyjne skanowanie powoduje blokadę który zastosował twój proces. Możesz również napotkać problemy, jeśli DBMS domyślnie stosuje blokowanie na poziomie strony; blokowanie jednego wiersza blokuje całą stronę i wszystkie wiersze na tej stronie.

Jest kilka sposobów, aby obalić to jako źródło kłopotów. Spójrz na plan zapytań; zbadaj indeksy; spróbuj wybrać z ID 1000000 zamiast 1 i sprawdź, czy inne procesy są nadal zablokowane.

Być może uczynienie mvcc stałym mogłoby to rozwiązać (w przeciwieństwie do konkretnych partii tylko: Ustaw migawkę poziomu izolacji transakcji):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[edycja: Październik 14]

Po przeczytaniu tego: lepsza współbieżność w Oracle niż SQL Server? i to: http://msdn.microsoft.com/en-us/library/ms175095.aspx

Gdy READ_COMMITTED_SNAPSHOT opcja bazy danych jest ustawiona na, mechanizmy stosowane do obsługi opcji są aktywowane natychmiast. Kiedy ustawianie READ_COMMITTED_SNAPSHOT opcja, tylko połączenie wykonujące dozwolone jest polecenie ALTER DATABASE w bazie danych. Nie może być inne otwarte połączenie w bazie danych dopóki baza danych ALTER nie zostanie ukończona. Na baza danych nie musi być w tryb dla jednego użytkownika.

Doszedłem do wniosku, że aby aktywować MVCC mssql na stałe w danej bazie danych, trzeba ustawić dwa FLAG:

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

Ok, pojedynczy select domyślnie używa izolacji transakcji "read Committed", która blokuje i tym samym zatrzymuje zapis do tego zestawu. Możesz zmienić poziom izolacji transakcji za pomocą

Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

Są one szczegółowo wyjaśnione w SQL Server BOL

Następnym problemem jest to, że domyślnie SQL Server 2K5 eskaluje blokady, jeśli masz więcej niż ~2500 blokad lub używasz więcej niż 40% 'normalnej' pamięci w transakcji blokady. Eskalacja idzie na stronę, a następnie tabelę lock

Możesz wyłączyć tę eskalację, ustawiając "trace flag" 1211t, zobacz BOL, aby uzyskać więcej informacji

Zakładam, że nie chcesz, aby żadna inna sesja była w stanie odczytać wiersz podczas uruchamiania tego konkretnego zapytania...

Owinięcie SELECT w transakcję podczas używania podpowiedzi blokującej (XLOCK,READPAST) spowoduje uzyskanie pożądanych wyników. Upewnij się tylko, że inne współbieżne odczyty nie używają WITH (NOLOCK). READPAST pozwala innym sesjom wykonać to samo SELECT, ale w innych wierszach.

BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT

Blokady aplikacji są jednym ze sposobów na zrolowanie własnego zamka z niestandardową szczegółowością, unikając" pomocnej " eskalacji blokady. Zobacz sp_getapplock .

Utwórz fałszywą aktualizację, aby wymusić blokadę rowlock.

UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

Po Tym "UPDATE " możesz zrobić swoje SELECT (ROWLOCK) i kolejne aktualizacje.

Spróbuj użyć:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

Powinno to uczynić blokadę wyłączną i utrzymać ją na czas trwania transakcji.

Zgodnie z Ten artykuł, rozwiązaniem jest użycie podpowiedzi WITH (REPEATABLEREAD).

Wróć do wszystkich zapytań, może masz jakieś zapytanie, które Wybierz bez ROWLOCK/dla podpowiedzi aktualizacji z tej samej tabeli, którą wybrałeś dla aktualizacji.

MSSQL często eskaluje te blokady wierszy do blokad na poziomie strony (nawet blokad na poziomie tabeli, jeśli nie masz indeksu w polu, które pytasz), zobacz to Wyjaśnienie . Ponieważ prosisz o aktualizację, mogę założyć, że potrzebujesz solidności na poziomie transakcji(np. finansowej, inwentaryzacyjnej itp.). Więc porady na tej stronie nie są dotyczy Twojego problemu. To tylko wgląd, dlaczego MSSQL eskaluje blokady .

Jeśli używasz już MSSQL 2005( i up), Są one oparte na MVCC, myślę, że nie powinieneś mieć problemu z blokadą na poziomie wiersza za pomocą podpowiedzi ROWLOCK/UPDLOCK. Ale jeśli używasz już MSSQL 2005 i nowszych, spróbuj sprawdzić niektóre z zapytań, które pytają o tę samą tabelę, którą chcesz zaktualizować, jeśli eskalują blokady, sprawdzając pola na klauzuli WHERE, jeśli mają indeks.

P. S.
Używam PostgreSQL, używa również MVCC have do aktualizacji, nie napotykam tego samego problemu. Eskalacje blokad są tym, co rozwiązuje MVCC, więc byłbym zaskoczony, gdyby MSSQL 2005 nadal eskalował blokady w tabeli z klauzulami WHERE, które nie mają indeksu na swoich polach. Jeśli tak (lock escalation) jest nadal w MSSQL 2005, spróbuj sprawdzić pola gdzie klauzule, jeśli mają indeks.

Zastrzeżenie: moje ostatnie użycie MSSQL jest tylko wersja 2000.

Musisz poradzić sobie z wyjątkiem w czasie commit i powtórzyć transakcję.

Pytanie - czy udowodniono, że ten przypadek jest wynikiem eskalacji blokady (tzn. jeśli prześledzisz za pomocą profilera zdarzenia eskalacji blokady, to na pewno to, co się dzieje, powoduje blokadę)? Jeśli tak, istnieje pełne wyjaśnienie i (raczej ekstremalne) obejście poprzez włączenie znacznika śledzenia na poziomie wystąpienia, aby zapobiec eskalacji blokady. Zobacz http://support.microsoft.com/kb/323630 znacznik 1211

Ale to prawdopodobnie będzie miało niezamierzone skutki uboczne.

Jeśli jesteś celowe blokowanie wiersza i utrzymywanie go zablokowanego przez dłuższy czas, a następnie używanie wewnętrznego mechanizmu blokowania transakcji nie jest najlepszą metodą (przynajmniej w SQL Server). Cała optymalizacja w SQL Server jest nastawiona na krótkie transakcje - wejdź, dokonaj aktualizacji, wyjdź. To jest powód eskalacji blokady.

Więc jeśli intencją jest" sprawdzenie " wiersza przez dłuższy czas, zamiast blokowania transakcji najlepiej użyć kolumny z wartościami i prosta instrukcja ol ' update, aby oznaczyć wiersze jako zablokowane lub nie.

Rozwiązałem problem rowlock w zupełnie inny sposób. Zdałem sobie sprawę, że sql server nie był w stanie zarządzać taką blokadą w satysfakcjonujący sposób. Zdecydowałem się rozwiązać to z programowego punktu widzenia za pomocą mutex... waitForLock... / align = "left" / ..

Próbowałeś READPAST?

Używałem UPDLOCK i READPAST razem, gdy traktowałem tabelę jak kolejkę.

Może spróbujesz najpierw zrobić prostą aktualizację tego wiersza (bez zmieniania żadnych danych)? Następnie możesz kontynuować wiersz, jak w został wybrany do aktualizacji.

UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

Edit : należy zawinąć w transakcję oczywiście

Edit 2 : innym rozwiązaniem jest użycie SERIALIZOWALNEGO poziomu izolacji