Saturday, August 28, 2010

โอ้! ขวางทาง Lock


ถ้าคุณเคยรับโทรศัพท์จากผู้ใช้ระบบที่อารมณ์บูดอันเนื่องมาจากธุรกรรมที่เขาหรือเธอกำลังทำอยู่บนโปรแกรมนั้นมันดูเหมือนแฮ็งก์ ๆ ช้า ๆ ไม่ไปไหนเลย หรือจากนักพัฒนาระบบที่ไม่เข้าใจว่าทำไม Session ทั้งหลายที่เกิดขึ้นจากการรันโปรแกรมที่เขาหรือเธอเขียนขึ้นนั้น มัน "Block" กันเอง คุณรู้ว่าจะมีประโยชน์มากที่สามารถระบุได้ว่าใคร (หรือ Session ใด) ไป Block ชาวบ้านเขา (หมายถึง Session อื่น) และจะมีประโยชน์ยิ่งขึ้นถ้ารู้ว่า Object ตัวใด (เช่น ตารางใด) กำลังถูก Block ไปจนกระทั่งรู้ว่าแถวใดกำลังถูก Block อยู่
ทดลองสร้าง Session ที่ล๊อคเรคคอร์ดที่ไป Block การล๊อคของเรคคอร์ดของ Session อื่น
{xtypo_info}ผู้แปล -- ปกติเมื่อ Session ต้องการทำการเปลี่ยนแปลงข้อมูล Session นั้นจะต้องทำการ Lock เรคคอร์ดที่ต้องการเปลี่ยนแปลงเสียก่อนโดยการใช้คำสั่ง SELECT ... FOR UPDATE, คำสั่ง DML บางอย่างเช่น Update, Delete จะทำการ Lock เรคคอร์ดที่มันไปกระทำไว้ทันที จนกว่าจะมีการ Commit หรือ Rollback คำสั่งนั้น ในขณะที่ Session กำลังรันคำสั่งเหล่านี้อยู่ (และยังไม่ได้ Commit หรือ Rollback) Session อื่น ๆ ที่รันคำสั่งที่จะทำการ Lock เรคคอร์ดเดียวกันกับเรคคอร์ดที่ถูก Lock อยู่ เราเรียกเหตุการณ์นี้ว่า Session แรกกำลัง Block Session หลังอยู่{/xtypo_info}

เริ่มต้นด้วยการสร้างสถานการณ์ที่ผู้ใช้ระบบคนหนึ่งไป Block ผู้ใช้ระบบอีกคนหนึ่ง ลองเปิด 2 Session แล้วรันคำสั่งต่อไปนี้ใน Session ที่ 1 เพื่อสร้างตารางทดสอบ

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a');

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.


ต่อไปก็ทำการล๊อคข้อมูลทั้งตาราง โดยยังอยู่ใน Session 1
SQL> select * from tstlock for update;

และใน Session 2 ลองพยายามอัพเดทแถวดู
SQL> update tstlock set bar='a' where bar='a' ;

คำสั่งข้างบนนี้จะแฮงก์ไปเฉย ๆ โดยถูก Block จาก Session 1 ที่กำลังล๊อคข้อมูลทั้งตารางอยู่

การค้นหา Session ที่กำลัง Block Session อื่นอยู่

Oracle ได้เตรียม View ชื่อ DBA_BLOCKERS ซึ่งจะแสดงรายการ SID ซึ่งกำลัง Block SID อื่นอยู่ แต่ว่าจากประสบการณ์ของตัวผู้เขียน พบว่าจะช้ากว่าการคิวรีจาก V$LOCK และไม่ได้ให้ข้อมูลอื่นใดเพิ่มเติมนอกจาก SID ที่กำลัง Block คนอื่นอยู่ ในขณะที่ V$LOCK คิวรีได้เร็วกว่าทำให้ง่ายในการระบุ Session ที่ Block คนอื่น ๆ อยู่พร้อมกับข้อมูลอื่น ๆ ที่เป็นประโยชน์ในการวิเคราะห์
SQL> select * from v$lock ;

ADDR     KADDR    SID TY ID1    ID2   LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
AF9E2C4C AF9E2C60 479 TX 131078 16739     0       6   685     0
ADDF7EC8 ADDF7EE0 422 TM 88519  0         3       0   697     0
ADDF7F74 ADDF7F8C 479 TM 88519  0         3       0   685     0
ADEBEA20 ADEBEB3C 422 TX 131078 16739     6       0   697     1
....     ....     ... .. ...... ..        .       .   ...     .


สังเกตคอลัมน์ BLOCK ถ้า Session ใด ๆ กำลัง Block Session อื่นอยู่ ค่า BLOCK จะเท่ากับ 1, นอกจากนั้นคุณสามารถจะชี้ Session ที่กำลังถูก Block ได้โดยเปรียบเทียบค่าในคอลัมน์ ID1 และ ID2, Session ที่ถูก Block จะมีค่าของ ID1 และ ID2 เหมือนกับ Session ที่ Block มันอยู่ และตัว Session ที่ถูก Block นี้จะมีค่าในคอลัมน์ REQUEST > 0 เสมอ เพื่อแสดงว่ามันกำลังร้องขออยู่ (แต่ยังไม่ได้)
ในคิวรีข้างบนเราจะเห็นว่า SID 422 กำลัง Block SID 479, SID 422 มีลักษณะคล้าย ๆ กับ Session 1 ในตัวอย่างของเรา และ SID 479 ก็คล้ายกับ Session 2
เพื่อให้ดูง่ายขึ้นเราอาจจะเขียนคิวรีแบบนี้
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block =1 and l2.request > 0
4 and l1.id1=l2.id1
5 and l1.id2=l2.id2;

SID 'ISBLOCKING' SID
--- ------------ ---
422 IS BLOCKING  479

1 row selected.


และเราอาจจะเพิ่มข้อมูลบางอย่างเข้าไป โดยการ Join กับ V$SESSION เพื่อให้ดูง่ายขึ้น
SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) is blocking '
3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.


ยังคงมีข้อมูลในวิว V$LOCK อีกมาก ก่อนที่จะสามารถอ่านข้อมูลเหล่านี้ เราจำเป็นจะต้องเข้าใจเพิ่มเติมเกี่ยวกับประเภทของการล๊อค และความหมายของคอลัมน์ ID1 และ ID2 เสียก่อน ขอบคุณที่ให้ความสนใจ โปรดติดตามตอนต่อไปนะคร๊าบ

แปลจาก What's blocking my lock? โดย Natalka Roshak on Sat, 2006-04-01 18:00 www.orafaq.com/blog/natalka_roshak

Thursday, August 5, 2010

Recreate Constraint กับ DBMS_METADATA


สมถวิวได้รับมอบหมายให้ดูแลระบบฐานข้อมูล Oracle มาได้สองเดือน โดยที่ทีมพัฒนาระบบได้ขอร้องให้เธอช่วยเป็น DBA จำเป็นให้กับทีมด้วย ด้วยไฟที่ร้อนแรงเธอแนะนำให้ทีมพัฒนาระบบสร้าง Foreign Key กับทุก ๆ ตารางที่มีการอ้างอิงไปยังตาราง Master เพื่อที่จะได้ไม่มีข้อมูลที่เป็น "กำพร้า" (Orphan) ในตารางลูก เช่นในระบบเก่ามีรายการชำระเงินบางรายการในตารางธุรกรรม ที่ไม่พบสัญญาในตารางที่เป็น Master ทำให้ผู้จัดการข้อมูลต้องเกาหัวแกร็ก ๆ โชคดีว่าข้อมูลการชำระเงินเหล่านี้เกิดขึ้นนานแล้ว และปัจจุบันไม่มีผลแล้ว

