T-SQL

Re: locking on update

B_gd_n[ ]Sahlean — Fri, 17 May 2013 13:51:27 GMT

Salut.

Planul de execuție real/efectiv de la UPDATE dbo.test (include planul pentru trigger > UDPATE dbo.test_new) este următorul:
http://s10.postimg.org/8v598lf21/Capture046.png

Planul demonstrează faptul că server-ul parcuge următoarele "etape":
1) Filtrează înregistrările după condiția col1 = 6 separat pentru fiecare dintre cele două tabele (dbo.test_new şi dbo.test_old) şi face un merge (union all) cu cele două seturi de înregistrări.
2) După activarea trigger-ului INSTEAD OF , UPDATE-ul real se execută pe dbo.test_new.

În legătură cu rezultatele obținute de către dvs.:
[ i ] Sesiunea 2 (SPID-ul 74 în cazul dvs.) execută cu succes etapa 1) și etapa 2). La etapa 2) obține și menține un loc eXclusiv pe înregistrarea care are Col1 = 6 (interogare auxiliară SELECT *, %%lockres%% FROM dbo.Test_New WITH(NOLOCK)). Tranzacţia rămâne deschisă şi lock-ul X este activ.
[ ii ] Sesiunea 3 (SPID-ul 75 în cazul dvs.) încearcă să execute etapa 1): Clustered Index Scan pe dbo.test_new şi dbo.test_old (vezi planul de execuție). Asta presupune ca operatorul Clustered Index Scan pe dbo.test_new să - virgulă - solicite un lock Shared și pe înregistrarea Col1 = 6.
[i ii ] Deoarece lock-urile X (SPID 74) și S (SPID 75) sunt incompatibile apare un blocaj, SPID-ul 75 fiind pus în stare de ”așteptare”. Acesta este și motivul pentru care reguest_status - ul pentru S este WAIT, spre deosebire de starea lock-ului X care este GRANT.

Ceea ce ar fi cu adevărat interesat la testul de mai sus (SQL 2008 R2) ar fi dacă
[ i ] sesiunea 2 ar executa UPDATE-ul pe dbo.test_new (tranzacția rămâne deschisă !!!) şi
[ ii ] sesiunea 3 ar executa un select fără NOLOCK :-)
adică
SELECT * FROM dbo.test_new;
kw: craigfr
Un articol interesant care are legătură cu UPDATE-urile folosite mai sus (SET Coloana = Coloana) a fost scris de către Aaron Bertrand aici:
http://www.sqlperformance.com/2012/10/t-sql-queries/conditional-updates

Toate acestea demonstrează ceea ce a spus actual președinte în perioada lui perioada de ministeriat.

Re: locking on update

rsocol — Thu, 16 May 2013 10:45:06 GMT

Problema este JOIN-ul pe col1, pe care nu exista niciun index. Eu as modifica trigger-ul, astfel:

ALTER TRIGGER [dbo].[trg_Test_Update]
ON [dbo].Test
INSTEAD OF UPDATE
AS
UPDATE dbo.Test_New SET
Col1 = inserted.Col1,
Col2 = inserted.Col2,
Col3 = inserted.Col3
FROM
inserted
WHERE
dbo.Test_New.ID = inserted.ID

In acest caz, vom vedea lock de tip U, in loc de S.

In plus, ar trebui sa ne asiguram ca nu sunt duplicate intre ID-urile dintre cele doua tabele, iar pentru asta ar trebui sa facem o verificare pe trigger-e (sau sa folosesc ID-uri din game separate, verificate cu CK-uri).

Razvan

Re: locking on update

tebbaerty — Thu, 16 May 2013 08:12:06 GMT

Salut,

Toata povestea de mai sus pare ca difera pentru view-uri.
Am facut urmatorul test :

--Sesiunea 1

--DROP TABLE Test_Old
--DROP TABLE Test_New
--DROP VIEW Test

CREATE TABLE dbo.Test_Old
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
);

CREATE TABLE dbo.Test_New
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL
);

GO

create view Test as
select * from Test_Old
union all
select * from Test_New

GO

CREATE TRIGGER [dbo].[trg_Test_Update]
ON [dbo].Test
INSTEAD OF UPDATE
AS
UPDATE dbo.Test_New SET
Col1 = inserted.Col1,
Col2 = inserted.Col2,
Col3 = inserted.Col3
FROM
inserted
WHERE
dbo.Test_New.Col1 = inserted.Col1

GO

INSERT dbo.Test_Old(Col1, Col2, Col3)
VALUES (1,11,111), (2,22,222), (2,33,333), (2,44,444), (3,55,555);

INSERT dbo.Test_New(Col1, Col2, Col3)
VALUES (6,66,666), (7,77,777), (8,88,888), (9,99,999);
GO

