SQL: Locking and Deadlock

วันนี้จะมาเล่าถึง Lock พื้นฐาน 3 ประเภทคือ
1. Share Lock (ไม่ต้องระบุ) เป็น lock ที่ถูกใช้ในการ select
2. Update Lock (updlock) เป็น lock ที่ถูกใช้ตอนที่จะมีการแก้ไขข้อมูล เช่น update
3. Exclusive Lock (xlock) เป็น lock ที่ถูกใช้เมื่อข้อมูลนั้นมีการแก้ไขแล้ว

การใช้ lock ประเภทต่างๆนั้นจะพูดถึงการใช้ใน transaction ที่มี isolation level เป็น serializable
เพราะว่า ที่ level นี้จะเหมือนกับการใส่ holdlock เพิ่มลงไปในทุก query ที่ใช้
holdlock นั้นหมายถึง ให้ถือ lock ใดๆที่ใช้ตอนนั้นไปจนกว่าจะจบ transaction ถ้าไม่ใส่ holdlock มันก็จะถือ lock ที่ใช้ไว้แค่สิ้นสุด statement ของการ query เท่านั้น

ตัวอย่างการอธิบาย lock ต่างๆ
- share lock จะใช้ร่วมกับ share lock และ update lock ได้
- update lock จะใช้ร่วมกับ share lock ได้เท่านั้น
- exclusive lock ไม่สามารถใช้ ร่วมกับ lock ใดๆได้

เช่น
Begin T1
Begin T2

T1.Select * From Table -- ข้อมูลที่ถูก Selct จะติด share lock
T2.Select * From Table -- ข้อมูลที่จะถูก Selct จะติด share lock อยู่แต่สามารถ Select ออกมาได้เพราะว่า การ Select ใช้ share lock ซึ่งจะใช้ร่วมกับ share lock ได้

End T2
End T1

อีกตัวอย่าง
Begin T1
Begin T2

T1.Select * From Table with (updlock)-- ข้อมูลที่ถูก Selct จะติด update lock
T2.Select * From Table -- ข้อมูลที่จะถูก Selct จะติด update lock อยู่แต่สามารถ Select ออกมาได้เพราะว่า การ Select ใช้ share lock ซึ่งจะใช้ร่วมกับ update lock ได้

End T2
End T1

อีกตัวอย่าง
Begin T1
Begin T2

T1.Select * From Table -- ข้อมูลที่ถูก Selct จะติด share lock
T2.Select * From Table with (updlock) -- ข้อมูลที่จะถูก Selct จะติด share lock อยู่แต่สามารถ Select ออกมาได้เพราะว่า การ Select นี้จะใช้ update lock ซึ่งจะใช้ร่วมกับ share lock ได้

End T2
End T1

อีกตัวอย่าง
Begin T1
Begin T2

T1.Select * From Table with (updlock) -- ข้อมูลที่ถูก Selct จะติด update lock
T2.Select * From Table with (updlock) -- ข้อมูลที่จะถูก Selct จะติด update lock อยู่ ไม่สามารถSelect ออกมาได้เพราะว่า การ Select นี้จะใช้ update lock ซึ่งจะใช้ร่วมกับ update lock ไม่ได้

End T2
End T1

อันนี้จะอธิบายเพิ่มเติมเรื่อง update lock
ตัวอย่างเช่นถ้ามี statement: Update Table1 Set Value=2 where ID = 1 ซึ่ง
พอ โปรแกรมมันรันถึงอันนี้ มันก็จะใส่ updlock ลงไปใน row ที่มี ID = 1 ซึ่งถ้า row นั้นไม่ได้ติด updlock หรือ xlock อยู่ มันก็จะใส่ updlock ของมันลงไปสำเร็จ
พอใส่แล้วจะเซตค่า Value=2 พอเปลี่ยนเสร็จ updlock นั้นจะถูกเปลี่ยนเป็น xlock ทันที คราวนี้ Transaction อื่นๆไม่มาสามรถใช้ row นี้ได้จนกว่า Transaction ที่เป็นเจ้าของ lock นี้จะเสร็จงาน(Committed, Rollback)

คงจะพอเข้าใจ lock
ต่อมาก็จะพูดถึง deadlock
รูปแบบทั้วไปของ deadlock ก็เช่น

T1.Update a
T2.Ubdate b
T1.Update b
T2.Update a -- deadlock

จะเห็นว่า T1 มี a ต้องการ b แต่ T2 มี b ต้องการ a แบบนี้ก็เกิด deadlock
วิธีแก้คือ
เรียงลำดับการใช้งานให้เป็นทิศทางเดียวกันเช่นใช้ a ก่อนเสมอแล้วใช้ b

อีกรูปแบบ
มีโปรแกรมหนึ่งมีลำดับการทำงานดังนี่

Begin Transaction
1. Select a
2. Update a
End Transaction

เมื่อมีคนสองคนรันโปรแกรมนี้พร้อมๆกันอาจจะเกิดลำดับการทำงานดังนี้
T1.Select a
T2.Select a
T1.Update a
T2.Update a -- deadlock

ตอนแรกเห็นแบบนี้ว่ามีการใช้แต่ค่า a มันจะ deadlock ได้อย่างไร
งั้นมาดูกลไกลึกๆ
-T1 ใส่ sharelock ลง a
-T2 ใส่ sharelock ลง a ใส่ได้เพราะ sharelock ใช้ร่วมกันได้
-T1 เปลี่ยน sharelock เป็น updlock เพราะจะอับเดท แล้วก็เปลี่ยนเป็น xlock เมื่อเปลี่ยนค่า
-T2 จะอับเดทเลยใส่ updlock แต่มันใส่ไม่ได้เพราะติด xlock จาก T1,T1 จะ Committed เพื่อจบงานก็ไม่ได้เพราะมันจะเปลี่ยนค่าถ้า T2 อ่านมาใหม่จะไม่ได้เหมือนอ่านครั้งแรก แต่ว่า Isolation เป็น Serializable ซึ่งจะต้องไม่เกิด Nonrepeatable read ก็เลย raise error deadlock

วิธีการแก้
ใส่ with (updlock) ลงใน select statement ทุก statement ที่จะตามด้วยการ update ค่าของ statement ที่ select ตอนแรก

T1.Select a with (updlock) -- ทำสำเร็จ
T2.Select a with (updlock) -- คราวนี้ มันจะคอยจน T1 ทำงานสำเร็จก่อนซึ่งมันยังไม่ select ค่าอะไรออกมา เพราะมันติด updlock ซึ่งใช้ร่วมกับ udplock ไม่ได้ มันจะ pause ตัวเองแล้วให้ T1 ทำต่อ
T1.Update a -- ทำสำเร็จ
T2.Select a with (updlock) -- ทำต่อหลังจาก T1 เสร็จแล้ว
T2.Update a -- ทำสำเร็จ

ไม่เกิด deadlock แล้ว

สรุปทางลดปัญหา Deadlock คือ
1. เรียงลำดับการใช้
2. ใส่ updlock

ขอบคุณที่มา http://www.bloggang.com/viewdiary.php?id=chaizzz&month=03-2006&date=08&group=1&gblog=8