Menggunakan SELECT FOR UPDATE dalam isolasi serialisabel

Halaman ini menjelaskan cara menggunakan klausa FOR UPDATE dalam isolasi yang dapat diserialisasi.

Mekanisme penguncian klausa FOR UPDATE berbeda untuk repeatable read dan serializable isolation. Dengan menggunakan isolasi yang dapat diserialisasi, saat Anda menggunakan kueri SELECT untuk memindai tabel, menambahkan klausa FOR UPDATE akan mengaktifkan kunci eksklusif di persimpangan tingkat perincian baris dan kolom, atau dikenal sebagai tingkat sel. Kunci tetap ada selama masa pakai transaksi baca-tulis. Selama waktu ini, klausa FOR UPDATE mencegah transaksi lain mengubah sel yang dikunci hingga transaksi saat ini selesai.

Untuk mempelajari cara menggunakan klausa FOR UPDATE, lihat panduan referensi GoogleSQL dan PostgreSQL FOR UPDATE.

Alasan menggunakan klausa FOR UPDATE

Di database dengan tingkat isolasi yang kurang ketat, klausa FOR UPDATE mungkin diperlukan untuk memastikan bahwa transaksi serentak tidak memperbarui data antara pembacaan data dan commit transaksi. Karena Spanner menerapkan serialisabilitas secara default, transaksi dijamin hanya berhasil di-commit jika data yang diakses dalam transaksi tidak basi pada waktu commit. Oleh karena itu, klausa FOR UPDATE tidak diperlukan untuk memastikan kebenaran transaksi di Spanner.

Namun, dalam kasus penggunaan dengan pertentangan penulisan yang tinggi, seperti saat beberapa transaksi secara bersamaan membaca dan menulis ke data yang sama, transaksi serentak dapat menyebabkan peningkatan pembatalan. Hal ini karena ketika beberapa transaksi serentak memperoleh kunci bersama, lalu mencoba mengupgrade ke kunci eksklusif, transaksi tersebut menyebabkan kebuntuan. Deadlock memblokir transaksi secara permanen karena setiap transaksi menunggu transaksi lain melepaskan resource yang dibutuhkan. Untuk membuat progres, Spanner membatalkan semua transaksi kecuali satu transaksi untuk menyelesaikan kebuntuan. Untuk mengetahui informasi selengkapnya, lihat Penguncian.

Transaksi yang menggunakan klausa FOR UPDATE akan mendapatkan kunci eksklusif secara proaktif dan melanjutkan eksekusi, sementara transaksi lain menunggu giliran untuk mendapatkan kunci. Meskipun Spanner mungkin masih membatasi throughput karena transaksi yang bertentangan hanya dapat dilakukan satu per satu, tetapi karena Spanner hanya membuat progres pada satu transaksi, Spanner menghemat waktu yang seharusnya digunakan untuk membatalkan dan mencoba ulang transaksi.

Oleh karena itu, jika mengurangi jumlah transaksi yang dibatalkan dalam skenario permintaan penulisan serentak penting, Anda dapat menggunakan klausa FOR UPDATE untuk mengurangi jumlah pembatalan secara keseluruhan dan meningkatkan efisiensi eksekusi workload.

Perbandingan dengan petunjuk LOCK_SCANNED_RANGES

Klausa FOR UPDATE memiliki fungsi yang mirip dengan petunjuk LOCK_SCANNED_RANGES=exclusive.

Ada dua perbedaan utama:

  • Jika Anda menggunakan petunjuk LOCK_SCANNED_RANGES, transaksi akan mendapatkan kunci eksklusif pada rentang yang dipindai untuk seluruh pernyataan. Anda tidak dapat memperoleh kunci eksklusif pada subkueri. Menggunakan petunjuk kunci dapat mengakibatkan perolehan lebih banyak kunci daripada yang diperlukan dan berkontribusi pada persaingan kunci dalam workload. Contoh berikut menunjukkan cara menggunakan petunjuk kunci:

    @{lock_scanned_ranges=exclusive}
SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
AS a ON a.SingerId = s.SingerId;

    Di sisi lain, Anda dapat menggunakan klausa FOR UPDATE dalam subkueri seperti yang ditunjukkan dalam contoh berikut:

    SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE AS a ON a.SingerId = s.SingerId;

  • Anda dapat menggunakan petunjuk LOCK_SCANNED_RANGES dalam pernyataan DML, sedangkan Anda hanya dapat menggunakan klausa FOR UPDATE dalam pernyataan SELECT.

Semantik kunci

