אחד הפיצ’רים הבסיסיים לסכימה של מסדי נתונים, היא היכולת להגדיר unique constraint.
מאחורי הקלעים, Unique Constraints מתבססים על Unique Indexes שנוצרים כדי לשרת את ה- constraint, כלומר – כדי לאפשר ל- SQL Server לבדוק בזמן ביצוע פעולות הוספה ועדכון שה- constraint לא מופר.

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

עם זאת, לפעמים נרצה לסנן שורות כפולות כדי להקל על השימוש במידע. למשל, אם יש לנו טבלת קישור שמקשרת בין אזרח בישראל לאבא שלו. ונניח שאנחנו מקבלים כל הזמן מידע על אזרחי ישראל ומי האבא שלהם, כאשר לעיתים המידע יחדש לנו (יספר לנו על קשר שלא ידענו עליו) ולעיתים לא (נקבל שוב מידע על קשר שאנחנו מכירים).
נניח גם (לטובת התרחיש הקצת מופרך) שעצם קבלת המידע לא מעניינת, כלומר אין לנו עניין לשמור תיעוד של עצם העובדה שקיבלנו קישור כזה (נניח כי הוא נשמר במקום אחר).
בתרחיש הזה – אין בעייה מבחינת נכונות המידע לשמור שורות כפולות. א’ הוא אבא של ב’ גם אם זה מופיע תיעוד לזה פעם אחת וגם אם מופיע לזה תיעוד מיליון פעמים. עם זאת, יש יתרונות להחזיק טבלה קטנה לעומת טבלה גדולה, ככה שיש לנו יתרון לשמור את הקישור הזה באופן יחיד.
כדי לעשות את זה אנחנו יכולים לנקוט באחת מהרבה הדרכים שקיימות כדי לשמור מידע סיכומי, או תצוגה סיכומית של המידע (כתבתי למשל על Indexed Views, שבהחלט אפשר לעשות בהם שימוש במקרה כזה).  אבל מה אם אנחנו מראש לא רוצים להחזיק את המידע ה-“מיותר” (כלומר, כל כפילות שהיא)?

לכאורה אנחנו נמצאים פה עם בעיית “UPSERT” מנוונת: אנחנו לא רוצים להכניס-או-לעדכן,  אנחנו רוצים להכניס-או-לוותר [אם יש כפילות].

לפני שאני אמשיך בהצגת Unique Indexes, אני רוצה להתייחס רגע לפיתרון הנאיבי של “נבדוק אם לא קיים ואז נכניס”. כלומר, נעשה תנאי של IF NOT EXISTS וע”ס הערכים שלו נפעל.
פיתרון הזה פחות טוב, ולא רק בגלל שהוא פחות אלגנטי ונוח. הוא גם לא מאפשר לנו לעבוד בצורה נוחה עם BULK INSERT-ים (כי אז אנחנו לא יכולים לשלב את הבדיקת IF NOT EXISTS), הוא מקשה עלינו מבחינת טרנזקציות שרצות במקביל (צריך לבחור isolation level כזה שיאפשר לנו לוודא שבין הבדיקה של ה- IF NOT EXISTS להכנסה לא הוכנס הערך מטרנזקציה אחרת) – ובנוסף, הוא גם לא חוסך לנו את האינדקס (כי אם יש לנו הרבה שורות בטבלה, הבדיקת IF NOT EXISTS עצמה תהיה יקרה). ואם כבר אינדקס, אז עדיף שזה יהיה תוך שימוש בפיצ’ר שמובנה במנגנון ומאפשר לנו להנות מיתרונות נוספים : –)

Unique Indexes והתנהגות במקרה של כפילויות

בגדול, unique index, זה אינדקס רגיל שמסומן ל- SQL Server שכל קומבינציה של ה- key columns שלו (לשם פשטות, אני אקרא לזה “הערך” שלו – למרות שכמובן זה יכול להיות שילוב של ערכים) הוא יוניקי. הוא למעשה “הרוח החיה” מאחורי כל unique constraint (כי הוא זה שמאפשר גישה מהירה כדי לבדוק שה- constraint נשמר).
Unique Indexes יכולים לשמש אותנו לאכיפת constraint, ובנוסף הם מספקים מידע חשוב לטובת ייצור Execution Plan. כאשר מובטחת ייחודיות של ערכים מדובר למעשה באמירה חזקה מאד על המידע שמאפשרת לבצע פעולות מסויימות בצורה יעילה יותר מה שמאפשר לייצר execution plan טוב יותר.

כאשר אנחנו יוצרים unique index, ההתנהגות הדיפולטית במקרה שאנחנו מכניסים ערך שקיים כבר היא פשוטה: לזרוק שגיאה (וכמובן שהשורה לא מתווספת). אם השגיאה הזאת קרתה כחלק מטרנזקציה (למשל, כאשר אנחנו עושים רצף של פעולות בטרנזקציה אחת, או למשל עושים שימוש ב- Bulk Insert ומכניסים מס’ שורות בבת אחת) – יתבצע rollback של הטרנזקציה, כלומר המצב יחזור לקדמותו ואף אחת מהפעולות שהתבצעה כחלק מהטרנזקציה לא תקרה. ההיגיון מאחורי זה ברור – אם רצף הפעולות הזה הביא למצב שבו ניסינו להכניס שורה עם ערך כפול, למרות שהוא אמור להיות ייחודי, אז משהו לא נכון קרה.

אבל, אנחנו יכולים להשפיע על ההתנהגות הזאת, ולהגיד ל- SQL Server שבמקום שגיאה יוציא אזהרה בלבד. מה המשמעות של אזהרה? לא מתבצע rollback. אנחנו מקבלים חיווי לכך שהיו שורות כפולות, אבל זה לא מונע מהטרנזקציה להתבצע בהצלחה.

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

יצירת Unique Index עם IGNORE_DUP_KEY

.איך עושים את זה? משתמשים ב- flag שנקרא IGNORE_DUP_KEY ומגדירים אותו ל- ON בעת יצירת ה- index.

CREATE UNIQUE NONCLUSTERED INDEX [IX_FatherSonInfo_Unique_SonID_FatherID] ON [dbo].[FatherSonInfo]

(

    [SonID] ASC,

    [FatherID] ASC

)WITH (IGNORE_DUP_KEY = ON)

מה אמרנו בפועל? ייצרנו אינדקס יוניקי חדש, ואמרנו שבמידה שמתבצעת פעולת הכנסה/עדכון לאינדקס הזה שתפגע בייחודיות – הוא ימנע את זה, מעצם הגדרתו כאינדקס יוניקי, אבל יעשה את זה עם אזהרה ולא עם שגיאה (לטובת הבהרה: כאשרמתבצעת פעולת הכנסה/עדכון על הטבלה אז מתבצעת פעולה על האינדקס – כי כל פעולה על הטבלה כרוכה בפעולה מתאימה על כל אינדקס שמוגדר בטבלה [למעט היוצא מין הכלל של Filtered Index, ועל זה בפוסט אחר]).

כמובן, שכאשר מריצים את ה- statement הזה חייבים שבטבלה לא יהיו כפילויות, אחרת נקבל שגיאה כזאת:

Msg 1505, Level 16, State 1, Line 5

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FatherSonInfo' and the index name 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

The statement has been terminated.

——————

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

with cte as (

    SELECT    ID,

            DateCreated,

            FatherID,

            SonID,

            RN = ROW_NUMBER()OVER(PARTITION BY FatherID, SonID ORDER BY ID)

    FROM FatherSonInfo

)

DELETE FROM cte

WHERE RN > 1

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

——————

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

שימוש באינדקס שמוגדר עם IGNORE_DUP_KEY=ON

אז אחרי שיצרנו את האינדקס הזה, שכאמור הוא אינדקס יוניקי שמסומן ב IGNORE_DUP_KEY = ON, כך שההתנהגות במקרה של כפילויות היא אזהרה בלבד, בואו נראה באמת מה קורה.

נתבונן רגע בשאילתה הבאה:

