március 2013 havi bejegyzések

Amikor a NOLOCK lockol, avagy hihetek e a szememnek?

Nem tudom, hogy ki látott már ilyet és ki nem, de én összefutottam a jelenséggel.
Sőt meg is fejtettem! 🙂
Tapaszalt szakembereknek gondolom nem új a dolog, a többieknek viszont jól jöhet az infó. Történt egyszer (?), hogy  borult az SQL szerver és vele együtt minden más is. Az SQL szerver általában 2 dologtól szokott megnyekkenni.

  1. Hardver erőforrás hiány (ide sorolom azt is, ha pl. meghal a diszk).
  2. Lock az adatbázisban

Az elsőt viszonylag könnyű felismerni, mert még monitorozás híján is a task manager-ből gyorsan ki lehet nyerni az információt. A második eset már bonyolultabb, mert hol keressem a lockok forrását? Hogy szüntessem meg? A forrás a katalógus nézetekből gyorsan elő ásható és néhány (nem több!) jól irányzott KILL 1-2 perc alatt rendet tesz és mehet tovább a biznic. De csak ezután jön az igazi munka! Elő kell keresni, hogy pontosan mi okozta a problémát, hol indult el a lavina. Első dolog, hogy elő vesszük a lock trace-t…

És itt akadtam bele, hogy a blokkolt processz egy írás volt (INSERT, UPDATE, DELETE), a blokkoló processz pedig egy SELECT nolock hinttel. Gyorsan meg is dörzsöltem a szemem, hátha csak a hajnali ébresztő miatt képzelgek, de nem. Úgyhogy ezt le fogjuk demózni 🙂

Előre bocsájtom a lock trace-t, aki szeretné nézegetni (persze csak a demó scriptét):


<blocked-process-report>
<blocked-process>
<process id="process5b6e988" taskpriority="0" logused="0" waitresource="KEY: 2:4179340514207072256 (8194443284a0)" waittime="8991" ownerId="177922454" transactionname="UPDATE" lasttranstarted="2011-03-08T07:47:00.630" XDES="0x13724f730" lockMode="X" schedulerid="4" kpid="2852" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-08T07:47:00.630" lastbatchcompleted="2011-03-08T07:46:51.350" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MSSQL" hostpid="2336" loginname="MSSQL\Administrator" isolationlevel="read committed (2)" xactid="177922454" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="1" stmtstart="38" sqlhandle="0x020000007fd22412e6578f7e0afa065f277538e223aa927a"/>
<frame line="1" sqlhandle="0x02000000846bda1e0e1e43f206630ce927805c592fe79d1f"/>
</executionStack>
<inputbuf>
update test_table set nr = 11 where id = 1   </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="93" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-03-08T07:46:43.480" lastbatchcompleted="2011-03-08T07:46:43.480" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MSSQL" hostpid="2336" loginname="MSSQL\Administrator" isolationlevel="read committed (2)" xactid="177919524" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack/>
<inputbuf>
select top 10 * from test_table (nolock) where id between 1 and 10   </inputbuf>
</process>
</blocking-process>
</blocked-process-report>

Aki még nem látott ilyen trace-t, annak mondom, hogy a felső query, ami blockolva van, az alsó, ami blockolja. És az alsóban bizony ott virít a nolock hint.

Nem húzom tovább az időt, adom a magyarázatot. Egy szimpla írást nem lockolhat a nolock és nem is lockol, hanem a lock trace működése hagy még kívánni valót maga után. A SELECT egy tranzakció része volt és a tranzakció egy előző batch utasítása lockolja a fenti UPDATE-et, viszont a lock trace az adott session utolsó batch-ét jeleníti meg, mint lockololó utasítás.

Így, hogy megvan a magyarázat már csak a demó hiányzik. Aki tud profiler-ezni, annak érdemes lehet végignyomkodni.

Futtassuk sorban a Session 1, Session 2, Session 3 (tehát 3 query ablak a management studioban) utasításait, a megadott instrukciók szerint.

