אתגר החודש – אתגר אפריל 2011

האתגר הבא מתרחש רק בסביבת  Oracle
נספר לכם מקרה אמיתי שקרה לנו אצל אחד מלקוחותינו.
האתגר של חודש זה הוא להסביר לנו מה התרחש ולמה הבעיה קרתה.

שידרגנו את המערכת בסביבת ה-TEST ונתנו לאנשי ה-QA לחרוש על המערכת כדי לוודא שהכל תקין ואין בעיות לאחר השדרוג.

קיבלנו דיווח שאחד התהליכים שעבד בצורה תקינה בסביבת 10g הפסיד לעבוד בגרסת 11g  והשגיאה שהתקבלה הייתה שגיאה שקשורה ל-Deadlock.

הסתכלנו ל-Alert log ובאמת ראינו את השגיאה  ORA-00060: Deadlock detected.

כשהסתכלנו בקובץ ה-Trace שמפרט את ה-sessions המשתתפים בתהליך שעבר Deadlock ואילו פעולות בוצעו ראינו את הפרטים הבאים:

*** 2011-03-20 23:47:00.704

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:
Deadlock graph

---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits
TM-0000c682-00000000        69     301    SX   SSX       68     249    SX   SSX
TM-0000c682-00000000        68     249    SX   SSX       69     301    SX   SSX
session 301: DID 0001-0045-00000013     session 249: DID 0001-0044-00000013
session 249: DID 0001-0044-00000013     session 301: DID 0001-0045-00000013
Rows waited on:
Session 301: obj - rowid = 0000C832 - AAAMgyAAIAAAuTLAAA
(dictionary objn - 51250, file - 8, block - 189643, slot - 0)
Session 249: obj - rowid = 0000C832 - AAAMgyAAIAAAuTLAAA
(dictionary objn - 51250, file - 8, block - 189643, slot - 0)
----- Information for the OTHER waiting sessions -----

Session 249:
sid: 249 ser: 23 audsid: 706284 user: 130/PMDS_PRACTICE_USER
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 68 O/S info: user: oracle, term: UNKNOWN, ospid: 12897
image: oracle@*****.com

client details:
O/S info: user: ***, term: ***** , ospid: 9732:25128
machine: Workgroup\***  program:***.exe
application name: ***.exe, hash value=2799981571
current SQL:

UPDATE SAM.TABLE1 SET DEFAULT_ADDRESS_ID=:DefaultAddressId1 WHERE ( SAM.TABLE1.ID = :Id2)
----- End of information for the OTHER waiting sessions -----

Information for THIS session:
----- Current SQL Statement for this session (sql_id=3a0c1j91597sy) -----
UPDATE SAM.TABLE1 SET DEFAULT_ADDRESS_ID=:DefaultAddressId1 WHERE ( SAM.TABLE1.ID = :Id2)
===================================================

כלומר ה-Sessions המשתתפים ב-Deadlock מנסים לבצע את העידכון הבא:

UPDATE SAM.TABLE1 
SET DEFAULT_ADDRESS_ID=:DefaultAddressId1
WHERE ( SAM.TABLE1.ID = :Id2)

מספר נתונים נוספים:

• התהליך עצמו מבצע קריאה סדרתית של שורות מקובץ אקסל המכיל שורות שמעדכנות רשומות קיימות בהתאם ל-ID  של הרשומה. אין באקסל רשומות כפולות – כלומר כל רשומה מעדכנת שורה שונה בטבלת TABLE1.
• מבדיקות שערכנו על התהליך, ראינו שהתהליך עובד בכל המערכות שמחוברות לבסיסי נתונים בגרסת 10g ונכשל על Deadlock על כל המערכות שמחוברות לבסיסי נתונים בגרסת 11g בצורה עקבית.
• סכמת המערכת במערכות המחוברות לבסיסי נתונים בגרסת 10g זהה למערכות המחוברות לבסיסי נתונים בגרסת 11g.
• האפליקציה שחוברה לבסיסי נתונים בגרסת 10g זהה לאפליקציה שחוברה לבסיסי נתונים בגרסת 11g.
• הרצנו Trace עם DBMS_MONITOR עם המאפיין binds=TRUE על מנת שנדע אלו נתונים התהליך מנסה לעדכן, וגילינו שהעידכון נעשה בכל פעם על רשומות שונות.

למה התהליך נכשל במערכת שבסיס הנתונים שלה עודכן לגרסה מתקדמת יותר ?

שיהיה בהצלחה!

צוות אתר ilDBA.

—————————————————————————————————————————————————

עידכון: 02.05.11

חבר הקהילה הזוכה של אתגר חודש אפריל 2011 הוא: אורן אפרתי!
אימייל יישלח לזוכה.

פיתרון האתגר של חודש זה:

ביחסים של יחסי PK-FK, שינוי על טבלה אחת גורר נעילה על הטבלה השניה, לצורך אכיפת data integrity.
באורקל 10g שינוי על טבלת הבן גרר נעילה מסוימת על טבלת האב (SS).
נעילה זו שלא הייתה מספיק חזקה,יכלה לגרום במקרי קצה ל-Race condition בין ה-sessions המעדכנים את הטבלה, ושהוביל במקרים מסויימים לפגיעה במידע.
הנ"ל תועד כבאג של 10g.
החל מ-11.1.0.6, הבאג תוקן. השינוי גרם לכך שעידכון על טבלאות עם FK גרר נעילה חזקה יותר (SX).
התהליך שתיארנו באתגר, עידכן בצורה סדרתית שורות רבות בטבלה. השוני באופי הנעילות בין הגרסאות השונות גרם במקרה הספציפי שתואר ל- deadlock.

יכולות להיות עוד סיבות לבעיה, אבל במקרה שאנחנו הצגנו, היה חסר אינדקס על עמודת DEFAULT_ADDRESS_ID. על עמודה זו שמעודכנת בפקודת ה-update מוגדר FK לטבלה אחרת.
ב-11.1, עקב הנעילה החזקה יותר, sessions שונים הצליחו להגיע למצב בו הם נעלו אחד את השני ולכן חטפו deadlock.
הפיתרון המיידי היה ליצור אינדקס על עמודת DEFAULT_ADDRESS_ID. ברגע שהרצנו שוב את התהליך, הוא הסתיים ללא שגיאות.

השינוי מתועד ב-note הבא של meta link:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PATCH&id=5909305.8

לינקים נוספים מעניינים בנושא:
http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/
http://markjbobak.wordpress.com/2008/06/09/11g-is-more-deadlock-sensitive-than-10g/

תודה לכל משתתפי האתגר,
מחר נפרסם את אתגר מאי. יש למה לחכות!
מי שמעוניין לקבל מושג לגבי אופי האתגר שיפורסם מחר, מוזמן להיכנס למאמר הבא של לירון אמיצי:
http://www.ildba.co.il/magazine/buffer-cache-and-full-scans/

צוות אתר ilDBA