INSERT INTO FatherSonInfo(DateCreated, FatherID, SonID)

VALUES

(GETDATE(), 1, 1),

(GETDATE(), 1,1),

(GETDATE(), 1,2)

אנחנו מכניסים פה באותו statement (שקל וחומר שזה אומר שזה באותה טרנזקציה) מס’ ערכים לטבלת FatherSonInfo – כאשר הראשון והשני כפולים.  התוצאה שנקבל היא ההודעה הבאה:

Duplicate key was ignored.

(2 row(s) affected)

כלומר, קיבלנו אזהרה – אבל השורות הוכנסו. בצורה הזאת, היינו יכולים להבטיח כבר בשלב ההכנסה שמצד אחד לא יהיו לנו שורות “מיותרות” בטבלה, מצד שני לא להעסיק את עצמנו עצמאית בשאילתה של IF NOT EXISTS ובכל זאת להבטיח ייחודיות של ערכים.

לעומת זאת, אם זה היה מתבצע מול טבלה שהאינדקס היוניקי מוגדר בה בתור IGNORE_DUP_KEY = OFF (או פשוט בלי אזכור של IGNORE_DUP_KEY, כי ה- default הוא OFF) אז היינו מקבלים שגיאה שנראית ככה:

Msg 2601, Level 14, State 1, Line 2

Cannot insert duplicate key row in object 'dbo.FatherSonInfo' with unique index 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

The statement has been terminated.

ולא הייתה מוכנסת אף שורה לטבלה.

אם הכפילות הייתה למשל בתאריך ובשעה, שהם לא חלק מה-key columns של ה- unique index שלנו – אז כמובן שבאף אחד מהמקרים זה לא היה מכשיל את הפעולה.

חשוב לציין גם שבעוד שהדוגמאות שהראיתי היו כשה- IGNORE_DUP_KEY מוגדר על nonclustered index, הוא יכול להיות מוגדר גם על unique clustered index באותה הצורה (וגם על ה- primary key).

מה עושים כאשר יש הרבה Key Columns?

אם יש לכם הרבה Key Columns שלפיהם אתם רוצים לעשות את היוניקיזציה – אתם עלולים להיתקל באחת משתי בעיות: (1) שמכיוון שיש הרבה עמודות, שלוקחות פוטנציאלית הרבה נפח, האינדקס יהיה גדול מבחינת הנפח שלו (וגם פעולות שונות עליו יהיו יותר איטיות, כי יערבו יותר IO), או ש- (2) תעברו את מגבלת 900 הבתים של ה- Key Column ואז בכלל לא תוכלו להגדיר את האינדקס כמו שאתם רוצים.

במקרה כזה, אפשר להוסיף עמודה, נקרא לה UniqueHash, שבה בזמן ההכנסה תחשבו MD5 hash (למשל) על ה- string שנוצר מ- concat של השדות שאתם רוצים שיהיו ה- key שלכם מופרדים באיזשהו seperator. למשל, נניח שהערכים שלי הם DataA, DataB, DataC,…DataZ אז נחשב hash על DataA~DataB~DataC ונשמור את ה- hash, כאשר את ה- unique index נגדיר על ה- hash בלבד.

היתרון של השיטה הזאת הוא שגודל האינדקס שלנו קטן, כי אם למשל הגודל המצטבר של כל העמודות הוא 300 בתים, אנחנו יכולים לחשב hash שייוצג בתור string בגודל של 32 תווים, כלומר 32 בתים, ולשמור אותו. ככה נחסוך גם בעלויות storage, וגם בעלויות למשל של אורך פעולות התחזוקה על האינדקס (למשל, כמה זמן לוקח REBUILD).

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

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

השפעה של IGNORE_DUP_KEY על עבודה מהקוד

אני רוצה להתייחס לכמה אספקטים חשובים שנוגעים לעבודה מקוד מול טבלאות שיש עליהם unique index שמוגדר עליו IGNORE_DUP_KEY = ON.

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

ADO.NET “קלאסי”

אם אתם משתמשים באובייקטים הבסייים של ADO.NET לטובת עבודה, כלומר עובדים עם SqlCommand / SqlConnection ישירות, אז אם היה מוגדר unique index עם IGNORE_DUP_KEY = OFF (הדיפולט) והייתם מכניסים ערכים כפולים, היה עף SqlException.
אם אתם מכניסים ערכים כפולים לאינדקס שמוגדר עם IGNORE_DUP_KEY = ON, תוכלו להירשם ל-event של SqlConnection שנקרא InfoMessage כדי לקבל את כל הטקסט של ה- messages, ובכלל זה את המידע על ה- duplicate key ignored.
דרך יותר אלגנטית תהיה לשלוף את הערך של @@ROWCOUNT, כלומר מספר השורות שהושפעו ע”י השאילתות הכנסה שלכם. אם הוא קטן ממספר השורות שהכנסתם בטבלה שיש לה אינדקס שמוגדר עם IGNORE_DUP_KEY=ON, הגיוני שלשם “נעלמו” השורות החסרות.

BULK INSERT

אם אתם משתמשים ב- SqlBulkCopy לטובת ההכנסות לטבלה שמוגדרת עם IGNORE_DUP_KEY=ON – הכל יהיה שקוף לכם. פשוט תזרקו את המידע, וייכנס רק מה שצריך להיכנס.

Entity Framework

כאשר אתם מבצעים הכנסת שורות באמצעות Entity Framework(ועדכון, כמובן) אז כשאתם עושים SaveChanges, מאחורי הקלעים Entity Framework בודק שהפעולה של ההוספה/עדכון אכן התבצעה. אם היא לא, נזרק exception מ- SaveChanges ששמו DbUpdateException. כלומר, כשננסה להריץ את הקוד הבא:

using (var data = new check1Entities())

{

    data.FatherSonInfoes.Add(new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now});

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.SaveChanges();

}

נקבל:

ה- Inner Exception פה הוא מסוג OptimisticConcurrencyException.

ננסה להבין קצת יותר מה קרה פה, ע”י זה שנסתכל נראית השאילתה שהוא מריץ מאחורי הקלעים .

חשוב לזכור שכשאנחנו עושים Save Changes, מאחורי הקלעים entity framework לוקח את רשימת השינויים שהוא מכיר (כברירת מחדל הוא עושה change tracking אוטומטי, אם מבטלים את זה [למשל כדי לשפר ביצועים של קריאה למתודה Add], חשוב לסמן את ה- Entity ב- state המתאים [Added/Modified/Deleted…]).
בהסתמך על רשימת השינויים, Entity Framework מבין איזה שאילתות צריך להריץ וגם באיזה סדר. למשל, אם יש לנו אובייקט A ש-“מכיל” הרבה אובייקטים מסוג B (כלומר, ל-B שמור המפתח של A) – אז קודם כל נדרשת הכנסה של A, קבלת ה-ID (המפתח) ואז ההכנסה של כל האובייקטים מסוג B ששוייכו ל-A, תוך שימוש במפתח כדי לעשות את הקישור ברמת ה-DB.

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

exec sp_executesql N'INSERT [dbo].[FatherSonInfo]([DateCreated], [FatherID], [SonID])

VALUES (@0, @1, @2)

SELECT [ID]

FROM [dbo].[FatherSonInfo]

WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int',@0='2016-10-26 21:37:55.0438727',@1=1,@2=2

נשים לב שהוא עושה INSERT, ואז שולף את ה-ID שהוקצה לישות (כי זה ה- key שמתמלא ע”י ה-DB, והוא רוצה למלא את ה- entity בצד של ה- client), אבל עושה את זה רק בתנאי שה- @@ROWCOUNT (כמות השורות שהושפעו) גדול מ-0.

המטרה של Entity Framework בבדיקות הללו ב-WHERE, היא ששיטת העבודה של Entity Framework עצמו מבוססת Optimistic Concurrecy (וכמובן, גם השאילתה יכולה לרוץ ב- Isolation Level מבוסס optimistic concurrency ברמת הדטאבייס). ולמעשה, באמצעות ה- WHERE הזה הוא בודק שההנחות שלו התקיימו בפועל. ששורה שהוא חשב שהתווספה אכן התווספה, או ששורה שהוא רוצה לעדכן באמת נראית טרם תחית העדכון כמו שהוא חושב שהיא נראית (כלומר, שהיא לא השתנתה במקביל “מתחת לרגליים”).

