[Database] ข้อควรระวังในการใช้ TABLOCKX

ก่อนอื่นเลยขอบอกเลยว่า ปัญหานี้เกิดจากความสะเพร่าของผมเอง (ยังดีที่ไม่ส่งโปรแกรมให้ลูกค้า ไม่งั้นยาว) เกิดจากการเขียน Query ที่ผิดพลาด ทำให้ Table ใน Database ถูก Lock ไม่ให้แก้ไข ซึ่งส่งผลให้ User คนอื่นๆทำงานไม่ได้

เดี๋ยวจะงงไปกันใหญ่ว่าทำไมผิดเขียนโปรแกรมแล้ว Table ดัน Lock ได้  ผมของอธิบายที่มากันก่อน

ปัญหา

• ปัญหา คือ เราต้องตรวจสอบการ Accept Transaction ของลูกค้า ว่าได้ถูก Accept โดยพนักงานไป หรือไม่ แต่ปัญหามันเกิดว่าเครื่องแรกกด Accept ไปแล้ว แต่เครื่องที่ 2 ไม่เห็นทำให้เกิดการ Accept รายการซ้ำ ทำให้ข้อมูลที่ได้ ไม่ถูกต้อง

การแก้ไข

• ทางทีมเลยใช้หลักการ ISOLATION Level ของฐานข้อมูลมาแก้ปัญหา โดยนำคำสั่ง TABLOCKX เพื่อ Lock Table ให้สามารถอ่านได้เพียงอย่างเดียวเท่านั้น
• แต่มีอีกสาเหตุด้วย คือ การเขียน Query ที่ ทำให้เกิด DeadLock คือ Table นั้นไม่สามารถแก้ไขได้ ลองดูตัวอย่างครับ

SELECT * 
FROM INVEST.COMPANY WITH (TABLOCKX)

• เขียนทั้งแบบ SELECT ทุก Column และไม่มีการกรองเงื่อนไขใดๆด้วย พอไปเจอคุณสมบัติของ TABLOCKX กลายเป็นว่าแทนที่จะ Lock Record ที่สนใจ กลายเป็นว่า Lock ทั้ง Table พออีก Process ที่เข้ามา INSERT หรือ SELECT มันอ่านไม่ได้ และเกิด Timeout ไปครับ
• ที่ควรทำตอน Lock Table ควรกำหนดเงื่อนไขด้วย และเลือกเฉพาะข้อมูลที่ต้องการใช้งานจริงๆครับ

SELECT COMPANYID 
FROM INVEST.COMPANY WITH (TABLOCKX)
WHERE COMPANYID = 23 
  AND ACTIVEFLAG = 'U'

หมายเหตุ การ Lock แบบนี้จะเป็นแบบ Exclusive คือ บังคับให้ Table นั้นมีได้เพียง 1  Connection หรือ 1 User เท่านั้น โดยใน

• SQL Server ใช้คำสั่ง TABLOCKX มีตัวิย

SELECT COMPANYID
FROM INVEST.COMPANY WITH (TABLOCKX)
WHERE COMPANYID = 23
AND ACTIVEFLAG = 'U'

• DB2 ใช้คำสั่ง LOCK TABLE SECURITY IN EXCLUSIVE MODE;

LOCK TABLE INVEST.COMPANY IN EXCLUSIVE MODE

SELECT COMPANYID
FROM INVEST.COMPANY WITH 
WHERE COMPANYID = 23
AND ACTIVEFLAG = 'U'

Reference

• http://greatfriends.biz/webboards/msg.asp?id=67190

• http://stackoverflow.com/questions/14973582/sql-table-not-locked-even-using-tablock-holdlock