ค้นหาและลบล็อคใน 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

คอลัมน์ที่ถูกบล็อกจะแสดง ID กระบวนการของกระบวนการที่บล็อกทรัพยากร นอกจากนี้ยังแสดงเวลารอสำหรับข้อความค้นหานี้ (เวลารอเป็นมิลลิวินาที) หากจำเป็น สามารถใช้เพื่อค้นหาการล็อกที่เก่าที่สุดหรือล่าสุดได้

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 ของแหล่งล็อค