--Sesiunea 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

UPDATE dbo.test SET
col1 = col1,
col2 = col2,
col3 = col3
where
col1 = 6

--rollback

--Sesiunea 3
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

UPDATE dbo.test SET
col1 = col1,
col2 = col2,
col3 = col3
where
col1 = 6

--rollback

--Sesiunea 4
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;

Si ce am obtinut (in sesiunea 4) este

resource_type resource_description request_mode request_type request_status request_session_id
KEY (010086470766) X LOCK GRANT 74
KEY (010086470766) S LOCK WAIT 75

Acum, de unde apare acel S ? Daca peste tot se face update.

Mersi,

Re: locking on update

crestinul — Tue, 24 Apr 2012 13:11:33 GMT

super tare! Multumesc

Re: locking on update

B_gd_n[ ]Sahlean — Wed, 18 Apr 2012 13:22:21 GMT

0) Ai schimbat textul iniţial al răspunsului tău? Era interesant.

1) În mod implicit, o restricţie PRIMARY KEY (ID INT IDENTITY PRIMARY KEY) pp. crearea unui index unic clustered.
Toate interogarile de mai sus folosesc (Clustered Index Scan) un index:
SELECT    i.name, i.index_id, i.type, i.type_desc
FROM    sys.indexes i
WHERE    i.object_id = OBJECT_ID('dbo.Test');
Rezultate:
name                       index_id type type_desc
-------------------------- -------- ---- ------------
PK__Test__3214EC275CC5B545 1        1    CLUSTERED

Să pp. că prin folosirea unui index înţelegem Seek.

2) Conform (http://msdn.microsoft.com/en-us/library/ms189849.aspx), SQL Server nu menţine lock-uri la nivel de coloană, nivel maxim de detaliere a lock-urilor fiind cel de înregistrare: RID lock (tabele heap) sau KEY lock (indecşi).

3) Dacă foloseşti nivelul implicit de izolare a tranzacţiilor (READ COMMITTED) lock-urile U vor fi ridicate imediat după ce SQL Server decide că înregistrarea citită nu tb. modificată. Cu alte cuvinte, în acest caz, lock-urile U (care nu sunt convertite in lock-uri X) nu sunt menţinute pâna la finalul tranzacţiei lucru care ar putea bloca alte tranzacţii care doresc lock-uri X, doar le-ar "încetini".

4) Chiar şi în situaţia sugerată (anterior) de către tine apar lock-uri U, chiar mai multe lock-uri de tip U deoarece sunt implicaţi doi indecşi (unul clustered şi altul nonclustered folosit pt. seek Col1 = 2):

SET NOCOUNT ON;

CREATE DATABASE CocoJambo;
GO

USE CocoJambo;
GO

CREATE TABLE dbo.Test
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL,
Col4 VARCHAR(5000) DEFAULT 'A' 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

PRINT 'CREATE INDEX IN_Test_Col1 ...';
CREATE INDEX IN_Test_Col1
ON dbo.Test(Col1);
GO

SET SHOWPLAN_TEXT ON; --Afiseaza planul de executie; Datele nu sunt modificate.
GO
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 1;
GO
SET SHOWPLAN_TEXT OFF;
GO

PRINT 'DROP INDEX dbo.Test.IN_Test_Col1'
DROP INDEX dbo.Test.IN_Test_Col1;
GO

PRINT 'CREATE INDEX IN_Test_Col1_#_Col2 ...'
CREATE INDEX IN_Test_Col1_#_Col2
ON dbo.Test(Col1)
INCLUDE(Col2);
GO

SET SHOWPLAN_TEXT ON;
GO
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 2;
GO
SET SHOWPLAN_TEXT OFF;
GO

--Test (Doar pe un server de test)

PRINT 'Index clustered -> [locked resourse hash]'
SELECT *, %%lockres%% [locked resourse hash] FROM Test WITH(INDEX=0);
PRINT 'IN_Test_Col1_#_Col2 -> [locked resourse hash]'
SELECT *, %%lockres%% [locked resourse hash] FROM Test WITH(INDEX=IN_Test_Col1_#_Col2);
SELECT i.index_id, i.name, i.type, i.type_desc, i.is_primary_key FROM sys.indexes i WHERE i.object_id = OBJECT_ID('dbo.Test');
DBCC IND('CocoJambo', 'dbo.Test', -1);

DBCC TRACEON(-1, 3604); --Redirectioneaza output-ul de la comenzile DBCC catre client
DBCC TRACEON(-1, 1200); --Afiseaza lock-urile
GO

BEGIN TRANSACTION;
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 2;
ROLLBACK

DBCC TRACEOFF(-1, 3604);
DBCC TRACEOFF(-1, 1200);

Rezultate:
CREATE INDEX IN_Test_Col1 ... (acest index este parţial şi "nu va putea fi utilizat"(există şi excepţii); PK___Test__... este indexul clustered)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Update(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), SET:([CocoJambo].[dbo].[Test].[Col2] = RaiseIfNullUpdate([Expr1003])))
       |--Compute Scalar(DEFINE:([Expr1003]=[CocoJambo].[dbo].[Test].[Col2]*(10)))
            |--Top(ROWCOUNT est 0)
                 |--Clustered Index Scan(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), WHERE:([CocoJambo].[dbo].[Test].[Col1]=(1)) ORDERED)

DROP INDEX dbo.Test.IN_Test_Col1
CREATE INDEX IN_Test_Col1_#_Col2 ... (acest index include va putea fi utilizat -seek- de către UPDATE)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Update(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), OBJECT:([CocoJambo].[dbo].[Test].[IN_Test_Col1_#_Col2]), SET:([CocoJambo].[dbo].[Test].[Col2] = RaiseIfNullUpdate([Expr1003])))
       |--Compute Scalar(DEFINE:([Expr1011]=[Expr1011]))
            |--Compute Scalar(DEFINE:([Expr1003]=[CocoJambo].[dbo].[Test].[Col2]*[@1], [Expr1011]=CASE WHEN CASE WHEN [CocoJambo].[dbo].[Test].[Col2] = [CocoJambo].[dbo].[Test].[Col2]*[@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
                 |--Top(ROWCOUNT est 0)
                      |--Index Seek(OBJECT:([CocoJambo].[dbo].[Test].[IN_Test_Col1_#_Col2]), SEEK:([CocoJambo].[dbo].[Test].[Col1]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

Index clustered -> [locked resourse hash]
ID          Col1        Col2        Col3        Col4 locked resourse hash
----------- ----------- ----------- ----------- ---- --------------------
1           1           11          111         A    (010086470766)
2           2           22          222         A    (020068e8b274)     <- WHERE Col1 = 2
3           2           33          333         A    (03000d8f0ecc)        WHERE Col1 = 2
4           2           44          444         A    (0400b4b7d951)        WHERE Col1 = 2
5           3           55          555         A    (0500d1d065e9)

IN_Test_Col1_#_Col2 -> [locked resourse hash]
ID          Col1        Col2        Col3        Col4 locked resourse hash
----------- ----------- ----------- ----------- ---- --------------------
1           1           11          111         A    (02006d47cbee)
2           2           22          222         A    (040060eff172)     <- WHERE Col1 = 2
3           2           33          333         A    (050005884dca)        WHERE Col1 = 2
4           2           44          444         A    (0600bcb09a57)        WHERE Col1 = 2
5           3           55          555         A    (080047d78c23)

index_id    name                        type_desc    is_primary_key
----------- -------------------------- ------------ --------------
1           PK__Test__3214EC277F60ED59 CLUSTERED    1
2           IN_Test_Col1_#_Col2         NONCLUSTERED 0

Lista cu paginile folosite de catre indecşii din tabela CocoJambo.dbo.Test ...
PageFID PagePID     IndexID     PageType IndexLevel
------- ----------- ----------- -------- ----------
1       79          1           10       NULL       <- PK__Test__3214EC277F60ED59 CLUSTERED
1       78          1           1        0          <- PK__Test__3214EC277F60ED59 CLUSTERED
1       90          2           10       NULL       <- IN_Test_Col1_#_Col2         NONCLUSTERED
1       89          2           2        0          <- IN_Test_Col1_#_Col2         NONCLUSTERED

Process 55 acquiring IX lock on OBJECT: 10:2105058535:0 (class bit2000000 ref1) result: OK

{Lock IU @ pagina 1:89 din indexul NonClst (vezi lista DBCC IND)}
Process 55 acquiring IU lock on PAGE: 10:1:89 (class bit0 ref1) result: OK

{Lock U @ cheia (040060eff172) din indexul NonClst (vezi IN_Test_Col1_#_Col2 -> [locked resourse hash])}
Process 55 acquiring U lock on KEY: 10:72057594038910976 (040060eff172) (class bit0 ref1) result: OK

{Lock IU @ pagina 1:78 din indexul Clst}
Process 55 acquiring IU lock on PAGE: 10:1:78 (class bit0 ref1) result: OK

{Lock U @ cheia (020068e8b274) din indexul Clst (vezi Index clustered -> [locked resourse hash]])}
Process 55 acquiring U lock on KEY: 10:72057594038779904 (020068e8b274) (class bit0 ref1) result: OK

{Conversie IU -> IX @ pagina 1:78 din indexul Clst}
Process 55 acquiring IX lock on PAGE: 10:1:78 (class bit2000000 ref0) result: OK

{Conversie U -> X @ cheia (020068e8b274) din indexul Clst}
Process 55 acquiring X lock on KEY: 10:72057594038779904 (020068e8b274) (class bit2000000 ref0) result: OK

{Conversie IU -> IX @ pagina 1:89 din indexul NonClst}
Process 55 acquiring IX lock on PAGE: 10:1:89 (class bit2000000 ref1) result: OK

{Conversie U -> X @ cheia (040060eff172) din indexul NonClst}
Process 55 acquiring X lock on KEY: 10:72057594038910976 (040060eff172) (class bit2000000 ref1) result: OK
Process 55 releasing lock reference on KEY: 10:72057594038910976 (040060eff172)
Process 55 releasing lock reference on PAGE: 10:1:89
Process 55 releasing lock reference on KEY: 10:72057594038779904 (020068e8b274)
Process 55 releasing lock reference on PAGE: 10:1:78
Process 55 releasing lock reference on KEY: 10:72057594038910976 (040060eff172)

Re: locking on update

crestinul — Fri, 06 Apr 2012 09:01:48 GMT

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

Re: locking on update

tebbaerty — Wed, 04 Apr 2012 13:51:19 GMT

wow :D

Ultimele 2 posturi sunt geniale.
Am vazut si video-ul din link si e foarte bun

Mersi B_gd_n[ ]Sahlean pentru timpul alocat pentru a scrie postul :D

Re: locking on update

Diana — Wed, 04 Apr 2012 10:38:36 GMT

Urmareste si
http://technet.microsoft.com/en-us/sqlserver/gg313762.aspx

Re: locking on update

B_gd_n[ ]Sahlean — Tue, 03 Apr 2012 10:48:48 GMT

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.

Re: locking on update

tebbaerty — Mon, 02 Apr 2012 07:17:57 GMT

M-a lamurit propozia asta :D :

"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). "

Care cred ca se aplica si pentru al 2-lea caz (index seek cu predicate).

Inteleg ca de fapt inca din momentul obtinerii resultsetului se pun exclusive locks, ci nu in momentul in care se face update-ul.
Astfel asta inseamna ca de fapt seriliazarea se va face inca din faza de obtinere a result seturilor.

Offtopic : Pentru cei care vor citi acest thread sa fie atenti si la lock escalation (daca este activat se va pune un lock exclusiv direct pe tabela daca numarul de randuri afectate vor depasii ~5000. Daca nu se reuseste escaladarea din prima se va incerca la fiecare ~1000 randuri. Lockul exclusiv pe tabela blocheaza toate selecturile care nu sunt cu (nolock) sau isolation level read uncommitted). Pe mine nu m-a interesat acest aspect :)

Offtopic : Pentru usurinta se poate folosi si sp_lock.

Mersi,

Re: locking on update

ovc — Sun, 01 Apr 2012 16:47:00 GMT

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 :)

locking on update

tebbaerty — Sat, 31 Mar 2012 08:42:31 GMT

Salut,

Incerc sa inteleg cum functioneaza lock impreuna cu update. Nu am reusit sa gasesc nimic care sa imi confirme teoria.

presupunem ca am urmatorul update:

update table set col3 = 1 where col1 = 2 and col2 = 3 and col3 = 1

Ce se intampla under the hood atat in varianta index seek cu/fara predicate cat si in varianta index scan ?

I. Daca se face index seek fara predicate inseamna ca se gaseste automat randul/randurile care ne intereseaza. Pentru a face seeking se pune shared lock pe randuri si dupa ce s-a obtinut result setul se trece la updlock si se face update-ul ?
Sau se face seeking cu updlock in mod automat.

II. Daca se face index seek cu predicate inseamna ca se gaseste un result set pe care ulterior mai aplica o filtrare pentru a obtine ce ne intereseaza. Pentru a face seeking se pune shared lock pe randuri dupa care se aplica filtrarea (tot cu shared lock) si dupa ce s-a obtinut result setul necesar se trece la updlock si se face update-ul ?
Sau se face seeking cu shared lock si filtrarea cu updlock. Sau altfel ?

III Dar la index scan care presupune parcurgerea tuturor inregistrarilor ? Ma indoiesc ca aici s-ar pune direct updlock.

Intreb pentru ca intr-un mediu multithreading este posibil cumva ca threaduri complet separate sa faca acelasi update de 2 ori ? Adica dupa ce 2 sau multe threaduri fac cautarea in acelasi timp (si daca cautarea se face cu shared lock este permis acelasi lucru) obtin result setul necesar dupa care trec la update unde o sa avem un updlock si se serializeaza accesul. Oricum dat fiind ca toate thredurile au obtinut resultsetul nu mai conteaza ca updateul se face serializat... tot se face de mai multe ori.

Ce parere aveti ?
Mersi,