B_gd_n[ ]Sahlean:

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.