עסקאות עם כמה דפי תדפיס

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

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

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

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

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

היקף העסקה

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

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

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

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

אם מתרחשת שגיאה במהלך טרנזקציה ואין מטפל בחריגים, השאילתה נכשלת ו-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 לא מאיץ שאילתות בתוך טרנזקציה.

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