חיפוש והסרה של מנעולים ב- Microsoft SQL Server

נעילות ב- MS SQL Server הן דרך אחת להבטיח את שלמות הנתונים כאשר שינויים מתבצעים על-ידי משתמשים מרובים בו-זמנית. MSSQL נועל אובייקטים בטבלה כאשר טרנזקציה מתחילה ומשחרר את המנעול כאשר העסקה מסתיימת. במאמר זה, נלמד כיצד למצוא מנעולים במסד הנתונים של MS SQL Server ולהסיר אותם.

ניתן לדמות מנעול על אחד השולחנות באמצעות עסקה לא גמורה (כזו שלא הושלמה על ידי חזרה או התחייבות). לדוגמה, השתמש בשאילתת SQL הבאה:

USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents

לפני ביצוע שינויים, SQL Server ינעל את הטבלה. כעת נסה לפתוח את SQL Server Management Studio ולבצע שאילתת בחירת SQL פשוטה:

SELECT * FROM tblStudents

השאילתה תקפא במצב "ביצוע שאילתת " עד לסיום הזמן הקצוב. הסיבה לכך היא שאילתת SELECT מנסה לגשת לנתונים בטבלה שננעלה על-ידי SQL Server.

כדי לקבל רשימה של כל השאילתות החסומות ב- MSSQL Server, הפעל את הפקודה

select cmd,* from sys.sysprocesses
where blocked > 0

SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID('testdb12') and blocked <> 0
order by blocked

העמודה חסום מציגה את מזהה התהליך של התהליך שחסם את המשאבים. הוא גם מציג את זמן ההמתנה עבור שאילתה זו (זמן המתנה באלפיות שניה). במידת הצורך, ניתן להשתמש בו כדי לחפש את המנעולים המוקדמים או העדכניים ביותר.

select * FROM
master.dbo.sysprocesses
where 1=1
--and blocked <> 0
and spid = 59

SPID התהליך מספק לך את קוד T-SQL של שאילתת SQL האחרונה שבוצעה על-ידי תהליך או טרנזקציה אלה:

DBCC INPUTBUFFER(59)

כדי להרוג בכוח את התהליך ולשחרר את המנעול, הפעל את הפקודה:

KILL number_of_session
GO

במקרה שלי, זה יהיה:

KILL 59

באפשרותך ליצור פרוצדורה מאוחסנת נפרדת אם מנעולים מתרחשים כל הזמן וברצונך לזהות את השאילתות הצורכות משאבים רבים ביותר:

CREATE PROCEDURE GetCurrentQueryCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

כעת, אם ברצונך לראות את שאילתת SQL שנעלה את הטבלה, כל שעליך לעשות הוא לציין את ה- SPID שלה:

Exec GetCurrentQueryCode 51

באפשרותך גם לקבל את קוד השאילתה sql_handle תהליך הנעילה. לדוגמה

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

באפשרותך להשתמש ב- Microsoft SQL Server Management Studio כדי לחפש מנעולים ב- MS SQL Server. באפשרותך להשתמש באחת מהשיטות הבאות:

• לחץ לחיצה ימנית על השרת, פתח את צג הפעילות והרחב את תהליכים. תראה רשימה של בקשות הממתינות לשחרור משאבים עם מצב מושעה.

  

• בחר מסד נתונים ונווט אל דוחות > כל העסקאות החסימות. הוא מציג גם רשימה של שאילתות נעולות ואת ה- SPID של מקור הנעילה.