Untuk mengurangi permintaan penulisan serentak dan biaya transaksi yang dibatalkan akibat kebuntuan, Spanner mengunci data di tingkat sel jika memungkinkan. Tingkat sel adalah tingkat data paling terperinci dalam tabel - titik data di persimpangan baris dan kolom. Saat menggunakan klausa FOR UPDATE, Spanner mengunci sel tertentu yang dipindai oleh kueri SELECT.

Dalam contoh berikut, sel MarketingBudget di baris SingerId = 1 dan AlbumId = 1 dikunci secara eksklusif dalam tabel Albums, sehingga mencegah transaksi serentak mengubah sel tersebut hingga transaksi ini di-commit atau di-roll back. Namun, transaksi serentak masih dapat memperbarui sel AlbumTitle di baris tersebut.

SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1
FOR UPDATE;

Transaksi serentak mungkin diblokir saat membaca data yang dikunci

Jika satu transaksi telah memperoleh kunci eksklusif pada rentang yang dipindai, transaksi serentak dapat memblokir pembacaan data tersebut. Spanner menerapkan serialisabilitas sehingga data hanya dapat dibaca jika dijamin tidak berubah oleh transaksi lain dalam masa aktif transaksi. Transaksi serentak yang mencoba membaca data yang sudah dikunci mungkin harus menunggu hingga transaksi yang memegang kunci di-commit, di-roll back, atau waktunya habis.

Dalam contoh berikut, Transaction 1 mengunci sel MarketingBudget untuk 1 <= AlbumId < 5.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Transaction 2, yang mencoba membaca MarketingBudget untuk AlbumId = 1, diblokir hingga Transaction 1 di-commit atau di-roll back.

-- Transaction 2
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1;

-- Blocked by Transaction 1

Demikian pula, transaksi yang mencoba mengunci rentang yang dipindai dengan FOR UPDATE akan diblokir oleh transaksi serentak yang mengunci rentang yang dipindai yang tumpang-tindih.

Transaction 3 dalam contoh berikut juga diblokir karena Transaction 1 telah mengunci sel MarketingBudget untuk 3 <= AlbumId < 5, yang merupakan rentang yang dipindai yang tumpang-tindih dengan Transaction 3.

-- Transaction 3
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 3 and AlbumId < 10
FOR UPDATE;

-- Blocked by Transaction 1

Membaca indeks

Pembacaan serentak mungkin tidak diblokir jika kueri yang mengunci rentang yang dipindai mengunci baris dalam tabel dasar, tetapi transaksi serentak membaca dari indeks.

Transaction 1 berikut mengunci sel SingerId dan SingerInfo untuk SingerId = 1.

-- Transaction 1
SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = 1
FOR UPDATE;

Transaction 2 hanya baca tidak diblokir oleh kunci yang diperoleh di Transaction 1, karena mengkueri tabel indeks.

-- Transaction 2
SELECT SingerId FROM Singers;

Transaksi serentak tidak memblokir operasi DML pada data yang sudah dikunci

Jika satu transaksi telah memperoleh kunci pada rentang sel dengan petunjuk kunci eksklusif, transaksi serentak yang mencoba melakukan penulisan tanpa membaca data terlebih dahulu pada sel yang dikunci dapat dilanjutkan. Transaksi diblokir pada commit hingga transaksi yang memegang kunci di-commit atau di-roll back.

Transaction 1 berikut mengunci sel MarketingBudget untuk 1 <= AlbumId < 5.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Jika Transaction 2 mencoba memperbarui tabel Albums, Transaction 2 akan diblokir untuk melakukannya hingga Transaction 1 melakukan commit atau roll back.

-- Transaction 2
UPDATE Albums
SET MarketingBudget = 200000
WHERE SingerId = 1 and AlbumId = 1;

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Baris dan celah yang ada dikunci saat rentang yang dipindai dikunci

Jika satu transaksi telah memperoleh kunci eksklusif pada rentang yang dipindai, transaksi serentak tidak dapat menyisipkan data di celah dalam rentang tersebut.

Transaction 1 berikut mengunci sel MarketingBudget untuk 1 <= AlbumId < 10.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 10
FOR UPDATE;

Jika Transaction 2 mencoba menyisipkan baris untuk AlbumId = 9 yang belum ada, Transaction 2 akan diblokir untuk melakukannya hingga Transaction 1 melakukan commit atau roll back.

-- Transaction 2
INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 9, "Hello hello!", 10000);

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Peringatan akuisisi kunci

