Menggunakan SELECT FOR UPDATE

Halaman ini menjelaskan cara menggunakan klausa FOR UPDATE di Spanner.

Saat Anda menggunakan kueri SELECT untuk memindai tabel, tambahkan klausa FOR UPDATE untuk mengaktifkan kunci eksklusif pada tingkat perincian baris dan kolom, atau dikenal sebagai tingkat sel. Kunci tetap ada selama masa aktif transaksi baca-tulis. Selama waktu ini, klausa FOR UPDATE mencegah transaksi lain mengubah sel yang terkunci hingga transaksi saat ini selesai. Untuk mempelajari lebih lanjut, lihat panduan referensi FOR UPDATE GoogleSQL dan PostgreSQL.

Alasan menggunakan klausa FOR UPDATE

Dalam database dengan tingkat isolasi yang kurang ketat, klausa FOR UPDATE mungkin diperlukan untuk memastikan bahwa transaksi serentak tidak memperbarui data antara membaca data dan melakukan commit transaksi. Karena Spanner selalu menerapkan serialisasi, transaksi hanya berhasil di-commit jika data yang diakses dalam transaksi tidak usang pada waktu commit. Oleh karena itu, klausa FOR UPDATE tidak diperlukan untuk memastikan ketepatan transaksi di Spanner.

Namun, dalam kasus penggunaan dengan pertentangan operasi tulis yang tinggi, seperti saat beberapa transaksi secara serentak membaca dan menulis ke data yang sama, transaksi serentak tersebut dapat menyebabkan peningkatan pembatalan. Hal ini karena saat beberapa transaksi serentak memperoleh kunci bersama, lalu mencoba mengupgrade ke kunci eksklusif, transaksi tersebut menyebabkan deadlock. Spanner kemudian membatalkan semua transaksi kecuali satu. Untuk mengetahui informasi selengkapnya, lihat Pemblokiran.

Transaksi yang menggunakan klausa FOR UPDATE memperoleh kunci eksklusif dan melanjutkan untuk dieksekusi, 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, hal ini menghemat waktu yang akan dihabiskan untuk membatalkan dan mencoba kembali transaksi.

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

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 memperoleh kunci eksklusif pada rentang yang dipindai untuk seluruh pernyataan. Anda tidak dapat memperoleh kunci eksklusif pada subkueri. Penggunaan petunjuk kunci dapat mengakibatkan lebih banyak kunci diperoleh dari yang diperlukan dan berkontribusi pada pertentangan 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 tulis serentak dan biaya transaksi yang dibatalkan akibat deadlock, Spanner mengunci data di tingkat sel jika memungkinkan. Saat menggunakan klausa FOR UPDATE, Spanner akan mengunci sel tertentu yang dipindai oleh kueri SELECT.

Dalam contoh berikut, sel MarketingBudget di baris SingerId = 1 dan AlbumId = 1 dikunci secara eksklusif di 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 memblokir pembacaan data yang terkunci

Jika satu transaksi telah memperoleh kunci eksklusif pada rentang yang dipindai, transaksi serentak dapat memblokir pembacaan data tersebut. Spanner menerapkan serialisasi 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 atau di-roll back.

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 melakukan 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 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 pemindaian 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 operasi tulis tanpa membaca data terlebih dahulu pada sel yang terkunci dapat dilanjutkan. Transaksi akan diblokir pada commit hingga transaksi yang memegang kunci melakukan commit atau melakukan rollback.

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, tabel tersebut akan diblokir agar tidak melakukannya hingga Transaction 1 melakukan commit atau melakukan rollback.

-- 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 akan dikunci saat rentang yang dipindai dikunci

Jika satu transaksi telah memperoleh kunci eksklusif pada rentang yang dipindai, transaksi serentak tidak dapat menyisipkan data dalam 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, baris tersebut akan diblokir hingga Transaction 1 melakukan commit atau melakukan rollback.

-- 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

Catatan akuisisi kunci

Semantik kunci yang dijelaskan memberikan panduan umum, tetapi bukan jaminan tentang cara kunci dapat diperoleh saat Spanner mengeksekusi transaksi yang menggunakan klausa FOR UPDATE. Mekanisme pengoptimalan kueri Spanner juga dapat memengaruhi kunci yang diperoleh. Klausa ini mencegah transaksi lain mengubah sel yang terkunci hingga transaksi saat ini selesai.

Semantik kueri

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

Penggunaan dalam pernyataan WITH

Klausa FOR UPDATE tidak memperoleh 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 intent untuk mengunci tidak di-propagate 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 kueri CTE yang dipindai akan memperoleh kunci.

Pada 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. Kunci diperoleh oleh kueri tingkat atas 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;

Menggunakan 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 saling pengecualian untuk mengeksekusi kode di luar Spanner: Jangan gunakan penguncian di Spanner untuk memastikan akses eksklusif ke resource di luar Spanner. Transaksi mungkin 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 ditahan selama upaya yang dilakukan.
  • 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 dieksekusi dalam transaksi operasi 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 membuat kueri tampilan.

Langkah Berikutnya