MySQL Transactions - חלק א'

פוסטים בסדרה:
"תסביר לי" - גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א' - עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב' - json_each  ו Generated Columns

נושאים מתקדמים ב MySQL: חלק ג׳ - מנועי אחסון, ומבנה האינדקסים

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

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

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

איך משתמשים בטרנזקציה? בבסיס, באופן הבא:

START TRANSACTION; 

-- do something  

COMMIT; 
-- or: ROLLBACK; 

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

למה להשתמש בטרנזקציות?

כיום, טרנזקציות הוא דבר "לא-קולי" ("not cool").

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

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

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

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

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

ובכן: 

• טרנזקציות הן אויב ל Scalability (ברוב המקרים), וכאשר צריך הרבה Scalability - עלינו להימנע מהן.
• גם במערכות המתמודדות עם Scalability, ישנם flows ותסריטים שעובדים ב volume נמוך יותר - ויש להם בעיות שטרנזקציות יכולות לפתור.
• חטאנו (גם) בשנות האלפיים, ועשינו שימוש-יתר, ביכולות שונות של בסיסי-הנתונים הרלציוניים: כמו טרנזקציות, Foreign Keys, או Triggers. עדיין, בשימוש מידתי - אלו כלים שימושים שיכולים לפתור בעיות רבות.
• הסיפור של Overselling של כלים וטכניקות, הוא לא מקרה יחידני. הוא קורה שוב ושוב, ויקרה שוב ושוב. תתרגלו.
• טרנזקציות הן פעמים רבות, הכלי הנכון והטוב ביותר לפתור בעיות.
• אם אתם מסוגלים להתגבר על הקושי שלא המציאו את הטרנזקציות בשנת 2018 בגוגל, והן לא מככבות במצעד ה"טכנולוגיות היפות והנכונות של 2019 [א]" - אז יש לכם סיכוי טוב להעשיר את סט הכלים שלכם בצורה מועילה.

נחזור לשאלה המקורית, שמסתבר שהיא לא טריוויאלית: "מדוע / מתי להשתמש בטרנזקציות"?

הנה שימושים מרכזיים לדוגמה:

• אנו רוצים לעדכן שתיים (או יותר) טבלאות בבסיס הנתונים בצורה עקבית: שלא יוותר מצב שאחת מעודכנת, אבל של תקלה - השנייה לא עודכנה, ויש לנו אי-עקביות בנתונים / נתונים חסרים.
• אנו רוצים לעדכן ערך בטבלה, בצורה עקבית ותחת racing condition אפשרי: שני threads (או מנגנון מקבילי אחר) רוצים לבצע שינוי שתלוי במצב הקיים - אבל ללא הגנה התוצאה יכולה להיות שגויה.
• אנו רוצים להשתמש בבסיס-הנתונים כמגנון סנכרון פשוט בין כמה שרתים. 
• אין לנו מנגנון אחר, ובסיס-הנתונים הוא מספיק טוב למשימה.
• שיפור ביצועים - זה נשמע לא אינטואטיבי, אבל במקרים מסוימים טרנזקציות דווקא יכולות לעזור לשפר ביצועים.
• ביצוע שינוי בנתונים בצורה זהירה ומפוקחת - (בסביבת פרודקשיין, למשל). נבצע את השינוי, נבחן את השלכותיו, ורק אז נעשה commit.

לכאורה אפשר לחשוב (בתמימות) שלכל המקרים יש פתרון זהה. שבשימוש בפקודות START TRANSACTION ... COMMIT - נפתרו בעיותינו. מובטח לנו שמה שציפינו, אכן יקרה, וללא תופעות לוואי מיותרות - כמובן.

הבעיה היא שבכדי להבטיח ACID, בסיס הנתונים משתמש בנעילות שפוגעות מאוד ביכולת לרוץ במקביל על אותם הנתונים.

ישנו Trade-off מאוד בסיסי פה:
יותר בטיחות כנגד יותר מקביליות / ביצועים.

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

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

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

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

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

אנחנו נראה שלשימוש בטנרזקציות יש השפעה שלילית אפשרית על הביצועים - גם כאשר שום טרנזקציה לא ״תקועה״ ומחכה למנעול.

