Sunday, December 26, 2010

โอ้! ขวางทาง Lock ตอนที่ 2/2


(ต่อจากตอนที่แล้ว โอ้! ขวางทาง Lock ตอนที่ 1/2)

ประเภทของการ Lock และคอลัมน์ ID1/ID2 ในตาราง V$LOCK
เราสามารถหาว่า Session ใดกำลัง Block Session อื่น ๆ อยู่ได้โดยไม่ยากจากการหาข้อมูลในตาราง V$LOCK โดยที่แรกที่เราจะดูคือคอลัมน์ TYPE ซึ่งแสดงประเภทของการ Lock ที่มีอยู่หลายตัว แต่ประเภทการ Lock ส่วนใหญ่นั้นเป็นการ Lock เพื่อการทำงานของ System เอง โดยปกติแล้ว System Lock จะอยู่เพียงครู่เดียว และการจูน Library Cache, Undo Logs หรืออื่น ๆ โดยใช้ข้อมูลใน V$LOCK ก็ไม่ค่อยจะได้ประโยชน์สักเท่าใด (ดูรายการ Lock แบบ System ได้ใน Oracle Database Reference ในบทที่เกี่ยวกับ V$LOCK)
ส่วนการ Lock ประเภทที่เรียกว่า User Lock นั้นมีเพียง 3 แบบ คืือ TX, TM และ UL, โดย
  • UL(User-Defined) คือ Lock ที่เกิดจากการกำหนดของ Package ชื่อ DBMS_LOCK
  • TX Lock เป็นการที่ Transaction หนึ่ง ๆ ทำการ Lock แถวของข้อมูลในตารางที่จะทำการเปลี่ยนแปลง (Row Transaction Lock), TX Lock จะเกิดขึ้นเพียงครั้งเดียวต่อหนึ่ง Transaction ที่จะทำการเปลี่ยนแปลงข้อมูล โดยไม่สำคัญว่าคุณจะเปลี่ยนแปลง Object (เช่นตาราง) จำนวนกี่ตัว คอลัมน์ ID1 และ ID2 จะชี้ไปที่ Rollback Segment และรายการ Transaction ที่เกิดขึ้นใน Transaction Table
  • TM Lock คือ DML Lock ซึ่งจะเกิดขึ้นตามจำนวนของ Object ที่ถูก Lock เพื่อทำการเปลี่ยนแปลง โดยคอลัมน์ ID1 จะแสดง Object ที่กำลังถูกเปลี่ยนแปลง
โหมดของการ Lock
คุณสามารถดูข้อมูลเพิ่มเติมเกี่ยวกับ TM และ TX Lock ได้โดยการดูที่คอลัมน์ LMODE (Lock Mode) และคอลัมน์ REQUEST ทั้งสองคอลัมน์ใช้รหัสตัวเลขชุดเดียวกันในการบอกสถานะของการ Lock โดยยิ่งตัวเลขมากการ Lock ก็จะยิ่งแน่นหนา (Exclusive - หมายถึงปิดกั้นการเข้าถึงของ Session อื่น ๆ มากขึ้น) เช่น 0 หมายถึงไม่ Lock เลย และ 6 หมายถึง Exclusive Lock
ปกติแล้วแต่ละ Session จะต้องขอ Exclusive TX Lock จึงจะทำการเปลี่ยนแปลงข้อมูลได้ (LMODE = 6) และถ้า Session ไม่สามารถขอ Exclusive Lock ได้เนื่องจากมีบางแถวของข้อมูลที่มันต้องการเปลี่ยนแปลงถูก Lock อยู่ก่อนหน้าแล้วโดย Session อื่น คอลัมน์ LMODE ของ Session นั้นก็จะเป็น 0 โดยมีคอลัมน์ REQUEST เป็น 6 เพื่อจะบอกว่ากำลัง (รอ) ร้องขอ Exclusive Lock อยู่ เราสามารถดูลักษณะการทำงานนี้ได้จากแถวของ V$LOCK ที่ได้แสดงไปในบทก่อนหน้าดังนี้

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------ -- -------- -------- ----- -------- ------- ------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1

สังเกตดูว่าคอลัมน์ ID1 และ ID2 ของ Session 2 ซึ่งกำลังถูก Block และรอขอ TX Lock (LMODE=0, REQUEST=6) ชี้ไปที่ Rollback และ Transaction Entries ของ Session 1 นั่นทำให้เราสามารถหา Session ที่กำลัง Block อยู่ได้
บางครั้งคุณอาจจะเห็น TX Lock ใน Mode 4 (LMODE=4), Shared Mode หมายถึง Data Block ที่เก็บแถวของข้อมูลที่เราต้องการเปลี่ยนแปลงไม่มี Interested Transaction List (ITL) เหลือ เราก็จะเห็น Session ร้องขอ TX Lock ใน Mode 4 ระหว่างที่รอให้ ITL ว่าง ดังนั้นถ้าเราเจอ TX-4 Lock เราอาจจะต้องเพิ่ม INITRANS พารามิเตอร์ให้กับ Object ตัวนั้น

