Salut,

presupunand ca pe tabela avem un index care include col3, col2 si col1 (poate contine si doar una dintre coloane daca selectivitatea este mare) atunci se va face un index seek. La un Index Seek se vor pun exclusive lock-uri doar pe randurile respective care sunt gasite in urma index seek-ului din planul de executie.

In cazul in care nu avem indexi sau criteriul de filtrare nu este foarte selectiv, atunci se va prefera un index scan. Dezavantajul este ca la citirea fiecarui rand se vor pune lockuri pentru a avea consistenta datelor (lockurile sunt puse pe fiecare rand doar in timpul citirii acelui rand). Acestea ar trebui sa fie shared locks (datele pot fi citite dar nu pot fi sterse/modificate in timpul acesta). Pe randurile care indeplinesc criteriul/criterile de filtrare, se vor pune exclusive locks (datele nu pot fi citite/sterse/modificate pe toata perioada tranzactiei).

In varianta cu scan, pe langa faptul ca trebuie citite toate inregistrarile din tabela, mai poate aparea un delay din cauza lock-urilor. De exemplu daca de la o tranzactie avem un shared lock pe o inregistrare, si vine o alta tranzactie care necesita un exclusive lock (update/delete), atunci a doua tranzactie trebuie sa astepte pana prima tranzactie elibereaza resursa.


Ca si test poti sa creezi urmatoarea tabela:
CREATE TABLE [dbo].[table1](
    [col1] [int] NOT NULL,
    [col2] [int] NULL,
    [col3] [int] NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
    [col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

apoi ruleaza intrun query window:
begin tran
update table1
set col2=2
where col3=3

Acest query va face un scan pe toata tabela.
Daca rulezi:
SELECTresource_type, request_mode, request_type,request_owner_type  
FROM sys.dm_tran_locks
WHERE request_session_id=60
   AND resource_database_id=14

resource_type                                                request_mode                                                 request_type    request_owner_type
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
DATABASE                                                     S                                                            LOCK                SHARED_TRANSACTION_WORKSPACE
PAGE                                                         IX                                                           LOCK                        TRANSACTION
KEY                                                          X                                                            LOCK                         TRANSACTION
OBJECT                                                       IX                                                           LOCK                      TRANSACTION

(4 row(s) affected)



S- shared lock
X - exclusive lock
IX - intent exclusive
Mai multe explicatii despre diferitele lock-uri aici: http://msdn.microsoft.com/en-us/library/ms175519.aspx

In timp ce update-ul este inca activ, toate operatile vor merge, care nu fac au nevoie sa citeasca sau sa scrie ceva pe randul/randurile afectate de update.


Sper ca asta iti raspunde la intrebare :)