שימוש ב-NoLock למעקב אחר עדכון טבלה

השימוש ב-NoLock כדי לשפר ביצועים ולהתגבר על נעילות מוכר ופופולרי מאוד.

מצד שני יש מי שהמונח NoLock גורם להם לעטות ארשת מבשרת רעות על פניהם, למנות את כל הסכנות הכרוכות בשימוש בו (dirty reads, phantom, non repeetable reads וכו') ולספר על שלל אסונות שקרו בגללו... אני מתייחס לשימוש בו כפי שאני מתייחס לשימוש בחשמל או במכונית: גם איתם צריך להיזהר ולדעת ממה להימנע, אבל הם כולם מועילים מאוד ושימושיים.

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

אם נריץ במקביל ל-Insert פקודת Select Count(*) From MyTbl , הפקודה תיתקע כי יש נעילה על הטבלה, ותרוץ רק כשה-Insert יגמר. לעומת זאת, אם נריץ פקודת Select Count(*) From MyTbl With (NoLock) - נקבל נתונים "מלוכלכים" ונוכל לראות כמה שורות נכנסו עד כה. כמובן: בשל כללי ה-ACID, פקודת ה-Insert היא טרנזקציה שיכולה להצליח - ואז כל השורות נכנסות, או להיכשל ואז אף שורה אינה נכנסת; אבל לא יתכן שחלק יכנסו לטבלה וחלק לא, אם כי ברור שמאחורי הקלעים מתבצעת הכנסה סדרתית של הנתונים, ואחרי זה ה-NoLock מאפשר לנו לעקוב.

לאחרונה קרה לי מקרה מעניין שהמחיש כיצד פעולת ה-Insert מתבצעת לתוך טבלה עם אינדקסים, בה הטרנזקציה מעדכנת את כולם. הרצתי פקודת Insert של כ-80 מיליון שורות, והפקודה  Select Count(*) From MyTbl With (NoLock) החזירה 0 שורות גם לאחר 5 דקות וגם לאחר 30 דקות של ריצה, והריצה של ה-Select נגמרה מייד. כשניסיתי מתוך סקרנות להריץ שליפה מעט שונה:  Select Count(*), Max(MyDate) From MyTbl With (NoLock) הריצה נמשכה יותר זמן והראתה שכל 80 מיליון השורות נמצאות בטבלה. מוזר: מדוע פעם את 0 שורות ופעם אחת 80 מיליון, ואם כבר נכנסו 80 מיליון שורות - מדוע הריצה של ה-Insert ממשיכה ולא נגמרת? מתברר כך: כשמריצים פקודת Select Count על טבלה עם אינדקסים, האופטימייזר לא יגש לטבלה עצמה (או ל-Clustered Index) אלא לאינדקס הכי "רזה" שיש לה, ויספור את השורות שם; כדי לחסוך ב-IO.

כאשר הרצתי את הפקודה הראשונה (רק עם ה-Count) האופטימייזר בחר את אחד האינדקסים, וכשהירצתי את הפקודה השנייה (עם ה-Count וה-Max) הוא נאלץ לפנות לטבלה עצמה ולא לאחד האינדקסים, מכיוון שהעמודה עליה התבצע ה-Max (עמודת MyDate) לא הייתה מאונדקסת. בשלב הזה הטבלה כבר הייתה מלאה ואילו האינדקס היה עדיין ריק. די ברור שהמערכת צריכה קודם כל למלא את הטבלה כי בכל שורה באינדקסים יש פוינטר לשורה הרלוונטית בטבלה, ובנוסף - רק לאחר שיש בטבלה נתונים - ניתן למיין אותם ולהכניס אותם לאינדקסים. בקיצור - במקרה הראשון המערכת בחרה כאמור את האינדקס הכי רזה, ולא מן הנמנע שבכוונה היא בחרה אינדקס שהיה עדיין ריק, כי הוא היה הכי קטן; ובמקרה השני נאלצה לגשת לטבלה - פעולה הרבה הרבה יותר כבדה מבחינת ה-IO, אבל העובדה שהטבלה הייתה מאוכלסת כבר ב-80 מיליון השורות אין פירושה שה-Insert נגמר בהצלחה, כי לא כל האינדקסים התמלאו.

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

אגב, אם לא ברור: להשתמש ב-Select Count כדי לדעת כמה שורות יש בטבלה ענקית - זה רעיון לא טוב, מכיוון שהמידע הזה שמור בטבלאות המערכת, וחבל לתת למערכת לטחון את הדיסק לשם כך. מי שלא זוכר מה השליפה שמחזירה את מספר השורות יכול לגשת לטבלה ב-SSMS, קליק ימני, Properties, ו-Storage. במקרה הנ"ל היה מדובר ב-Select Count עם NoLock כדי לעקוב אחר Insert כבד תוך כדי ריצה, ולכן זה שונה כי טבלאות המערכת אינן מתעדכנות תוך כדי, אלא לאחר גמר הריצה (והטרנזקציה).

מתוך סקרנות הרצתי 6 פקודות Select Count With Nolock עם Hint על כל אחד מששת האינדקסים (כולל ה-Clustered Index), וזה מה שקיבלתי:

בשורה האחרונה רואים את השליפה מהטבלה עצמה (Clustred Primary Key) שהתמלאה ראשונה ב-80 מיליון שורות. בשורות השנייה והחמישית רואים שני אינדקסים (Idx_T, Idx_I) שגם הם התמלאו כבר. בשורות השלישית והרביעית רואים שני אינדקסים (Idx_S, Idx_P) שעדיין לא התחילו להתעדכן. בשורה הראשונה רואים אינדקס (Idx_A) שכרגע מתעדכן ושבשלב הזה הוא חצי מלא - כ-40 מיליון שורות. לסיכום: אפשר להיעזר ב-NoLock לעקוב אחר פקודות Insert כבדות (ובשינויים המתבקשים גם אחר פקודות Delete & Update), אלא שיש לעקוב בהתחלה אחר מילוי הטבלה עצמה (בעזרת Hint מתאים), ולאחר מכן אחר האינדקסים השונים.