ITL (Interested Transaction List)
เมื่อแถวของข้อมูลในตารางถูก Lock โดย Trasaction ใด ๆ ข้อมูลการ Lock นั้นจะถูกเก็บอยู่ในส่วนหัวของ Block ของข้อมูล (Block Header) ที่แถวของข้อมูลนั้นเก็บอยู่ ข้อมูลส่วนนี้เราเรียกว่า Interested Transaction List (ITL) โดยแบ่งเป็น Slot, ใน ITL เป็นที่เก็บข้อมูลของ Transaction Address และ Rowid, ITL สามารถเก็บข้อมูลการ Lock ได้หลาย Transaction โดยเมื่อ Transaction ร้องขอ Lock มันก็จะใส่ rowid ของแถวที่ต้องการไว้ใน ITL และเมื่อ Transaction อื่น ๆ ต้องการ Lock มันก็จะทำเช่นเดียวกัน เมื่อ Transaction ทำการ Commit หรือ Rollback มันก็จะปล่อย Lock พร้อมทั้งข้อมูลใน Slot ของ Block Header ด้วย เพื่อคืนพื้นที่ให้สามารถนำมาใช้ได้ต่อไป การเซ้ทพารามิเตอร์ INITTRANS และ MAXTRANS ตอนสร้างตารางเป็นตัวกำหนดจำนวน Slot ที่สามารถเปิดได้เมื่อจำนวน Transaction ที่ต้องการเข้าเกินกว่าจำนวน Slot ที่มี Slot ใหม่จะถูกเปิดตราบใดที่ยังไม่เกิน MAXTRANS อย่างไรก็ดีถ้าจำนวน Slot เกินจำนวนที่จะขยายได้แล้ว (เนื่องจากพื้นที่ใน Block ไม่พอแล้ว หรือเกิน MAXTRANS) Transaction จะต้องรอซึ่งจะทำให้เกิดการ Lock ในโหมด 4 ขึ้น

TM Lock โดยปกติจะร้องขอที่ Mode 3 (LMODE=3, Shared-Row Exclusive) และ 6 ซึ่งถ้าเป็นคำสั่ง DDL (เช่นพวกคำสั่ง ALTER เป็นต้น) จะร้องขอ TM Exclusive Lock (CREATE TABLE ไม่ต้องใช้ TM Lock เพราะ Object นั้นไม่เคยมีอยู่) ส่วนคำสั่ง DML จะต้องใช้ Shared-Row Exclusive Lock ดังนั้นจากแถวที่เราได้จาก V$LOCK เราสามารถเห็นว่าเป็น DML Lock

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ----- -- ------ ---- ----- -------- ------ ------
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0

การระบุ Object ที่ถูก Lock อยู่
เราสามารถจะระบุ Object ที่ถูก Lock ได้จากคอลัมน์ ID1 จากแถวใน V$LOCK ที่เป็น TM Lock โดยนำไป Join กับตาราง DBA_OBJECTS ดังนี้

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

การระบุแถวในตารางที่ถูก Lock
ส่วนใหญ่แล้วการรู้ว่าตารางใดที่ถูก Lock ก็เพียงพอแล้ว แต่หากเราต้องการข้อมูลถึงระดับแถวที่ถูก Lock ก็สามารถทำได้ ดังนี้

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88519 16 171309 0
ซึ่งให้ข้อมูลกับเราเกี่ยวกับ Object ID, Relative File Number, Block ใน Data File และแถวที่อยู่ใน Block ซึ่ง Session กำลังรอ (Lock) อยู่ ข้อมูล 4 ตัวนี้เมื่อนำมาประกอบกันก็จะเป็น ROWID แบบขยายนั่นเอง (Extended ROWID) เราสามารถจะสร้าง Extended ROWID จากข้อมูลทั้งสี่นี้โดยใช้ Package ชื่อ DBMS_ROWID ซึ่งมีฟังก์ชัน ROWID_CREATE ที่ใช้ในการแสดง ROWID

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------ ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA

ซึ่งทำให้เราสามารถนำมาหาแถวของข้อมูลที่เราต้องการในตารางได้

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1 a

บทสรุป
เราได้เห็นวิธีการที่จะระบุ Session ที่ Block Session อื่น และวิธีการค้นหาเพื่อตรวจสอบแถวของข้อมูลที่มี Session รอทำการ Lock อยู่ และดิฉันหวังว่า เราได้เรียนรู้บางสิ่งบางอย่างบ้างเกี่ยวกับกระบวนการที่เกี่ยวข้องกับ V$LOCK

เกี่ยวกับผู้เขียน
Natalka Roshak เป็น Senior Oracle and Sybase Database Administrator, เป็นนักวิเคราะห์ระบบ และ Architect ทำงานอยู่ใน Kingston, Ontario และทำงานเป็นที่ปรึกษาในอเมริการเหนือ สคริปต์และทิปของเธอสามารถหาดูได้จาก Online DBA Toolkit ที่ http://toolkit.rdbms-insight.com/

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