במקרה שיש כישלון, ולמעשה לא מתאמת שום דבר על ה-WHERE כמו במקרה שלנו, הוא מניח שהאופטימיות נכשלה – ושזה מעיד על בעייה. ואז נזרק DbUpdateException שעוטף InnerException מסוג OptimisticConcurrencyException.

עם זאת, במקרה שלנו, אנחנו מראש תכננו שכך זה יהיה. יש שורות שלא מתווספות, וזה מבחינתנו לא תקלה. כלומר, עף פה exception בלי שבאמת היה מצב חריג או מצב שלא רצינו.

אנחנו יכולים לכתוב קוד בסגנון הזה:

using (var data = new check1Entities())

{

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 91, SonID = 8, DateCreated = DateTime.Now });

    try

    {

        data.SaveChanges();

    }

    catch (DbUpdateException ex)

    {

        var optimisticConcurrencyException = ex.InnerException as OptimisticConcurrencyException;

        if (optimisticConcurrencyException != null)

        {

            if (optimisticConcurrencyException.StateEntries.Any(k => k.State != EntityState.Added))

            {

                throw;

            }

        }

    }

}

שבו אנחנו זורקים את ה- exception מחדש רק אם הוא נוגע ל-entity שהוא לא Added (ואז זה יכול להיות רלוונטי), אבל מכיוון ש- SaveChanges מבצע את הפעולות אחת-אחת, אם הוא נכשל בהכנסה של השורה הראשונה הוא למעשה לא יכניס את השורה השנייה והשלישית.

אפשר לעקוף את זה בכל מיני דרכים, למשל להפריד לחלקים ולהכניס כל פעם entity בודד ולעשות SaveChanges. או לחלופין, במקרה של exception, לעשות לולאה של retries ולהוריד כל פעם את ה- entity הבעייתי. אבל, כאמור, כל אלה הם מעקפים – כי אין באמת פיתרון אלגנטי (לפחות לא כזה שאני מכיר).

הבעייה המהותית פה היא שגם אם הייתה דרך לבטל את המנגנון של הבדיקה של העדכונים – לא היה טוב להחיל את ההתנהגות הזאת באופן גורף, כי יש סיכוי שמתישהו (אפילו בלי שנשים לב) ה- SaveChanges שלנו יגלם בתוכו שינויים נוספים, מעבר ל- INSERT-ים האלה שאנחנו יודעים שגורמים ל- exception הנ”ל, שעבורם השגיאה כן תהיה רלוונטית.

מעבר לזה, ה- exception הזה הוא לא הבעייה היחידה שלנו. כאשר אנחנו מיישמים את הטריק הזה ברמת ה-DB, אנחנו צריכים להיות מודעים לו ברמת הקוד כאשר אנחנו עובדים עם Entity Framework, כי חלק מה-“קסמים” שהוא עושה עבורנו עלולים להיפגע. למשל, אם הישות שאנחנו מוסיפים לטבלה מהסוג הזה מכילה relationship מול טבלאות אחרות שבה לידי ביטוי בתור object graph, גם אם “נבלע” את הדילוג על השורה הספיציפית, נשאלת השאלה מה אנחנו עושים עם כל האובייקטים שהוא מפנה אליהם ולהיפך? במקרה כזה, לא ניתן יהיה לייצג את ה- object graph של ה- entities בתור קשרים בין שורות שונות בטבלאות שלנו – כי למשל אחת מהשורות לא הוכנסה.

למעשה, ברגע שאנחנו מגדירים IGNORE_DUP_KEY = ON, זה מכריח אותנו להתמודד עם מצב שבו אנחנו “מדלגים” על הכנסת שורה מסויימת. וזה אומר שאנחנו לא יכולים להשתמש בחלק מהפיצ’רים של Entity Framework אם entities שייכנסו לטבלה שבה קיים אינדקס כזה.

מה שמביא אותנו להמלצה שלי – אם אתם מיישמים את הטריק הזה עם Entity Framework, תדאגו להבדיל בקוד שלכם את ההכנסה לטבלה המדוברת ביחס לטבלאות אחרות.

עבור פריטים בודדים

אם אתם בד”כ מכניסים פריטים בודדים בכל פעם, אתם יכולים להפריד את זה ליחידת קוד (מתודה/מחלקה/whatever) שאחראית על ההוספה, בצורה שיהיה ברור למי שמשתמש בה שלא מדובר בהוספה סטנדרטית של Entity Framework, כי מכיוון שאנחנו מראש מודעים למצב שיש סיכוי שההכנסה שלנו לא תתבצע ואנחנו רוצים שזה יקרה, זה מגביל אותנו בפיצ’רים מסויימים (כמו relationship שמתמפה ל- object graph) שלא נרצה לעשות בהם שימוש.

במקרה כזה מה שנעשה זה נוודא שאת ההכנסה אנחנו עושים מ- instance נפרד של ה-context שלנו, ואז אנחנו יודעים שרק הפריט המסויים הזה כלול למעשה במשימה שמגולמת ב- SaveChanges, ולכן אם עף exception של OptimisticConcurrencyException, נוכל להתעלם ממנו בבטחה.

static void Main(string[] args)

{

    AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    AddSingleItem(new FatherSonInfo() {FatherID = 91, SonID = 81, DateCreated = DateTime.Now});

}

static void AddSingleItem(FatherSonInfo item)

{

    using (var data = new check1Entities())

    {

        data.FatherSonInfoes.Add(item);

        try

        {

            data.SaveChanges();

        }

        catch (DbUpdateException ex) when (ex.InnerException is OptimisticConcurrencyException)

        {

            //ignore.

        }

    }

}

אם אנחנו מכניסים מספר פריטים

במקרה כזה, מומלץ שכבר נעשה שימוש בפיצ’ר ה- Bulk Insert ואז אפשר להשתמש ב- EntityFramework.BulkInsert לטובת ההכנסה, כמו בקוד הבא (אפשר לקרוא עוד על BULK INSERT פה):

using (var data = new check1Entities())

{

    List<FatherSonInfo> items = new List<FatherSonInfo>()

    {

        new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

        new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

        new FatherSonInfo() {FatherID = 91, SonID = 8, DateCreated = DateTime.Now}

    };

    data.BulkInsert(items);

}

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

בהצלחה!

הקדמה

SQL Server, כמו כל דטאבייס רלציוני טוב, עונה על סט של עקרונות שנקראים ACID: ר”ת של Atomicity, Consistency, Isolation, Durability שמטרתם להבטיח את שלמות הנתונים במסד הנתונים. ממש ממש בגדול – העקרונות הללו אומרים שכשאתם מכניסים מידע לדטאבייס הוא נשאר שם, כשאתם שולפים נתונים אתם מקבלים נתונים אמיתיים ונכונים [בלי להיכנס להגדרה של “אמיתיים ונכונים” עכשיו]. בפוסט הזה אני רוצה להתמקד רק באחת מהמילים הללו: Isolation. מדובר למעשה בהגדרה (לכאורה) פשוטה: ההבטחה שגם אם שתי טרנזקציות רצות במקביל, תוצאת הביצוע שלהן בפועל תהיה כאילו הן רצו אחת אחרי השנייה.
כמובן, שבפועל אנחנו כן נרצה שדברים יעבדו במקביל. ולכן, נרצה להגדיר באמת מה מובטח במקרה ששתי טרנזקציות רצות במקביל ומתעסקות מול אותם הנתונים, ומה לא מובטח.

בפועל, קיימת יותר מהגדרה אחת כזאת, ואפשר לבחור בין התנהגויות שונות שמבטיחות דברים שונים ויש להן עלויות שונות. בפוסט הזה אני אפרוט מה ה- Isolation Levels הזמינים ב- SQL Server (למרות שבפועל המונחים די דומים במרבית הדטאבייסים הרלציוניים) ומה המשמעות של כל אחד.

