Friday, April 03, 2009

How to find out the SQL statements causing deadlock?

เมื่อพูดถึงเรื่อง dead lock หลายคนคงถอนหายใจเพราะเคยเจอกันมาบ้างไม่มากก็น้อย
ปัจจัยที่ทำให้เกิด dead lock ไม่ว่าจะบน client, server, windows app, web หรือแม้กระทั่ง database นั้น
ล้วนเกิดจากการที่ชุดคำสั่งตั้งแต่สองชุดขึ้นไปใช้ทรัพยากรณ์ในระบบร่วมกันโดยขาดการควบคุมที่ดี จึงก่อให้เกิด
การรอกันเพื่อใช้งานทรัพยากร์ สามารถดูตัวอย่างการเกิด deadlock ได้ที่ นี้

แล้วเราจะหาต้นตอของการทำให้เกิด dead lock ในระบบของเราได้อย่างไรกันล่ะ ยิ่งถ้าเกิดกับระบบที่มีความซับซ้อนแล้วล่ะก็
ลำพังแต่นั่งพิจารณา ตรวจสอบ code และ business เพียงอย่างเดียวคงเหมือนกับการงมเข็มในมหาสมุทร

วันนี้ผมเลยขอนำเสนอ การหาสาเหตุที่แท้จริงของการทำให้เกิด dead lock บนฐานข้อมูล DB2 ครับ
สำหรับใน DB2 นั้นเมื่อเราสร้าง database ขึ้นมา deadlock event monitor ที่ชื่อว่า DB2DETAILDEADLOCK
ก็จะถูกสร้างขึ้นมาด้วยอยู่แล้วครับ เราสามารถสั่ง stop/start ได้ด้วยคำสั่ง

db2 set event monitor db2detaildeadlock state 0
ในกรณีที่ต้องการ stop db2detaildeadlock event monitor

db2 set event monitor db2detaildeadlock state 1
ในกรณีที่ต้องการ start db2detaildeadlock eventmonitor

แต่ในที่นี้ผมขอแนะนำให้ทำการ create dead lock event monitor ของเราเองขึ้นมาดีกว่าครับ
เนื่องจากรายละเอียดที่ได้จาก DB2DETAILDEADLOCK นั้นยังไม่เพียงพอต่อความต้องการ(ของผม)
เริ่มจาก

CREATE EVENT MONITOR DEADLOCKMON FOR
DEADLOCKS WITH DETAILS HISTORY VALUES
WRITE TO TABLE

ซึ่งสามารถดู syntax ได้จาก นี้

เมื่อสร้าง deadlock event monitor ด้วยคำสั่งตามข้างบนแล้ว db2 จะสร้าง table ดังต่อไปนี้ขึ้นมาให้เรา
โดยอัตโนมัติบน schema ที่ตรงตามกับ user ที่รันคำสั่งนี้

1. CONNHEADER_DEADLOCKMON - จะเก็บข้อมูลของ connection ที่ connect เข้ามาครับ
2. DEADLOCK_DEADLOCKMON - เก็บรายการ deadlock พร้อมทั้ง statement สุดท้ายที่ทำให้เกิด deadlock
3. DLCONN__DEADLOCKMON - เก็บรายการ deadlock โดยอ้างอิงตาม application ที่เกี่ยวข้องกับการกิด deadlock
4. DLLOCK_DEADLOCKMON - เก็บรายการ lock ทั้งหมดที่เกี่ยวข้องกับการเกิด deadlock
5. STMTHIST_DEADLOCKMON - เก็บรายการ sql statement ทั้งหมดของแต่ละการทำงานของแต่ละ application
6. STMTVALS_DEADLOCKMON - เก็บ input parameter ของ statement ในข้อ 5

ซึ่งหากใช้คำสั่งแตกต่างไปจากนี้สามารถอ่านละเอียดเพิ่มเติมของแต่ละ table ได้ที่

http://publib.boulder.ibm.com/infocenter/db2...

ที่ผมเลือกที่จะใช้วิธีให้ db2 เขียนข้อมูลเกี่ยวกับ deadlock ลง table ก็เพราะให้ง่ายในการค้นหาและปรับเปลี่ยนมุมมองครับ

หลังจากที่เราสร้าง DEADLOCKMON สำหรับเป็น deadlock event monitor เป็นที่เรียบร้อยแล้วขึ้นตอนต่อมาก็
ให้ไปเปิดให้ Heath Monitor ของ db2 ทำงานครับและกำหนดค่าให้กับ Health Monitor ว่าถ้าเกิด deadlock ก็ให้ส่ง mail มาบอกเราครับ
เพื่อที่จะได้ตรวจสอบ แก้ไข และเก็บข้อมูลได้ทันถ่วงทีครับ เสร็จแล้วเราก็เริ่ม start DEADLOCKMON ได้เลยครับ
หลังจากนั้นก็รอรับ mail อย่างเดียว

เมื่อได้รับ mail ว่าเกิด deadlock แล้วเราก็จัดการเปิด db2 font end ขึ้นมาเลยครับและรัน sql statement ดังต่อไปนี้



SELECT
*
FROM
ADMINISTRATOR.DEADLOCK_DEADLOCKMON

เพื่อแสดงรายการ deadlock ทั้งหมดที่เกิดขึ้น


SELECT
AGENT_ID,
APPL_ID,
APPL_ID_HOLDING_LK,
DEADLOCK_ID,
START_TIME,
STMT_TEXT
FROM
ADMINISTRATOR.DLCONN_DEADLOCKMON
ORDER BY
START_TIME

เพื่อ list ออกมาดูว่ามี application ไหนที่ทำให้เกิด deadlock ครับ แนะนำว่าให้ลองพยายามสังเกตุ APPL_ID กับ
APPL_ID_HOLDING_LK ของแต่ละ deadlock id ให้ดีครับ

SELECT
DEADLOCK_ID,
PARTICIPANT_NO,
STMT_FIRST_USE_TIME,
STMT_ISOLATION,
STMT_HISTORY_ID,
STMT_TEXT
FROM
ADMINISTRATOR.STMTHIST_DEADLOCKMON
WHERE
DEADLOCK_ID IN (1,2,3)
ORDER BY
STMT_FIRST_USE_TIME

เพื่อแสดง sql statement ทั้งหมดที่มีส่วนทำให้เกิด deadlock ครับในที่นี้ where condition ของผมนั้นสามารถดัดแปลง
ให้มา join กับ DEADLOCK_DEADLOCKMON ได้ครับ จุดที่น่าสังเกตุคือให้ order by DEADLOCK_ID แล้วพยายาม
พิจารณา statment ประกอบกับ PARTICIPANT_NO ครับแล้วจะพบคำตอบของสาเหตุการเกิด deadlock ครับ

แชน

1 comment:

Blogger said...

Did you know that that you can generate cash by locking special pages of your blog or site?
To start just open an account on AdscendMedia and run their content locking widget.