Class: Google::Cloud::Spanner::Transaction

Inherits:
Object
  • Object
show all
Defined in:
lib/google/cloud/spanner/transaction.rb

Overview

Transaction

A transaction in Cloud Spanner is a set of reads and writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.

All changes are accumulated in memory until the block passed to Client#transaction completes. Transactions will be automatically retried when possible. See Client#transaction.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  # Read the second album budget.
  second_album_result = tx.read "Albums", ["marketing_budget"],
                                keys: [[2, 2]], limit: 1
  second_album_row = second_album_result.rows.first
  second_album_budget = second_album_row.values.first

  transfer_amount = 200000

  if second_album_budget < 300000
    # Raising an exception will automatically roll back the
    # transaction.
    raise "The second album doesn't have enough funds to transfer"
  end

  # Read the first album's budget.
  first_album_result = tx.read "Albums", ["marketing_budget"],
                                keys: [[1, 1]], limit: 1
  first_album_row = first_album_result.rows.first
  first_album_budget = first_album_row.values.first

  # Update the budgets.
  second_album_budget -= transfer_amount
  first_album_budget += transfer_amount
  puts "Setting first album's budget to #{first_album_budget} and " \
       "the second album's budget to #{second_album_budget}."

  # Update the rows.
  rows = [
    {singer_id: 1, album_id: 1, marketing_budget: first_album_budget},
    {singer_id: 2, album_id: 2, marketing_budget: second_album_budget}
  ]
  tx.update "Albums", rows
end

Instance Method Summary collapse

Instance Method Details

#batch_update(request_options: nil, call_options: nil) {|batch_update| ... } ⇒ Array<Integer>

Executes DML statements in a batch.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  begin
    row_counts = tx.batch_update do |b|
      statement_count = b.batch_update(
        "UPDATE users SET name = 'Charlie' WHERE id = 1"
      )
    end
    puts row_counts.inspect
  rescue Google::Cloud::Spanner::BatchUpdateError => err
    puts err.cause.message
    puts err.row_counts.inspect
  end
end

Update using SQL parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  begin
    row_counts = tx.batch_update do |b|
      statement_count = b.batch_update(
        "UPDATE users SET name = 'Charlie' WHERE id = 1",
        params: { id: 1, name: "Charlie" }
      )
    end
    puts row_counts.inspect
  rescue Google::Cloud::Spanner::BatchUpdateError => err
    puts err.cause.message
    puts err.row_counts.inspect
  end
end

Yields:

Yield Parameters:

Raises:

  • (Google::Cloud::Spanner::BatchUpdateError) —

    If an error occurred while executing a statement. The error object contains a cause error with the service error type and message, and a list with the exact number of rows that were modified for each successful statement before the error.



714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
 
# File 'lib/google/cloud/spanner/transaction.rb', line 714

def batch_update request_options: nil, call_options: nil, &block
  ensure_session!

  request_options = build_request_options request_options
  safe_execute do |seqno|
    response = session.batch_update tx_selector, seqno,
                                    request_options: request_options,
                                    call_options: call_options, &block
    batch_update_results = BatchUpdateResults.new response
    update_wrapped_transaction! batch_update_results.transaction
    response.result_sets.each do |result_set|
      update_precommit_token! result_set.precommit_token if result_set.precommit_token
    end
    batch_update_results.row_counts
  end
end

#commit_timestamp ⇒ ColumnValue

Creates a column value object representing setting a field's value to the timestamp of the commit. (See Client#commit_timestamp)

This placeholder value can only be used for timestamp columns that have set the option "(allow_commit_timestamp=true)" in the schema.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new

db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.insert "users", [
    { id: 5, name: "Murphy", updated_at: tx.commit_timestamp }
  ]
end



1235
1236
1237
 
# File 'lib/google/cloud/spanner/transaction.rb', line 1235

def commit_timestamp
  ColumnValue.commit_timestamp
end

#delete(table, keys = []) ⇒ Object

Deletes rows from a table. Succeeds whether or not the specified rows were present.

All changes are accumulated in memory until the block passed to Client#transaction completes.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction { |tx| tx.delete "users", [1, 2, 3] }



1069
1070
1071
1072
 
# File 'lib/google/cloud/spanner/transaction.rb', line 1069

def delete table, keys = []
  ensure_session!
  @commit.delete table, keys
end

#execute_query(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) ⇒ Google::Cloud::Spanner::Results Also known as: execute, query, execute_sql

Executes a SQL query.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query "SELECT * FROM users"

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query(
    "SELECT * FROM users WHERE active = @active",
    params: { active: true }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query with a SQL STRUCT query parameter as a Hash:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_hash = { id: 1, name: "Charlie", active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Specify the SQL STRUCT type using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_hash = { id: 1, name: nil, active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash },
    types: { user_struct: user_type }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Or, query with a SQL STRUCT as a typed Data object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_data = user_type.struct id: 1, name: nil, active: false

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_data }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query options:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", query_options: {
    optimizer_version: "1",
    optimizer_statistics_package: "auto_20191128_14_47_22UTC"
  }

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", call_options: call_options

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end



422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
 
# File 'lib/google/cloud/spanner/transaction.rb', line 422

def execute_query sql, params: nil, types: nil, query_options: nil,
                  request_options: nil, call_options: nil
  ensure_session!

  params, types = Convert.to_input_params_and_types params, types
  request_options = build_request_options request_options
  route_to_leader = LARHeaders.execute_query true

  safe_execute do |seqno|
    results = session.execute_query sql, params: params, types: types,
                                    transaction: tx_selector, seqno: seqno,
                                    query_options: query_options,
                                    request_options: request_options,
                                    call_options: call_options,
                                    route_to_leader: route_to_leader,
                                    precommit_token_notify: method(:update_precommit_token!)

    update_wrapped_transaction! results.transaction

    results
  end
end

#execute_update(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) ⇒ Integer

Executes a DML statement.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1"
  )
end

Update using SQL parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = @name WHERE id = @id",
    params: { id: 1, name: "Charlie" }
  )
end

Update using query options

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1",
    query_options: {
      optimizer_version: "1",
      optimizer_statistics_package: "auto_20191128_14_47_22UTC"
    }
  )
end

Update using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1",
    call_options: call_options
  )
end



609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
 
# File 'lib/google/cloud/spanner/transaction.rb', line 609

def execute_update sql, params: nil, types: nil, query_options: nil,
                   request_options: nil, call_options: nil
  results = execute_query sql, params: params, types: types,
                          query_options: query_options,
                          request_options: request_options,
                          call_options: call_options

  # Since this method is calling `execute_query`, the transaction is going to be updated,
  # and the `results` object is going to be set up with precommit token notification reference,
  # so we don't need to do anything special here.

  # Stream all PartialResultSet to get ResultSetStats
  results.rows.to_a
  # Raise an error if there is not a row count returned
  if results.row_count.nil?
    raise Google::Cloud::InvalidArgumentError,
          "DML statement is invalid."
  end
  results.row_count
end

#fields(types) ⇒ Fields

Creates a configuration object (Fields) that may be provided to queries or used to create STRUCT objects. (The STRUCT will be represented by the Data class.) See Client#execute and/or Fields#struct.

For more information, see Data Types - Constructing a STRUCT.

Examples:

Create a STRUCT value with named fields using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  named_type = tx.fields(
    { id: :INT64, name: :STRING, active: :BOOL }
  )
  named_data = named_type.struct(
    { id: 42, name: nil, active: false }
  )
end

Create a STRUCT value with anonymous field names:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  anon_type = tx.fields [:INT64, :STRING, :BOOL]
  anon_data = anon_type.struct [42, nil, false]
end

Create a STRUCT value with duplicate field names:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  dup_type = tx.fields [[:x, :INT64], [:x, :STRING], [:x, :BOOL]]
  dup_data = dup_type.struct [42, nil, false]
