Transaksi multi-pernyataan

BigQuery mendukung transaksi multi-pernyataan di dalam satu kueri, atau di beberapa kueri saat menggunakan sesi. Dengan transaksi multi-pernyataan, Anda dapat melakukan operasi mutasi, seperti menyisipkan atau menghapus baris di satu atau beberapa tabel, serta meng-commit atau me-roll back perubahan secara atomik.

Penggunaan untuk transaksi multi-pernyataan mencakup:

• Melakukan mutasi DML pada beberapa tabel sebagai satu transaksi. Tabel dapat mencakup beberapa set data atau project.
• Melakukan mutasi pada satu tabel dalam beberapa tahap, berdasarkan komputasi menengah.

Transaksi menjamin properti ACID dan mendukung snapshot isolation. Selama transaksi, semua operasi baca akan menampilkan snapshot konsisten dari tabel yang dirujuk dalam transaksi tersebut. Jika pernyataan dalam transaksi mengubah tabel, perubahan tersebut akan terlihat oleh pernyataan berikutnya dalam transaksi yang sama.

Catatan: Pembacaan dari sumber data eksternal tidak dijamin konsisten dalam transaksi jika sumber data dasar berubah selama transaksi.

Cakupan transaksi

Suatu transaksi harus terdapat dalam satu kueri SQL, kecuali saat berada dalam Session mode. Kueri dapat berisi beberapa transaksi, tetapi tidak bisa disusun bertingkat. Anda dapat menjalankan transaksi multi-pernyataan di beberapa kueri dalam satu sesi.

Untuk memulai transaksi, gunakan pernyataan BEGIN TRANSACTION. Transaksi berakhir jika salah satu dari hal berikut terjadi:

• Kueri mengeksekusi pernyataan COMMIT TRANSACTION. Pernyataan ini meng-commit semua perubahan yang dilakukan di dalam transaksi secara atomik.
• Kueri mengeksekusi pernyataan ROLLBACK TRANSACTION. Pernyataan ini mengabaikan semua perubahan yang dilakukan di dalam transaksi.
• Kueri berakhir sebelum mencapai salah satu dari dua pernyataan ini. Dalam hal ini, BigQuery akan me-roll back transaksi secara otomatis.

Jika terjadi error selama transaksi dan kueri memiliki pengendali pengecualian, BigQuery akan mentransfer kontrol ke pengendali pengecualian. Di dalam blok pengecualian, Anda dapat memilih apakah akan meng-commit atau me-roll back transaksi.

Jika terjadi error selama transaksi dan tidak ada pengendali pengecualian, kueri akan gagal dan BigQuery akan otomatis me-roll back transaksi.

Contoh berikut menunjukkan pengendali pengecualian yang me-roll back transaksi:

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;

Laporan yang didukung dalam transaksi

Jenis pernyataan berikut didukung dalam transaksi:

• Pernyataan kueri: SELECT
• Pernyataan DML: INSERT, UPDATE, DELETE, MERGE, dan TRUNCATE TABLE

• Pernyataan DDL tentang entity sementara:

  • CREATE TEMP TABLE
  • CREATE TEMP FUNCTION
  • DROP TABLE di tabel sementara
  • DROP FUNCTION di fungsi sementara

Pernyataan DDL yang membuat atau menghapus entity permanen, seperti set data, tabel, dan fungsi, tidak didukung di dalam transaksi.

Fungsi tanggal/waktu dalam transaksi

Dalam transaksi, fungsi tanggal/waktu berikut memiliki perilaku khusus:

• Fungsi CURRENT_TIMESTAMP, CURRENT_DATE, dan CURRENT_TIME menampilkan stempel waktu saat transaksi dimulai.

• Anda tidak dapat menggunakan klausa FOR SYSTEM_TIME AS OF untuk membaca tabel setelah stempel waktu saat transaksi dimulai. Tindakan ini akan menampilkan error.

Contoh transaksi

Contoh ini mengasumsikan bahwa ada dua tabel bernama Inventory dan NewArrivals, yang dibuat sebagai berikut:

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');

Tabel Inventory berisi informasi tentang inventaris saat ini, dan NewArrivals berisi informasi tentang item yang baru tiba.

Transaksi berikut memperbarui Inventory dengan kedatangan baru dan menghapus data yang sesuai dari NewArrivals. Dengan asumsi bahwa semua pernyataan berhasil diselesaikan, perubahan di kedua tabel akan di-commit secara atomik sebagai satu transaksi.

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;

