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

view raw sql-monitor.sql hosted with ❤ by GitHub

    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

view raw sql-lock-kill.sql hosted with ❤ by GitHub

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

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

view raw sql-lock-status.ts hosted with ❤ by GitHub

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