Session 1: Készítsünk egy teszt táblát, némi adattal.


use tempdb
go

set nocount on;
go

if OBJECT_ID('test_table') is not null
drop table test_table
go

create table test_table (id int identity(1,1) primary key, nr int not null)
go

insert into test_table (nr)
select top 100
row_number() over(order by (select null))
from sys.columns

select * from test_table

Session 2: Egyesével futtassuk az első három sort, így külön batch-ekben küldjük be az sql szerver részére az utasításokat.


begin tran -- 1. jelöljük ki a sort és csak ezt futtassuk
update test_table set nr = 2 where id = 1 -- 2. jelöljük ki a sort és csak ezt futtassuk
select top 10 * from test_table (nolock) where id between 1 and 10 -- 3. jelöljük ki a sort és csak ezt futtassuk
-- rollback

Session 3: Futtassuk az alábbi utasítást.


update test_table set nr = 11 where id = 1

Pár másodperc múlva a profiler elkapja, hogy a nolock-os utasítás (Session 2) lockolja a update-t (Session 3-at). Remélem a demó nálatok is sikeres lett.

Ha a Session2-ben az utasításokat nem egyenként, hanem együtt futtatjuk, akkor a begin tran utasítástól fogjuk látni a lockoló folyamatot, mert az volt az utolsó batch, amit beküldtünk. Bemásolom annak is az eredményét:


<blocked-process-report>
<blocked-process>
<process id="process5b6e988" taskpriority="0" logused="0" waitresource="KEY: 2:4179340514207072256 (8194443284a0)" waittime="8936" ownerId="177927304" transactionname="UPDATE" lasttranstarted="2011-03-08T08:48:15.700" XDES="0x13724ee80" lockMode="X" schedulerid="4" kpid="2852" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-08T08:48:15.700" lastbatchcompleted="2011-03-08T07:47:59.183" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MSSQL" hostpid="2336" loginname="MSSQL\Administrator" isolationlevel="read committed (2)" xactid="177927304" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="1" stmtstart="38" sqlhandle="0x020000007fd22412e6578f7e0afa065f277538e223aa927a"/>
<frame line="1" sqlhandle="0x02000000846bda1e0e1e43f206630ce927805c592fe79d1f"/>
</executionStack>
<inputbuf>
update test_table set nr = 11 where id = 1   </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="93" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-03-08T08:48:08.163" lastbatchcompleted="2011-03-08T08:48:08.163" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MSSQL" hostpid="2336" loginname="MSSQL\Administrator" isolationlevel="read committed (2)" xactid="177926557" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack/>
<inputbuf>
begin tran
update test_table set nr = 2 where id = 1
select top 10 * from test_table (nolock) where id between 1 and 10
--rollback   </inputbuf>
</process>
</blocking-process>
</blocked-process-report>

Ami még fontos, hogy a nolock-os select helyett bármit írhatnánk. Ha pl. egy másik táblára lekérdezést, aminek abszolút semmit köze a másik táblához vagy egy SELECT 1-et, ezeket azt mutatná a trace, mint lockoló folyamat. Bug vagy nem bug, jó szívatós 🙂

Hogy NE védekezzünk a dupla sorok ellen

Mindenki igyekszik elkerülni, hogy bizonyos adatok duplán kerüljenek be az adatbázisába. Erre vannak közkedvelt sémák. Én egy elterjedt, de rossz sémát mutatok be, kétféle módozatban. Aki adatbázissal foglalkozik egyiket vagy másikat biztosan látta már.

Első séma:

begin tran
    if not exists (select * from table where column = value)
        insert into table (field1, field2, ...) values (value1, value2, ...)
commit

Elsőre jónak tűnik a dolog, mert csak akkor insert-álunk, ha még nem létezik az adat.
Nézzük ugyanezt egy kicsit más formában.

Második séma:

begin tran
    insert into table (field1, field2, ...)
        select value1, value2, ...
        where not exists (select * from table where column = value)
commit

Ez már trükkösebb, mint az előző, mert 1 utasításba tettük bele az egészet és azt egy tranzakcióba.

Többeknek a véleménye, hogy a fenti műveletekkel csak akkor lenne gond, hogy egy időben többen küldenék be ugyanazt a parancsot, de mivel tranzakcióban van, így az megvéd a dupla soroktól. És egyébként is, ha a fenti állítás igaz, akkor ez csak olyan helyen lehet probléma, ahol sokan használják a rendszert.

Ezek után gondolja e valaki azt, hogy pl. az utóbbi utasítás tranzakció nélkül csak akkor eredményezhet dupla insert-et, ha ketten vagy többen, ugyanabban a pillanatban küldik be ugyanazt az utasítást? Ha eredményezheti, akkor megvéd e a tranzakció a dupla insert-től?

Nos a helyzet az, hogy nem véd meg. De tovább megyek, nem csak akkor keletkezhetnek dupla sorok, ha egyszerre érkezik be az utasítás több helyről, hanem akkor is, ha az utasítások között több másodperces, perces, órás, stb. különbség van. És ez igaz mindkét esetre. (Ezek az un. fantom rekordok lesznek read committed szinten).

Magyarázat:

Az adatbázisok világában a futási időt nagyban befolyásolják a lock-ok. Tegyük fel, hogy a táblán amiben insert-álni akarok lock van. Futtatom insert-et, de mivel lock van rajta várakozok, hogy hozzáférjek a táblához. A várakozásom bizonytalan ideig tart, lehet több perc, óra is, addig amíg a lock fel nem szabadul. Amíg várakozok egy másik felhasználó is beküldi ugyanazt az utasítást. Már ketten várakozunk. Amikor felszabadul a lock a tábláról, az SQL szerver egyszerre kezdi el feldolgozni a parancsokat, mindegy, hogy ki küldte be előbb az utasítást. Ha unique kulcsunk lenne a mezőn, akkor az megvédene minket a dupla insert-től, de most nincs. (Egyébként vannak esetek, amikor van létjogosultsága, hogy ne legyen).

És akkor jöjjenek a demók

1.) Készítsük el a teszt táblákat:


use tempdb
go

if object_id('tbl_double_insert') is not null
    drop table tbl_double_insert

if object_id('tbl_wait_time') is not null
    drop table tbl_wait_time
go

create table tbl_double_insert (
    id int not null identity (1,1) primary key,
    name nvarchar(255) not null,
    created_session nvarchar(255) not null,
    test_id int not null,
    created_date datetime not null default getdate(),
    command_date datetime not null
)

-- Ide fogjuk a következő futás idejét beletenni
create table tbl_wait_time (
    to_time datetime not null,
    test_data uniqueidentifier not null default newid(),
    test_id int not null default 1
)

go

create index IDX_name on tbl_double_insert (name)
create index IDX_test_id on tbl_double_insert (test_id)

insert into tbl_wait_time (to_time) values (getdate())

Demó 1:

A demót a következőképpen kell futtatni. Indítsuk el a Session 1-et, majd mihamarabb (3 mp-en belül) a Session 2 és Session 3-at is. A Session 3 a végén vissza fogja adni a tábla tartalmát. Remélem nálatok is ott lesznek a dupla sorok 🙂
A következőképpen futtassuk: Nyissunk 3 ablakot a management studióba és másoljuk sorban be az alábbi query-ket.

Session 1: Ezzel a session-al fogunk lockot generálni.


-- Session
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @to_time nvarchar(12), @name uniqueidentifier, @test_id int, @count int = 0

while @count < 10
begin
    update tbl_wait_time
    set to_time = dateadd(second, 3, getdate()),
        test_data = newid(),
        test_id = abs(checksum(newid()))

    select
        @to_time = cast(cast(to_time as time)as nvarchar(12)),
        @name = test_data,
        @test_id = test_id
    from tbl_wait_time

    select @name = test_data from tbl_wait_time

    begin tran
        update tbl_double_insert with (rowlock, serializable)
        set created_date = getdate()
        where name = @name

        waitfor time @to_time
    rollback

    set @count += 1
end

Session 2:


-- Session 1
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @name uniqueidentifier, @test_id int = 0, @old_test_id int = -1
while @old_test_id <> @test_id
begin
    set @old_test_id = @test_id
    select @name = test_data, @test_id = test_id from tbl_wait_time

    begin tran
        if not exists (select * from tbl_double_insert where test_id = @test_id)
            insert into tbl_double_insert (name, created_session, test_id, command_date)
            values (@name, 'Session 1', @test_id, getdate())
    commit

    waitfor delay '00:00:01.000'
end

Session 3:


-- Session 2
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @name uniqueidentifier, @test_id int = 0, @old_test_id int = -1
while @old_test_id <> @test_id
begin
    set @old_test_id = @test_id
    select @name = test_data, @test_id = test_id from tbl_wait_time

    begin tran
        if not exists (select * from tbl_double_insert where test_id = @test_id)
            insert into tbl_double_insert (name, created_session, test_id, command_date)
            values (@name, 'Session 2', @test_id, getdate())
    commit

    waitfor delay '00:00:02.100'
end

select * from tbl_double_insert order by id

Demo 2

Ezt ugyanúgy kell csinálni és futtatni a mint a Demó 1-et, és ugyanúgy meg kell kapjuk a dupla sorokat is. Figyeljük majd meg, hogy a created_date és a command_date mezők nem egyeznek meg! Egyik mezőbe az az értékfog kerülni, amikor, a select fut a másikba, amikor a sort lerakjuk a táblába. Ez kicsit többet árul így el 🙂

Session 1: Ezzel  session-al fogunk lock-ot generálni.


-- Session
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @to_time nvarchar(12), @name uniqueidentifier, @test_id int, @count int = 0

while @count < 10
begin
    update tbl_wait_time
    set to_time = dateadd(second, 3, getdate()),
        test_data = newid(),
        test_id = abs(checksum(newid()))

    select
        @to_time = cast(cast(to_time as time)as nvarchar(12)),
        @name = test_data,
        @test_id = test_id
    from tbl_wait_time

    select @name = test_data from tbl_wait_time

    begin tran
        update tbl_double_insert with (rowlock, serializable)
        set created_date = getdate()
        where name = @name

        waitfor time @to_time
    rollback

    set @count += 1
end

Session 2


-- Session 1
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @name uniqueidentifier, @test_id int = 0, @old_test_id int = -1
while @old_test_id <> @test_id
begin
    set @old_test_id = @test_id
    select @name = test_data, @test_id = test_id from tbl_wait_time

    begin tran
        insert into tbl_double_insert (name, created_session, test_id, command_date)
        select @name, 'Session 1', @test_id, getdate()
        where not exists (select * from tbl_double_insert where test_id = @test_id)
    commit

    waitfor delay '00:00:01.000'
end

Session 3


-- Session 2
use tempdb
go
set nocount on;
set transaction isolation level read committed;
go

declare @name uniqueidentifier, @test_id int = 0, @old_test_id int = -1
while @old_test_id <> @test_id
begin
    set @old_test_id = @test_id
    select @name = test_data, @test_id = test_id from tbl_wait_time

    begin tran
        insert into tbl_double_insert (name, created_session, test_id, command_date)
        select @name, 'Session 2', @test_id, getdate()
        where not exists (select * from tbl_double_insert where test_id = @test_id)
    commit

    waitfor delay '00:00:02.100'
end

select * from tbl_double_insert order by id

Ez egy nagyon egyszerű demozása a sorok duplikálódásának. A valóságban ennél több utasítás szokott lenni egy tranzakciókban. Ha duplikálódnak a soraink és a fenti utasításokkal insert-álunk, akkor szinte biztos, hogy ilyen problémába ütköztünk.