חקירת deadlocks ב- SQL Server (חלק 1)

08/12/2011

אביאל אילוז

clip_image002

MCT, מומחה ב-SQL Server, בעל 15 שנות ניסיון בתחום ומנהל אקדמי של מכללת נאיה

(אני מודה לעדי כהן מנאיה טכנולוגיות על הערותיו החשובות למאמר).


בעיה שעולה מעת לעת באפליקציות המבוססות על SQL Server היא הופעתו של אירוע deadlock, הדורשת את התערבותו של צוות הפיתוח או ה-DBA כדי לחקור את מקורותיו. ההתחקות אחר הגורמים ל-deadlock עשויה להיות מורכבת וממושכת ולעתים אף מתסכלת – אך ללא ספק מאתגרת.

כדי לעשות זאת באופן שיטתי ויעיל יש צורך להכיר את הכלים שמספק לנו SQL Server לשם כך ולהשתמש בהם. בסדרה בת מספר מאמרים נעסוק בנושא זה ונתוודע לכלים ולשיטות האלה ולאופן שימושם, ובמיוחד נראה איך מנתחים נכון את המידע המופק מכלים האלה אודות מקורותיו של deadlock.

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

מה זה deadlock?

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

תיאורתית, מצב זה יכול להמשך לעולם כששתי הטרנזקציות אוחזות זו בזו ומשפיעות על טרנזקציות אחרות לגמרי המנסות לגשת לאותם נתונים, אך SQL Server מחסל את המצב הזה ע"י בחירת אחת הטרנזקציות כ- victim – קורבן שישלם בחייו למען הכלל; טרנזקצית ה-victim מבוטלת (ע"י rollback אוטומטי) וכל נעילותיה משתחררות ובכך מסתיים מצב ה-deadlock.

כדי לא להשאיר את הטרנזקציה המבוטלת מבוישת, מוענקת לה אחר כבוד שגיאה מס' 1205 המודיעה לה כי נבחרה כ-victim :

Msg 1205, Level 13, State 56, Line 9

Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

עשה זאת בעצמך

כדי להמחיש ולהסביר את התופעה ביתר פירוט נריץ את קטעי קוד הבאים באופן הבא (במסד הנתונים AdventureWorks2008):

 

לחלון שאילתא ראשון נעתיק את הקוד הבא עבור טרנזקציה A:

–Transaction A

USE AdventureWorks2008

GO

BEGIN TRANSACTION

–Statement 1

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

 

–Hold for 10 seconds

WAITFOR DELAY '00:00:10'

 

–Statement 2

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

 

COMMIT

GO

 

לחלון שאילתא נוסף נעתיק את הקוד הבא עבור טרנזקציה B:

–Transaction B

USE AdventureWorks2008

GO

BEGIN TRANSACTION

–Statement 1

UPDATE HumanResources.Department

SET Name = Name + ' added text'

WHERE DepartmentID = 1

 

–Hold for 10 seconds

WAITFOR DELAY '00:00:10'

 

–Statement 2

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

 

COMMIT

GO

 

יש לנו כאן שתי טרנזקציות אשר מצד אחד מעדכנות שתי טבלאות שונות כל אחת, ומאחזרות מאותן שתי טבלאות; הסיטואציה הזאת, באופן שבו כתוב הקוד, תגרום לאירוע deadlock.

ברוב המקרים יופיע אירוע deadlock שמתשתתפות בו שתי טרנזקציות בשני תהליכים שונים, אך יתכנו מצבים שבו יופיע deadlock כתוצאה מנעילה מעגלית שמשתתפות בה יותר משתי טרנזקציות.

נראה עכשיו כיצד זה קורה ונסביר את המתרחש.

נריץ את הקוד של טרנזקציה בחלון הראשון ונעבור מיד לחלון השני (תוך 10 שניות, אל תלכו לענות לטלפון) ונריץ את הקוד של טרנזקציה B. מה יקרה?

1.       בחלון הראשון תפתח טרנזקציה A ומייד יורץ משפט 1 המעדכן את הטבלה Sales.SaleOrderDetails ברשומה האחת העונה על תנאי ה-WHERE.

 

2.       מאחר שפעולת העדכון מתבצעת מתוך טרנזקציה מפורשת (BEGIN TRANSACTION) שטרם הסתיימה, הטבלה המתעדכנת Sales.SaleOrderDetails ננעלת ברשומה הספציפית ע"י Lock מסוג X  – נעילה אקסקלוסיבית לטובת טרנזקציה A המונעת עדכון או קריאה של רשומה זאת ע"י טרנזקציות בו-זמניות אחרות.

 

