โอ้! ขวางทาง 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

ORA_ROWSCN ล๊อคเรคคอร์ดของ Web Application ยังไง ไม่ให้เกิดปัญหา Concurrency

Updated: 1/4/2007

ถ้าคุณเคยเขียน Application ที่เป็น Web base แล้วเคยใช้คำสั่ง SELECT FOR UPDATE คุณอาจจะพบกับปัญหาที่ session ของ web หลุดไปหลังจากที่คุณ SELECT FOR UPDATE ซึ่งเป็นเรื่องที่เกิดขึ้นได้บ่อยๆ ใน Web เทคโนโลยี พอคุณ select ข้อมูลด้วยคำสั่งดังกล่าวเพื่อล๊อค ไม่ให้ใครเข้ามาแก้ไขข้อมูล row เดียวกับของคุณในขณะที่ user กำลังแก้ข้อมูลในหน้า Web ยังไม่ทันที่จะ Submit ก็มีเหตุบางอย่างทำให้ Web session นั้นหลุดไปผลก็คือข้อมูลไม่ได้ถูกอัพเดท แล้ว row นั้นก็จะล๊อคอยู่อย่างนั้นจนกว่า Oracle (หรือตัวคุณเอง) จะมา Kill session นั้นออกไป ซึ่งในระหว่างก่อนจะมีการ Kill session นี้ คนอื่นจะเข้ามาอัพเดท row เดียวกันนี้ไม่ได้เลย
Oracle10g ได้ให้ feature ที่เรียกว่า ORA_ROWSCN ซึ่งเป็น pseudo column ที่จะให้ค่าของ SCN หรือ System Change Number ซึ่งสรุปแบบง่าย ๆ ก็คือหมายเลขที่จะเปลี่ยนไปทุกครั้งที่มีการ commit ทรานแซคชั่น ลองดูจากตัวอย่างดีกว่าครับ

SQL> select ora_rowscn, empno, ename from emp where rownum <> update emp set ename = 'สมชาย' where empno = 7369;
1 row updated.

SQL> commit;
Commit complete.

ถ้าเรา select ข้อมูลขึ้นมาใหม่จะเห็นว่า ORA_ROWSCN เปลี่ยนไป

SQL> select ora_rowscn, empno, ename from emp;
ORA_ROWSCN EMPNO ENAME
---------- ---------- ----------
315968 7369 สมชาย
315951 7499 ALLEN
315951 7521 WARD
315951 7566 JONES

4 rows selected.

จากจุดนี้เองทำให้เราสามารถรู้ได้ว่ามีการเปลี่ยนแปลงข้อมูลไปหลังจากที่เราได้ select ข้อมูลไว้ก่อนหรือเปล่า ซึ่งทำให้เราสามารถเพิ่มเติมเงื่อนไขในการ update ข้อมูลเข้าไป เช่น
SQL> update emp set ename = 'สมโชค' where empno = 7369 and ORA_ROWSCN = 315968;
0 rows updated.

หากว่าก่อนที่เราจะรันคำสั่งข้างบน มี user คนอื่นได้อัพเดทเรคคอร์ดนี้ไปเป็นค่าอื่นแล้ว เราจะพบว่า row นั้นจะไม่ถูกอัพเดทเพราะว่าเลข ORA_ROWSCN ได้เปลี่ยนไปนั่นเอง จากนี้เราอาจจะเช็คว่าถ้าจำนวนของเรคคอร์ดที่ถูกอัพเดทมีค่าเป็น 0 ก็ให้ raise exception ขึ้นมา

SQL> select ora_rowscn, empno, ename from emp where empno = 7369;
ORA_ROWSCN EMPNO ENAME
---------- ---------- ----------
315980 7369 สมชาย

การจะใช้ feature นี้ เราจะต้องสร้างตารางใหม่ โดยเพิ่มคำว่า ROWDEPENDENCIES เข้าไป เนื่องจากโดยปกติ Oracle จะอัพเดท SCN ในระดับ Database Block ข้อมูลที่อยู่ใน Block เดียวกันจะถูกอัพเดท SCN พร้อม ๆ กันซึ่งเราจะไม่รู้ว่า เรคคอร์ดใดที่ถูกอัพเดทบ้าง เมื่อเราสร้างตารางให้เป็นแบบ ROWDEPENDENCIES เรคคอร์ดที่มีการเปลี่ยนแปลงในการ commit แต่ละครั้งจะมี ORA_ROWSCN เดียวกัน