למה אמור להיות אכפת לך מזה כמפתח?

נניח שאתה מפתח אפליקציה שהיא multi-threaded. די ברור לך למה אכפת לך איזה פעולה היא thread-safe ואיזה לא. מה אתה מבצע בצורה שמבטיחה thread safety (למשל עם Interlocked, או עם lock וכו’) ומה לא.
מאותה הסיבה צריך להיות אכפת לך מה ההתנהגות ברמת ה- DB. ההתנהגות במקרה של עבודה במקביל מול ה-DB מגדירה למעשה את התנהגות האפליקציה שלך.

כמה מונחים חשובים

לפני שנצלול לסוגי ה- Isolation Levels השונים, בואו נסביר רגע מס’ מושגים ובעיות שיעזרו לנו להבדיל בין סוגי ה- Isolation Levels שזמינים לנו.

• העדכון האבוד: נניח שלמה יש 250 ש”ח. בנוסף, נניח שיש לנו טרנזקציה A וטרנזקציה B. ושתיהן מתעסקות עם החשבון של משה כאשר טרנזקציה A קוראת כמה כסף יש בחשבון של משה ומוסיפה לו 100 ש”ח, וטרזנקציה B קוראת כמה כסף יש בחשבון של משה ומוריד ממנו 50 ש”ח. כמה כסף יהיה למשה בסוף? ככל הנראה, היינו רוצה שיהיה לו 300 ש”ח. אבל, אם אנחנו מסתכלים על ריצה מקבילית, ללא שימוש באיזשהם כלים שדואגים ליצירת אפקט שמדמה סדר הגיוני של ההרצה – היינו יכולים לקבל גם 200 ש”ח וגם 350 ש”ח כתשובות אפשריות לאחר ריצת שתי הטרנזקציות, כי כל אחת מהן קראה איזשהו ערך ועדכנה.
  ברור לנו שהתמודדות עם מצב כזה היא הבסיס שלנו בדרישה של Isolation. אנחנו מוכנים לפעילות מקבילית, כל עוד התוצאה הסופית נכונה.
• Dirty Reads: המונח הזה מדבר על קריאות של מידע ששונה ע”י טרנזקציה כלשהי שטרם הסתיימה. כלומר, אנחנו קוראים מידע שלא מובטח שאי פעם היה נכון. לא מדובר על מידע שאולי לא יהיה נכון עוד שנייה (ואז אנחנו יכולים להגיד שהוא נכון לרגע הקריאה), אלא מידע שייתכן שלעולם לא היה אמור להיקרא כי הוא שונה ע”י טרנזקציה שעדיין בתהליך ואז ייתכן שיש לה עוד שינויים שהיא רוצה לעשות על המידע, או שהיא סיימה לעשות את השינויים עם המידע שלנו, אבל תיכשל בהמשך הפעולה שלה, תעשה rollback ואז המידע שלנו מעולם לא היה נכון. האם אני רוצה לאפשר לקרוא מידע כזה?
• Phantom Reads: דמיינו שיש לנו טרנזקציה שמריצה את אותה שליפה בהתחלה ובסוף, כאשר השליפות הן על טווח של שורות. למשל, אנחנו מביאים את כל העסקאות שבוצעו מישראל בתחילת הטרנזקציה, ואז בסוף הטרנזקציה שוב עושים את אותה השליפה. נניח גם שבמקביל רצה עוד טרנזקציה שהוסיפה עוד עסקה מישראל. כלומר, השורות שראינו בטרנזקציה A בתחילת הטרנזקציה ובסוף הטרנזקציה הן לא אותן שורות, כי התווספה שורה באמצע. האם מצב זה תקין מבחינתנו? אולי כן ואולי לא, ובכל אופן יש לזה השפעה על רמת הבידוד הנדרשת בין טרנזקציות.
• Repetable Reads: בדומה ל- Phantom Reads, אבל עכשיו אנחנו לא מסתכלים על טווח שורות אלא על שורה ספיציפית. למשל, קראתי את הנתונים על לקוח מסויים בתחילת הטרנזקציה, ולאחר מכן בסוף הטרנזקציה אני שוב קורא את הנתונים של אותו הלקוח. מה ההתנהגות שאני רוצה שתהיה אם במקביל רצה טרנזקציה אחרת, שמעדכנת את הנתונים של אותו הלקוח? אני רוצה לראות את אותם הנתונים שראיתי בהתחלה? את הנתונים העדכניים (ואז ראיתי בתוך אותה טרזנקציה שתי גרסאות שונות של אותה השורה)? או אולי אני בכלל לא רוצה שמצב כזה יקרה?

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

ה- Isolation Levels שקיימים ב- SQL Server

ב- SQL Server קיימים מס’ Isolation Levels שאפשר לקבוע לטרנזקציה. הקביעה מתבצעת באמצעות הוספת השורה SET TRANSACTION ISOLATION LEVEL xxx בתחילת הטרנזקציה, כאשר במקום xxx מופיע שם ה- Isolation Level.

מבוססי נעילות

דרך אחת להבטיח “Isolation” בין שתי טרנזקציות, היא להשתמש בנעילות. כלומר, שהטרנזקציה שמתעסקת עם המידע מסמנת (לוגית) את המידע הזה כנעול על ידה (כאשר יש סוגי נעילות שונים) וטרנזקציה אחרת שמגיעה להתעסק עם אותו המידע מכבדת את הנעילות שלקחה הטרנזקציה הקודמת.  בצורה כזאת, אנחנו משיגים אפקט של סנכרון שמבוסס על זה שבמקרה של “התנגשות רצונות” בין טרנזקציות, אחת ממתינה לשנייה שתסיים, תשחרר את הנעילות שלה – ואז ממשיכה. מנגנון הנעילות, וסוגי הנעילות השונים שיש ב- SQL Server, מורכבים וארוכים מספיק כדי למלא פוסט שלם משלהם, אבל מכיוון שזאת לא המטרה של הפוסט הזה, נגיד שיש נעילות, יש סוגי נעילות ששונים בינהם ומאפשרות נעילה על רכיבים שונים ובגרנולריות שונה.

SERIALIZABLE

נניח שהיינו רוצים לפתור איזושהי בעית סנכרון בין שני threads בקוד שאנחנו כותבים. אחת הדרכים הבסיסית ביותר הייתה לשים את כל הבלוק של הקוד שעלול להתנגש תחת lock, כאשר הוא כתוב ברצף. ואז מובטח לנו שעבור רצף הפעולות שלנו אנחנו מחזיקים את הנעילה, והיא לא הולכת לשום מקום עד שאנחנו מסיימים. את התרחיש הזה אפשר להקביל ל Serializable Isolation Level.

הקונספט פשוט: אנחנו רוצים להבטיח שאנחנו רואים רק את המידע הכי עדכני, ושהוא נשאר אותו הדבר לכל אורך הריצה של הטרנזקציה שלנו – אלא אם כן אנחנו שינינו אותו. בנוסף, אנחנו לא מאפשרים לטרנזקציות אחרות לראות data
איך אנחנו משיגים את זה? מפזרים lock על כל שורה שקראנו (ובוודאי שעל כל שורה שעדכנו). לא רק זה, אלא מפזרים lock על כל הטווח שבין שתי שורות שקראנו. כלומר, אם קראנו שורה שה- ID שלה הוא 10 ושורה שה- ID שלה הוא 100, נשים lock על כל הטווח שבאמצע.
בצורה הזאת אנחנו הולכים למעשה בגישה הכי מחמירה. אנחנו כמובן משלמים על זה במקביליות, שאותה אנחנו מורידים ל-0. לא תהיה מקביליות על אותו המשאב בין טרנזקציות שמוגדרות כ- Serializable.

REPEATABLE READ

