טרנזקציות ב-SQLite: הסבר על BEGIN, COMMIT ו-ROLLBACK

טרנזקציה היא חבילה של הכול או כלום

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

הדוגמה הקלאסית היא העברת כסף:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

שתי פקודות ה-UPDATE שייכות יחד. אם מסד הנתונים היה קורס ביניהן, ל-Ada היו 2000 סנט פחות ול-Boris לא היה שום דבר נוסף. עטיפה שלהן ב-BEGIN ... COMMIT הופכת את הזוג לאטומי: שתיהן קורות, או אף אחת.

Autocommit: ברירת המחדל שאתם כבר משתמשים בה

כל פקודת SQL שהרצתם עד עכשיו הייתה טרנזקציה. SQLite נמצא כברירת מחדל במצב autocommit: כל פקודה מקבלת BEGIN ו-COMMIT מובלעים סביבה.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

שלוש הכנסות, שלוש טרנזקציות נפרדות, שלוש פעמים גישה לדיסק כדי לבצע fsync לשינוי. זה בסדר לכתיבות בודדות אבל איטי לטעינות גדולות, וזה אומר שאי אפשר לבטל קבוצת פקודות כיחידה אחת. BEGIN מכבה את ה-autocommit עד ה-COMMIT או ה-ROLLBACK הבא.

ROLLBACK: להעמיד פנים שזה לא קרה

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

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

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

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

האצת הכנסות גדולות

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

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

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

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN מקבל מצב שקובע מתי SQLite לוקח נעילות:

• BEGIN DEFERRED (ברירת המחדל): אין נעילה בכלל עד שקוראים או כותבים. נעילת הכתיבה נלקחת בעצלות, בפקודת הכתיבה הראשונה.
• BEGIN IMMEDIATE: תופס את נעילת הכתיבה מיד. חיבורים אחרים עדיין יכולים לקרוא, אבל אף חיבור אחר לא יכול להתחיל לכתוב.
• BEGIN EXCLUSIVE: כמו IMMEDIATE, ובנוסף אף חיבור אחר לא יכול גם לקרוא. במצב WAL הוא מתנהג בדיוק כמו IMMEDIATE. ההבדל משנה רק במצב ה-rollback journal הישן יותר.

BEGIN DEFERRED;     -- same as plain BEGIN
BEGIN IMMEDIATE;    -- reserve the write lock now
BEGIN EXCLUSIVE;    -- reserve everything (rollback-journal mode)

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

BEGIN IMMEDIATE פותר את זה: אם אתם יודעים שתכתבו, בקשו קודם את נעילת הכתיבה. החיבור השני נחסם (או נכשל מהר) מיד, לפני שהוא עושה עבודה שהיה צריך לזרוק.

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

קריאה בתוך טרנזקציה רואה תמונת מצב

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

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, your own write is visible
COMMIT;

SELECT n FROM counter;

אתם רואים את הכתיבות שלכם שעוד לא נשמרו. חיבורים אחרים לא. ברגע שמבצעים COMMIT, הערך החדש נהיה גלוי לכולם. לזה מתכוונים כשאומרים ש-SQLite הוא serializable: אין כפתור של READ COMMITTED לסובב, כי ברירת המחדל היא כבר הרמה החזקה ביותר.

טרנזקציה בקוד אפליקציה

בתוכנית אמיתית, התבנית היא בדרך כלל try/except (או try/catch) סביב הגוף, עם ROLLBACK בנתיב השגיאה:

-- Pseudocode for any client library
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

רוב ספריות הלקוח (sqlite3 של Python, better-sqlite3 וכו') עוטפות את זה בשבילכם עם בלוק with או עם פונקציית עזר transaction(). כדאי לבדוק את התיעוד של הספרייה שלכם: ברירות המחדל לא תמיד מה שהייתם מצפים. ל-sqlite3 של Python במיוחד הייתה היסטורית התנהגות autocommit מוזרה. גרסאות חדשות הוסיפו פרמטר autocommit מסודר כדי לתקן את זה.

דברים שמכשילים אנשים

• DDL בתוך טרנזקציות עובד. CREATE TABLE, ALTER TABLE ואפילו DROP TABLE ניתנים לביטול. SQLite יוצא דופן בזה: מסדי נתונים רבים שומרים DDL אוטומטית.
• VACUUM לא יכול לרוץ בתוך טרנזקציה. גם כמה פקודות תחזוקה אחרות לא. הריצו אותן במצב autocommit.
• COMMIT שנכשל הוא כישלון אמיתי. אם COMMIT מחזיר SQLITE_BUSY (נדיר אבל אפשרי), הטרנזקציה לא נשמרה. הקוד שלכם צריך לטפל בזה, בדרך כלל בניסיון חוזר.
• טרנזקציות ארוכות חוסמות כותבים. טרנזקציה שנשארת פתוחה דקות תחסום כותבים אחרים לדקות. פתחו אותן מאוחר, סגרו אותן מהר.

הבא בתור: Savepoints

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