טרנזקציות עם כמה הצהרות

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

אפשר להשתמש בטרנזקציות עם כמה הצהרות כדי:

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

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

היקף העסקה

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

כדי להתחיל עסקה, משתמשים בהצהרה BEGIN TRANSACTION. העסקה מסתיימת כשאחד מהמקרים הבאים מתרחש:

• השאילתה מריצה הצהרת COMMIT TRANSACTION. ההצהרה הזו מבצעת באופן אטומי את כל השינויים שנעשו בתוך הטרנזקציה.
• השאילתה מריצה הצהרת ROLLBACK TRANSACTION. ההצהרה הזו מבטלת את כל השינויים שבוצעו בטרנזקציה.
• השאילתה מסתיימת לפני שהיא מגיעה לאחת משתי ההצהרות האלה. במקרה כזה, BigQuery מבצעת אוטומטית החזרה של הטרנזקציה למצב הקודם.

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

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

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

בדוגמה הבאה מוצג handler של חריגות שמבטל טרנזקציה:

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

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

אלה סוגי ההצהרות שנתמכים בטרנזקציות:

• הצהרות שאילתה: SELECT
• פקודות DML: ‏ INSERT,‏ UPDATE,‏ DELETE,‏ MERGE ו-TRUNCATE TABLE

• הצהרות DDL על ישויות זמניות:

  • CREATE TEMP TABLE
  • CREATE TEMP FUNCTION
  • ‫DROP TABLE בטבלה זמנית
  • ‫DROP FUNCTION בפונקציה זמנית

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

פונקציות של תאריך/שעה בעסקאות

בטרנזקציה, לפונקציות התאריך/השעה הבאות יש התנהגויות מיוחדות:

• הפונקציות CURRENT_TIMESTAMP,‏ CURRENT_DATE ו-CURRENT_TIME מחזירות את חותמת הזמן של תחילת הטרנזקציה.

• אי אפשר להשתמש בסעיף FOR SYSTEM_TIME AS OF כדי לקרוא טבלה מעבר לחותמת הזמן שבה התחילה העסקה. אם תנסו לעשות את זה, תקבלו הודעת שגיאה.

דוגמה לעסקה

בדוגמה הזו אנחנו מניחים שיש שתי טבלאות בשם Inventory ו-NewArrivals, שנוצרו באופן הבא:

CREATE OR REPLACE TABLE mydataset.Inventory
(
 product string,
 quantity int64,
 supply_constrained bool
);

CREATE OR REPLACE TABLE mydataset.NewArrivals
(
 product string,
 quantity int64,
 warehouse string
);

INSERT mydataset.Inventory (product, quantity)
VALUES('top load washer', 10),
     ('front load washer', 20),
     ('dryer', 30),
     ('refrigerator', 10),
     ('microwave', 20),
     ('dishwasher', 30);

INSERT mydataset.NewArrivals (product, quantity, warehouse)
VALUES('top load washer', 100, 'warehouse #1'),
     ('dryer', 200, 'warehouse #2'),
     ('oven', 300, 'warehouse #1');

הטבלה Inventory מכילה מידע על המלאי הנוכחי, והטבלה NewArrivals מכילה מידע על פריטים חדשים שהגיעו.

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

BEGIN TRANSACTION;

-- Create a temporary table that holds new arrivals from 'warehouse #1'.
CREATE TEMP TABLE tmp
  AS SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the NewArravals table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the records from the temporary table into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

-- Drop the temporary table and commit the transaction.
DROP TABLE tmp;

COMMIT TRANSACTION;

בו-זמניות של עסקאות

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

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

• ‫SELECT דפי חשבון
• פעולות קריאה של BigQuery Storage Read API
• שאילתות מ-BigQuery BI Engine
• ‫INSERT דפי חשבון
• משימות טעינה שמשתמשות בנטייה WRITE_APPEND כדי להוסיף שורות
• כתיבה בסטרימינג

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

צפייה בפרטי העסקאות

‫BigQuery מקצה מזהה עסקה לכל עסקה עם כמה הצהרות. מזהה העסקה מצורף לכל שאילתה שמתבצעת בתוך העסקה. כדי לראות את מזהי העסקאות של המשימות, שולחים שאילתה לתצוגות INFORMATION_SCHEMA.JOBS* בעמודה transaction_id.

כשמריצים טרנזקציה עם כמה הצהרות, BigQuery יוצר משימת צאצא לכל הצהרה בטרנזקציה. לכל טרנזקציה, לכל משימת צאצא שמשויכת לטרנזקציה הזו יש את אותו ערך transaction_id.

בדוגמאות הבאות מוסבר איך למצוא מידע על העסקאות שלכם.

חיפוש כל העסקאות שבוצעו או בוטלו

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

SELECT transaction_id, parent_job_id, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "COMMIT_TRANSACTION" AND error_result IS NULL;

השאילתה הבאה מחזירה את כל הטרנזקציות שבוטל בהן השינוי.

SELECT
  transaction_id, parent_job_id, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "ROLLBACK_TRANSACTION" AND error_result IS NULL;

איך מוצאים את שעת ההתחלה ושעת הסיום של עסקה

השאילתה הבאה מחזירה את זמני ההתחלה והסיום של מזהה עסקה מסוים.

SELECT transaction_id, start_time, end_time, statement_type
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE transaction_id = "TRANSACTION_ID"
AND statement_type IN
  ("BEGIN_TRANSACTION", "COMMIT_TRANSACTION", "ROLLBACK_TRANSACTION")
ORDER BY start_time;

איתור הטרנזקציה שבה פועלת משימה

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

SELECT transaction_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'JOB_ID';

חיפוש המשימה הנוכחית שפועלת בתוך טרנזקציה

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

SELECT job_id, query, start_time, total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE transaction_id = 'TRANSACTION_ID' AND state = RUNNING;

חיפוש העסקאות הפעילות שמשפיעות על טבלה

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

WITH running_transactions AS (
  SELECT DISTINCT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  EXCEPT DISTINCT
  SELECT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    statement_type = 'COMMIT_TRANSACTION'
    OR statement_type = 'ROLLBACK_TRANSACTION'
)
SELECT
  jobs.transaction_id, parent_job_id, session_info, query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs, running_transactions
WHERE
  destination_table = ("PROJECT_NAME", "DATASET_NAME", "TABLE_NAME")
  AND jobs.transaction_id = running_transactions.transaction_id;

חיפוש העסקאות הפעילות שפועלות בעסקה עם כמה הצהרות

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

SELECT DISTINCT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
EXCEPT DISTINCT
SELECT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
  AND (statement_type = 'COMMIT_TRANSACTION'
       OR statement_type = 'ROLLBACK_TRANSACTION');

מגבלות

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

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

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

• ‫BI Engine לא מאיץ שאילתות בתוך טרנזקציה.

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