[DB2] WORKING WITH ISOLATION LEVEL

ดองไว้นานมาสำหรับ Blog นี้ 555 เพราะตอนแรกได้ทำ Research เพิ่มคิวการทำงาน โดยใช้ Database เป็นตัวเก็บคิว ซึ่งปัญหาที่จะพบ คือ เราต้องควบคุมการดึงข้อมูลใน Step ต่างๆ เพื่อไม่ให้เกิดปัญหาหลุคคิวได้ โดยทางฝั่ง SQL Server มีคำสั่งใช้งานตรงตัวอยู่แล้ว อาจจะมีฟีเจอร์เพิ่มขึ้นมาจากที่กำหนดไว้ใน ANSI บ้าง ซึ่งผมมาเขียนสรุปใน Blog ถัดไปครับ คราวนี้เรามาลองดูที่พระเอกของเราบ้าง อย่าง DB2 เนี่ย ว่าเราใช้งาน ISOLATION LEVEL กันอย่างไรครับ

ก่อนอื่น ผมขอสรุปเรื่อง ISOLATION LEVEL แบบรวบรัดสุดๆก่อนนะ ว่ามันมีอะไรบ้าง และทำไมต้องมีนะ
ISOLATION LEVEL คือ ระดับการจัดการของข้อมูล เพื่อให้ Transaction นั้นถูกต้อง
โดยไอ้เจ้า ISOLATION LEVEL ตามมาตรฐาน ANSI ได้กำหนดไว้ว่าทุกๆ DBMS ต้องมีอย่างน้อย 4 อย่าง ได้แก่

• Read-Uncommitted : อ่าน Tx ได้ตลอด แต่จะเกิดปัญหา Lost Update
• Read-Committed : อ่าน Tx ที่มีการ Commit แล้วเท่านั้น แต่จะเกิดปัญหา Unrepeatable Read
• Repeatable-Read : อ่าน Tx ที่มีการ Commit และ Lock ข้อมูลนั้นไว้ แต่จะเกิดปัญหา Phantoms Read
• Serializable : มีการจัดการข้อมูลในระดับที่สูงสุด มองง่ายว่ามีการ Lock Transaction หรือ Table นั้นๆทั้งหมด เพื่อแก้ปัญหาต่างๆที่กล่าวมา แต่มีข้อเสีย คือ เกิด Dead Lock ได้ง่าย

หมายเหตุ: ตัว DB2 ตัว DBMS default เป็น Cursor stability ครับ

ตัวอย่างการใช้งานบ้าง

• ลอง Set ที่ Connection เลย กำหนดคำสั่ง ดังนี้

db2 terminate db2 change isolation to ur db2 connect to sample

• Tune ที่ Query โดยเราใส่คู่กับ Keyword WITH ดังนี้

SELECT * FROM USER 
ORDER BY USERNAME 
FETCH FIRST 1 ROW ONLY WITH RS ;

SELECT * FROM ACTIVEUSER 
  LEFT OUTER JOIN USER 
    ON ACTIVEUSER.USERID = USER.USERID 
WITH RR

• กำหนดใน Transaction

-- ถ้า Lock ในระดับ Row อยู่ User คนอื่นๆสามารถ Read Data ได้ แต่ Update Data ไม่ได้ 
-- ถ้า Lock ในระดับ Table สามารถ Read/Write ข้อมูลได้ แต่ไม่สามารถใช้คำสั่ง SQL ประเภท DDL ได้ (พวก ALTER, CREATE และ DROP เป็นต้น) 
-- Share Lock สามารถเกิด่อเนื่องได้หลายๆครั้ง จากหลายๆ User หรือ Connection 
LOCK TABLE SECURITY IN SHARE MODE; 

-- Lock แบบ Exclusive นี้ บังคับให้ 1 user หรือ 1 Connection ในการจัดการข้อมูล (INSERT, UPDATE, DELETE) เท่านั้น 
-- สามารถมี Lock แบบ Exclusive เกิดขึ้นได้เพียงครั้งเดียวเท่านั้น ในระดับ Row หรือ Table 
LOCK TABLE SECURITY IN EXCLUSIVE MODE; 

-- แบบสุดท้าย LOCK แบบ UPDATE เป็นระดับ Row Lock เกิดขึ้นเมื่อเราใช้คำสั่ง 
SELECT... FOR UPDATE 
-- เพื่อมั่นใจว่า Row นั้นจะไม่ถูก User คนอื่นๆ Read/Write จนกว่า User ที่จองไว้จะทำงานกับ Row นั้นเรียบร้อย 

• แก้ไขที่ Table

-- Force the row level lock - Default
ALTER TABLE EQUITY LOCKSIZE ROW
-- Force the table level lock
ALTER TABLE EQUITY LOCKSIZE TABLE

• กำหนดใน Store ผ่านคำสั่ง SET CURRENT ISOLATION __ หรือ  SET ISOLATION LEVEL=__ ดังนี้

SET CURRENT ISOLATION = RR   --{RR, RS, CS, UR}
--or
SET ISOLATION UR -- {RR, RS, CS, UR}

Reference

• DB2 Tuning Database Application by Isolation Levels
• DB2 Isolation Levels