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 mode sesi dinonaktifkan, jika terjadi error selama transaksi dan tidak ada pengendali pengecualian, kueri akan gagal dan BigQuery akan otomatis me-roll back transaksi.

Jika mode sesi diaktifkan dan tidak ada pengendali pengecualian, transaksi akan tetap aktif hingga sesi dihentikan atau di-roll back secara eksplisit. Terlepas dari mode sesi, sebaiknya tambahkan pengendali pengecualian untuk memastikan transaksi di-roll back dengan benar sesegera mungkin agar tidak menahan kunci dan resource lebih lama dari yang diperlukan.

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 memperbarui atau menghapus baris dalam tabel, transaksi tersebut akan memperoleh kunci tingkat tabel, bukan mengunci partisi individual. Transaksi atau pernyataan DML lain yang mengubah baris dalam tabel yang sama tidak dapat berjalan secara bersamaan, meskipun menargetkan partisi yang berbeda dari tabel berpartisi. BigQuery membatalkan transaksi yang bertentangan. Namun, BigQuery mengantrekan pernyataan DML yang bertentangan yang berjalan di luar transaksi untuk dijalankan nanti, bergantung pada batas antrean.

Peringatan: BigQuery otomatis mengantrekan hingga 20 pernyataan DML bermutasi mandiri per tabel. Namun, transaksi multi-pernyataan memiliki batas konkurensi 1 transaksi mutasi per tabel, dan BigQuery tidak mengantrekannya. Jika transaksi multi-pernyataan serentak mencoba mengubah tabel yang terkunci, transaksi tersebut akan mengabaikan antrean dan langsung gagal dengan pesan error berikut: Transaction is aborted due to concurrent update against table.

Saat Anda menjalankan pernyataan DML yang bermutasi di dalam transaksi multi-pernyataan, BigQuery akan menahan kunci tulis pada tabel target hingga transaksi di-commit atau di-roll back, atau hingga kueri atau sesi multi-pernyataan induk berakhir. Perilaku ini berlaku untuk transaksi dalam kueri multi-pernyataan, prosedur tersimpan, atau sesi aktif, seperti koneksi ODBC atau JDBC. BigQuery tidak melepaskan kunci saat pernyataan DML individual selesai. Komputasi yang berjalan lama dalam transaksi terbuka yang sama membuat tabel tetap terkunci dan menyebabkan transaksi mutasi serentak pada tabel tersebut gagal.

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 karena pernyataan INSERT tidak bertentangan satu sama lain. Untuk mencegah beberapa transaksi yang seharusnya hanya menyisipkan baris agar tidak berjalan secara serentak pada tabel, mulai setiap transaksi dengan pernyataan DELETE yang tidak mengubah baris, seperti DELETE FROM TABLE_NAME WHERE FALSE;. Menyertakan pernyataan ini akan menyebabkan BigQuery memperoleh kunci tingkat tabel pada pernyataan DELETE, sehingga transaksi serentak lainnya yang juga menjalankan pernyataan mutasi pada tabel tersebut akan mengalami konflik dan dibatalkan.

Menghindari dan menyelesaikan konflik konkurensi

Untuk mengurangi konflik konkurensi transaksi, gunakan praktik berikut:

• Hapus wrapper transaksi yang tidak diperlukan: jika prosedur tersimpan atau kueri multi-pernyataan hanya menjalankan satu pernyataan DML yang mengubah, seperti pernyataan MERGE, jangan gabungkan pernyataan tersebut dalam pernyataan BEGIN TRANSACTION dan COMMIT TRANSACTION. Satu pernyataan DML sudah bersifat atomik. Menjalankan pernyataan di luar transaksi multi-pernyataan memungkinkan BigQuery menerapkan antrean DML.
• Coba lagi transaksi yang gagal dengan backoff eksponensial: saat transaksi multi-pernyataan gagal karena update serentak, BigQuery akan me-roll back transaksi dan melepaskan kunci tabel. Karena BigQuery dapat memerlukan waktu beberapa detik untuk melepaskan kunci di lapisan metadata, coba lagi transaksi yang gagal menggunakan strategi backoff eksponensial. Tunggu minimal 60 detik sebelum percobaan ulang pertama.

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 mendapatkan transaksi yang terkait dengan ID pekerjaan 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 tersebut juga akan menampilkan ID pekerjaan induk. Jika transaksi berjalan dalam sesi, transaksi 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');

Menemukan tugas yang tumpang-tindih untuk transaksi yang gagal

Jika transaksi atau pernyataan DML gagal dengan error update serentak, pemeriksaan histori kueri dari setiap pernyataan DML mungkin tidak menampilkan pernyataan DML yang aktif pada saat kegagalan. Situasi ini terjadi saat kueri atau sesi multi-pernyataan induk menjalankan pernyataan DML sebelumnya di dalam transaksi dan terus menjalankan pernyataan lain sambil menahan kunci tabel.

Untuk workload lintas project, Anda dapat membuat kueri tabel virtual INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION, bukan INFORMATION_SCHEMA.JOBS_BY_PROJECT. Kueri berikut menggabungkan tugas DML turunan dengan tugas induknya di INFORMATION_SCHEMA.JOBS_BY_PROJECT untuk mengidentifikasi tugas, skrip, atau sesi mutasi yang tumpang-tindih dengan tugas yang gagal:

SELECT
  child_jobs.job_id,
  child_jobs.parent_job_id,
  child_jobs.transaction_id,
  child_jobs.session_info.session_id,
  child_jobs.statement_type,
  child_jobs.start_time AS statement_start_time,
  child_jobs.end_time AS statement_end_time,
  parent_jobs.start_time AS parent_start_time,
  parent_jobs.end_time AS parent_end_time,
  COALESCE(parent_jobs.start_time, child_jobs.start_time) AS lock_window_start,
  COALESCE(
    parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP()
  ) AS lock_window_end,
  child_jobs.error_result.message AS error_message,
  child_jobs.query
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS child_jobs
LEFT JOIN
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS parent_jobs
  ON child_jobs.parent_job_id = parent_jobs.job_id
WHERE
  child_jobs.creation_time
    BETWEEN TIMESTAMP_SUB(TIMESTAMP 'START_TIME', INTERVAL 6 HOUR)
    AND TIMESTAMP_ADD(TIMESTAMP 'END_TIME', INTERVAL 1 HOUR)
  AND child_jobs.destination_table.project_id = 'PROJECT_ID'
  AND child_jobs.destination_table.dataset_id = 'DATASET_NAME'
  AND child_jobs.destination_table.table_id = 'TABLE_NAME'
  AND child_jobs.statement_type IN (
    'UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE'
  )
  AND COALESCE(parent_jobs.start_time, child_jobs.start_time)
    <= TIMESTAMP 'END_TIME'
  AND COALESCE(parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP())
    >= TIMESTAMP 'START_TIME'
ORDER BY
  lock_window_start;

Ganti kode berikut:

• REGION: region set data (misalnya, us).
• START_TIME: stempel waktu start_time dari tugas yang gagal (misalnya, 2026-06-15 10:00:00 UTC).
• END_TIME: stempel waktu end_time dari tugas yang gagal (misalnya, 2026-06-15 10:05:00 UTC).
• PROJECT_ID: ID project yang berisi tabel target.
• DATASET_NAME: nama set data yang berisi tabel target.
• TABLE_NAME: nama tabel target.

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.

• Metadata untuk sumber data eksternal tidak dapat dimuat ulang dalam transaksi menggunakan prosedur sistem.