הקונספט של REPETABLE READ הוא להיות כמו SERIALIZABLE, רק קצת פחות מחמיר. איך זה בא לידי ביטוי שזה “פחות מחמיר”? אמנם נועלים על כל שורה שקוראים, בצורה כזאת שאחרים לא יכולים לקרוא (ובטח שלא לעדכן) אותה. אבל, לא נועלים על הטווח בין שתי שורות שקוראים. כלומר, אם קוראים שורה שה- ID שלה הוא 5 ושורה שה- ID שלה הוא 100, תהיה נעילה על שתי השורות הללו שתמנע עדכון וקריאה שלהן מטרנזקציות אחרות, אבל על השורות 6-99 לא תהיה נעילה – בניגוד ל- SERIALIZABLE.

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

READ COMMITTED

בעוד שגם ב- SERIALIZABLE וגם ב- REPEATABLE READ מובטח לנו שאם אנחנו קוראים את אותה השורה אנחנו נקבל את אותו המידע, פה מובטח לנו משהו חלש משמעותית – שנקבל מידע שהוא committed. כלומר, אם קראנו את אותה שורה בתחלת ובסוף הטרנזקציה, יכול להיות שנקבל ערכים שונים אם טרנזקציה אחרת שרצה בתור READ COMMITTEDעדכנה את המידע בין לבין.  עם זאת, מובטח שבכל אופן לא נקבל Dirty Read.

כלומר, יכולה להיות טרנזקציה A שקוראת שורה מסויימת, ואז טרנזקציה B מתחילה ומעדכנת את השורה הזאת. נניח שבאותו רגע טרנזקציה A מנסה לקרוא שוב את השורה. היא תצטרך להמתין עד שטרנזקציה B תעשה COMMIT, אבל ברגע שטרנזקציה B תעשה COMMIT, היא תקרא שוב את השורה ותקבל את הערך העדכני.

READ COMMITTED הוא ה- isolation level הדיפולטי ב- SQL Server, כי הוא מצד אחד מבטיח נכונות של המידע במקרה של הרצות מקביליות, ומצד שני מאפשר רמה גבוהה יותר של מקביליות מ- REPEATABLE READ ובטח מ-SERIALIZABLE.

READ UNCOMMITEED

מדובר ב- Isolation Level המינימליסטי ביותר, שלא מבטיח כלום. הוא יכול לשמש רק לקריאות (לא ניתן לבצע פעולות CRUD – יצירה, עדכון או מחיקה) ומאפשר לקרוא מידע שטרנזקציה פעילה שינתה, אבל עדיין לא עשתה commit (כלומר, ייתכן שהוא מעולם לא היה נכון ולעולם לא יהיה נכון). במילים אחרות – מאפשר Dirty Reads. מדובר ב- Isolation Level שלא מתאים אם רוצים לקבל מידע נכון, אבל הוא כן מבטיח שנוכל לקבל מידע בלי קשר לטרנזקציות אחרות שרצות ומעדכנות מידע.  כלומר, הוא מאפשר לנו לקבל מידע כלשהו.

כדאי לציין שלא רק שלא מובטח שנקבל מידע שאי פעם היה נכון, אלא יש עוד כמה תופעות לוואי ל- Dirty Reads: אנחנו יכולים לקבל את אותה שורה מס’ פעמים, לקבל שורה שנמחקה, לקבל מידע “חצי נכון” – ובקיצור, לקבל מידע שאינו אמין בהגדרה.

מעקב ב- Production אחר המתנות לקבלת נעילות

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

ב- SQL Server Management Studio, ב- Object Explorer, קליק ימני על שרת אליו אתם מחוברים ופתיחת ה- Activity Monitor, תפתח מסך המאפשר הבנה של סטאטוס השרת ברגע זה (כמובן, בהנחה שלמשתמש שלכם יש הרשאות מתאימות). ה-section העליון, Processes, מאפשר לעקוב אחרי תהליכים שרצים כעת ב- SQL Server.

SQL Server כולל “מיני מערכת הפעלה” מובנית, פנימית שלו, שנקראת SOS (ר”ת של SQL Server OS). אחד הרכיבים הבסיסיים בה, הוא ה- Scheduler. כל Scheduler ממופה ל- thread של מערכת ההפעלה, ויש לו תור של משימות שהוא מריץ אותם. כמות ה- Schedulers שפועלת ברגע נתון חסומה מלמעלה ע”י כמות ה- logical cores של מערכת ההפעלה. אחת המטרות העיקריות בשיטת העבודה הזאת היא להקטין את כמות ה- context-switch שנוצרים ע”י ה- scheulder של windows, ולעשת ניהול עצמאי של המשימות.

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

כל פעם שמשימה כזאת מוותרת על המשך זמן הריצה שלה, היא מספרת גם למה היא ממתינה (IO, lock, latch וכו’), ואם היא נחסמת ע”י תהליך אחר, גם מה התהליך שחוסם אותה.

במסך ה- Activity Monitor ניתן לראות זאת בקלות:

בצילום מסך הזה ניתן לראות ש- Session ID מס’ 52 ממתין במשך 39 מילי-שניות בהמתנה מסוג LCK_M_S, כלומר היא ממתין לקבל shared lock. אנחנו רואים שמי שחוסם אותו הוא session מס’ 54, כאשר אם אנחנו מסתכלים על השורה של session מס’ 54 אנחנו רואים שהוא מריץ עכשיו UPDATE, והוא בעצמו ברגע זה ממתין ב-wait  מסוג LOGBUFFER. אם נעשה קליק ימני על השורה->Details נוכל לראות בדיוק מה מורץ שם.

בכל אופן, מה שאני עשיתי, זה לפתוח ב-session מס’ 54 טרנזקציה שבה אני מעדכן כמה שורות, ואני מנסה לשלוף עליהם במקביל ב- session מס’ 52, ולכן הוא נעול (כי הוא פועל ב-isolation level של READ COMMITTED) עד שהטרנזקציה שב- session 54 תעשה commit.

Isolation Levels שאינם מבוססי נעילה

אם נסתכל על ה- Isolation Levels שראינו בינתיים: SERIALIZABLE, REPEATABLE READ, READ COMMITTED ו- READ UNCOMMITTED הם כולם מבוססים על נעילות (ועל התעלמות מנעילות, במקרה של READ UNCOMMITTED).
עם זאת, נעילות זאת לא הדרך היחידה להבטיח isolation בין טרנזקציות שונות.

Optimistic Concurrency

לנעילות יש עלות. נניח שהייתה לנו איזושהי הבטחה כלשהי שהסיכוי ל- contention, כלומר להתנגשות בין טרנזקציות הוא אפסי ביותר. לא סתם “בטל בשישים” אלא בטל במיליון מול כמות הפעמים בהם אין contention. היינו יכולים במקרה כזה ללכת על גישה אחרת, שהיא קצת יותר “success oriented”. להגיד שאנחנו לא נועלים בכלל על קריאות, וכאשר אנחנו מעדכנים אנחנו בודקים האם המידע השתנה בין מתי שקראנו אותו למתי שעדכנו אותו. כלומר, האם המידע שאנחנו מעדכנים בפועל, הוא באמת המידע שאנחנו מצפים לעדכן (והוא לא השתנה מתחת לרגליים).

ב- SQL Server נעשה שימוש ב- Optimistic Concurrency  משולב במנגנון של versioning גם בעת עבודה עם SNAPSHOT ISOLATION LEVEL ו- READ COMMITTED SNAPSHOT ISOLATION LEVEL (שנדבר עליהם ממש עוד מעט) וגם במנגנון ה- In-Memory OLTP (שלא יוזכר בפוסט זה).

שמירת גרסאות

אם תשימו לב ל- Isolation Levels מבוססי הנעילות, תשימו לב שיש שם בעייה מהותית: יש מצבים שבהם מישהו רוצה לקרוא מידע – רק לקרוא אותו – וננעל כי מישהו אחר עדכן אותו ועדיין לא עשה commit. ואז, כדי למנוע dirty reads, אם אנחנו סתם רוצים לקרוא מידע, אפילו אם נעשה את זה בטרנזקציה שהיא ב- READ COMMITTED (הדיפולט, ההכי פחות מחמיר שעדיין מבטיח נכונות של המידע) – נינעל ונצטרך להמתין שמישהו יסיים לכתוב. וכל מה שאנחנו רוצים לעשות זה רק לקרוא. מבאס.