สมถวิวในนามของ DBA จำเป็นได้บรรจงสร้าง Constraint ต่าง ๆ ไม่ว่า Primary Key, Foreign Key ลงในตารางที่ทีมพัฒนากำลังเขียนโปรแกรมกันอยู่ เพื่อควบคุมการอ้างอิงกันของข้อมูลให้ถูกต้องที่สุด และป้องกันการปวดหัวในอนาคต วันหนึ่งสมโคดซึ่งเป็น Developer ในทีมได้บ่นกับสมถวิวว่า ทำไมต้องมี Constraint เยอะแยะ ทดสอบโปรแกรมลำบากมาก จะทำข้อมูลทดสอบก็ยาก จะลบก็ยาก ต้องไปตามลบกันเป็นทอด ๆ และแนะนำสมถวิวว่า "เอาออกเหอะ ตัวเอง เด๋วทีมดีเวลล็อป ควบคุมจากโปแก็มเอง" สมโคดกะจะให้โปรแกรมเป็นตัวเช็ค โดยคิดว่าการให้ User เลือกจากดร็อปดาวน์ลิสต์น่าจะทำให้ข้อมูลที่เข้ามาจะต้องมีในตารางมาสเตอร์เท่านั้น

สมถวิวเองก็เคยเจอปัญหานี้เหมือนกันคือไอ้เจ้า Foreign Key นี้มันช่างทำให้การทำงานเก้งก้างจัง จะลบก็ยาก จะเพิ่มก็ยาก แต่จะให้ลบ หรือไม่สร้างเลยละก็ เมินเสียเถอะ! เธอไม่อยากต้องมาปวดหัวกับข้อมูลประหลาด ๆ ในอนาคต เอ...แต่จะแก้ปัญหาอย่างไรดี เธอรู้ว่าการ Disable Constraint ก็ไม่สามารถจะแก้ปัญหานี้ได้ในบางกรณี แต่จะทำยังไงดีเธอเตรียมกระดาษกับปากกาเอาไว้แล้ว เธอจะจดรายละเอียด Constraint ให้ละเอียดเลย พร้อมทั้งคอลัมน์ต่าง ๆ ที่อยู่ใน Constraint ฐานข้อมูลตัวนี้มีแค่ 200 ตารางเอง แล้วก็มีตารางที่ต้องอ้างอิงตารางอื่นประมาณแค่ 150 ตาราง เธอรู้สึกปวดมวนในท้องขึ้นมาทันที แล้วถ้าเธอสร้างผิดแล้วไม่เหมือนเดิมล่ะ...เอถ้าเราสามารถจะลบ Constraint ออก และสร้างใหม่ได้แบบง่าย ๆ ก็ดีสินะ

สมถวิวคิดถูกแล้วที่จะควบคุมมาตรฐานหรือ Intergrity ของฐานข้อมูลซึ่งเป็นหน้าที่หนึ่งของ DBA แต่ปัญหาของการนี้คือเราจะต้องเก็บเอา Foreign Key Constraint เก่าเอาไว้ก่อนลบ แล้วเอามาสร้างใหม่เมื่อต้องการกลับมาสร้างกลับเพื่อควบคุม Constraint อีกครั้ง โชคดีที่ Oracle ได้เตรียม Utility สำหรับการสร้าง DDL (Data Discription Language - คำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆ ที่เกี่ยวกับข้อมูล เช่นตาราง, อินเด็กซ์เป็นต้น) เอาไว้ให้แล้ว ซึ่งน่าจะช่วยสมถวิวในการ Drop และสร้าง Constraint ใหม่ได้มาก ลองดูคำสั่งข้างล่างนี้

SQL> select
2 to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
3 from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

TO_CHAR(DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME))
-------------------------------------------------------------------------

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE


คำสั่งข้างบนนี้สามารถรันได้จาก Schema ที่เก็บตารางต่าง ๆ ได้ ไม่ต้องมีสิทธิ์เป็น DBA, สังเกตตรง DBMS_METADATA ซึ่งเป็น Package ชื่อก็บอกอยู่แล้วว่าเกี่ยวกับ Metadata หรือเป็นอะไรที่เกี่ยวกับข้อมูลของ Data อีกที ตัวฟังก์ชันที่เรานำมาให้ใช้คือ "GET_DDL" ซึ่งจะแสดง DDL หรือคำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆที่เกี่ยวกับข้อมูลดังได้กล่าวมาแล้วตัวอย่างคำสั่งที่สามารถสร้างขึ้นมาได้จากฟังก์ชันนี้เช่น คำสั่งในการสร้างตาราง, คำสั่งในการสร้างอินเด็กซ์ หรือจากตัวอย่างนี้คือคำสั่งในการสร้าง Constraint เป็นต้น สังเกตว่าเราใช้คำสั่งนี้ร่วมกับ Data Dictionary View เช่นในกรณีนี้คือ USER_CONSTRAINTS ถ้าเราใช้คำสั่ง
{xtypo_code}SQL> select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

เราจะได้ชื่อ Constraint ทั้งหมดที่เป็น Foreign Key เราเพียงแต่ใส่ฟังก์ชัน GET_DLL (ซึ่งเป็นส่วนหนึ่งของ Package ชื่อ DBMS_METADATA) เข้าไปเราก็จะได้ลิสต์ของคำสั่งในการสร้าง Constraint ที่เป็น Foreign Key ทั้งหมดใน Schema ปัจจุบัน

SQL> select to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
2 from user_constraints where constraint_type = 'R';

***ตรงฟังก์ชัน to_char เราสามารถเอาออกได้ ที่ใส่ไว้เนื่องจากข้อมูลที่ได้มีประเภทข้อมูลเป็น CLOB บาง Interface อาจจะไม่สามารถแสดงผลได้

ตรงที่เป็น 'REF_CONSTRAINT' เป็นค่าคงที่ที่เราต้องรู้ว่าจะใส่คำว่าอะไรในการคิวรีเช่นถ้าต้องการ Index ก็อาจใช้คำสั่งร่วมกับ Dictionary View ชื่อ USER_INDEXES เช่น
SQL> select to_char(DBMS_METADATA.get_ddl('INDEX',INDEX_NAME))
2 from USER_INDEXES;

TO_CHAR(DBMS_METADATA.GET_DDL(
-------------------------------------------------------------------------

CREATE INDEX "SCOTT"."IND_MVDEPT_DNAME" ON "SCOTT"."MV_DEPT" ("DNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT1" ON "SCOTT"."MV_DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
...
...
ดูค่าคงที่สำหรับ Object Type ได้ที่ http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

เมื่อเราได้คำสั่งมาแล้วเราก็สามารถที่จะบันทึกเก็บไว้ หลังจากที่เรา Drop เอา Constraint (หรือ Index) ออกแล้วเราก็สามารถนำคำสั่งเหล่านี้กลับมารันเพื่อให้วัตถุที่เป็น Constraint หรือ Index ต่าง ๆ กลับมาดังเดิม เช่นในกรณีของสมถวิว เธออาจจะรันคำสั่งนี้ขึ้นมาเก็บไว้ก่อน แล้วจึง Drop Constraint เมื่อทำงานต่าง ๆ ที่มี Constraint เป็นอุปสรรคเสร็จแล้ว ก็สามารถสร้าง Constraint ขึ้นมาใหม่โดยใช้คำสั่งที่เก็บไว้ข้างต้น สมถวิวก็จะสามารถคงความเป็น DBA ที่ดีไว้ โดยที่ยังสามารถให้ความสะดวกกับเพื่อนร่วมงานของเธอได้
เวอร์ชั่น: Oracle9i,10g