עיבוד תנועות בסביבת SQL Transaction Processing 1

עיבוד תנועות בסביבת SQL Transaction Processing 1

עיבוד תנועות - מטרה • שמירה על אמינות ושלמות בסיס הנתונים בסביבה עתירת תנועות ומרובת משתמשים • מערכת RDBMS צריכה להבטיח את הצלחת רצף פקודות העדכון ולא רק את הצלחת הפקודה הבודדת 2

דוגמא לביצוע תנועה • אירוע: רישום סטודנט שמספרו 012 לקורס- M 001לסמסטר קיץ 7002 3

דוגמא לביצוע תנועה Grades ציונים STUDENT_ID COURSE_ID SEMESTER TERM GRADE מס. סטודנט מס. קורס סמסטר מועד ציון 105 C-55 SUM 2007 A 70 210 M-100 AUT 2008 A 90 105 M-100 SUM 2007 B 50 105 C-200 AUT 2008 A 85 210 C-200 AUT 2008 A 80 210 B-10 WIN 2008 A 50 105 B-40 WIN 2008 B 70 245 M-100 AUT 2008 A 80 245 B-10 AUT 2008 A 70 200 C-200 AUT 2008 B 50 200 B-10 AUT 2008 A 65 245 B-40 WIN 2007 A 95 200 M-100 SUM 2007 B 90 310 M-100 SUM 2007 A 100 4

