Menggunakan isolasi baca yang dapat diulang

Halaman ini menjelaskan cara menggunakan isolasi baca yang dapat diulang di Spanner.

Repeatable read adalah tingkat isolasi yang memastikan bahwa semua operasi baca dalam transaksi melihat snapshot database yang konsisten sebagaimana adanya pada awal transaksi. Di Spanner, tingkat isolasi ini diterapkan menggunakan teknik yang juga biasa disebut snapshot isolation. Pendekatan ini bermanfaat dalam skenario konkurensi baca-tulis tinggi di mana banyak transaksi membaca data yang mungkin dimodifikasi oleh transaksi lain. Dengan menggunakan snapshot tetap, bacaan yang dapat diulang menghindari dampak performa dari tingkat isolasi yang dapat diserialisasi dan lebih ketat. Dengan konkurensi optimis defaultnya, operasi baca dapat dijalankan tanpa memperoleh kunci dan tanpa memblokir operasi tulis serentak, yang berpotensi menghasilkan lebih sedikit transaksi yang dibatalkan yang mungkin perlu dicoba lagi karena konflik serialisasi. Dengan konkurensi pesimistis, operasi baca menggunakan snapshot, tetapi kunci eksklusif berlaku untuk data yang dibaca dari kueri FOR UPDATE atau petunjuk lock_scanned_ranges=exclusive, dan data yang ditulis dengan kueri DML. Konkurensi pesimistis juga mengurangi kemungkinan konflik tulis-tulis. Untuk mengetahui informasi selengkapnya, lihat Ringkasan tingkat isolasi dan Kontrol serentak.

Menetapkan tingkat isolasi

Anda dapat menetapkan tingkat isolasi pada transaksi baca-tulis di tingkat klien database atau tingkat transaksi menggunakan metode berikut:

Library klien

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	pb "cloud.google.com/go/spanner/apiv1/spannerpb"
)

func writeWithTransactionUsingIsolationLevel(w io.Writer, db string) error {
	ctx := context.Background()

	// The isolation level specified at the client-level will be applied
	// to all RW transactions.
	cfg := spanner.ClientConfig{
		TransactionOptions: spanner.TransactionOptions{
			IsolationLevel: pb.TransactionOptions_SERIALIZABLE,
		},
	}
	client, err := spanner.NewClientWithConfig(ctx, db, cfg)
	if err != nil {
		return fmt.Errorf("failed to create client: %w", err)
	}
	defer client.Close()

	// The isolation level specified at the transaction-level takes
	// precedence over the isolation level configured at the client-level.
	// REPEATABLE_READ is used here to demonstrate overriding the client-level setting.
	txnOpts := spanner.TransactionOptions{
		IsolationLevel: pb.TransactionOptions_REPEATABLE_READ,
	}

	_, err = client.ReadWriteTransactionWithOptions(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		// Read the current album title
		key := spanner.Key{1, 1}
		row, err := txn.ReadRow(ctx, "Albums", key, []string{"AlbumTitle"})
		if err != nil {
			return fmt.Errorf("failed to read album: %v", err)
		}
		var title string
		if err := row.Column(0, &title); err != nil {
			return fmt.Errorf("failed to get album title: %v", err)
		}
		fmt.Fprintf(w, "Current album title: %s\n", title)

		// Update the album title
		stmt := spanner.Statement{
			SQL: `UPDATE Albums
				SET AlbumTitle = @AlbumTitle
				WHERE SingerId = @SingerId AND AlbumId = @AlbumId`,
			Params: map[string]interface{}{
				"SingerId":   1,
				"AlbumId":    1,
				"AlbumTitle": "New Album Title",
			},
		}
		count, err := txn.Update(ctx, stmt)
		if err != nil {
			return fmt.Errorf("failed to update album: %v", err)
		}
		fmt.Fprintf(w, "Updated %d record(s).\n", count)
		return nil
	}, txnOpts)

	if err != nil {
		return fmt.Errorf("transaction failed: %v", err)
	}
	return nil
}

Java

