עסקה ב-SQL Server היא א קבוצה רציפה של הצהרות או שאילתות לבצע משימות בודדות או מרובות במסד נתונים. לכל עסקה עשויות להיות פעולות קריאה, כתיבה, עדכון או מחיקה בודדות או שילוב של כל הפעולות הללו. כל עסקה חייבת לקרות שני דברים ב-SQL Server:
- או שכל השינוי יצליח כאשר העסקה מתבצעת.
- לחלופין, כל השינויים מבוטלים כאשר העסקה מבוטלת.
לא ניתן לבצע עסקה עד להשלמת כל הפעולות בסט. זה אומר שאם ארגומנט כלשהו נכשל, פעולת העסקה תיכשל. כל עסקה מתחילה בהצהרת ה-SQL הניתנת להפעלה הראשונה ומסתיימת כשהיא מוצאת commit או rollback, באופן מפורש או מרומז. הוא משתמש ב- לְבַצֵעַ אוֹ גלגל לאחור הצהרות באופן מפורש, כמו גם במרומז כאשר נעשה שימוש במשפט DDL.
הייצוג הציורי שלהלן מסביר את תהליך העסקה:
בוטנים מול אגוז אדמה
הדוגמה הבאה תסביר את הרעיון של עסקה:
דוגמה זו תשתמש במערכת מסד הנתונים הבנקאיים כדי להסביר את הרעיון של עסקה. נניח שלקוח בנק רוצה למשוך כסף מחשבונו באמצעות מצב כספומט. הכספומט יכול לבצע פעולה זו בשלושת השלבים:
- ה צעד ראשון היא לבדוק את זמינות הסכום המבוקש בחשבון.
- ה צעד שני מנכה את הסכום מהחשבון אם הסכום פנוי ולאחר מכן מעדכן את יתרת החשבון.
- ה צעד שלישי הוא לכתוב את פעולת משיכת הכסף בקובץ היומן. שלב זה כותב שהעסקה מוצלחת או נכשלה. אם זה מצליח, כתוב את שינוי הנתונים במסד הנתונים. אחרת, העסקה תחזור למצבה הקודם.
העיקרון הבסיסי מאחורי טרנזקציות הוא שאם אחת מההצהרות מחזירה שגיאה, כל מערך השינויים מתבטל כדי להבטיח שלמות הנתונים. ואם העסקאות יצליחו, כל השינויים יהיו קבועים במסד הנתונים. לפיכך, אם יש הפסקת חשמל או בעיות אחרות בעת משיכת כסף מכספומט, עסקאות מבטיחות שהיתרה שלנו תישאר עקבית. הצהרת טרנזקציה מבצעת את הפעולות הללו בצורה הטובה ביותר מכיוון שארבעת מאפייני המפתח של העסקה הופכים את כל הפעולות למדוייקות ועקביות יותר. ארבעת הנכסים של העסקה מכונים ACID.
נכסי עסקה
מאפייני העסקה מכונים נכסי ACID (אטומיות, עקביות, בידוד, עמידות), אשר דנים בפירוט להלן:
אָטוֹמִיוּת: מאפיין זה מבטיח שכל ההצהרות או הפעולות הכלולות בעסקה חייבות להתבצע בהצלחה. אחרת, כל העסקה תבוטל, וכל הפעולות יוחזרו למצבן הקודם כאשר פעולה כלשהי נכשלת.
עֲקֵבִיוּת: מאפיין זה מבטיח שמסד הנתונים משתנה רק כאשר עסקה תתבצע בהצלחה. הוא גם אחראי להגנה על נתונים מפני קריסות.
בידוד: נכס זה מבטיח שכל העסקאות מבודדות מעסקאות אחרות, כלומר כל פעולה בעסקה מופעלת באופן עצמאי. זה גם מבטיח שההצהרות יהיו שקופות זו לזו.
עֲמִידוּת: מאפיין זה מבטיח שהתוצאה של עסקאות מחויבות נשמרת במסד הנתונים לצמיתות גם אם המערכת קורסת או נכשלה.
מצבי עסקאות ב-SQL Server
ישנם שלושה מצבי עסקאות שונים שבהם SQL Server יכול להשתמש:
מצב עסקה לביצוע אוטומטי: זהו מצב עסקאות ברירת המחדל של שרת SQL. זה יעריך כל משפט SQL כעסקה, והתוצאות מחויבות או מתגלגלות לאחור בהתאם. כך ההצהרות המוצלחות מתחייבות מיד, בעוד שההצהרות הכושלות מתגלגלות מיד לאחור.
מצב עסקה מרומז. מצב זה מאפשר ל-SQL Server להתחיל את הטרנזקציה המרומזת עבור כל משפט DML, אך הוא דורש במפורש שימוש בפקודות commit או rollback בסוף ההצהרות.
מצב עסקה מפורשת: מצב זה מוגדר על ידי המשתמש המאפשר לנו לזהות במדויק את נקודות ההתחלה והסיום של העסקה. זה יבוטל אוטומטית במקרה של שגיאה קטלנית.
בקרת עסקאות
להלן הפקודות המשמשות לשליטה בעסקאות:
הערה: אנו יכולים להשתמש רק במשפטי DML (INSERT, UPDATE ו-DELETE) עבור פקודות שפת בקרת עסקאות. איננו יכולים להשתמש בהם בזמן יצירה או שחרור של טבלאות מכיוון שפעולות אלו מחויבות למסד הנתונים באופן אוטומטי.
מצב עסקה
זה מציין כיצד מתנהלות העסקאות במהלך חייהן. הוא מתאר את המצב הנוכחי של העסקה וכן כיצד תעובד העסקה בעתיד. מדינות אלו מגדירות את הכללים הקובעים אם עסקה מתחייבת או מבוטלת.
הבה נתאר כל מצבי עסקה ב-SQL Server:
מצב פעיל: העסקה נמצאת במצב פעיל בזמן ביצוע הוראות העסקה. זה משתנה ל- 'מדינה מחויבת חלקית' אם כל פעולות 'קריאה וכתיבה' הושלמו ללא שגיאות. אם הוראה כלשהי נכשלת, היא משתנה ל'מצב נכשל'.
מחויב חלקית: כאשר כל פעולות הקריאה והכתיבה הושלמו, השינוי מתבצע בזיכרון הראשי או במאגר המקומי. המדינה תלך ל 'מדינה מחויבת' אם השינויים נעשו קבועים במסד הנתונים. אחרת, זה עובר ל'מצב הכושל'.
מצב נכשל: עסקה עוברת למצב נכשל כאשר הוראה של עסקה כלשהי נכשלת או שינוי קבוע במסד הנתונים נכשל.
מצב שהופסק: העסקה עוברת מא 'מצב כושל' ל 'מדינה שהופסקה' כאשר מתרחש כשל כלשהו. השינויים מוסרים או חוזרים לאחור מכיוון ששינויים אלה מבוצעים רק במאגר המקומי או בזיכרון הראשי במצבים קודמים.
מדינה מחויבת: עסקה הושלמה ונכנסת למצב זה כאשר השינויים נעשים קבועים במסד הנתונים ומסתיימים ב- 'מצב סיום'.
מדינה שהופסקה: אם אין החזרה והעסקה נמצאת ב- 'מדינה מחויבת' המערכת עקבית ומוכנה לעסקה חדשה בעוד שהישנה מסתיימת.
יישום עסקה ב-SQL Server
הבה ניקח כמה דוגמאות כדי להבין כיצד אנו יכולים ליישם את העסקה ב-SQL Server. כאן נשתמש ב- 'מוצר' טבלה כדי להדגים את כל מצבי העסקאות.
סקריפטים של SQL הבאים יוצרים את טבלת המוצר במסד הנתונים שנבחר:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
לאחר מכן, בצע את הסקריפטים שלהלן כדי להכניס נתונים לטבלה זו:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
בצע את המשפט SELECT כדי לאמת את הנתונים:
דוגמה לעסקת COMMIT
מומלץ לחלק את הצהרות SQL המשמשות בטרנזקציה למספר חלקים לוגיים. ואז, נוכל להחליט אם להתחייב או להחזיר את הנתונים לאחור. השלבים הבאים ממחישים ליצירת עסקה:
- התחל את העסקה באמצעות התחל בעסקה פקודה.
- כתוב את הצהרות ה-SQL וחלק אותם בהתאם לצרכים שלנו
- להשתמש ב לְבַצֵעַ הצהרה כדי להשלים את העסקה ולשמור את השינויים לצמיתות.
להלן הפקודות המסבירות את פעולות ה-COMMIT ב-SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
אם לא נמצאה שגיאה, נראה את הפלט הבא שבו כל משפט SQL של עסקה מבוצע באופן עצמאי:
לא ניתן להחזיר את ההצהרות INSERT ו-UPDATE לאחר ביצוע העסקה. כאשר אנו מאמתים את הטבלה לאחר פעולת ה-commit, נראה את הנתונים הבאים:
דוגמה לעסקת ROLLBACK
נשתמש בפקודה ROLLBACK כדי לבטל את כל העסקאות שעדיין לא נשמרו במסד הנתונים ולחזור לנקודה שבה החלה העסקה. הדוגמה הבאה מסבירה את פעולת ה-ROLLBACK ב-SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
ברגע שנבצע את העסקה הנ'ל, נוכל לראות שהיא תתבצע בהצלחה. עם זאת, זה לא ישפיע על כל שינוי במסד הנתונים כי עד שלא נבצע את הצהרת COMMIT או ROLLBACK, השינויים לא יכולים להפוך קבועים. לכן, יש לנו אפשרות להשתמש בפקודה ROLLBACK טרנזקציות כדי להחזיר את כל פעולות מסד הנתונים לאחור. להלן הצהרת העסקה המלאה:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
שימוש במשתנה הגלובלי @@Error בעסקאות:
המשתנה הזה הוא משמש כדי לבדוק אם יש שגיאה או לא. הדוגמה שלהלן מסבירה את הרעיון שלה. כאן נתחיל תחילה את העסקה באמצעות הפקודה BEGIN ולאחר מכן נכתוב שני הצהרות insert. לאחר מכן, נשתמש במשתנה המערכת הגלובלית @@שְׁגִיאָה בתוך ה הצהרת IF כדי לבדוק שגיאה. אם הערך גדול מ-0, זה אומר שיש שגיאה כלשהי. כעת, העסקה חוזרת לאחור; אחרת, העסקה מתבצעת.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
כאשר העסקה שלעיל מבוצעת, נבחין שהיא הוחזרה לאחור. זה נובע מהניסיון שלנו להוסיף ערך כפול בעמודת המפתח הראשי.
עסקת ביטול אוטומטי
רוב העסקאות מכילות יותר משאילתה אחת. בזמן ביצוע העסקה, אם אחת מהמשפטים של SQL מייצרת שגיאה, לא מתרחשים שינויים במסד הנתונים, ושאר ההצהרות לא מבוצעות. מושג זה ידוע כ-Auto Rollback Transaction ב-SQL Server. בואו נשתמש בדוגמה פשוטה כדי להדגים את התהליך הזה.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
עסקה זו מייצרת את הפלט הבא:
בפלט זה, אנו יכולים לראות שהצהרת ה-insert בוצעה בהצלחה. עם זאת, ביצוע הצהרת העדכון מצאה שגיאה עקב בעיית המרת סוג הנתונים. במקרה זה, ה-SQL Server אינו מאפשר שינויים במסד הנתונים, כלומר פעולת ה-insert אינה מוסיפה ערך כלשהו, והמשפט select אינו מבוצע.
נקודת שמירה בעסקאות
נקודת השמירה מכניסה סימון מיוחד לעסקה המאפשרת לנו לבטל את כל השינויים שבוצעו לאחר נקודת השמירה. הוא משמש גם להחזרת כל חלק ספציפי של העסקה ולא את כל העסקה. אנחנו יכולים להגדיר את זה על ידי שימוש ב- SAVE TRANSACTION sp_name הַצהָרָה. הדוגמה הבאה תסביר את השימוש ב-savepoint בטרנזקציות שמבצעות את הצהרת ה-insert ומחזירה את הצהרת המחיקה.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
ראה את התוצאה שלהלן שבה אנו יכולים לראות את מזהה המוצר 116 נמחק, ו-117 מוכנס בפלט הראשון. עם זאת, בפלט השני, פעולת המחיקה מתבטלת בגלל נקודת השמירה.
כיצד לשחרר נקודת שמירה בעסקה?
נקודת שמירת שחרור משמשת להסרת נקודת השמירה בעלת השם מהטרנזקציה הנוכחית מבלי להחזיר את התוצאות של שאילתות שבוצעו לאחר נקודת השמירה. ל-MySQL יש את הפקודה הזו, אבל SQL Server לא מספק שום פקודה לשחרור נקודת שמירה. במקום זאת, הם משוחררים אוטומטית בתום עסקת התחייבות או החזרה לאחור, כך שאיננו צריכים לדאוג לגביהם ביניים.
עסקה משתמעת ב-SQL Server
אנו יכולים להגדיר עסקה משתמעת על ידי הפעלת האפשרות IMPLICIT_TRANSACTIONS. הדוגמה הבאה תסביר את המושג הזה בקלות:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
בעסקה זו השתמשנו בשתי אפשרויות @@OPTION ו-@@TRANCOUNT. ה-@@OPTOPN מספק את המידע לגבי אפשרויות ה-SET הנוכחיות, ו-@@TRANCOUNT מספק את הצהרת BEGIN TRANSACTION בהפעלה הנוכחית.
כעת, ביצוע העסקה יחזיר את הפלט שלהלן:
עסקה מפורשת ב-SQL Server
יש להגדיר עסקה מפורשת באמצעות הפקודה BEGIN TRANSACTION מכיוון שהיא מזהה את נקודת ההתחלה של העסקה המפורשת. אנו יכולים להגדיר את העסקה המפורשת ב-SQL Server כדלקמן:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
בתחביר, האפשרות trans_name מציינת שם ייחודי של עסקה. ה @trans_name_var מציין משתנה המוגדר על ידי המשתמש המאחסן את שם העסקה. סוף - סוף, ה סימן אפשרות מאפשרת לנו לסמן עסקה ספציפית בקובץ היומן.
העסקה המפורשת דרך הפקודה BEGIN TRANSACTION קיבלה נעילה בהתאם לרמת הבידוד של המשאבים הקשורים לעסקה. זה עוזר להפחית בעיות נעילה. ראה את הדוגמה שלהלן:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
הנה הפלט:
עסקה מסומנת ב-SQL Server
העסקה המסומנת משמשת להוספת תיאור לעסקה ספציפית בקובצי היומן. אנו יכולים להשתמש בו כנקודת שחזור במקום תאריך ושעה בעת שחזור מסד נתונים למצב קודם. עלינו לדעת שהסימן מתווסף בקובצי היומן רק כאשר העסקה המסומנת משנה את מסד הנתונים. אנו יכולים להבין את הרעיון שלו באמצעות הדוגמה הבאה.
נניח ששינינו את מסד הנתונים בטעות, ואיננו יודעים את הרגע המדויק של שינוי הנתונים; במקרה כזה, שחזור נתונים עשוי להימשך זמן רב. עם זאת, אם נשתמש בעסקאות המסומנות, זה יכול להיות כלי שימושי לקביעת התזמון המדויק של שינויים בנתונים.
התחביר הבא ממחיש את העסקה המסומנת ב-SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
כאן עלינו להגדיר את שם העסקה ולאחר מכן להוסיף אפשרות WITH MARK. בדוגמה להלן, אנו הולכים למחוק רשומות ולהוסיף את הסימן בקובץ היומן:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
ה היסטוריה של logmark הטבלה כלולה ב מסד נתונים msdb ושומר מידע לגבי כל עסקה מסומנת שבוצעה. בצע את ההצהרה שלהלן כדי לקבל את הפרטים מטבלת logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
בשם עסקה ב-SQL Server
אנחנו יכולים גם לספק שם לעסקה שלנו ב-SQL Server. תמיד מומלץ להשתמש בטרנזקציה בעלת השם כאשר עובדים עם עסקאות רבות בשאילתה אחת. הדוגמה שלהלן מסבירה כיצד לשנות שם של עסקה:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
הנה הפלט:
סיכום
מאמר זה ייתן סקירה מלאה של העסקה בהצהרות SQL Server. עסקאות מועילות במערכות מסדי נתונים יחסיים מכיוון שהן מבטיחות שלמות מסד הנתונים.