דוגמא לביצוע תנועה : • האירוע מצריך - * הוספת שורה חדשה לטבלת ציונים )עמודת הציונים * ( ריקה עדכון מספר הסטודנטים שנרשמו לקורס בטבלת קורסים INSERT INTO GRADES • (COURSE_ID, STUDENT_ID, SEMESTER, TERM) • • UPDATE COURSES • VALUES (‘M-100’, ’ 210’, ’SUM 2007’, ’A’) H SET CUR_ENROLL=CURR_ENROLL+1 • 5 WHERE COURSE_ID=‘M-100’ • J

דוגמא לביצוע תנועה • רצף של 2 הפקודות חייב להתבצע כאילו הן פקודה אחת • אחרת בסיס המתונים יהיה משובש ולא אמין 6

דוגמא נוספת • עדכון מספר שורות תוך כדי ביצוע פקודה אחת: עדכון כל הציונים )מתן בונוס( לכל 7002 הסטודנטים שלמדו בקורס 001 - M בסמסטר קיץ 8991 7002 SUM 7

הגדרת תנועה 8

תכונות של תנועה • כל תנועה חייבת לקיים 4 תכונות ) (ACID * אטומיות - ) (Atomicity תנועה חייבת להתבצע בשלמות * עקביות - ) (Consistency תנועה חייבת להעביר את בסיס הנתונים ממצב תקין אחד למצב תקין אחר אפילו שתוך כדי פעולתה התנועה מפירה זמנית את תקינות * אי בסיס הנתונים תלות - ) (Independency תנועות חייבות להתבצע באופן בלתי תלוי זו מזו * נצחיות - ) (Durability ברגע שהתנועה 9 הסתימה בהצלחה העדכונים חייבים להירשם בבסיס הנתונים

הפקודה COMMIT כפקודת SQL • מאפשרת לתוכנית היישום : * להודיע למערכת RDBMS שהתנועה הסתימה בהצלחה * כל פקודות העדכון שהיו צריכות להתבצע כחלק מהתנועה - בוצעו ומצב בסיס הנתונים - תקין 01

דוגמא COMMIT - הפקודה • (COURSE_ID, STUDENT_ID, SEMESTER, TERM) • VALUES (‘M-100’, ’ 210’, ’SUM 2007’, ’A’) • • UPDATE COURSES • INSERT INTO GRADES H SET CUR_ENROLL=CURR_ENROLL+1 • WHERE COURSE_ID=‘M-100’ • J COMMIT • 11

הפקודה ROLLBACK גלילה לאחור • מאפשרת לתוכנית היישום לבקש מבסיס הנתונים לבטל את כל העדכונים שבוצעו מתחילת התנועה 21

ROLLBACK - הפקודה AUT 2008 13

מודל התנועות - Transaction Model לפי תקן SQL • מגדיר את האופן שבו מערכת RDBMS מזהה את תחילת התנועה, את סיומה המוצלח או את כשלון ביצוע התנועה 41

מודל התנועות • תחילת תנועה - פקודת העדכון הראשונה בתוכנית או פקודת העדכון הראשונה לאחר הפקודה COMMIT • סיום תנועה תקין - או ע”י ביצוע הפקודה COMMIT או אם תוכנית היישום מסתימת • סיום תנועה לא תקין - או ע”י ביצוע הפקודה ROLLBACK או אם תוכנית היישום “עפה” 51

בדיקה מושהית של אילוצים • שפת SQL מכילה פקודה המאפשרת לקבוע האם בדיקת אילוץ )כגון: מספר מכסימלי של בחינות לסטודנט( תתבצע מיד לאחר כל פקודת עדכון של טבלה )ברירת מחדל( או להשהות הבדיקה לאחר סיום מוצלח של התנועה • אם בדיקת האילוץ תיכשל מערכת RDBMS לא תבצע את התנועה כולה והיא תבוטל • 61 דוגמא : SET CONSTRAINTS MAX_NUM_OF_EXAMS DEFFERED

יומן אירועים ) (LOG FILE • המנגנון המאפשר להפעיל את פקודת ה - ROLLBACK הינו יומן האירועים 71

יומן אירועים • הרשומה מכילה את הנתונים האלה: * שם התנועה * הזמן והתאריך בו בוצעה התנועה * זיהוי המשתמש ותחנת העבודה שממנו בוצעה התנועה * הפעולה שבוצעה )ביטול, עדכון, הוספת, תחילת תנועה, סוף * שם הטבלה שבה בוצעה תנועה( * תוכן השורה לפני הפעולה העדכון ) (Before Values כולל מפתח השורה 81 * תוכן השורה לאחר העדכון ) (After Values

תהליך שיחזור לאחור - Backward Recovery • מופעל במקרה של ביצוע פקודת ROLLBACK : יומן האירועים יקרא בסדר כרונולוגי * * מערכת RDBMS תחזיר הפוך את בסיס הנתונים למצבו שלפני העדכון 91

תהליך שיחזור לפנים - Recovery Forward • יומן האירועים משמש גם ב- “שיחזור לפנים” של בסיס הנתונים במקרה של תקלה • ניתן להפעיל על קובץ הגיבוי האחרון את כל השורות “שאחרי העדכון ) ” (After Image לפי סדר כרונולוגי שבו הם בוצעו 02

דוגמא של קטע מקובץ יומן אירועים id-Tran Date Time -User id Terminal Action Before Values Table After Values Grd-01 12/02/2008 07: 30: 35 Dan Ter-05 Start Grd-01 12/02/2008 07: 31: 01 Dan Ter-05 Update Dpt-08 12/02/2008 07: 31: 28 Ron Ter-08 Start Grd-01 12/02/2008 07: 32: 02 Dan Ter-05 Insert Grades Dpt-08 12/02/2008 07: 32: 25 Ron Ter-08 Delete Departments Grd-01 12/02/2008 07: 32: 54 Dan Ter-05 Commit Dpt-07 12/02/2008 07: 33: 10 Eyal Ter-10 Start Dpt-08 12/02/2008 07: 33: 18 Ron Ter-08 Commit Dpt-07 12/02/2008 07: 33: 34 Eyal Ter-10 Insert ………… Grades Departments ………… 21

דוגמא של קטע מקובץ יומן אירועים הפעיל תנועה 70 - Dpt • המשתמש Eyal בשעה 01: 33: 7 והספיק להוסיף שורה לטבלת מחלקות • התנועה לא הספיקה להסתיים מכיוון שלא נרשמה רשומת Commit ביומן האירועים • כדי לבצע Rollback נצטרך לבטל את השורה מטבלת “מחלקות” שהמפתח שלה רשום ביומן “After האירועים כחלק מעמודת ” “Before Values 22

פרוטוקול “רישום מראש” • פרוטוקול Write Ahead Log Protocol קיים ברוב מערכות ה - RDBMS • לפי פרוטוקול זה מערכת RDBMS מעדכנת תחילה את יומן האירועים ורק לאחר מכן את בסיס הנתונים 32

עדכון בו-זמני Concurrent Updates • מצב שבו 2 משתמשים או יותר מנסים לעדכן בסיס נתונים אחד באותו זמן • בסביבה מרובת משתמשים (Multi User ) Environment כל משתמש מקבל עותק משלו של תוכנית היישום בזיכרון • העותק תופס 2 שטחי זיכרון: * עבור פקודות התוכנית * עבור שטח עבודה שבו נרשמים המשתנים והנתונים המעובדים 42

שיטת ה - Reentrant רב-כניסות • אפשרות אחרת - רק עותק אחד של תוכנית היישום נשמר יחד עם מראה מקום נפרד עבור כל משתמש • כל משתמש מקבל שטחי עבודה נפרדים 52

Multitasking • שיטת עבודה של מערכת הפעלה לפיה מספר משימות מתבצעות במקביל על אותו מעבד • המערכת מקצה את המעבד לכל משימה לפרק זמן מסוים, מפסיקה את ביצוע המשימה ומקצה המעבד למשימה אחרת • זה מתבצע בעזרת מראה מקום המצביע על הפקודה האחרונה שבוצעה בכל משימה 62

גישות בניהול תנועות • גישת הנעילות ) (Oracle • גישת הגירסאות 72

בעיית “העדכון האבוד" ) (Lost Update • נגרמת כתוצאה מעדכון בו-זמני • דוגמא: 2 סטודנטים מנסים להירשם באותו זמן לאותו קורס • נתבונן במה שקורה עם טבלת “קורסים” שמכילה בין השאר 2 עמודות : * “מספר סטודנטים מכסימלי לקורס” * “מספר סטודנטים שכבר נרשמו לקורס” 82

בעיית “העדכון האבוד" ) (Lost Update • סטודנט א’ נרשם לקורס 002 - C • תוכנית היישום מבצעת Select ושולפת את שורת קורס 002 - C מבסיס הנתונים אל שטח העבודה שהוקצה לסטודנט א’ 92

בעיית “העדכון האבוד" ) (Lost Update • סטודנט ב’ מבקש להירשם לאותו קורס 03

בעיית “העדכון האבוד" ) (Lost Update • סטודנט א’ מאשר את ההרשמה • תוכנית היישום: * מגדילה ב- 1 את מונה “מספר הסטודנטים שכבר נרשמו” * מעדכנת את בסיס הנתונים Commit * מבצעת *משחררת את שטח העבודה של סטודנט א’ 13

בעיית “העדכון האבוד" ) (Lost Update • סטודנט ב’ מאשר אף הוא את ההרשמה 23

בעיית “העדכון האבוד" ) (Lost Update • הבעיה - כל משתמש קרא את אותם נתונים מבסיס הנתונים לשטחי העבודה שבזיכרון מבלי להתייחס לעובדה שהנתונים עומדים להתעדכן בינתיים ע”י משתמש אחר 33

בעיית עדכון רשומה שנמצאת בתהליך עדכון ) (Uncommitted Update • הבעיה נוצרת כאשר תוכנית יישום אחת )תוכנית ב’( נמצאת בתהליך עדכון שורה שעודכנה קודם לכן ע”י תוכנית יישום אחרת )תוכנית א’( שעדיין לא ביצעה Commit ואח”כ יבוצע גלילה לאחור של תוכנית א’ 43

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא • מופעלות 2 תוכניות יישום שונות: * אחת - לעדכון המספר המכסימלי של סטודנטים לקורס )מ- 58 ל- 59 ( * השניה - רישום לקורס 53

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא 63

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא 73

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא 83

בעיית ניתוח נתונים לא עקבי Inconsistent Analysis • • 93 תוכנית א’ מפיקה דו”ח המציג את מספר הסטודנטים שנרשמו בפועל לפי קורס תוכנית זו מתחילה לעבוד ולשלוף נתונים מבסיס הנתונים תוך כדי העבודה מתחילה לפעול תוכנית ב’ לרישום סטודנטים לקורס ומעדכנת את נתוני אחד הקורסים שכבר נקראו ע”י תוכנית א’ כאשר תוכנית א’ מסיימת לעבוד היא מציגה נתונים שכבר אינם נכונים מאחר ותוך כדי פעולתה בסיס הנתונים התעדכן

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

רמת הנעילה (Locking ) Granularity • תחום בסיס הנתונים שכפוף להוראת הנעילה 14

רמות הנעילה האפשריות • עמודה בתוך שורה (Column Level ) Locking מונעת גישה מתוכניות אחרות המבקשות לעדכן אותה עמודה באותו שורה • לא מונעת גישה של תוכניות אחרות לאותה שורה כדי לעדכן עמודות אחרות • בגלל המורכבות - רוב מערכות RDBMS אינן תומכות בנעילה ברמה זו 24

רמות הנעילה האפשריות ) (Row Level Locking • שורה *מערכת RDBMS תבצע נעילה של כל השורה מבלי להתייחס אילו עמודות בשורה מתעדכנות * רוב המערכות המסחריות תומכות בשיטת נעילה זו • דף - ) (Page Level Locking פעולות קלט/פלט מתבצעות תמיד ברמה של דף פיסי ) (Page שיכול להכיל שורה אחת או יותר 34

רמות הנעילה האפשריות ) (Table Level Locking • טבלה *מערכת RDBMS תבצע נעילה של כל הטבלה מבלי להתייחס אילו שורות ואילו עמודות בטבלה מתעדכנות * קלה למימוש אולם מקטינה את כמות העבודה במקביל * אינה מתאימה לסביבה מרובת-משתמשים ) (Data Base Level Locking • בסיס הנתונים *קלה ביותר לשימוש * מיושמת במצבים נדירים 44

סוגי הנעילות ) (Lock Type ) (Exclusive Lock • נעילה בלבדית אף תוכנית יישום אחרת אינה מורשית לקרוא או לעדכן את הנתונים ) Shared Lock • נעילה שיתופית *תוכנית יישום המבקשת לקרוא שורה מסוימת מחזיקה אותה בסטטוס של “נעילה שיתופית” ובכך מתירה לתוכניות אחרות לקרוא אותה שורה אך לא לעדכן אותה * ברגע שהתוכנית מבקשת לעדכן את השורה היא צריכה להעביר את הנעילה מ- “שיתופית” ל- 54 “בלבדית”

