W normalnych warunkach, czyli z ustawieniami domyślnymi każdy zapis nakłada na czas transakcji blokadę EXCLUSIVE czyli na wyłączność. Odczyty natomiast nakładają tzw. SHARE LOCK, czyli blokadę dzieloną. Na danym rekordzie może istnieć kilka SHARE LOCK, ale tylko jeden EXCLUSIVE LOCK. Jeżeli rekord ma nałożoną blokadę EXCLUSIVE, to nie można nałożyć na niego blokady SHARED.
Takie domyślne działanie można zmieniać poprzez TRANSACTION ISOLATION LEVEL.
Rozważmy taki przykład:
Użytkownik A rozpoczyna swoją transakcję:
BEGIN TRANSACTION
SELECT * FROM Person.Address WHERE PostalCode='92173'
W tym samym czasie (no może ciut później) użytkownik B wstawia rekord o tym samym postal code:
BEGIN TRANSACTION
INSERT INTO [AdventureWorks].[Person].[Address]
([AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate])
VALUES
('Ostrobramska 101A'
,''
,'Warsaw'
,9
,'92173'
,NEWID()
,GETDATE())
Jeśli użytkownik B zatwierdzi swój zapis poprzez COMMIT TRANSACTION, a użytkownik A ponowi swoje zapytanie, to stwierdzi, że między pierwszym odczytem, a drugim odczytem doszło do zmian – dodano jeden rekord. Jeżeli analiza sporządzana w transakcji przez użytkownika A będzie czuła na tego rodzaju zmiany, to będzie można zabezpieczyć się przed zmiennymi odczytami w obrębie jednej transakcji ustawiając poziom izolacji transakcji na SERIALIZABLE.
Użytkownik A zmienia swoje działanie na następujące:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM Person.Address WHEREPostalCode='92173'
Poziom izolacji transakcji SERIALIZABLE nakłada blokadę nie tylko na istniejące rekordy o PostalCode=’92173’, ale także blokuje jakiekolwiek zmiany (w tym dopisywanie) rekordów o wartościach spełniających warunek klauzuli WHERE. Teraz więc, aż do zakończenia transakcji użytkownika A nikt nie może dodawać, usuwać, czy modyfikować rekordów z PostalCode=’92173’.
Rzeczywiście, jeżeli użytkownik B uruchomi teraz swój zapis:
BEGIN TRANSACTION
INSERT INTO [AdventureWorks].[Person].[Address]
([AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate])
VALUES
('Ostrobramska 101A'
,''
,'Warsaw'
,9
,'92173'
,NEWID()
,GETDATE())
To jego zapytanie będzie czekać na zwolnienie blokady. Dopiero kiedy użytkownik A zakończy swoją transakcję przez COMMIT lub ROLLBACK, to transakcja użytkownika B będzie mogła kontynuować pracę.
TRANSACTION ISOLATION LEVEL SERIALIZABLE jest jednym z mocniejszych w SQL serwerze i jego nadużywanie może spowodować wydłużenie czasów blokad.
Przejrzyj także inne artykuły o transakcjach w TSQL publikowane na mobilo (mobilo24.eu):
- Jaka jest różnica między SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED a Query Hint NOLOCK?
- Co robi SAVE TRANSACTION w TSQL?
- Jak zobaczyć zawartość log-a transkacyjnego w MS SQL Server
