ORA-00054 Resource Busy มันคืออะไรกัน!?

หลายคนคงเคยเจอปัญหาน่าปวดหัวเกี่ยวกับ Error ที่มี Code ว่า ORA-00054 หลังจากที่เราพยายาม Execute DDL command บนตารางใดๆ ซักตารางนึง ซึ่งวันนี้ผมจะมาขยายความให้ชัดเจนว่าเจ้า Error ตัวนี้มันเกิดมาจากอะไร? และวิธีแก้ไข/วิธีเลี่ยงมีอะไรบ้างครับ…

ORA-00054: resource busy and acquire with NOWAIT specified.
Cause: Resource interested is busy.
Action: Retry if necessary.

สาเหตุของปัญหานี้หลักๆ เลยนั้นมาจากการที่เราพยายามที่จะใช้ DDL command (CREATE, DROP, ALTER) บน Object ที่มีการ Lock อยู่หรือ DML (INSERT, UPDATE, DELETE หรือ SELECT FOR UPDATE) ซึ่งจะทำให้เกิด ORA-00054 นั่นเองครับ ลองดูตัวอย่างด้านล่างนี้ครับ

ORA-00054
Example #1

SQL> -- SESSION #1 --
SQL> CREATE TABLE TB_TEST AS SELECT * FROM DBA_OBJECTS WHERE 2=1;

Table created.

SQL> INSERT INTO TB_TEST SELECT * FROM DBA_OBJECTS WHERE ROWNUM < 5;

4 rows created.

สำหรับ Session #1 ผมจะทำการ Create table แล้วทำการ INSERT ข้อมูลลงไปโดยที่ยังไม่ได้ทำการ Commit ครับ และใน Session #2 ผมจะทำการ CREATE INDEX บน TB_TEST เรามาดูผลที่ได้กันครับ

SQL> -- SESSION #2 --
SQL> CREATE INDEX IDX_TEST ON TB_TEST(OBJECT_ID);
CREATE INDEX IDX_TEST ON TB_TEST(OBJECT_ID)
                         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

จากผลด้านบนจะเห็นได้ว่าเกิด ORA-00054 หลังจากที่เราพยายามที่จะ CREATE INDEX ครับ นั่นก็คือการที่เราพยายามใช้ DDL command บน Object ที่ถูก Lock อยู่ครับ ที่นี้มาลองดูตัวอย่างที่ 2 กันครับ

ORA-00054
Example #2

SQL> -- SESSION #1 --
SQL> SELECT * FROM TB_TEST FOR UPDATE;

OWNER                          OBJECT_NAME                                                                                                                   SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS                            ICOL$                                                                                                                                                                20              2 TABLE               21-AUG-13 21-AUG-13 2013-08-21:15:29:02 VALID   N N N
SYS                            I_USER1                                                                                                                                                              44             44 INDEX               21-AUG-13 21-AUG-13 2013-08-21:15:29:02 VALID   N N N
SYS                            CON$                                                                                                                                                                 28             28 TABLE               21-AUG-13 21-AUG-13 2013-08-21:15:29:02 VALID   N N N
SYS                            UNDO$                                                                                                                                                                15             15 TABLE               21-AUG-13 21-AUG-13 2013-08-21:15:29:02 VALID   N N N

SQL>

ในตัวอย่างนี้ Session #1 ได้ทำการ SELECT FOR UPDATE บน TB_TEST ครับ ซึ่งมีความหมายเดียวกับการที่เราใช้ DML อย่าง INSERT หรือ UPDATE นั่นคือ Object นั้นๆ จะถูก Lock ไว้ แล้ว Session #2 ผมจะลองทำการ ALTER TABLE ครับ เรามาดูผลลัพธ์กันครับ

SQL> -- SESSION #2 --
SQL> ALTER TABLE TB_TEST DROP COLUMN OBJECT_ID;
ALTER TABLE TB_TEST DROP COLUMN OBJECT_ID
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

จะเห็นว่าเราได้ผลลัพธ์เดียวกับตัวอย่างแรกเลยครับ เนื่องจาก SELECT FOR UPDATE จะทำการ Acquire Lock เช่นเดียวกับ DML อื่นๆ

วิธีแก้ไขและหลีกเลี่ยงเจ้า Error ตัวนี้

1. ไม่เป็นไร เรารอนายได้! วิธีนี้ถ้าเราใจเย็นมากพอก็รอครับ อย่าไปรีบร้อน ฐานข้อมูลไม่หนีเราไปไหนแน่นอน
2. เรารอได้แต่อย่านานนักหละ! วิธีนี้เป็นการยืดเวลาการรอออกไปอีกนิดหน่อยครับ ใช้คำสั่งตามนี้ได้เลยครับ โดยตัวเลขที่ใส่นั้นหน่วยเป็นวินาทีครับ

alter session set DDL_LOCK_TIMEOUT = 300;

3. ถ้าเรารีบไปส่งรถละก็ (บ้า! ไม่ใช่ Taxi) ฆ่าเจ้า Session ที่ก่อปัญหาซะ วิธีนี้ออกจะโหดไปนิดหน่อย แต่เป็นวิธีที่ดีที่สุดในการแก้ไขนี้ครับ

alter system kill session 'sid,serial#';

สุดท้ายนี้วิธีการแก้ไขปัญหานี้นั้นขึ้นอยู่กับความเหมาะสมของแต่ละ Application ด้วยนะครับ ว่าระบบนั้นๆ เหมาะแก่การแก้ปัญหาแบบไหนมากที่สุด

ปล. ผมเคยเจอเคส ORA-00054 ในลักษณะที่เราทำการ CREATE TABLE AS SELECT แล้วรอไม่ไหวเลยคิล Session นั้นๆ ไป แต่ Session นั้นๆ ยังไม่หลุดออกไปจากฐานข้อมูลครับ พอเราทำการ CREATE อีกครั้งปรากฏว่าเจอ ORA-00054 เลยครับ วิธีแก้ไขคือต้องเข้าไปทำการ Kill process บน OS ครับ

ขอขอบคุณข้อมูลเพิ่มเติมจาก
Tech on the Net

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s