טבלת החלטות לקבלת נעילות 64

Consistent read • • • 74 אם נוצר מצב של "הרעבה" ) (Starvation "הרעבה" – יישום מבקש לבצע קריאה בלבד לרשומה נעולה למשך זמן רב פיתרון – Consistent Read קריאה של רשומה מגירסה קודמת )לפני העדכון( מצריך ניהול גירסאות

טבלת החלטות לקבלת נעילות בצע נעילת כתיבה קרא מגירסה קודמת 84

נעילה ללא מוצא ) (Deadlock • מצב נצחי של המתנה לשורה • מצב שבו 2 תוכניות יישום )או יותר( נועלות אובייקט שהתוכנית השניה מבקשת להשתמש בו 94

נעילה ללא מוצא - דוגמא • 2 תוכניות יישום המעדכנות 2 שורות שונות אולם בסדר הפוך : * תוכנית א’ מתחילה לפעול ושולפת שורה A ומבצעת לשורה נעילת בלבדית )כתיבה( לקראת עדכון * תוכנית ב’ מתחילה לפעול ושולפת שורה B ומבצעת לה נעילה בלבדית )כתיבה( 05 * תוכנית א’ מבקשת לקרא את שורה B ולבצע לה נעילה בלבדית מכיוון ששורה זו כבר נעולה התוכנית מוכנסת למצב המתנה * תוכנית ב’ מבקשת לקרא את שורה A ולבצע לה נעילה מכיוון ששורה זו כבר נעולה התוכנית מוכנסת בלבדית למצב המתנה

היחלצות ממצב של נעילה ללא מוצא • ביצוע כל הנעילות לפני עדכון - * כל תוכנית צריכה להכריז מראש על כל השורות שהיא מבקשת לנעול * מערכת RDBMS מתחילה לבצע את הנעילות * אם שורה מסוימת כבר נעולה משחררים את כל השורות שכבר ננעלו והתהליך מתחיל מחדש * קשה ליישם כי בד”כ תוכנית יישום אינה יודעת מראש את כל השורות שתרצה לעדכן 15

היחלצות ממצב של נעילה ללא מוצא • איתור מצב נעילה ללא מוצא - * מערכת RDBMS קובעת זמן המתנה (Time ) Out כך שאם תוכנית יישום ממתינה מעבר לו תופסק פעולתה של תוכנית יישום כל שהיא תוך כדי ביצוע פעולה Rollback וביטול כל העדכונים שהתוכנית כבר ביצעה * הקריטריון לבחירת התוכנית שפעולתה התנועה האחרונה שהתחילה תופסק: התנועה שהספיקה לפעול לבצע הכי מעט עדכונים 25

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא • תוכנית יישום 1 TR קוראת שורה של קורס- C 002מטבלת “קורסים” ומעדכנת אותה • תוכנית יישום 2 TR קוראת את השורה של מחלקת CS מטבלת “מחלקות”, קוראת את השורה של קורס 002 - C מטבלת “קורסים” ולבסוף מעדכנת את השורה של מחלקת CS בטבלת “מחלקות” 35

בעיית עדכון רשומה שנמצאת בתהליך עדכון - דוגמא TR 1 • SELECT (C-200) COURSES • UPDATE (C-200) COURSES • TR 2 • SELECT (CS) DEPARTMENTS • SELECT (C-200) COURSES • UPDATE (CS) DEPARTMENTS • 54

דוגמא לניהול נעילות ע”י מערכת RDBMS 55

דוגמא לניהול נעילות ע”י מערכת RDBMS 65

גירסאות • • • 75 מערכת Hibernet – Framework מתוצרת Apache סביבה מונחית עצמים שפת JAVA בסיס נתונים Oracle ה- Framework מנהל את העבודה ) (UPDATE , SELECT עם הנתונים

גירסאות – מספר עדכני ו-מספר שוטף • • 85 לכל שורה בטבלה נשמר "מספר גרסה" יישום המבקש לעדכן שורה בטבלה – קורא שורה מהדיסק )בעזרת ה- (Framework היישום )בעזרת ה- (Framework מעלה ב- 1 את ה- "מספר הגרסה" של שורת הטבלה היישום שומר את השורה בשטח העבודה שלו ב- RAM עם "מספר הגירסה" – "מספר גירסה של היישום"

גירסאות – עדכון שורה • כאשר יישום מבקש לעדכן רשומה הוא מבצע פקודה: • PDATE…WHERE • "מספר גרסה של היישום" ="מספר הגירסה )של שורת הטבלה(" • אם השוויון אינו מתקיים היישום מתחיל מחדש • אם השוויון מתקיים ונרשמה פקודת - Commit העדכון יתבצע 95

דוגמא: 2 יישומים מבקשים לרשום 2 סטודנטים לאותו קורס - בעיית העדכון האבוד • מספר גירסה של שורת קורס: 002 - 5 = C • יישום א' רוצה לעדכן רשומה לכן הוא קורא רשומת הקורס מהדיסק ומעלה את המספר הגרסה ל- 6 כך שב- RAM שלו מצויה עותק הרשומה עם גירסה 6 • יישום ב' רוצה לעדכן אותה רשומה לכן הוא קורא אותה רשומה מהדיסק מעלה את מספר הגרסה ל- 7 כך שב- RAM שלו מצויה עותק גירסה 7 הרשומה עם 06

דוגמא: 2 יישומים מבקשים לרשום 2 סטודנטים לאותו קורס • היישום הראשון מבצע פקודת UPDATE אשר לא תתבצע כי מספר הגרסה ב- RAM שלו הינה ואילו "מספר הגירסה" של השורה 6 )היישום יתחיל הינו 7 להתבצע מחדש( • היישום השני מבצע פקודת UPDATE אשר תתבצע כי מספר הגירסה ב- RAM שלו הינה 7 ל- "מספר הגירסה" של הזהה השורה 16

בעיית עדכון רשומה שנמצאת בתהליך עדכון ) (Uncommitted Update • • • 26 מספר גירסה של שורת קורס: 002 - 5 = C יישום א' רוצה לעדכן רשומה )מספר נרשמים מכסימלי מ- 58 ל- 59( הוא קורא רשומת הקורס מהדיסק ומעלה את המספר כך שב- RAM שלו מצויה עותק הרשומה הגרסה ל- 6 עם גירסה 6 היישום מבצע פקודת UPDATE אשר לכאורה תתבצע כי מספר הגרסה ב- RAM זהה ל- "מספר הגירסה" של השורה ואולם מאחר ולא בוצע COMMIT כי אז לא תתבצע עדיין כתיבה על הדיסק ממש ) RDBMS יחכה עד לפקודת ה- (COMMIT יישום ב' רוצה לעדכן אותה רשומה )מספר נרשמים עד כה מ- 83 ל- 93( לכן הוא קורא רשומה מהדיסק )ללא שינויים של מעלה את מספר הגרסה ל- 7 כך שב- RAM יישום א'( שלו מצויה עותק הרשומה עם גירסה 7