static void isolationLevelSetting(DatabaseId db) {
  // The isolation level specified at the client-level will be applied to all
  // RW transactions.
  DefaultReadWriteTransactionOptions transactionOptions =
      DefaultReadWriteTransactionOptions.newBuilder()
          .setIsolationLevel(IsolationLevel.SERIALIZABLE)
          .build();
  SpannerOptions options =
      SpannerOptions.newBuilder()
          .setDefaultTransactionOptions(transactionOptions)
          .build();
  Spanner spanner = options.getService();
  DatabaseClient dbClient = spanner.getDatabaseClient(db);
  dbClient
      // The isolation level specified at the transaction-level takes precedence
      // over the isolation level configured at the client-level.
      .readWriteTransaction(Options.isolationLevel(IsolationLevel.REPEATABLE_READ))
      .run(transaction -> {
        // Read an AlbumTitle.
        String selectSql =
            "SELECT AlbumTitle from Albums WHERE SingerId = 1 and AlbumId = 1";
        String title = null;
        try (ResultSet resultSet = transaction.executeQuery(Statement.of(selectSql))) {
          if (resultSet.next()) {
            title = resultSet.getString("AlbumTitle");
          }
        }
        System.out.printf("Current album title: %s\n", title);

        // Update the title.
        String updateSql =
            "UPDATE Albums "
                + "SET AlbumTitle = 'New Album Title' "
                + "WHERE SingerId = 1 and AlbumId = 1";
        long rowCount = transaction.executeUpdate(Statement.of(updateSql));
        System.out.printf("%d record updated.\n", rowCount);
        return null;
      });
}

Node.js

// Imports the Google Cloud Spanner client library
const {Spanner, protos} = require('@google-cloud/spanner');
// The isolation level specified at the client-level will be applied
// to all RW transactions.
const defaultTransactionOptions = {
  isolationLevel:
    protos.google.spanner.v1.TransactionOptions.IsolationLevel.SERIALIZABLE,
};

// Instantiates a client with defaultTransactionOptions
const spanner = new Spanner({
  projectId: projectId,
  defaultTransactionOptions,
});

function runTransactionWithIsolationLevel() {
  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);
  // The isolation level specified at the request level takes precedence over the isolation level configured at the client level.
  const isolationOptionsForTransaction = {
    isolationLevel:
      protos.google.spanner.v1.TransactionOptions.IsolationLevel
        .REPEATABLE_READ,
  };

  database.runTransaction(
    isolationOptionsForTransaction,
    async (err, transaction) => {
      if (err) {
        console.error(err);
        return;
      }
      try {
        const query =
          'SELECT AlbumTitle FROM Albums WHERE SingerId = 1 AND AlbumId = 1';
        const results = await transaction.run(query);
        // Gets first album's title
        const rows = results[0].map(row => row.toJSON());
        const albumTitle = rows[0].AlbumTitle;
        console.log(`previous album title ${albumTitle}`);

        const update =
          "UPDATE Albums SET AlbumTitle = 'New Album Title' WHERE SingerId = 1 AND AlbumId = 1";
        const [rowCount] = await transaction.runUpdate(update);
        console.log(
          `Successfully updated ${rowCount} record in Albums table.`,
        );
        await transaction.commit();
        console.log(
          'Successfully executed read-write transaction with isolationLevel option.',
        );
      } catch (err) {
        console.error('ERROR:', err);
        transaction.end();
      } finally {
        // Close the database when finished.
        await database.close();
      }
    },
  );
}
runTransactionWithIsolationLevel();

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
from google.cloud.spanner_v1 import TransactionOptions, DefaultTransactionOptions

# The isolation level specified at the client-level will be applied to all RW transactions.
isolation_options_for_client = TransactionOptions.IsolationLevel.SERIALIZABLE

spanner_client = spanner.Client(
    default_transaction_options=DefaultTransactionOptions(
        isolation_level=isolation_options_for_client
    )
)
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

# The isolation level specified at the request level takes precedence over the isolation level configured at the client level.
isolation_options_for_transaction = (
    TransactionOptions.IsolationLevel.REPEATABLE_READ
)

def update_albums_with_isolation(transaction):
    # Read an AlbumTitle.
    results = transaction.execute_sql(
        "SELECT AlbumTitle from Albums WHERE SingerId = 1 and AlbumId = 1"
    )
    for result in results:
        print("Current Album Title: {}".format(*result))

    # Update the AlbumTitle.
    row_ct = transaction.execute_update(
        "UPDATE Albums SET AlbumTitle = 'A New Title' WHERE SingerId = 1 and AlbumId = 1"
    )

    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(
    update_albums_with_isolation, isolation_level=isolation_options_for_transaction
)

C++