end



1174
1175
1176
 
# File 'lib/google/cloud/spanner/transaction.rb', line 1174

def fields types
  Fields.new types
end

#insert(table, *rows) ⇒ Object

Inserts new rows in a table. If any of the rows already exist, the write or request fails with error AlreadyExistsError.

All changes are accumulated in memory until the block passed to Client#transaction completes.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.insert "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end



947
948
949
950
 
# File 'lib/google/cloud/spanner/transaction.rb', line 947

def insert table, *rows
  ensure_session!
  @commit.insert table, rows
end

#range(beginning, ending, exclude_begin: false, exclude_end: false) ⇒ Google::Cloud::Spanner::Range

Creates a Cloud Spanner Range. This can be used in place of a Ruby Range when needing to exclude the beginning value.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  key_range = tx.range 1, 100
  results = tx.read "users", [:id, :name], keys: key_range

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end



1207
1208
1209
1210
1211
 
# File 'lib/google/cloud/spanner/transaction.rb', line 1207

def range beginning, ending, exclude_begin: false, exclude_end: false
  Range.new beginning, ending,
            exclude_begin: exclude_begin,
            exclude_end: exclude_end
end

#read(table, columns, keys: nil, index: nil, limit: nil, request_options: nil, call_options: nil) ⇒ Google::Cloud::Spanner::Results

Read rows from a database table, as a simple alternative to #execute_query.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.read "users", [:id, :name]

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end



835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
 
# File 'lib/google/cloud/spanner/transaction.rb', line 835

def read table, columns, keys: nil, index: nil, limit: nil,
         request_options: nil, call_options: nil
  ensure_session!

  columns = Array(columns).map(&:to_s)
  keys = Convert.to_key_set keys
  request_options = build_request_options request_options
  route_to_leader = LARHeaders.read true

  safe_execute do
    results = session.read table, columns, keys: keys, index: index, limit: limit,
                           transaction: tx_selector,
                           request_options: request_options,
                           call_options: call_options,
                           route_to_leader: route_to_leader,
                           precommit_token_notify: method(:update_precommit_token!)
    update_wrapped_transaction! results.transaction
    results
  end
end

#replace(table, *rows) ⇒ Object

Inserts or replaces rows in a table. If any of the rows already exist, it is deleted, and the column values provided are inserted instead. Unlike #upsert, this means any values not explicitly written become NULL.

All changes are accumulated in memory until the block passed to Client#transaction completes.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.replace "users", [{ id: 1, name: "Charlie", active: false },
                       { id: 2, name: "Harvey",  active: true }]
end



1043
1044
1045
1046
 
# File 'lib/google/cloud/spanner/transaction.rb', line 1043

def replace table, *rows
  ensure_session!
  @commit.replace table, rows
end

#transaction_id ⇒ String

Identifier of the transaction results were run in.



174
175
176
177
178
 
# File 'lib/google/cloud/spanner/transaction.rb', line 174

def transaction_id
  return @grpc.id if existing_transaction?
  safe_begin_transaction!
  @grpc.id
end

#update(table, *rows) ⇒ Object

Updates existing rows in a table. If any of the rows does not already exist, the request fails with error NotFoundError.

All changes are accumulated in memory until the block passed to Client#transaction completes.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.update "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end



994
995
996
997
 
# File 'lib/google/cloud/spanner/transaction.rb', line 994

def update table, *rows
  ensure_session!
  @commit.update table, rows
end

#upsert(table, *rows) ⇒ Object Also known as: save

Inserts or updates rows in a table. If any of the rows already exist, then its column values are overwritten with the ones provided. Any column values not explicitly written are preserved.

All changes are accumulated in memory until the block passed to Client#transaction completes.

Examples:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.upsert "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end



899
900
901
902
 
# File 'lib/google/cloud/spanner/transaction.rb', line 899

def upsert table, *rows
  ensure_session!
  @commit.upsert table, rows
end