בעיית עדכון רשומה שנמצאת בתהליך עדכון ) (Uncommitted Update • היישום הראשון מבצע פקודת ROLLBACK אשר בעצם מוחק את שורת הטבלה מזיכרון ה- RAM של היישום ולא מהדיסק • היישום השני מבצע פקודת UPDATE אשר תתבצע כי מספר הגירסה ב- RAM שלו הינה 7 ל- "מספר הגירסה" של הזהה השורה 36

תנועה עם מספר עדכונים • לדוגמא: רישום סטודנט לקורס • כולל עדכון "מספר סטודנטים שנרשמו עד כה" וכן הוספת שורה בטבלת "ציונים" • ברגע שנרשמה פקודת COMMIT כי אז לכל עדכון ועדכון - * מתבצעת השוואה בין מספר הגירסה של שורת הטבלה עם מספר הגרסה של היישום * אם קיים שוויון יתבצע העדכון פיזית בדיסק * אחרת ימחקו כל העדכונים מזיכרון ה- RAM של היישום * היישום יתחיל להתבצע מחדש 46

תרגילים • 1. מנה לפחות 2 סיבות מדוע תנועה עלולה להסתיים בכישלון 2. הסבר את החשיבות של קובץ יומן האירועים ואיזה שירותים הוא מספק 3. חווה דעתך על אורך התקופה שבה כדאי לשמור יומן אירועים 56 4. הסבר מהו “שיחזור לאחור” ומהו “שיחזור

תרגילים • 4 נתון מצב הנעילות הבא: * משתמש דן נעל את האובייקטים 1 : R 3, R 2, R וממתין לאובייקט *משתמש רון נעל את האובייקטים 4 R 6, R 5, R וממתין לאובייקט 4 R *משתמש אייל נעל אובייקטים 8 : R 9, R וממתין לאובייקט 01 R *משתמש צבי נעל אובייקטים 01 R 11, R וממתין ל 2 *R הראה כיצד בא לביטוי העובדה שזהו 66 מצב של נעילה ללא מוצא