void IsolationLevelSetting(std::string const& project_id,
                           std::string const& instance_id,
                           std::string const& database_id) {
  namespace spanner = ::google::cloud::spanner;
  using ::google::cloud::Options;
  using ::google::cloud::StatusOr;

  auto db = spanner::Database(project_id, instance_id, database_id);

  // The isolation level specified at the client-level will be applied
  // to all RW transactions.
  auto options = Options{}.set<spanner::TransactionIsolationLevelOption>(
      spanner::Transaction::IsolationLevel::kSerializable);
  auto client = spanner::Client(spanner::MakeConnection(db, options));

  auto commit = client.Commit(
      [&client](
          spanner::Transaction const& txn) -> StatusOr<spanner::Mutations> {
        // Read an AlbumTitle.
        auto sql = spanner::SqlStatement(
            "SELECT AlbumTitle from Albums WHERE SingerId = @SingerId and "
            "AlbumId = @AlbumId",
            {{"SingerId", spanner::Value(1)}, {"AlbumId", spanner::Value(1)}});
        auto rows = client.ExecuteQuery(txn, std::move(sql));
        for (auto const& row :
             spanner::StreamOf<std::tuple<std::string>>(rows)) {
          if (!row) return row.status();
          std::cout << "Current album title: " << std::get<0>(*row) << "\n";
        }

        // Update the title.
        auto update_sql = spanner::SqlStatement(
            "UPDATE Albums "
            "SET AlbumTitle = @AlbumTitle "
            "WHERE SingerId = @SingerId and AlbumId = @AlbumId",
            {{"AlbumTitle", spanner::Value("New Album Title")},
             {"SingerId", spanner::Value(1)},
             {"AlbumId", spanner::Value(1)}});
        auto result = client.ExecuteDml(txn, std::move(update_sql));
        if (!result) return result.status();
        std::cout << result->RowsModified() << " record updated.\n";

        return spanner::Mutations{};
      },
      // The isolation level specified at the transaction-level takes
      // precedence over the isolation level configured at the client-level.
      // kRepeatableRead is used here to demonstrate overriding the client-level
      // setting.
      Options{}.set<spanner::TransactionIsolationLevelOption>(
          spanner::Transaction::IsolationLevel::kRepeatableRead));

  if (!commit) throw std::move(commit).status();
  std::cout << "Update was successful [spanner_isolation_level_setting]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading;
using System.Threading.Tasks;
using IsolationLevel = System.Data.IsolationLevel;

public class IsolationLevelAsyncSample
{
    public async Task IsolationLevelAsync(string projectId, string instanceId, string databaseId)
    {
        // Create client with IsolationLevel=Serializable.
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId};IsolationLevel=Serializable";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        // Create transaction options overriding IsolationLevel to RepeatableRead.
        var transactionOptions = SpannerTransactionCreationOptions.ReadWrite
            .WithIsolationLevel(IsolationLevel.RepeatableRead);

        using var transaction = await connection.BeginTransactionAsync(transactionOptions, null, CancellationToken.None);

        using var cmd = connection.CreateSelectCommand("SELECT AlbumTitle FROM Albums WHERE SingerId = 1 AND AlbumId = 1");
        cmd.Transaction = transaction;
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine($"AlbumTitle: {reader.GetFieldValue<string>("AlbumTitle")}");
            }
        }

        using var updateCmd = connection.CreateDmlCommand("UPDATE Albums SET AlbumTitle = 'A New Title' WHERE SingerId = 1 AND AlbumId = 1");
        updateCmd.Transaction = transaction;
        var rowCount = await updateCmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} records updated.");

        await transaction.CommitAsync();
    }
}

REST

Anda dapat menggunakan TransactionOptions.isolation_level REST API untuk menetapkan tingkat isolasi pada transaksi baca-tulis dan hanya baca di tingkat transaksi. Opsi yang valid adalah TransactionOptions.SERIALIZABLE dan TransactionOptions.REPEATABLE_READ. Secara default, Spanner menetapkan tingkat isolasi ke isolasi serialisabel.

Anda dapat menggunakan driver Spanner untuk menetapkan tingkat isolasi dan mode kunci baca sebagai parameter koneksi di tingkat koneksi atau sebagai opsi pernyataan SET di tingkat transaksi. Untuk mengetahui informasi selengkapnya tentang setiap driver, lihat Ringkasan driver.

Anda juga dapat mengonfigurasi konkurensi penguncian untuk setiap tingkat isolasi. Untuk informasi selengkapnya, lihat Kontrol serentak.

Kasus penggunaan yang tidak didukung

  • Anda tidak dapat menetapkan isolasi baca yang dapat diulang pada transaksi DML yang dipartisi.
  • Semua transaksi hanya baca sudah beroperasi pada snapshot tetap dan tidak memerlukan kunci, sehingga menyetel isolasi repeatable read dalam jenis transaksi ini tidak mengubah perilaku apa pun.
  • Anda tidak dapat menyetel isolasi baca yang dapat diulang pada operasi hanya baca, sekali pakai, dan partisi menggunakan library klien Spanner. Library klien Spanner tidak akan memiliki opsi untuk menyetel isolasi baca yang dapat diulang pada operasi kueri hanya baca, sekali pakai, dan partisi.

Langkah berikutnya