3.       בשלב זה הקוד של טרנזקציה A מושהה למשך 10 שניות (ע"י ה- WAITFOR) כדי לאפשר לנו להריץ את הקוד של טרנזקציה B בחלון השני – ואכן זה מה שעשינו.

 

4.       בחלון השני נפתחת טרנזקציה B ומייד יורץ משפט 1 בקוד זה המעדכן את הטבלה HumanResources.Department ברשומה האחת העונה על תנאי ה-WHERE.

 

5.       גם כאן, כמו בטרנזקציה A, הטבלה המתעדכנת HumanResources.Department ננעלת ברשומה הספציפית ע"י Lock מסוג X – נעילה אקסקלוסיבית לטובת טרנזקציה B המונעת עדכון או קריאה של רשומה זאת ע"י טרנזקציות בו-זמניות אחרות, ובכללן טרנזקציה A השכנה.

 

6.       לאחר השהיה של 10 שניות, ממשיך הקוד של טרנזקציה A לרוץ ועובר למשפט 2 המאחזר רשומה אחת מתוך הטבלה HumanResources.Department – אותה רשומה הנעולה כרגע ע"י טרנזקציה B בשל העדכון.

 

7.       הבקשה לאיחזור הרשומה דורשת נעילה מסוג S (נעילת Shared) על רשומה זאת. בקשת נעילה מסוג S אינה נענית כאשר יש נעילה מסוג X על אותו מקור נתונים, ולכן טרנזקציה A ממתינה (Wait) לסיום העדכון בטרנזקציה B ולשחרור הנעילה X מן הרשומה. טרנזקציה A נמצאת במצב Blocked – היא חסומה ע"י טרנזקציה B. (על רמת התאימות בין סוגי נעילות שונים ראו ב-Lock Compatibility).

 

8.       בינתיים, לאחר השהיה של 10 שניות, ממשיכה טרנזקציה B בעבודתה ועוברת למשפט 2, המאחזר רשומה אחת מהטבלה Sales.SaleOrderDetails – כזכור, אותה רשומה הנעולה כעת ע"י טרנזקציה A בשל העדכון.

 

9.       לכן גם טרנזקציה B ממתינה לסיום עדכון הרשומה המבוקשת בטרנזקציה A, ולכן גם טרנזקציה B במצב Blocked.

 

וזהו מצב ה-Deadlock: שתי הטרנזקציות ממתינות זו לזו וחסומות זו על ידי זו, ואין מושיע. גרוע מכך, טרנזקציות אחרות במערכת המבקשות לגשת לאותם נתונים נעולים, ממתינות כל העת והאפליקציה עלולה להיעצר ולהיתקע.

המצב הזה, תיאורתית, יכול להמשך לנצח, אך כאמור SQL Server מתערב בנעשה ומחסל את המריבה ע"י בחירת קורבן (Victim), הפעם, ככל הנראה בדמות טרנזקציה B, אשר תופסק ותבוטל (ע"י Rollback), תוך כדי הופעתה של שגיאה מס' 1205 המתוארת למעלה. לרוב, הקורבן יהיה הטרנזקציה שעלות המשאבים שהושקעה בה היתה הנמוכה ביותר ולכן ביטולה ע"י rollback יהיה הקצר ביותר. החלטה זו נקבעת באופן אוטומטי, אך ניתן להשפיע עליה באמצעות משפט SET DEADLOCK_PRIORITY.

 

בדוגמא הזאת, אילצנו בכח מצב של deadlock, אשר נוצר כתוצאה מגישה לנתונים בסדר שונה בכל אחת מהטרנזקציות. לו סדר הגישה לטבלאות היה זהה בשתי הטרנזקציות, היינו אז עדים להמתנה ולחסימה אך לא ל-deadlock (נסו בעצמכם והווכחו).

ואכן אחת הדרכים למנוע deadlock היא להקפיד על סדר גישה קבוע לנתונים בטרנזקציות השונות שבאפליקציה. אלא שהדוגמא הזאת היא דוגמא פשטנית מאוד, לצורך ההדגמה בלבד. בעולם האמיתי ובמערכות טרנזקטיביות מהירות, deadloack יכול להופיע גם מסיבות אחרות. למשל, כאשר SQL Server משנה באופן אוטומטי את סוג הנעילה או את רמת האוביקט הננעל (למשל ב-Lock Escalation) תוך כדי פעולות הטרנזקציה. או למשל כאשר משפט UPDATE עסוק גם בעדכון אינדקסים המסייעים לאיחזור בטרנזקציות בו-זמניות אחרות.

אבל בכל המקרים העיקרון זהה למה שקרה בדוגמא שראינו; השוני הוא ברמת המורכבות של התהליך, ובעקבות כך עד כמה מורכב ומאתגר להתחקות אחר מקורות התופעה.

SQL CSI

הבעיה המונחת כרגע לפתחנו היא כיצד לאתר את מקור אירוע ה-deadlock ולהבין מה באמת קרה שם. אם יש קורבן ויש אחראים, אז צריך מז"פ!

במאמר השני בסדרה נעסוק בחקירת ה-deadlock, תוך שימוש בדוגמא שלנו, ע"י שני כלים לפחות, המספקים לנו מידע מפורט אודות אירוע ה-deadlock ומקורותיו. האחד הוא Trace Flags והשני SQL Server Profiler.

ניתן גם לקבל מידע אודות deadlock באמצעות מנגנון ה-Extended Events ששודרג ושופר בגרסת SQL Server 2008 R2 ועוד יותר בגרסה הבאה SQL Server 2012. שימוש במנגנון זה הוא ברמה מתקדמת יותר ולכן יוקדש לו ככל הנראה מאמר נפרד.

על ניתוח ועל פתרון מקרים מעניינים של deadlock קראו במאמרה של מיכל סומך.

  

 

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

2 תגובות

  1. דן08/12/2011 ב 13:27

    עבודה יפה!

    הגב
  2. מיכאל15/08/2014 ב 12:09

    ממש תודה 🙂
    מוסבר יפה מאוד.

    הגב