דרך אחת “לפתור” את הבעייה היא לקרוא באמצעות READ UNCOMMITTED. אבל אז – לא מובטחת נכונות של המידע. דרך אחרת, היא להשתמש ב- isolation level מבוסס גרסאות (versioning). כלומר, כשאני אקרא את המידע, אני לא רוצה לקרוא את המידע שחלקו מעודכן וחלקו לא – אני רוצה לקרוא את הגרסא הנכונה האחרונה של המידע. אני לא רוצה לראות “שברי מידע” מטרנזקציה שפועלת ברגע זה, אבל אני לא רוצה להמתין שהיא תסתיים (ובטח לא רוצה לעכב טרנזקציה אחרת מלהתחיל).
אני רוצה לקרוא את הגרסא הנכונה האחרונה של המידע, ואם הוא משתנה באמצע, להמשיך ולהבטיח שכל הקריאות שאני עושה יהיו שייכות לאותה גרסא שראיתי בהתחלה.

למעשה, אנחנו רוצים “הפרדת רשויות” בין מי שקורא למי שכותב. שמי שקורא לא יחסום את מי שכותב, ושמי שכותב לא יחסום את מי שקורא. lock-free, אבל שהמידע בכל זאת יהיה נכון.

SNAPSHOT ISOLATION

ראשית, נציין שכדי לעשות שימוש ב- SNAPSHOT ISOLATION יש לאפשר את זה קודם ברמת ה-DB באמצעות הפקודה:

ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON

כאשר אנחנו עושים שימוש ב- SNAPSHOT ISOLATION מובטח לנו שנוכל לקרוא כל שורה שנרצה. מובטח לנו גם שכל שורה שנקרא שייכת לאותה “גרסא” של מידע, כי כל שינוי שקורה במקביל למעשה יוצר “version” של השורה שלנו ב- tempdb (ובכך בא לידי ביטוי אחד החסרונות של עבודה עם row-versioning, היא יוצרת יותר עומס על tempdb וצריך לוודא שהחומרה שלנו תעמוד בכך, בהתאם לעומס שיש לנו וכמות הגרסאות שיהיו). כלומר, אנחנו יכולים לקרוא כל מידע שאנחנו רוצים, ולא מונעים מאף עדכון לקרות בטרנזקציות מקבילות.

מה לגבי עדכון בטרנזקציה שלנו? אז אנחנו יכולים לעדכן כמה שאנחנו רוצים, ואולי זה יצליח ואולי לא. פה בא לידי ביטוי אלמנט ה- Optimistic Concurrency. במצבים מסויימים אנחנו עלולים להיתקל ב- update conflict. למשל, אם טרנזקציה אחרת עדכנה את הערכים “מתחת לרגליים” שלנו. יש כל מיני דרכים להתגבר על הבעייה, למשל באמצעות שימוש ב- hint בשם UPDLOCK כדי לסמן כבר בזמן הקריאה שאנחנו עומדים לעדכן ערכים מסויימים. עם זאת, בפועל, SNAPSHOT ISOLATION מתאים בעיקר לתרחיש שיש לנו הרבה reader-ים שאנחנו לא רוצים שייחסמו מ- writer-ים שפועלים ב- Isolation Level שונה. או לחלופין, יש לנו גם writer-ים שפועלים ב SNAPSHOT ISOLATION, אבל אנחנו יודעים שלא יהיו בינהם התנגשויות, לא בינם לבין עצמם ולא כלפי אחרים

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

READ COMMITTED SNAPSHOT ISOLATION

אחת האפשרויות שקיימות ב- SQL Server היא להחליף את ההתנהגות של READ COMMITTED בהתנהגות מבוססת versioning. ההחלפה הזאת מתבצעת באופן גורף, ברמת דטאבייס, ולאחר שעושים אותה READ COMMITTED יתפקד למעשה בתור “READ COMMITTED SNAPSHOT”.

אז מה בעצם אומר READ COMMITTED SNAPSHOT? הבסיס דומה ל- SNAPSHOT ISOLATION (במובן של הניהול גרסאות למידע) למעט ההבדלים הבאים:

• בעוד שב-SNAPSHOT ISOLATION ניהול הגרסא מתבצע ברמת הטרנזקציה ב- READ COMMITTED SNAPSHOT ISOLATION הוא מתבצע ברמת ה-statement. כלומר, אם אנחנו פותחים טרנזקציה, בודקים את מס’ השורות בטבלה A ולאחר מכן את מס’ השורות בטבלה B, כאשר במקביל, בין בדיקת השורות בשתי הטבלאות – טרנזקציה אחרת מכניסה שורות לטבלה B ועושה commit:
  • ב-SNAPSHOT ISOLATION: נקבל את מס’ השורות בטבלה B כמו שהוא היה בתחילת הטרנזקציה, ולכן כשבוצעה ההוספה מקודם לטבלה B, עדיין נשמר המידע על הגרסא שבה אין את השינוי הזה – וזה המידע שנקרא.
  • ב-READ COMMITTED SNAPSHOT ISOLATION: נקבל את מס’ השורות בטבלה B לאחר ההוספה, כי ניהול הגרסאות הוא ברמת ה- statement ומכיוון שזה statement חדש אנחנו נסתכל על הגרסא הכי עדכנית עכשיו.
• כפועל יוצא מה-bullet הקודם, העדכונים הם למעשה pessimistic concurrency, ולא optimistic concurrency. מכיוון שכל statement עומד בפני עצמו מבחינת versioning, כאשר מגיעים להריץ את ה-UPDATE, אז SQL Server לא מחוייב בגלל הגדרת ה- ISOLATION LEVEL לקרוא את הגרסא שמתאימה לתחילת הטרנזקציה. ולכן, ההתנהגות במקרה של עדכונים שונה. בפעולות עדכון לא מתבצעת גישה לגרסא ספיציפית, אלא מתבצעת גישה לנתון העדכני ביותר ונעשה שימוש בנעילות על מנת להבטיח עדכון.
  כלומר, בכל הקשור לעדכונים, READ COMMITTED SNAPSHOT ISOLATION מתבסס על נעילות ולא על optimistic concurrency, כך ש-writer-ים ימתינו אחד לשני מצד אחד, אך מצד שני – לא ניתקל בעדכונים שנכשלים בגלל שהערך שעודכן הוא לא הערך העדכני ביותר, כפי שעלול לקרות ב- SNAPSHOT ISOLATION.

למעשה, השילוב של שתי הנקודות הללו הופך את השימוש ב- READ COMMITTED SNAPSHOT ISOLATION למתאים לרוב סוגי האפליקציות, ללא יותר מדי התאמות ושינויים. בנוסף, באפליקציות שמערבות קריאות וכתיבות מקבילות, או שבמקביל אליהן נעשה מול ה-DB גם קריאות ועדכונים ע”י אפליקציות אחרות – היתרון בא אפילו יותר לידי ביטוי. מרוויחים שקריאות לא חוסמות כתיבות (ולהיפך), אולם גם מקבלים התנהגות עדכון “נורמלית” (במובן שהיא יותר כמו שאנחנו רגילים), שלא דורשת שינויים אפליקטיביים רבים.

למעשה, קיימים בשוק שרתי DB שונים שבהם ההתנהגות הדיפולטית היא למעשה ההתנהגות של READ COMMITTED SNAPSHOT. ב-SQL Server זה לא מופעל כברירת מחדל, ויש לאפשר את ההתנהגות באמצעות הפקודה:

ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

צריך כמובן להיות מודעים להשפעות הנלוות, בעיקר העומס הגבוהה יותר שיווצר על tempdb בשל ה- versioning. יש עוד מס’ נקודות שצריך להכיר, ועליהם מומלץ לקרוא ב-msdn. בכל אופן, למרבית האפליקציות כנראה יש יתרון רב במעבר לעבודה עם READ COMMITTED SNAPSHOT ISOLATION

שליטה על ה- Isolation Level מקוד

הגדרת הטרנזקציה ובחירת ה- IsolationLevel

עבודה עם טרנזקציות מקוד .NET מתבצעת באמצעות שימוש ב- System.Transactions.TransactionScope, או באמצעות יצירת SqlTransaction עם המתודה BeginTransaction של SqlConnection.

לשני האובייקטים ניתן להעביר (ב- constructor של TransactionScope או כפרמטר של BeginTransaction) את ה- Isolation Level המבוקש באמצעות ה- enum המתאים.

אם אתם עושים שימוש היום בטרנזקציות בקוד שלכם, ולא העברתם אף פרמטר במפורש, אז כנראה שכבר השתלם לכם לקרוא את הפוסט הזה. הדיפולט הוא IsolationLevel.Serializable, כלומר לעשות שימוש ב- Isolation Level המחמיר ביותר שיש.
ייתכן שזאת אופציה טובה לברירת-מחדל של ה- framework (פחות סיכוי לטעויות, תואם לסטנדרט של SQL), אבל מצד שני – מדובר בבחירה שהיא לא מתאימה למרבית האפליקציות. עכשיו, אחרי שהבנתם את המשמעות של כל ה- Isolation Levels השונים, כנראה שתוכלו לבחור משהו שמתאים באמת לאופי של הפעילות שאתם מבצעים מול ה-DB. ככל הנראה, במרבית המקרים רצוי לעבוד עם ReadCommitted (כאשר אם רוצים לעשות שימוש ב- row version, רצוי להפעיל ברמת ה-DB את הפיצ’ר של READ COMMITTED SNAPSHOT).

אם אתם לא מגדירים טרנזקציה בעצמכם, ולמשל עובדים עם Entity Framework, אז כשאתם קוראים ל- SaveChanges נעשה שימוש דווקא ב- default הגיוני יותר בעבודה מול SQL Server, שהוא READ COMMITTED.

השפעות הגדרת טרנזקציה על שאילתות עתידיות

כאשר אתם מייצרים connection חדש באמצעות SqlConnection (או באופן עקיף, באמצעות עבודה עם Entity Framework או ORM אחר), אתם למעשה לא בהכרח מייצרים connection חדש. כדי לשפר את ביצועי האפליקציות שעובדות מול מסדי נתונים, מופעל ע”י ADO.NET מאחורי הקלעים מנגנון של connection pool, ש-“ממחזר” למעשה connections בהם נעשה שימוש, כדי לא להקים ולסגור כל הזמן connection-ים מול ה-DB תוך תשלום ה- overhead של פעולות אלה.

כדי לייצר הפרדה לוגית בין instance-ים שונים של SqlConnection, או במילים אחרות כדי לעשות אשלייה של connection חדש, מורץ בין “מחזורים” שונים של ה- connnection פעולה שמטרתה “לאפס” את ה- connection, באמצעות קריאה ל- sp_reset_connection.

עד SQL Server 2014 לא התאפס ה- Isolation Level בעת “מיחזור” ה- connection. כלומר, אם בקוד הגדרנו טרנזקציה (ונניח לא העברנו Isolation Level, כך שהיא רצה כברירת מחדל בתור SERIALIZABLE), ולאחר מכן יצרנו SqlConnection אחר ואפילו לא הגדרנו בו טרנזקציה – עדיין הוא “יסחוב” את ה- Isolation Level שהגדרנו קודם. הרבה פעמים, זה ממש לא מה שנרצה. במיוחד אם לא היינו מודעים גם ל- default המחמיר.

החל מ- SQL Server 2014, בוצע שינוי כך ש- sp_reset_connection מאפס את ה- Isolation Level להיות READ COMMITTED, הדיפולט של SQL Server (שכמובן, יתפקד כ- READ COMMITTED SNAPSHOT אם הגדרנו זאת).

שילוב בין התנהגויות שונות באותה טרנזקציה

על אף שלטרנזקציה מוגדר ISOLATION LEVEL אחד, ניתן עדיין לשלב כדי לקבל התנהגויות של Isolation Level מסוג Y בתוך טרנזקציה שעובדת עם Isolation Level מסוג X.

דוגמא טובה לזה, היא שאם יש לנו טרנזקציה שרצה ב- READ COMMITTED ISOLATION, ואנחנו רוצים לקרוא מידע (שאילתת SELECT)  ולאפשר dirty-reads, אנחנו יכולים לעשות שימוש ב- hint שנקרא NOLOCK, כלומר לכתוב את השאילתה כך:

BEGIN TRANSACTION

 SELECT col1, col2

 FROM tbl1 WITH(NOLOCK)

 SELECT col1, col2

 FROM tbl2

COMMIT 

כך נשיג שהשליפה של tbl1 מתבצעת בתור READ UNCOMMITTED, בזכות ה- hint של ה- NOLOCK, ואילו השליפה מול tbl2 מתבצעת READ COMMITTED (ברירת המחדל, כי לא הגדרנו isolation level).

דוגמא אחרת, היא שאם הפעלנו את הפיצ’ר של READ COMMITTED SNAPSHOT ISOLATION, והיינו רוצים לעשות שליפה ספיציפית שמתנהגת בתור READ COMMITTED מבוסס הנעילות, היינו יכולים להשתמש ב- hint שנקרא READCOMMITTEDLOCK (צורת השימוש דומה לדוגמא מקודם, פשוט להחליף את NOLOCK) כדי לקבל התנהגות של READ COMMITTED מבוסס נעילות, על אף שלאחר הפעלת הפיצ’ר הזה ה-READ COMMITTED הופך להיות מבוסס versioning.

בצורה דומה יש hints נוספים – UPDLOCK, HOLDLOCK ועוד, שניתן לקרוא עליהם ב- msdn כאן. קחו בחשבון שיש לא מעט hints שם, חלקם לא קשורים ל- ISOLATION LEVEL, אבל בכל אופן כדאי לקרוא ולדעת מה האפשרויות השונות שיש.

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

לפני כמה ימים פרסמתי פוסט על משהו קטן שעבדתי עליו בין תרגיל אינפי לתרגיל לינארית, שכולל למעשה אוסף של functions ו- stored procedures המאפשרים עבודה מול Redis מ- SQL Server, כאשר מרבית הפרוייקט זה סה”כ תפירה בין קוד C# קיים לעבודה מול Redis ל- SQL Server – שמתבצעת באמצעות SQL CLR, פיצ’ר חביב (למרות שהיה יכול להיות טוב משמעותית) שמאפשר לשלב ולכתוב רכיבים שונים בדטאבייס (functions, stored procedures וכו’) ב- C#.

הדרישה שהייתה לי לצרכי פיתוח הייתה פשוטה – שבסוף כל build יהיה לי קובץ install.sql שכשאני מריץ אותו הוא מכיל את כל ה- CREATE statements של ה- CLR functions / procedures שלי, ו- uninstall.sql שכולל את ה- DROP statements הרלוונטיים. הדבר העיקרי ממנו רציתי להימנע זה התענוג (המפוקפק) בלהכין create statements לכל פונקציה בנפרד, ואז כל פעם שמשנים במהלך הפיתוח את הפרמטרים לעדכן גם בקוד C# וגם בסקריפט ה-SQL-י ולייצר למעשה שכפול מיותר של הגדרות.

הפיתרון הראשון שאליו הסתכלתי היה לממש הכל באמצעות SSDT. למי שלא מכיר, SSDT (SQL Server Data Tools), הוא פרוייקט של מיקרוסופט של תוסף ל- Visual Studio שמאפשר לייצג דטאבייס בתור פרוייקט ב-Visual Studioהכולל item-ים שונים: טבלאות, views, stored procedures, טריגרים, SQL CLR Functions – בקיצור, פחות או יותר כל מה שרוצים. SSDT תומך בתרחיש deployment באמצעות dacpac או באמצעות יצירת סקריפטי מיגרציה SQL-יים.
בעבר עשיתי מספר ניסיונות ב-SSDT לשימושים היותר קונבנציונליים שלו (ניהול סכימה של DB בתור פרוייקט) ופסלתי אותו כי הוא לא היה נוח (גם מבחינת תרחיש העבודה, וגם לשימוש באופן כללי). החלטתי שזה יכול להיות ניסיון טוב להשתמש בו.