Konkurensi transaksi

Jika transaksi mengubah (memperbarui atau menghapus) baris dalam tabel, transaksi atau pernyataan DML lain yang mengubah baris dalam tabel yang sama tidak dapat berjalan secara bersamaan. Transaksi yang bertentangan akan dibatalkan. Pernyataan DML bertentangan yang berjalan di luar transaksi akan dimasukkan ke dalam antrean untuk dijalankan nanti, bergantung pada batas antrean.

Operasi yang membaca atau menambahkan baris baru dapat berjalan serentak dengan transaksi. Misalnya, salah satu operasi berikut dapat dilakukan secara serentak di tabel saat transaksi mengubah data di tabel yang sama:

• laporan SELECT
• Operasi baca BigQuery Storage Read API
• Kueri dari BigQuery BI Engine
• laporan INSERT
• Tugas pemuatan yang menggunakan disposisi WRITE_APPEND untuk menambahkan baris
• Penulisan streaming

Jika transaksi hanya membaca tabel atau menambahkan baris baru ke tabel, operasi apa pun dapat dijalankan secara serentak di tabel tersebut.

Melihat informasi transaksi

BigQuery menetapkan ID transaksi ke setiap transaksi multi-pernyataan. ID transaksi dilampirkan di setiap kueri yang dieksekusi di dalam transaksi. Untuk melihat ID transaksi tugas Anda, buat kueri tampilan INFORMATION_SCHEMA.JOBS* untuk kolom transaction_id.

Saat transaksi multi-pernyataan berjalan, BigQuery akan membuat tugas turunan untuk setiap pernyataan dalam transaksi. Untuk transaksi tertentu, setiap tugas turunan yang terkait dengan transaksi tersebut akan memiliki nilai transaction_id yang sama.

Contoh berikut menunjukkan cara menemukan informasi transaksi Anda.

Menemukan semua transaksi yang di-commit atau di-roll back

Kueri berikut menampilkan semua transaksi yang berhasil di-commit.

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;

Kueri berikut menampilkan semua transaksi yang berhasil di-roll back.

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;

Menemukan waktu mulai dan berakhir transaksi

Kueri berikut menampilkan waktu mulai dan berakhir untuk ID transaksi yang ditentukan.

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;

Menemukan transaksi tempat tugas dijalankan

Kueri berikut membuat transaksi yang dikaitkan dengan ID tugas tertentu. Metode ini menampilkan NULL jika tugas tidak berjalan di dalam transaksi multi-pernyataan.

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

Menemukan tugas saat ini yang berjalan di dalam transaksi

Kueri berikut menampilkan informasi tugas yang sedang berjalan di dalam transaksi yang ditentukan, jika ada.

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;

Menemukan transaksi aktif yang memengaruhi tabel

Kueri berikut menampilkan transaksi aktif yang memengaruhi tabel yang ditentukan. Untuk setiap transaksi aktif, jika transaksi berjalan sebagai bagian dari kueri multi-pernyataan seperti dalam prosedur tersimpan, transaksi juga akan menampilkan ID tugas induk. Jika transaksi berjalan dalam sesi, transaksi tersebut juga akan menampilkan info sesi.

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;

Menemukan transaksi aktif yang berjalan di transaksi multi-pernyataan

Kueri berikut menampilkan transaksi aktif untuk tugas tertentu yang ditentukan oleh ID tugas yang menjalankan transaksi multi-pernyataan.

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');

Batasan

• Transaksi tidak dapat menggunakan pernyataan DDL yang memengaruhi entitas permanen.
• Di dalam transaksi, tampilan terwujud ditafsirkan sebagai tampilan logis. Anda masih dapat membuat kueri pada tampilan terwujud di dalam transaksi, tetapi tidak akan menghasilkan peningkatan performa atau pengurangan biaya dibandingkan dengan tampilan logis yang setara.

• Transaksi multi-pernyataan yang gagal akan memicu operasi rollback, yang mengurungkan semua perubahan yang tertunda dan mencegah percobaan ulang.

• Satu transaksi dapat mengubah data maksimal 100 tabel dan dapat melakukan maksimal 100.000 modifikasi partisi.

• BI Engine tidak mempercepat kueri di dalam transaksi.