SQL Server Lock ve DeadLock Nedir ? Lock Analizi ve Çözümü

Merhaba Sevgili Dostlar,
Bu yazımızda SQL 'de lock ve deadlock oluşturan durumları nasıl bulabileceğimizi,bize yardımcı olabilecek bazı script'leri sizler ile paylaşacağım.

Okurken ne dinlemeli ?



SQL Lock bir sql process'in kaynağa erişmek isterken başka bir process tarafından bloklanması, kaynağa ulaşmak için beklemesidir. Blok eden process'in işi bittiğinde blok ortadan kalkar. Fakat bu bloklanma genelde uzun sürer istenmeyen bir durumdur. Aşagıdaki görsel SQL Lock 'ı anlatmaktadır.

SQL Deadlock ise yine benzer bir durumda bloklanan process'in de  kendisini bloklayan process'i bloklamasıdır. Biraz karışık bir durum fakat aşağıdaki görsel daha iyi durumu anlatacaktır.

SQL Deadlock'ları SQL Server kendi algılayıp process'leri otomoatik kill ettiği için bizim bu durumu çözmemiz için yapmamız gereken bir şey yoktur. Lock durumlarını aşağıdaki anlatımdaki gibi algılayabiliriz. Fakat temelinde yanlış sql cümlecikleri yatar. Dolayısı ile analiz edilip birbirini bloklayan SP 'ler projeye özgü çözümlenmelidir.

Gelelim Lock durumlarının algılanıp çözülmesine. Eğer uygulamamız kullandığı SP'ler ile DB'de bloklama yapıyorsa bu hoş bir durum değildir. Bloklama yaptığını aşağıda sp_who2 komutunu dayanarak yazılmış bir script ile görebiliriz.Sistem tablolarını  da kullanarak oluşturulmuş oldukça zengin bir sorgudur. SQL Server Activity Monitor'ün sağladığı bir çok avantajı sağlayıp bize bilgi sunar.

DECLARE @loginame nvarchar(60)

    DECLARE @whotbl TABLE    (      SPID        INT    NULL     ,Status    VARCHAR(50)    NULL     ,Login        SYSNAME    NULL     ,HostName    SYSNAME    NULL     ,BlkBy        VARCHAR(5)    NULL     ,DBName    SYSNAME    NULL     ,Command    VARCHAR(1000)    NULL     ,CPUTime    INT    NULL     ,DiskIO    INT    NULL     ,LastBatch VARCHAR(50)    NULL     ,ProgramName VARCHAR(200)    NULL     ,SPID2        INT    NULL     ,RequestID INT    NULL     )

     INSERT INTO @whotbl
     EXEC sp_who2  @loginame = @loginame

    SELECT W.*
          ,CommandText = sql.text          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid

Lock kendiliğinden oluşuyorsa gözlemleyebiliriz. BlkBy kolonu bu sorguyu çalıştırıdığımızda - şekilnde boş değil ise yerinde örneğin 56 yazıyor ise sizin işleminizi 56 ID'li SQL Process blokluyor demektir. SPID=56 olan işlem incelenmelidir. Blok hali hazırda yok ise blok oluşturabileceğini düşündüğümüz SP 'de kullanılan tablodan nolock olmadan sorgu çekerek lock oluşturmaya çalışabiliriz.

Bu script ile sql process'in harcadığı CPU zamanını , kullandığı Disk I/O 'yu görebiliriz. Ayrıca Show XML plan kolonuna tıkladığımızda SP'nin nerede ne kadar kaynak tükettiğini bulabiliriz.

Process Statülerine değinecek olursak :

Running: Geçerli işleyen işlem
Background : İşlem arka planda çalışıyor.
Rollback: İşlem geri alınıyor.
Suspended: İşlem başka bir işlemi bekliyor .(Lock durumu)
Runnable: İşlem running kuyruğuna atılmış geçerli işleyen işlem olmayı bekliyor.


Eğer bloklama yapan işlemleri kill etmek istiyor isek aşağıdaki sqripti kullanabiliriz.Yalnız process'lei kill ettiğimizde datanın bozulabileceğini unutmayalım.

       CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),      Login  VARCHAR(255),HostName  VARCHAR(255),      BlkBy  VARCHAR(255),DBName  VARCHAR(255),      Command VARCHAR(255),CPUTime INT,      DiskIO INT,LastBatch VARCHAR(255),      ProgramName VARCHAR(255),SPID2 INT,      REQUESTID INT)INSERT INTO #sp_who2 EXEC sp_who2SELECT      *FROM        #sp_who2


DECLARE @v_spid INTDECLARE c_Suspends CURSOR   FAST_FORWARD FOR   SELECT SPID FROM #sp_who2 sw_ WHERE sw_.Status='SUSPENDED'

OPEN c_Suspends
FETCH NEXT FROM c_Suspends INTO @v_spid
WHILE (@@FETCH_STATUS=0)BEGIN  PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'  EXEC('KILL '+@v_spid)  FETCH NEXT FROM c_Suspends INTO @v_spid
END
CLOSE c_Suspends
DEALLOCATE c_Suspends

DECLARE @v_blid VARCHAR(255)DECLARE c_Blockeds CURSOR   FAST_FORWARD FOR   SELECT BlkBy FROM #sp_who2 sw_ WHERE sw_.BlkBy!='  .'
OPEN c_Blockeds
FETCH NEXT FROM c_Blockeds INTO @v_blid
WHILE (@@FETCH_STATUS=0)BEGIN  PRINT 'KILLing '+@v_blid +'...'  EXEC('KILL '+@v_blid)  FETCH NEXT FROM c_Blockeds INTO @v_blid
END
CLOSE c_Blockeds
DEALLOCATE c_Blockeds

DROP TABLE #sp_who2

-----------------------------------------

SELECT  * from #sp_who2 sw_ WHERE sw_.BlkBy!='  .' OR sw_.Status='SUSPENDED' 

Sağlıkla kalın , hoşça kalın.

 Kaynaklar:

https://blog.pythian.com/locks-blocks-deadlocks/

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql