ACID Transactions and Stored Procedures
מהי טרנזקציה?
במערכת מידע, תנועה (באנגלית: transaction) היא פעולה לוגית לשינוי נתונים המורכבת מסדרת פעולות בדידות. או במילים פשוטות יותר סדרת פעולות SQL המתבצעות כיחידה אחת.
(כל הדוגמאות המוצגות במאמר יכתבו ב MySQL)
איך נראית טרנזקציה?
START TRANSACTION;
UPDATE Employee SET Name = 'Shay Sarussi' WHERE EmployeeID = 123;
UPDATE Department SET DepartmentName = 'R&D' WHERE DepartmentID = 10;
INSERT INTO Employee (EmployeeID, Name, DepartmentID) VALUES (124, 'Dana’ ‘Israeli', 20);
DELETE FROM Department WHERE DepartmentID = 30;
UPDATE Employee SET DepartmentID = 20 WHERE EmployeeID = 125;
COMMIT;
- התחלה עם `START TRANSACTION`.
- ה-`UPDATE` הראשון משנה את שם העובד (מזהה 123) לShay Sarussi'.
- ה-`UPDATE` השני משנה את שם המחלקה (מזהה 10) ל'R&D'.
- הפקודה `INSERT` מוסיפה עובדת חדשה (מזהה 124) בשם 'Dana Israeli' למחלקה 20.
- הפקודה `DELETE` מסירה מחלקה (מזהה 30) ממסד הנתונים.
- `UPDATE` נוסף משנה את המחלקה של העובד 125 למחלקה 20.
- לבסוף, `COMMIT` משמש ליישום כל השינויים הללו באופן קבוע.
כמו בכל טרנזקציה מורכבת, חשוב לוודא שכל פעולה תקפה ואינה מפרה אילוצים או כללים של מסד הנתונים. אם יש טעות בכל שלב, יש להשתמש ב-`ROLLBACK;` כדי לבטל את כל הטרנזקציה ולשמור על שלמות מסד הנתונים.
בהמשך הראה דוגמא ל ROLLBACK
מהו ACID?
עקרון ה-ACID הוא למעשה ראשי התיבות של:
אטומיות (Atomicity), עקביות (Consistency), בידוד (Isolation), ועמידות (Durability).
עקרון זה מתאר ארבעה תכונות מרכזיות של טרנזקציה (Transaction).
אטומיות (Atomicity)
כל השינויים לנתונים מתבצעים כאילו הם פעולה יחידה – כגוש אחד בלתי ניתן להפרדה.
אם אחד השלבים נכשל, זה אומר שהטרנזקציה כולה לא תתבצע. כלומר, כל השינויים מתבצעים, או שאף אחד מהם לא מתבצע.
(הטרנזקציה ״תתחייב״ רק אם כל הפעולות היו מוצלחות).
עקביות (Consistency)
מבטיחה שטרנזקציה תעביר את מסד הנתונים ממצב תקף אחד למצב תקף אחר, תוך שמירה על כל האילוצים של מסד הנתונים.
לדוגמה:
למשל אם קבעתי במסד הנתונים שגיל של אדם יכול להיות עד 130 לכל היותר , לא יכול להיות מצב בו לאחר סיום טרנזקציה אני אקבל באחד הרשומות שלי שיש גיל של אדם בן 230.
עמידות (Durability)
אם טרנזקציה הסתיימה בהצלחה. אז השינוי שנגרם עקב השלמת הטרנזקציה חייב להתקיים במערכת גם אם היא נופלת מיד אחרי ביצוע הטרנזקציה.
איך טרנזקציה יכולה להיות מאושרת אבל לא להיכתב בדיסק?
נניח שיש לנו העברה בה מועברים 10 ש״ח מא' לב'. תחילה מוסרים 10 ש״ח מא', אחר כך מוסיפים 10 ש״ח לב'. בשלב זה, מודיעים למשתמש שהעסקה הייתה מוצלחת.
יחד עם זאת, השינויים עדיין ממתינים בתור ב disk buffer , ומחכים להיכתב בדיסק עצמו.
פתאום הסרבר שלנו נופל והשינויים אבדו, אך המשתמש מניח (בצדק) שהשינויים נשמרים.
איך מתגברים על הבעיה ?
הדרך הבסיסית ביותר להשיג עמידות היא באמצעות שימוש ב Transaction log
תפקידו של ה Transaction log הוא לאפשר לנו לעבד מחדש ולשחזר את ה Transaction, קרי לבנות
מחדש את מצב המערכת לזה שהיה לפני הקריסה – בדרך כלל למצב העקבי האחרון של מסד הנתונים.
לפיכך השינויים שיש לבצע בנתונים האמיתיים מתבצעים תחילה ב Transaction log , ולאחר מכן מתבצע העדכון האמיתי.
למשל בואו ניקח סיטואציה שממחישה נפילה והתאוששות:
- מצב 1: זהו המצב ההתחלתי של מסד הנתונים לפני שהתרחשה הטרנזקציה.
- נניח שאנו רוצים לשנות את את מסד הנתונים ממצב 1 למצב 2. זהו סט הפעולות ש Transaction מבצע (לדוגמא, עדכון רשומות, הכנסת נתונים חדשים וכו').
- לפני שהשינויים מיושמים במסד הנתונים למעבר ממצב 1 למצב 2, הם ראשית מתועדים ב Transaction log. ה Transaction log מאוחסן באחסון לא נדיף, כלומר הוא נשמר אפילו אם המערכת נתקלת בתקלה. מטרת ה Transaction log כמו שהזכרנו לעיל היא להבטיח שניתן יהיה להחיל מחדש או לבטל כל שינוי כדי לשמר את שלמותו של מסד הנתונים.
- כעת נניח שקרתה תקלת מערכת או קריסה שמתרחשת לאחר שהשינויים נכתבו ב Transaction log אך לא התבצעו במסד הנתונים עצמו.
- לאחר שהמערכת הצליחה להתאושש מהקריסה, מערכת מסד הנתונים משתמשת ב Transaction log כדי לשחזר את מצב 2, ומבטיחה שכל השינויים שהיו אמורים להתרחש באמת יתבצעו, למרות התקלה.
לסיכום: בזכות תכונת העמידות (Durability), מסד הנתונים מסוגל להתאושש למצב עקבי לאחר תקלה.
דבר המבטיח לנו ש Transaction שדווחו כמוצלחות למשתמש (כמו העברת כספים) אכן נשמרות במסד הנתונים.
בידוד (Isolation)
בידוד מתייחס למצב ביניים של טרנזקציה, כך שהיא בלתי נראית לטרנזקציות אחרות.
במילים יותר פשוטות הטרנזקציות מבוצעות במקביל ״כאילו״ הן מבוצעות ברצף.
תכונת הבידוד מבטיחה שטרנזקציות מקבילות לא מתערבות זו בזו והשפעות של טרנזקציה אחת מבודדות מטרנזקציות אחרות עד לרגע ביצועה.
עוד יתרון מאוד חשוב שאנחנו משיגים עם TRANSACTION פרט לעקרונות ה ACID הוא:
- אופטימיזציה של ביצועים: ביצוע מספר פקודות בתוך טרנזקציה יכול להיות יעיל יותר מאשר ביצוע כל אחת מהן בנפרד. זה מכיוון שטרנזקציות מפחיתות את העומס של הקמה וסגירה חוזרת
ונשנית של חיבורים ומקטינות את העלות של אישור פעולות כאשר מבוצעות פעולות מרובות.
מהו Stored Procedures ?
פְּרוֹצֵדוּרָה מאוחסנת (באנגלית: Stored Procedure) היא שיטת כתיבה של שאילתות SQL המאוחסנות בשרת של בסיס הנתונים.
הפרוצדורה המאוחסנת כוללת קוד של התחלה, קוד של סיום, פרמטרים וגוף הפרוצדורה, שבו נכתבות הפעולות המתבצעות בפרוצדורה. הפרוצדורה המאוחסנת נכתבת בשפת SQL, שהשימוש בה נעשה בכל פעולה שנוגעת למסד הנתונים.
דוגמה ב MySQL:
כאשר Stored Procedure מוצהר, MySQL מקמפל את קוד הפרוצדורה ושומר אותו במטמון (קאש).
כעת אם נקרא ל Stored Procedure באמצעות הפקודה CALL, נגרום לביצוע של הקוד שכבר קומפל.
דומה לרעיון של פונקציה 🙂
דוגמא ל Stored Procedure בשילוב עם Transactions:
CREATE PROCEDURE UpdateEmployeeAndDepartment()
BEGIN
DECLARE exit handler FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
UPDATE Employee SET Name = 'Shay Sarussi' WHERE EmployeeID = 123;
UPDATE Department SET DepartmentName = 'R&D' WHERE DepartmentID = 10;
INSERT INTO Employee (EmployeeID, Name, DepartmentID) VALUES (124, 'Dana’ ‘Israeli', 20);
DELETE FROM Department WHERE DepartmentID = 30;
UPDATE Employee SET DepartmentID = 20 WHERE EmployeeID = 125;
COMMIT;
END
כדי לקרוא לפרוצדורה זו יש להשתמש ב:
CALL UpdateEmployeeAndDepartment();
בדומה לפונקציות, ניתן להעביר פרמטרים ל Stored Procedure ובנוסך לכך Stored Procedure יש פקודות תנאי ולולאות, והוא יכול לקרוא ל Stored Procedure אחר.
במסדי נתונים SQL, במיוחד במערכות מורכבות, נהוג ולעיתים מומלץ לשלב Stored Procedure עם טרנזקציות. שילוב זה מציע מספר יתרונות ומתאים לעקרונות של שמירה על שלמות הנתונים וטיפול יעיל בפעולות מורכבות.
כמו כן אחד היתרונות של השילוב הנ״ל הוא שזה נותן לנו מנגנון ל ״טיפול בשגיאות״.
כלומר באמצעות שימוש בטרנזקציות בתוך Stored Procedure, ניתן לנהל טיפול בשגיאות ביעילות.
אם חלה שגיאה בכל חלק של הפרוצדורה, ניתן להתחיל חזרה אחורה ROLLBACK כדי לבטל את כל השינויים שנעשו במהלך הטרנזקציה.
נשים לב לדבר הבא:
– אם מתרחשת שגיאת SQL באחת מהפקודות (בדוגמא לעיל), הקוד שמתבצע הוא ROLLBACK, מה שגורם לביטול כל השינויים שנעשו במהלך הטרנזקציה הזו.
– לעומת זאת אם כל הפקודות מתבצעות בהצלחה, ה-COMMIT יעשה את כל השינויים כ דבר קבוע.
ישנן מספר יתרונות לשימוש ב Stored Procedure.
- הם מפחיתים את התעבורה בין היישומים לשרת מכיוון שבמקום לשלוח שאילתות,נשלח רק את שם ה הפרוצדורה לשרת.
- Stored Procedure יכול להיקרא על ידי Stored Procedure אחר, וזה מפחית את הכפלת הקוד.
- אנחנו נקבל ביצועים טובים יותר מבחינת מהירות, מכיוון שהקוד יכול להיות מקומפל מראש במקום לנתח את השאילתה בכל פעם.
למרות היתרונות המצוינים לעיל, ל Stored Procedure יש מגבלות מסוימות:
- קשה לאתר באגים עם Stored Procedure מכיוון שהם מופעלים אוטומטית ולא ניתן להחיל נקודות בדיקה בקוד.
- Stored Procedure גורם לעומס משאבים ועשוי לגרום לשימוש יתר בזיכרון ובמעבד. אין דרך לבטל שינוי שנעשה, לאחר שנעשה.
פוסטים ומאמרים נוספים ניתן למצוא בדף הלינקדין shay Sarussi Elshten
