Güncel
Loading...

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.

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_who2
SELECT      *
FROM        #sp_who2



DECLARE @v_spid INT
DECLARE 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



Share on Google Plus

About Sema KUDU

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

2 yorum :

  1. Bu yorum yazar tarafından silindi.

    YanıtlaSil
  2. merhabalar sizinle baya bir bilgi alış verişi yapıcaz galiba :)

    YanıtlaSil

Türkiye yenilmez,Millet Eğilmez!