צריך להבין שהתרחיש המקורי אליו מיועד SSDT שונה מהתרחיש שהיה לי במקרה הספיציפי הזה. התרחיש המקורי הוא כאמור שימוש לניהול סכימה של DB. בתרחיש המסויים שלי, אני רוצה לנהל פרוייקט שמייצג משהו שאנשים יכולים “להתקין” לתוך ה-DB שלהם. ובכל זאת, חשבתי שבזכות היכולת שלו לעקוב אחרי שינויי סכימאות, והעובדה שניתן להוסיף אליו CLR functions וכו’ הוא יתאים. די מהר התבדיתי – העבודה מולו מאד לא נוחה. כשאומרים לו לייצר סקריפטים (ולא לעבוד עם DAC), אז כל ייצור סקריפט לפרוייקט קטנטן לוקח מס’ דקות. אמנם לא רב, אבל הרבה יותר מהמס’ שניות שנראה לי סביר ונוח לצרכי עבודה שוטפת. בנוסף, התברר לי שהוא לא תומך בפונקציונאליות בסיסית מאד שרציתי – למשל קביעת default values שונים ל- CLR functions (ב-connect זה עדיין בקשה פתוחה).
מכיוון שהוא לא היה כיפי לשימוש, וגם לא נתן כל מה שרציתי – החלטתי שהוא לא מתאים לצורך הזה.

אז מכיוון שלמרות הכל לא רציתי לכתוב בעצמי באמצעות copy-paste את ה-SQL-ים של ההתקנה וההסרה, החלטתי לפתח משהו פשוט שייצר אותם עבורי עם Reflection. בסופו של דבר, יצרתי פרוייקט שמכיל אוסף של attributes שמתארים את כל ה- metadata הנוסף שצריך עבור ייצור הסקריפטים הללו, מעבר לזה שקיים כבר ב SqlFunctionAttribute וב- SqlProcedureAttribute.  כל מתודה שיצרתי שאמורה להתווסף כ- CLR function/procedure כוללת את ה- attributes הרלוונטיים.
בנוסף, יצרתי console application קטן שמייצר ע”ס ה-attributes האלה CREATE / DROP statements ומשלב אותם בקובץ טקסט שמכיל טמפלייט רלוונטי (שכולל אפשרות גם לעשות include לקבצי SQL אחרים). יש קובץ טמפלייט גם להתקנה וגם להסרה, שעל פיהם מג’ונרט הסקריפט התקנה והסרה הסופי, באמצעות post build events.

מכיוון שרוב הפרוייקט שלי מבוסס על CLR funcitons ו- CLR procedures, זה מה שהיה חשוב לי לפתור. את הקוד T-SQL שכן קיים, אני משלב בקובץ template באמצעות תמיכה ב- include (כלומר, אין משהו חכם שמתמודד עם stored procedures / functions שכתובים ב- T-SQL).  כמובן שפונקציונאליות אחרת שאני לא משתמש בה בכלל (כמו CLR Triggers) בכלל  לא כלולה בפרוייקט הזה.

אם אתם כותבים משהו שמבוסס על SQL CLR (ובכנות, נראה לי שזה משהו די נדיר), יכול להיות שזה יהיה רלוונטי עבורכם (למרות שאני מקווה ש- SSDT פשוט ישתפר, כי הוא הפיתרון הטבעי לזה). הקוד הרלוונטי זמין ב- GitHub.

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

למשל, אם אנחנו רוצים לשלוף מ- sys.tables ולהציג את התוצאות כ-XML בפורמט טבלאי, כל שצריך לעשות זה להוסיף FOR XML statement בסוף. ניתן בצורה הזאת גם להשיג שליטה מוגבלת על מבנה ה-XML שמתקבל. למשל, בדוגמא הבאה, אני מגדיר שיהיה root element בשם items ושכל שורה תהיה עטופה בתור item:

SELECT *

FROM sys.tables

FOR XML PATH('item'), ROOT('items')

ככה ייראה ה-XML שיתקבל:

אבל, מה קורה כשאנחנו רוצים להפוך את ה-XML הזה לתצורה טבלאית בחזרה? לצורך כך יש לנו את הפונקציה OPENXML. הפונקציה הזאת יודעת לקבל handle שמתקבל ע”י קריאה ל- sp_xml_preparedocument שעושים לפני כן, ויודעת להפוך את ה-XML הזה לתצורה של שורות וטבלאות.

מתי זה מסתבך? כשרוצים לעשות משהו גנרי שהופך XML מהסגנון הזה שראינו ל- rowset, בלי שאנחנו יודעים את שמות העמודות. למה זה מסתבך? כי OPENXML דורשת לקבל את שמות העמודות וה- data type שיהיה ב- rowset שיוחזר.

מה אפשר לעשות? לחלק את הבעייה לשתי שלבים: בשלב הראשון, לקבל את השמות של האלמנטים. בשלב השני, לקרוא ל- OPENXML ולהעביר את שמות האלמנטים שיקבלנו.

ה-stored procedure הבא עושה בדיוק את זה.. מייצר את רשימת העמודות מה-XML (הוא מצפה ל-XML במבנה של root element בשם items ובתוכו אלמנטים בשם item) ואז משתמש ב-OPENXML בשביל להפוך את ה-XML ל-rowset עם שמות העמודות שמועברות דינמית:

CREATE PROCEDURE [dbo].[ConvertXmlToRowset]

(

    @input xml

)

AS

BEGIN

    DECLARE @cols table (ColName nvarchar(500))

    INSERT INTO @cols(ColName)

    SELECT    

            keyValueSet.Name

    FROM   @input.nodes('/items/item') T(c)

    OUTER APPLY (

                    SELECT CAST(T.c.query('.') as xml) xmlData

                )  rowXml

    OUTER APPLY (

                    SELECT

                        C.Name

                    FROM rowXml.xmlData.nodes('/item/*') as T(C)

                    OUTER APPLY (

                                    SELECT

                                        T.C.value('local-name(.)', 'nvarchar(max)') as Name

                                    UNION ALL

                                    SELECT

                                        A.C.value('.', 'nvarchar(max)') as Value

                                    FROM T.C.nodes('@*') as A(C)

                                ) as C

                ) keyValueSet

    DECLARE @colsNames NVARCHAR(2000)

    SELECT @colsNames =COALESCE(@colsNames + ', ', '') + '[' +ColName + '] nvarchar(max)'

    FROM @cols

    GROUP BY ColName

    DECLARE @query nvarchar(max)=

    '

    DECLARE @doc nvarchar(max) = ''' + CAST(@input as nvarchar(max)) + '''

    DECLARE @idoc int 

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT    *

    FROM       OPENXML (@idoc, ''/items/item'',2)

            WITH (' + @colsNames + ')

    '

    EXECUTE(@query)

END

GO

ודוגמא לשימוש:

DECLARE @xml xml = (SELECT * FROM sys.tables FOR XML PATH('item'), ROOT('items'))

EXECUTE redisql.ConvertXmlToRowset @input = @xml

החסרון בשיטה הזאת, הוא שה-rowset שמתקבל בסוף לא זהה למקורי. ה- data type המקורי של העמודות לא נשמר, ובמימוש הזה גם הסדר לא נשמר. הפיתרון הנ”ל היה אחד הפתרונות ששקלתי כשמימשתי את שמירת ה- Rowsets ב- RediSql, ופסלתי אותו בסוף בגלל הסיבה הזאת – הוא לא מייצג נאמנה את המקור. עם זאת, במידה שזה פחות חשוב לכם ומבחינתכם לקבל את כל המידע בתור nvarchar(max) מתאים, עדיין יכול להיות מדובר בפיתרון שימושי.

בהצלחה.