Welcome to Sign in | Join | Help

Re: locking on update

  •  04-06-2012, 12:01 PM

    Re: locking on update

    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.


    daca folosesc un Index nu o sa mai am lockuri U pe coloanele pe care nu le actualizez pentru ca database engine o sa-mi citeasca indexul nu recordurile din tabela , o sa am lockuri de Update doar pe recordul 2
    Secolul XXI ori va fi religios ori nu va fi deloc
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems