[MSSQL] ตรวจสอบ Table ว่าถูก Lock หรือใช้งานโดย Application หรือไม่ ?

ช่วงนี้น่าจะเป็การเขียน Blog ที่ดองๆไว้ให้เสร็จนะครับ (อันนี้ดองมาตั้งแต่ปี 2014 มาใส่ภาพประกอบตอน 2017 555) วันนี้สุดท้ายของเดือนแล้ว มาดูกันสำหรับตัว MS SQL Server เราตรวจสอบได้อย่างไรว่า Table ถูก Lock ไว้นะครับ โดยเจ้าตัว MS SQL Server สามารถตรวจสอบได้ว่า Tabel ไหนถูก Lock โดยดูจาก Query ชุดนี้ครับ

USE <<your database name>>
GO
SELECT OBJECT_NAME(P.OBJECT_ID) AS TABLENAME
	 , RESOURCE_TYPE
	 , RESOURCE_DESCRIPTION
FROM SYS.DM_TRAN_LOCKS L
	JOIN SYS.PARTITIONS P 
		ON L.RESOURCE_ASSOCIATED_ENTITY_ID = P.HOBT_ID

ตัวอย่าง

ผมทดสอบ โดยทำให้ Table Company ถูก Lock ด้วยคำสั่ง SQL ดังนี้

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT *
FROM INVEST.COMPANY
WHERE COMPANYCODE BETWEEN 'A' AND 'E'
--DO NOT FORGET TO EXECUTE ROLLBACK TRAN WHEN TEST FINISH
--ROLLBACK TRAN

ทดสอบ Run และดูผลลัพธ์

USE investdb;
GO
SELECT OBJECT_NAME(P.OBJECT_ID) AS TABLENAME
	 , RESOURCE_TYPE
	 , RESOURCE_DESCRIPTION
FROM SYS.DM_TRAN_LOCKS L
	JOIN SYS.PARTITIONS P 
		ON L.RESOURCE_ASSOCIATED_ENTITY_ID = P.HOBT_ID

ดูผลลัพธ์การพบว่า Query ที่เขียนขึ้นสามารถดีกได้ว่า Table ที่ถูก Lock คือ Table Company (แต่ผลลัพธ์ อาจจะเยอะนิดนึงนะครับ เดี๋ยว Blog ตอนหน้ามาจัดผลลัพธ์ให้อ่านง่ายขึ้นครับ)

NOTE: ทดสอบเสร็จอย่าลืมคำสั่ง ROLL BACK นะครับ

--DO NOT FORGET TO EXECUTE ROLLBACK TRAN WHEN TEST FINISH
ROLLBACK TRAN

ถ้าเกิดลอง Run แล้วเกิด Error

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. สามารถแก้ได้โดย

• เปลี่ยนไปใช้ User ที่มีสิทธิเข้าดูใน System database "Master" อาทิ เช่น Windows Authentication User หรือ SA เป็นต้น
• เพิ่มสิทธิให้ User เดิมนี่แหละ สามารถดูข้อมูลใน System database "Master" ได้ ด้วยคำสั่ง

GRANT VIEW SERVER STATE TO <<user>>;
--Example
GRANT VIEW SERVER STATE TO invest;

สำหรับใครสนใจของตัว DB2 สามารถอ่านได้จาก Blog นี้ครับ [DB2] มาดักดูว่าใครทำ DB ค้าง

Reference

• sys.dm_tran_locks (Transact-SQL)
• sys.partitions (Transact-SQL)