זו הזדמנות טובה להזכיר את Amdahl's law המראה את הקשר בין החלק בפעולה שאינו מקבילי - לחסמים על מקביליות, לא משנה בכמה threads נשמש....
כדי להשלים את התמונה, שווה להכיר גם את ה Universal Scalability Law (בקיצור USL) שמפרמל מהי מקביליות, וממנו ניתן לראות שניסיון לדחוף יותר עבודה מקיבולת מסויימת - דווקא תאט את המערכת עוד יותר.

מודל המקביליות של InnoDB

לב מודל המקביליות של InnoDB, בדומה לבסיסי-נתונים רבים אחרים, מבוסס על שני כלים עיקריים:

• נעילה פסימיסטית - לקיחת מנעול על משאב (טבלה, רשומה, וכו') בצורה שתגביל פעולות מקביליות אחרות - אך תמנע בעיות של עקביות-נתונים.
• מחלקים את הנעילות ל-2 סוגים:
• נעילה משותפת (Shared lock) עבור פעולות קריאה. אני קורא ערך ואוסר על שינוי הערך - אבל לא יפריע לי שטרנזקציות נוספות יקראו את הערך גם.
• נעילה בלעדית (Exclusion lock) - לצורך שינוי הערך. אני תופס את המנעול, ולא אתן לשום טרנזקציה אחרת אפילו לקרוא את הערך (כי הוא עומד להשתנות)
• נעילה אופטימיסטית - מאפשרת לתת לפעולות לרוץ במקביל, לגלות "התנגשויות" ואז להתמודד איתן.
• לפעמים נרצה להכשיל את אחת הטרנזקציות (או יותר - אם יש כמה). לפעמים נסכים לקבל חוסר אחידות בקוד.
• למרות שיש עבודה משמעותית נוספת בגילוי וטיפול ב"התנגשויות", אנו מאפשרים מקביליות בין הפעולות - שזה יתרון שלא יסולא בפז (הציצו שוב בתרשים שלמעלה - כמה צמצום החלקים ה"בלעדיים" הוא חשוב).

מבחינת אלגוריתמים MySQL, בדומה לרוב בסיסי-הנתונים הרלציוניים משתמשים בשני אלגוריתמים עיקריים:

• 2PL (קיצור של Two-Phase Locking) עבור נעילה פסימיסטית. הרעיון הוא שנחלק את הפעולה לשני שלבים:
• שלב ראשון - בו ניתן רק לתפוס מנעולים.
• שלב שני - בו ניתן רק לשחרר מנעולים.
• לרוב נרצה לתפוס מנעולים ע"פ סדר מסוים ("קודם אובייקט מסוג X ורק אז אובייקט מסוג Y") - על מנת להימנע מ deadlocks.
  במקרים אחרים, אנו יכולים להחליט לתפוס בסדר לא-קבוע על מנת לצמצם זמני-נעילה ולהגביר מקביליות, במחיר שטרנזקציות יבוטלו לנו. המשמעות: נצטרך לפעמים לנסות להריץ אותן כמה ניסיונות - עד שנצליח לתפוס את המנעולים הרצויים.
• MVCC (קיצור של Multi-version concurrency control) הרעיון שבו אני "מעתיק" הנתונים שטרנקזציה צריכה הצידה, ואז היא חיה ב״סביבה וירטואלית משלה״, ללא התעסקות ב racing conditions או צורך בנעילות.
• במימוש של InnoDB, לא באמת מעתיקים נתונים, אלא משתמשים בעמודות טכנית של המערכת לכל טבלה, המנהלת איזה עותק של הנתונים שייך לכל טרנזציה, ואלו ערכים נמחקו.
• כמובן שיש מחיר שנוסף בניהול "העותק הוירטואלי". למשל: כאשר טרנזקציה מבקשת ערך שבטיפול של טרנזקציה אחרת - על בסיס הנתונים לבצע הדמיה של rollback של הטרנזקציה האחרת על מנת לדעת אלו ערכים צריכים להיות לטרנזקציה הנוכחית.
• למרות המחיר הנוסף בפעולות הללו - הוא אינו דורש בלעדיות ולכן scales well.

בעיות חוסר-עקביות

כל טרנזקציה ב MySQL היא, כברירת מחדל, ברמת הפרדה (Isolation Level) שנקראת Repeatable Read.
ישנן 4 רמות הפרדה שהוגדרו ע"י התקן ANSI-SQL 92 ומקובלות בכל בסיסי-הנתונים הרלציוניים המוכרים.

למרות שרמות ההפרדה והתופעות האפשריות[ב] הוגדרו בתקן ה ANSI-SQL - ההתנהגויות בין בסיסי-הנתונים עדיין מעט שונות.
למשל: SQL Server לא מגן בפני Phantom Reads ברמת הפרדה של Repeatable Read, אבל כן מגן בפני Write Skew או Lost Update. אורקל משתמש רק ב MVCC ולא ב 2PL, מה שתורם למקביליות - אבל גם אומר שרמת הפרדה של Serializable לא מגנה בפני Write Skew....

בקיצור: Tradeoffs. Tradeoffs everywhere.




רשימת התופעות הבעייתיות האפשריות:

• Dirty Write - כאשר שתי טרנזקציות יכולות לשנות את אותו השדה, כך שטרנזקציה אחת משנה את הערך לשנייה. 
• בגלל השימוש ב MVCC (או גישה יותר מחמירה: 2PL) - זה לא יקרה אף פעם בטרנזקציה של MySQL.
• Dirty Read - הטרנזקציה יכולה לקרוא שינוי של טרנזקציה אחרת שהוא עדיין לא committed. הערך הזה יכול להתבטל (rollback) מאוחר יותר - בעוד הטרנזקציה שלנו משתמשת בו. התוצאה עשויה להיות שנשתמש בערך שאין לו ייצוג תקין בשאר המערכת (למשל: id לרשומה שלא קיימת). לא משהו...
• Not Repeatable Read (בקיצור: NRR) - הטרנזקציה קוראת ערך משדה x בנקודת זמן t1, וכאשר היא קוראת שוב את השדה הזה בנקודת זמן מאוחרת יותר, t2 - ערך השדה הוא שונה. כלומר: טרנזקציה אחרת עשתה commit (אולי autocommit) - והערך שאנו רואים איננו עקבי. ההתנהגות ה NRR שוברת את תמונת "העולם המבודד" שרצינו ליצור לטרנזקציה שלנו - ובמקרים רבים היא יכולה להיות בעייתית.
• Phantom Read - הטרנזקציה ביצעה קריאה של תנאי מסוים (נניח: year between 2016 and 2018) וקיבלה סדרה של רשומות, אך בינתיים טרנזקציה אחרת עשתה commit והוסיפה רשומות חדשות לטווח. כלומר: אם ניגש שוב לטווח - התוצאה תהיה שונה.
• זו וריאציה מורכבת יותר של NRR. בעוד NRR ניתן לפתור בעזרת נעילה המאפשרת קריאות-בלבד מרשומה שאליה ניגשה הטרנזקציה, נעילה של טווח שנובע מפרדיקט היא דבר מסובך למדי - גם עבור בסיס נתונים משוכלל.
• Read Skew - וריאציה נוספת של NRR: ישנן 2 טבלאות עם קשר לוגי ביניהן. שדה x באחת משפיע או מושפע על שדה y בטבלה השנייה. הטרנזקציה קוראת ערך x מטבלה אחת, אך בינתיים טרנזקציה אחרת משנה את x וגם את y (בצורה עקבית). בסיס הנתונים לא יודע בוודאות על הקשר, וכאשר אנו קוראים את y - עדיין עלולים לקבל את הערך הישן - לפני העדכון של הטרנזקציה השנייה. התסריט הזה מעט מבלבל - הנה דוגמה מפורטת.
• Write Skew - וריאציה דומה, בה שתי טרנזקציות קוראות את שני הערכים x ו y, ואז אחת מעדכנת את x - בעוד השנייה מעדכנת את y. התוצאה - עקביות הנתונים נשברה. הלינק מלמעלה מספק גם דוגמה מפורטת ל Write skew.
• Lost update - שתי טרנזקציות קוראות ערך ומעדכנות אותו. אחד העדכונים יאבד - מבלי שנדע שכך אכן קרה. במקרה של counter, למשל - הנזק מוחשי מאוד. גם בשדות המכילים ריבוי פריטים (כמו json) - הנזק הוא ברור. ישנם גם מקרים נוספים בהם התוצאה היא בעייתית.

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

חזרה לתסריטים מתחילת הפוסט

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

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

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


אם מעוניינים ב Atomicity, היכולת של טרנזקציה להתבטל מבלי להשאיר "שאריות" אחריה בפעולת Rollback, אזי רמת ההפרדה של Read Committed עשויה להספיק - ולאפשר מקביליות רבה.


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


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


בשתי הדוגמאות האחרונות, אם אנו יודעים בוודאות שהגישה הקריטית היא רק לרשומה בודדת (למשל: קריאת ערך - ואז עדכון) - אזי Read committed היא רמה מספיק טובה. כל הרמות הגבוהות יותר מגינות מפני תסריטים של ריבוי רשומות / טבלאות.
עם אופטימיזציות כאלו כדאי להיות זהירים: משהו עשוי להשתנות בתוכן הטרנזצקיה, מבלי שמבצע השינוי יזכור / יבין שעליו להעלות את רמת ההפרדה.
מצד שני, יש כמה יתרונות משמעותיים לשימוש ב Read Committed (בקיצור RC) על פני Repeatable Read (בקיצור RR) מבחינת ביצועים:

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

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


עוד שני תסריטים שציינתי ולא כיסינו הם אלו:

שיפור ביצועים - זה מקרה ייחודי - אך שימושי.
InnoDB מחזיק binary log, על כל שינוי שבוצע בינתיים עבור התאוששות מהתרסקות ועבור replications. כדי לשמור על הלוג ככשיר להתאוששות, עליו לבצע flush ללוג (כלומר: לדיסק) על כל טרנזקציה שמתבצעת.
אם אנו מבצעים עשרות או מאות שינויים (למשל: הכנסה של רשומות חדשות), בשל ה autocommit - אנו נחייב את המנוע לבצע עשרות או מאות flushes לדיסק (פעולה יקרה).
שימוש בטרנזקציה (הכי פשוטה) - יאפשר לבסיס הנתונים לבצע flush יחיד.

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

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

בד"כ נשתמש ברמת Repeatable Read לכאלו שינויים. כבני-אדם, אנו נחזיק את הטרנזקציה פתוחה לנצח (דקות? יותר?) - ולכן כדאי להימנע מ Serialization - במידת האפשר.

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

כיצד עושים "התערבות ידנית"?
אלו מנגנוני-נעילה נוספים, הרצים מ"אחורי-הקלעים", קיימים ב InnoDB? (למשל: הזכרנו את ה gap lock)
כיצד מאתרים בעיות של נעילות בעייתיות / עודפות?

על כל זה - נדבר בפוסט ההמשך.



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

----

לינקים רלוונטיים

התיעוד הרשמי של רמות ההפרדה השונות ב MySQL.
הבדלי-מימוש וביצועים בין Read Committed ל Repeatable Read.

------

[א] החל מחודש ספטמבר, נהוג כבר להתמקד רק בטכנולוגיות השנה הבאה. #שנה_נוכחית_זה_פח

[ב] התקן המקורי של ANSI-SQL 92 זיהה רק 3 תופעות אפשריות של חוסר עקביות בנתונים, אך מאמר שהגיע שלוש שנים אחריו, הציף עוד 4 מקרים בעייתיים נוספים.

[ג] בעצם, בסיסי-נתונים לא רלציוניים היו תמיד: היררכיים (קדמו, והתחרו עם בסיסי-נתונים רלציוניים - תקופה מסוימת), Time series, מבוססי-אובייקטים, מבוססי-XML, גיאוגרפיים, ועוד.
הם פשוט לרוב נשארו נישה, ולא הצליחו לייצר רעש, כמו הגל האחרון.