Semantik kunci yang dijelaskan memberikan panduan umum, tetapi tidak menjamin cara tepat kunci dapat diperoleh saat Spanner menjalankan transaksi yang menggunakan klausa FOR UPDATE. Mekanisme pengoptimalan kueri Spanner juga dapat memengaruhi kunci mana yang diperoleh. Klausul ini mencegah transaksi lain mengubah sel yang dikunci hingga transaksi saat ini selesai.

Sintaksis kueri

Bagian ini memberikan panduan tentang sintaksis kueri saat menggunakan klausa FOR UPDATE.

Penggunaan yang paling umum adalah dalam pernyataan SELECT tingkat teratas. Contoh:

SELECT SingerId, SingerInfo
FROM Singers WHERE SingerID = 5
FOR UPDATE;

Contoh ini menunjukkan cara menggunakan klausa FOR UPDATE dalam pernyataan SELECT untuk mengunci secara eksklusif sel SingerId dan SingerInfo dari WHERE SingerID = 5.

Penggunaan dalam pernyataan WITH

Klausa FOR UPDATE tidak mendapatkan kunci untuk pernyataan WITH saat Anda menentukan FOR UPDATE dalam kueri tingkat luar dari pernyataan WITH.

Dalam kueri berikut, tidak ada kunci yang diperoleh oleh tabel Singers, karena niat untuk mengunci tidak diteruskan ke kueri ekspresi tabel umum (CTE).

WITH s AS (SELECT SingerId, SingerInfo FROM Singers WHERE SingerID > 5)
SELECT * FROM s
FOR UPDATE;

Jika klausa FOR UPDATE ditentukan dalam kueri CTE, rentang yang dipindai dari kueri CTE akan memperoleh kunci.

Dalam contoh berikut, sel SingerId dan SingerInfo untuk baris tempat SingerId > 5 dikunci.

WITH s AS
  (SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5 FOR UPDATE)
SELECT * FROM s;

Penggunaan dalam subkueri

Anda dapat menggunakan klausa FOR UPDATE dalam kueri tingkat luar yang memiliki satu atau beberapa subkueri. Penguncian diperoleh oleh kueri tingkat teratas dan dalam subkueri, kecuali dalam subkueri ekspresi.

Kueri berikut mengunci sel SingerId dan SingerInfo untuk baris dengan SingerId > 5.

(SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5) AS t
FOR UPDATE;

Kueri berikut tidak mengunci sel apa pun dalam tabel Albums karena berada dalam subkueri ekspresi. Sel SingerId dan SingerInfo untuk baris yang ditampilkan oleh subkueri ekspresi dikunci.

SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE;

Digunakan untuk membuat kueri tampilan

Anda dapat menggunakan klausa FOR UPDATE untuk membuat kueri tampilan seperti yang ditunjukkan dalam contoh berikut:

CREATE VIEW SingerBio AS SELECT SingerId, FullName, SingerInfo FROM Singers;

SELECT * FROM SingerBio WHERE SingerId = 5 FOR UPDATE;

Anda tidak dapat menggunakan klausa FOR UPDATE saat menentukan tampilan.

Kasus penggunaan yang tidak didukung

Kasus penggunaan FOR UPDATE berikut tidak didukung:

  • Sebagai mekanisme eksklusi timbal balik untuk menjalankan kode di luar Spanner: Jangan gunakan penguncian di Spanner untuk memastikan akses eksklusif ke resource di luar Spanner. Transaksi dapat dibatalkan oleh Spanner, misalnya, jika transaksi dicoba lagi, baik secara eksplisit oleh kode aplikasi atau secara implisit oleh kode klien, seperti driver JDBC Spanner, hanya dijamin bahwa kunci dipegang selama upaya yang dilakukan.
  • Jika dikombinasikan dengan petunjuk LOCK_SCANNED_RANGES: Anda tidak dapat menggunakan klausa FOR UPDATE dan petunjuk LOCK_SCANNED_RANGES dalam kueri yang sama, atau Spanner akan menampilkan error.
  • Dalam kueri penelusuran teks lengkap: Anda tidak dapat menggunakan klausa FOR UPDATE dalam kueri yang menggunakan indeks penelusuran teks lengkap.
  • Dalam transaksi hanya baca: Klausa FOR UPDATE hanya valid dalam kueri yang berjalan dalam transaksi baca-tulis.
  • Dalam pernyataan DDL: Anda tidak dapat menggunakan klausa FOR UPDATE dalam kueri dalam pernyataan DDL, yang disimpan untuk dieksekusi nanti. Misalnya, Anda tidak dapat menggunakan klausa FOR UPDATE saat menentukan tampilan. Jika penguncian diperlukan, klausa FOR UPDATE dapat ditentukan saat mengkueri tampilan.

Langkah Berikutnya