1) Pentru UPDATE
- când sunt citite înregistrările acestea sunt blocate folosind lock-uri U iar atunci
- când trebuiesc modificate înregistrările, lock-urile U sunt convertite în lock-uri X.
Lock-urile U sunt compatibile cu cele S(hared) dar incompatibile cu alte lock-uri U sau X.
Asta pp. că dacă două sesiuni încearcă să modifice aceeași înregistrare, prima sesiune v-a obține un lock U în timp ce a doua sesiune va trebui să aștepte finalizare tranzacției din prima sesiune.
Dacă rulezi următorul script în trei sesiuni separate SSMS
--Sesiunea 1
--DROP TABLE Test
CREATE TABLE dbo.Test
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
);
GO
INSERT dbo.Test(Col1, Col2, Col3)
VALUES (1,11,111), (2,22,222), (2,33,333), (2,44,444), (3,55,555);
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
SELECT *
FROM .dbo.Test
WHERE Col1 = 3;
--ROLLBACK;
--Sesiunea 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
UPDATE dbo.Test
SET Col2 = Col2 * 10;
ROLLBACK;
--Sesiunea 3
SELECT l.resource_type, l.resource_description, l.request_mode, l.request_type, l.request_status, l.request_session_id
FROM sys.dm_tran_locks l
WHERE l.resource_type = 'KEY'
ORDER BY l.resource_description, l.request_session_id;
SELECT *, %%lockres%% AS resource_description
FROM dbo.Test WITH(NOLOCK)
ORDER BY %%lockres%%;
se observă la final (sesiunea 3)
resource_type resource_description request_mode request_type request_status request_session_id
------------- -------------------- ------------ ------------ -------------- ------------------
KEY (010086470766) X LOCK GRANT 57
KEY (020068e8b274) X LOCK GRANT 57
KEY (03000d8f0ecc) X LOCK GRANT 57
KEY (0400b4b7d951) X LOCK GRANT 57
KEY (0500d1d065e9) S LOCK GRANT 56
KEY (0500d1d065e9) X LOCK CONVERT 57
KEY (0500d1d065e9) U LOCK GRANT 57
(7 row(s) affected)
ID Col1 Col2 Col3 resource_description
----------- ----------- ----------- ----------- --------------------------------
1 1 110 111 (010086470766)
2 2 220 222 (020068e8b274)
3 2 330 333 (03000d8f0ecc)
4 2 440 444 (0400b4b7d951)
5 3 55 555 (0500d1d065e9)
(5 row(s) affected)
faptul că
- sesiunea 1 @@spid=56 execută SELECT și obţine (request_status=GRANT) un lock S pe înregistrarea ID=4 / (0500d1d065e9), înregistrare care verifică condiţia Col1 = 3
- sesiunea 2 @@spid=57 execută UPDATE și obţine un lock U (request_status=GRANT) pe acceași înregistrare ID=4 / (0500d1d065e9) la citirea înregistrării (U este compatibil cu S)
- sesiunea 2 @@spid=57 execută UPDATE și încearcă să obțină un lock X (request_status=CONVERT) pe înregistrarea ID=4 / (0500d1d065e9); Altfel spus, sesiunea 2 încearcă să transforme lock-ul U în lock X (request_status=CONVERT). Conform MSDN "A convert request status indicates that the requestor has already been granted a request for the resource and is currently waiting for an upgrade to the initial request to be granted."
- deoarece (a) sesiunea 1 are deja un lock S, (b) sesiunea 2 încearcă să obţină X (U -> X) iar (b) S şi X sunt incompatibile => sesiunea 2 este pusă în aşteptare fapt care este indicat prin statusul request_status=CONVERT.
2) Dacă la citirea înregistrărilor UPDATE ar utiliza lock-uri S(hared în locul de U) atunci când două sesiuni ar încerca să actualizeze aceeași înregistrare
Timp Sesiunea 1 Sesiunea 2
T1 lock S
T2 lock S (posibil deoarece lock-urile S sunt compatibile)
T3 încearcă
conversie
S -> X (imposibil deoarece sesiunea1.X şi sesiunea2.S sunt incompatibile => sesiunea 1 este pusă în aşteptare)
T4 încearcă
conversie
S -> X (imposibil deoarece sesiunea2.X şi sesiunea1.S sunt incompatibile => sesiunea 2 este pusă în aşteptare)
s-ar obţine un deadlock.
Deoarece SQL Server foloseşte la UPDATE un lock tip U (nu S) la citirea înregistrărilor (înainte de a decide dacă trebuiesc modificate) vom avea
Timp Sesiunea 1 Sesiunea 2
T1 lock U
T2 lock U (lock-urile U sunt incompatibile => sesiunea 2 trebuie să aştepte a prima sesiune să finalizeze tranzacţia)
iar deadlock-ul amintit mai sus este evitat.
3) În ceea ce priveşte durata pentru care sunt menţinute lock-urile U, în urma execuţiei următorului script folosind diverse nivele de izolare a tranzacţiilor READ COMMITTED, REPEATABLE READ şi SERIALIZABLE
--Sesiunea 4
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
UPDATE dbo.Test
SET Col2 = Col2 * 10
WHERE Col1 = 2;
SELECT l.resource_type, l.resource_description, l.request_mode, l.request_type, l.request_status, l.request_session_id
FROM sys.dm_tran_locks l
WHERE l.resource_type = 'KEY'
ORDER BY l.resource_description, l.request_session_id;
SELECT *, %%lockres%% AS resource_description
FROM dbo.Test WITH(NOLOCK)
ORDER BY %%lockres%%;
ROLLBACK;
se poate observa
--READ COMMITTED, REPEATABLE READ
resource_type resource_description request_mode request_type request_status request_session_id
------------- -------------------- ------------ ------------ -------------- ------------------
KEY (020068e8b274) X LOCK GRANT 58
KEY (03000d8f0ecc) X LOCK GRANT 58
KEY (0400b4b7d951) X LOCK GRANT 58
(3 row(s) affected)
sau
--SERIALIZABLE
(1 row(s) affected)
ID Col1 Col2 Col3 resource_description
----------- ----------- ----------- ----------- --------------------------------
1 1 11 111 (010086470766)
2 2 220 222 (020068e8b274)
3 2 330 333 (03000d8f0ecc)
4 2 440 444 (0400b4b7d951)
5 3 55 555 (0500d1d065e9)
(5 row(s) affected)
resource_type resource_description request_mode request_type request_status request_session_id
------------- --------------------------------- ------------ -------------- ------------------
KEY (010086470766) RangeS-U LOCK GRANT 58
KEY (020068e8b274) RangeX-X LOCK GRANT 58
KEY (03000d8f0ecc) RangeX-X LOCK GRANT 58
KEY (0400b4b7d951) RangeX-X LOCK GRANT 58
KEY (0500d1d065e9) RangeS-U LOCK GRANT 58
KEY (ffffffffffff) RangeS-U LOCK GRANT 58
(6 row(s) affected)
ID Col1 Col2 Col3 resource_description
----------- ----------- ----------- ----------- --------------------------------
1 1 11 111 (010086470766)
2 2 220 222 (020068e8b274)
3 2 330 333 (03000d8f0ecc)
4 2 440 444 (0400b4b7d951)
5 3 55 555 (0500d1d065e9)
(5 row(s) affected)
că după citirea unei înregistrări care nu tb. actualizată de către UPDATE (ID=1 sau ID=5; caz în care nu este necesară conversia U -> X) lock-ul iniţial este ridicat sau este menţinut în funcţie de nivelul de izolare astfel:
Nivele de izolare Se menţine lock-ul U pâna la sf. tranzacţiei ?
READ COMMITTED, REPEATABLE READ NU
SERIALIZABLE DA (se foloseşte RangeS-U în loc de U)
4) Calea de acces la date ([CLUSTERED] INDEX SCAN, TABLE SCAN, INDEX SEEK) şi lock-urile utilizate de către SQL Server sunt aspecte diferite.