SQL Server Isolation Level - Lock ให้ตรงใจไม่ให้โดนด่า

วันนี้เรามาดูปัญหาเกี่ยวกับ รอ จากการ Lock ของ SQL Server ว่าเราสามารถตรวจสอบว่าโปรแกรมที่ช้าเกิดจากประมวลผลช้า หรือ รออะไรอยู่ทำไมถึงนานแสนนานกว่าจะทำอะไรได้แต่ละที

จะรู้ได้อย่างไรว่าโดน Lock อยู่ ?

วิธีแรกแบบ GUI สามารถดูจาก Activity Monitor ว่ามี Process ของเรา Process อะไร Lock อยู่จาก Column Block By แล้วนำ spid ไปหาว่าเป็นของคำสั่งไหน

อีกวิธีจะเป็นการใช้ Query ในการดู โดยใช้คำสั่ง sp_lock เพื่อดูดว่า Process ของเรารอ (wait) Process ของใครอยู่ แล้วนำ Process ID นั้นมาดูว่าทำอะไรอยู่จากคำสั่ง

dbcc inputbuffer (เลข ProcessID)

แก้ปัญหาแบบไม่ต้องปรับ Code

เนื่องจากค่าเริ่มต้นของ SQL Server จะมีการ Lock ไม่ให้อ่านข้อมูลได้ ถ้ามีคนอื่นยังแก้ไข้อมูลไม่ เสร็จ เช่น BEGIN TRAN ไว้ แล้วยังไม่ COMMIT หรือแว๊ปไปทำอะไรที่ใช้เวลานานจนส่งผลให้คนอื่นต้องรอ รอ รอ โดยไม่รู้ว่ารออะไร ดังนั้น เราจะมาเริ่มต้นจากการทำความเข้าใจวิธีการ Lock ของ SQL Server กันก่อน ว่าจะ​โดน Lock กันตอนไหน อย่างไรบ้าง

SQL Server Isolation Level

1. Read Committed (ค่าเริ่มต้น: ห้ามอ่านถ้ายังไม่ commit)

2. Read Uncommitted (อ่านค่าใหม่ที่สุด ไม่ต้องรอการ commit)

3. Repeatable Read (ถ้าอ่านอยู่ ห้ามใครแก้ไข/ลบข้อมูล แต่เพิ่มได้)

4. Serializable (ถ้าอ่านอยู่ ห้ามใครเพิ่ม/แก้ไข/ลบข้อมูล)

5. Snapshot (อ่านค่าที่ถูก commit ครั้งล่าสุด ไม่ต้องรอ commit)

โดยวิธีที่เป็นทีนิยมในการแก้ปัญหาการอ่านข้อมูลไม่ได้ถ้ามีคน Lock อยู่ จะใช้การตั้งค่า Isolaton Level เป็น Snapshot ที่มีข้อดีกว่าแบบ Read Uncommitted ที่เราจะไม่รู้เลยว่าค่าที่อ่านได้จะถูก Commit หรือไม่ ถ้าไม่ถูก Commit จะหมายถึงเรานำข้อมูลที่ไม่ถูกต้องหรือไม่มีอยู่จริงมาใช้ ในขณะที่แบบ Snapshot เป็นข้อมูลจริง ที่อาจจะถูกเปลี่ยนข้อมูลใหม่ถ้ามีการ Commit

แต่ Snapshot จะต้องมีการตั้งค่าในครั้งแรกก่อน โดยต้องไม่มี User เข้าใช้งานในการตั้งค่านี้ โดยใช้คำสั่งดังนี้ (รันครั้งเดียวใช้ได้ยาวๆ)

ALTER DATABASE db1 SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE db1 SET READ_COMMITTED_SNAPSHOT ON

เพียงเท่านี้ปัญหารอคอยเพราะโดนคนอื่น Lock อยู่ก็จะหมดไป :)