async Task RefillMarketingBudgetsAsync(int firstAlbumBudget,
int secondAlbumBudget)
{
string connectionString =
$"Data Source=projects/{s_projectId}/instances/{s_instanceId}"
+ $"/databases/{s_databaseId}";
// Create connection to Cloud Spanner.
using (var connection =
new SpannerConnection(connectionString))
{
await connection.OpenAsync();
var cmd = connection.CreateUpdateCommand("Albums",
new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Int64 },
});
for (int i = 1; i <= 2; ++i)
{
cmd.Parameters["SingerId"].Value = i;
cmd.Parameters["AlbumId"].Value = i;
cmd.Parameters["MarketingBudget"].Value = i == 1 ?
firstAlbumBudget : secondAlbumBudget;
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task UpdateDataWithNumericAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
List <Venue> venues = new List <Venue>
{
new Venue {
VenueId = 4, Revenue = SpannerNumeric.Parse("35000")
},
new Venue {
VenueId = 19, Revenue = SpannerNumeric.Parse("104500")
},
new Venue {
VenueId = 42, Revenue = SpannerNumeric.Parse("99999999999999999999999999999.99")
},
};
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await Task.WhenAll(venues.Select(venue =>
{
// Update rows in the Venues table.
using var cmd = connection.CreateUpdateCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64, venue.VenueId },
{ "Revenue", SpannerDbType.Numeric, venue.Revenue }
});
return(cmd.ExecuteNonQueryAsync());
}));
Console.WriteLine("Data updated.");
}
private async Task UpdateValueAsync(SpannerConnection writeConnection)
{
var writeCommand = writeConnection.CreateUpdateCommand(_fixture.TableName);
writeCommand.Parameters.Add("k", SpannerDbType.String, _key);
writeCommand.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
await writeCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private void UpdateValue(SpannerConnection writeConnection)
{
var writeCommand = writeConnection.CreateUpdateCommand(_fixture.TableName);
writeCommand.Parameters.Add("k", SpannerDbType.String, _key);
writeCommand.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
writeCommand.ExecuteNonQuery();
}
private async Task IncrementByOneAsync(SpannerConnection connection, bool orphanTransaction = false)
{
var retrySettings = RetrySettings.FromExponentialBackoff(
maxAttempts: int.MaxValue,
initialBackoff: TimeSpan.FromMilliseconds(250),
maxBackoff: TimeSpan.FromSeconds(5),
backoffMultiplier: 1.5,
retryFilter: ignored => false,
RetrySettings.RandomJitter);
TimeSpan nextDelay = TimeSpan.Zero;
SpannerException spannerException;
DateTime deadline = DateTime.UtcNow.AddSeconds(30);
while (true)
{
spannerException = null;
try
{
// We use manually created transactions here so the tests run on .NET Core.
using (var transaction = await connection.BeginTransactionAsync())
{
long current;
using (var cmd = connection.CreateSelectCommand($"SELECT Int64Value FROM {_fixture.TableName} WHERE K=@k"))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Transaction = transaction;
var fetched = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
current = fetched is DBNull ? 0L : (long)fetched;
}
using (var cmd = connection.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, current + 1);
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!orphanTransaction)
{
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
return;
}
// Keep trying for up to 30 seconds
catch (SpannerException ex) when(ex.IsRetryable && DateTime.UtcNow < deadline)
{
nextDelay = retrySettings.NextBackoff(nextDelay);
await Task.Delay(retrySettings.BackoffJitter.GetDelay(nextDelay));
spannerException = ex;
}
}
}
private async Task UpdateValueInTransactionScope(SpannerConnection writeConnection)
{
var writeCommand = writeConnection.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", SpannerDbType.String, _key },
{ "Int64Value", SpannerDbType.Int64, 0 }
});
await writeCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private async Task IncrementByOneAsync(SpannerConnection connection, bool orphanTransaction = false)
{
SpannerException spannerException;
do
{
spannerException = null;
try
{
//We'll do manually created transactions here so the tests run on .net core
using (var transaction = await connection.BeginTransactionAsync())
{
long current;
using (var cmd =
connection.CreateSelectCommand(
"SELECT Int64Value FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", SpannerDbType.String, _key }
}))
{
cmd.Transaction = transaction;
var fetched = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
current = fetched is DBNull ? 0L : (long)fetched;
}
using (var cmd = connection.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", SpannerDbType.String, _key },
{ "Int64Value", SpannerDbType.Int64, current + 1 }
}))
{
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!orphanTransaction)
{
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
}
catch (SpannerException ex)
{
spannerException = ex;
}
} while (spannerException?.IsRetryable ?? false);
}
public async Task RefillMarketingBudgetsAsync(int firstAlbumBudget, int secondAlbumBudget)
{
using var connection = new SpannerConnection(ConnectionString);
await connection.OpenAsync();
for (int i = 1; i <= 2; ++i)
{
var cmd = connection.CreateUpdateCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, i },
{ "AlbumId", SpannerDbType.Int64, i },
{ "MarketingBudget", SpannerDbType.Int64, i == 1 ? firstAlbumBudget : secondAlbumBudget },
});
await cmd.ExecuteNonQueryAsync();
}
}
public async Task ReadUpdateDeleteAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: ReadUpdateDeleteAsync
using (var connection = new SpannerConnection(
$"Data Source=projects/{_projectId}/instances/{_instanceName}/databases/{_databaseName}"))
{
await connection.OpenAsync();
// Read the first two keys in the database.
var keys = new List <string>();
var selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
using (var reader = await selectCmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
if (keys.Count < 3)
{
keys.Add(reader.GetFieldValue <string>("Key"));
continue;
}
break;
}
}
// Update the Int64Value of keys[0]
// Include the primary key and update columns.
var updateCmd = connection.CreateUpdateCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[0] },
{ "Int64Value", SpannerDbType.Int64, 0L }
});
await updateCmd.ExecuteNonQueryAsync();
// Delete row for keys[1]
var deleteCmd = connection.CreateDeleteCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[1] }
});
await deleteCmd.ExecuteNonQueryAsync();
}
// End sample
}
public void CloneUpdateWithParameters()
{
var connection = new SpannerConnection("Data Source=projects/p/instances/i/databases/d");
var command = connection.CreateUpdateCommand(
"T", new SpannerParameterCollection
{
{"P1", SpannerDbType.String},
{"P2", SpannerDbType.Float64}
});
var command2 = (SpannerCommand)command.Clone();
Assert.Same(command.SpannerConnection, command2.SpannerConnection);
Assert.Equal(command.CommandText, command2.CommandText);
Assert.Equal(command.Parameters.Count, command2.Parameters.Count);
Assert.NotSame(command.Parameters, command2.Parameters);
Assert.True(command.Parameters.SequenceEqual(command2.Parameters));
}
private SpannerCommand CreateSpannerCommand(
SpannerConnection spannerConnection,
SpannerTransaction transaction,
ModificationCommand modificationCommand)
{
SpannerCommand cmd;
switch (modificationCommand.EntityState)
{
case EntityState.Deleted:
cmd = spannerConnection.CreateDeleteCommand(modificationCommand.TableName);
break;
case EntityState.Modified:
cmd = spannerConnection.CreateUpdateCommand(modificationCommand.TableName);
break;
case EntityState.Added:
cmd = spannerConnection.CreateInsertCommand(modificationCommand.TableName);
break;
default:
throw new NotSupportedException(
$"Modification type {modificationCommand.EntityState} is not supported.");
}
cmd.Logger = new SpannerLogBridge <DbLoggerCategory.Database.Command>(_logger);
cmd.Transaction = transaction;
foreach (var columnModification in modificationCommand.ColumnModifications)
{
cmd.Parameters.Add(
_typeMapper.GetMapping(columnModification.Property).CreateParameter(cmd,
columnModification.ColumnName,
columnModification.UseOriginalValueParameter
? columnModification.OriginalValue
: columnModification.Value, columnModification.Property.IsNullable));
}
return(cmd);
}
public static async Task RunPlanetAuctionAsync(
string projectId, string instanceId, string databaseId, bool showConsoleOutput)
{
/*
* - Get Random Planet with SharesAvailable > 0
* - Get Cost/Share Amount
* - Get Random Player with PlanetDollars > Cost/Share Amount
* - Subtract Planet's Available Shares
* - Subtract Player's PlanetDollars
* - Insert entry into Transaction table
*/
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection =
new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction =
await connection.BeginTransactionAsync())
{
long planetId = 0;
string planetName = "";
long sharesAvailable = 0;
long costPerShare = 0;
string playerId = "";
string playerName = "";
long planetDollars = 0;
// Create statement to select a random planet
var cmd = connection.CreateSelectCommand(
"SELECT PlanetId, PlanetName, SharesAvailable, DIV(PlanetValue, SharesAvailable) as ShareCost "
+ "FROM (SELECT * FROM Planets TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE SharesAvailable > 0 LIMIT 1");
//cmd.Transaction = transaction;
// Excecute the select query.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the planet's ID.
planetId = reader.GetFieldValue <long>("PlanetId");
// Read the planet's Name.
planetName = reader.GetFieldValue <string>("PlanetName");
// Read the planet's shares available.
sharesAvailable = reader.GetFieldValue <long>("SharesAvailable");
// Read the planet's cost per share.
costPerShare = reader.GetFieldValue <long>("ShareCost");
}
}
if (showConsoleOutput)
{
Console.WriteLine($"Planet: {planetName}");
Console.WriteLine($"Planet sharesAvailable:{sharesAvailable}");
Console.WriteLine($"Planet costPerShare: {costPerShare.ToString("N0")}");
}
// Create statement to select a random player.
cmd = connection.CreateSelectCommand(
"SELECT PlayerId, PlayerName, PlanetDollars FROM "
+ "(SELECT * FROM Players TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE PlanetDollars >= @costPerShare LIMIT 1",
new SpannerParameterCollection {
{ "costPerShare", SpannerDbType.Int64 }
});
cmd.Parameters["costPerShare"].Value = costPerShare;
//cmd.Transaction = transaction;
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerId = reader.GetFieldValue <string>("PlayerId");
playerName = reader.GetFieldValue <string>("PlayerName");
planetDollars = reader.GetFieldValue <long>("PlanetDollars");
}
}
if (showConsoleOutput)
{
Console.WriteLine($"1 Share of {planetName} sold to {playerName} "
+ $"for {costPerShare.ToString("N0")} Planet Dollars");
Console.WriteLine($"{playerName} now has {planetDollars.ToString("N0")} Planet Dollars");
}
if (planetId != 0 && playerId != "")
{
// Subtract 1 from planet's shares available.
using (cmd = connection.CreateUpdateCommand(
"Planets", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "SharesAvailable", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
sharesAvailable--;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["SharesAvailable"].Value = sharesAvailable;
await cmd.ExecuteNonQueryAsync();
}
// Subtract cost per share from player's planet dollars.
using (cmd = connection.CreateUpdateCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
planetDollars -= costPerShare;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlanetDollars"].Value = planetDollars;
await cmd.ExecuteNonQueryAsync();
}
// Insert record of transaction in Transactions table.
using (cmd = connection.CreateInsertCommand(
"Transactions", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "PlayerId", SpannerDbType.String },
{ "TimeStamp", SpannerDbType.Timestamp },
{ "Amount", SpannerDbType.Int64 }
}))
{
cmd.Transaction = transaction;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["TimeStamp"].Value = SpannerParameter.CommitTimestamp;
cmd.Parameters["Amount"].Value = costPerShare;
await cmd.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
else
{
_failedTransactions++;
if (showConsoleOutput)
{
Console.WriteLine("PlanetId or PlayerId was invalid.");
}
}
}
if (showConsoleOutput)
{
Console.WriteLine("1 Transaction complete");
}
}
}
public async Task <IActionResult> Index(Form sendForm)
{
var model = new HomeIndex();
model.Content = sendForm.Content;
model.SavedNewContent = true;
// Spanner connection string.
string connectionString =
$"Data Source=projects/{_options.ProjectId}/instances/{_options.InstanceId}"
+ $"/databases/{_options.DatabaseId}";
// Insert Player if PlayerID not present in sent form data.
string playerId = "";
if (string.IsNullOrEmpty(sendForm.PlayerId))
{
// Insert Player Code
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
using (var cmd = connection.CreateInsertCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlayerName", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx;
playerId = Guid.NewGuid().ToString("N");
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlayerName"].Value = sendForm.Content;
cmd.Parameters["PlanetDollars"].Value = 1000000;
cmd.ExecuteNonQuery();
}
await tx.CommitAsync();
}
}
model.PlayerId = playerId;
}
else
{
model.PlayerId = sendForm.PlayerId;
playerId = sendForm.PlayerId;
}
// Submit transaction for Player purchase of 1 planet share.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction =
await connection.BeginTransactionAsync())
{
long planetId = 0;
string planetName = "";
long sharesAvailable = 0;
long costPerShare = 0;
//string playerId = playerId;
string playerName = "";
long planetDollars = 0;
// Create statement to select a random planet
var cmd = connection.CreateSelectCommand(
"SELECT PlanetId, PlanetName, SharesAvailable, DIV(PlanetValue, SharesAvailable) as ShareCost "
+ "FROM (SELECT * FROM Planets TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE SharesAvailable > 0 LIMIT 1");
// Excecute the select query.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the planet's ID.
planetId = reader.GetFieldValue <long>("PlanetId");
// Read the planet's Name.
planetName = reader.GetFieldValue <string>("PlanetName");
// Read the planet's shares available.
sharesAvailable = reader.GetFieldValue <long>("SharesAvailable");
// Read the planet's cost per share.
costPerShare = reader.GetFieldValue <long>("ShareCost");
}
}
// Create statement to select player details.
cmd = connection.CreateSelectCommand(
"SELECT PlayerId, PlayerName, PlanetDollars FROM Players "
+ "WHERE PlayerId = @playerId",
new SpannerParameterCollection {
{ "playerId", SpannerDbType.String }
});
cmd.Parameters["playerId"].Value = playerId;
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerId = reader.GetFieldValue <string>("PlayerId");
playerName = reader.GetFieldValue <string>("PlayerName");
planetDollars = reader.GetFieldValue <long>("PlanetDollars");
}
}
if (planetDollars >= costPerShare && planetId != 0)
{
// Subtract 1 from planet's shares available.
using (cmd = connection.CreateUpdateCommand(
"Planets", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "SharesAvailable", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
sharesAvailable--;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["SharesAvailable"].Value = sharesAvailable;
await cmd.ExecuteNonQueryAsync();
}
// Subtract cost per share from player's planet dollars.
using (cmd = connection.CreateUpdateCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
planetDollars -= costPerShare;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlanetDollars"].Value = planetDollars;
await cmd.ExecuteNonQueryAsync();
}
// Insert record of transaction in Transactions table.
using (cmd = connection.CreateInsertCommand(
"Transactions", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "PlayerId", SpannerDbType.String },
{ "TimeStamp", SpannerDbType.Timestamp },
{ "Amount", SpannerDbType.Int64 }
}))
{
cmd.Transaction = transaction;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["TimeStamp"].Value = SpannerParameter.CommitTimestamp;
cmd.Parameters["Amount"].Value = costPerShare;
await cmd.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
model.Status = $"1 Share of {planetName} sold to {playerName} "
+ $"for {costPerShare.ToString("N0")} Planet Dollars. "
+ $"{playerName} now has {planetDollars.ToString("N0")} Planet Dollars.";
}
else
{
if (planetId == 0)
{
model.Status = "Failed to acquire a valid Planet share. Please retry.";
}
else
{
// Player doesn't have enough Planet Dollars to purchase planet share
model.Status = $"{planetDollars.ToString("N0")} Planet Dollars is not enough to purchase a share of {planetName}";
}
}
}
}
return(View(model));
}
public async Task UpdateDataWithJsonAsync(string projectId, string instanceId, string databaseId)
{
List <Venue> venues = new List <Venue>
{
// If you are using .NET Core 3.1 or later, you can use System.Text.Json for serialization instead.
new Venue
{
VenueId = 19,
VenueDetails = JsonConvert.SerializeObject(new
{
rating = 9,
open = true,
})
},
new Venue
{
VenueId = 4,
VenueDetails = JsonConvert.SerializeObject(new object[]
{
new
{
name = "room 1",
open = true,
},
new
{
name = "room 2",
open = false,
},
})
},
new Venue
{
VenueId = 42,
VenueDetails = JsonConvert.SerializeObject(new
{
name = "Central Park",
open = new
{
Monday = true,
Tuesday = false,
},
tags = new string[] { "large", "airy" },
}),
},
};
// Create connection to Cloud Spanner.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await Task.WhenAll(venues.Select(venue =>
{
// Update rows in the Venues table.
using var cmd = connection.CreateUpdateCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64, venue.VenueId },
{ "VenueDetails", SpannerDbType.Json, venue.VenueDetails }
});
return(cmd.ExecuteNonQueryAsync());
}));
Console.WriteLine("Data updated.");
}
public async Task AbortedThrownCorrectly()
{
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
// Note: deeply nested using statements to ensure that we dispose of everything even in the case of failure,
// but we manually dispose of both tx1 and connection1.
using (var connection1 = new SpannerConnection(_fixture.ConnectionString))
{
using (var connection2 = new SpannerConnection(_fixture.ConnectionString))
{
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
using (var tx1 = await connection1.BeginTransactionAsync())
{
// TX1 READ
using (var cmd = CreateSelectAllCommandForKey(connection1))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
using (var tx2 = await connection2.BeginTransactionAsync())
{
// TX2 READ
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
var thrownException = await Assert.ThrowsAsync <SpannerException>(() => reader.ReadAsync());
Assert.True(thrownException.IsRetryable);
}
}
}
}
}
}
}
public async Task AbortedThrownCorrectly()
{
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
var connection1 = new SpannerConnection(_fixture.ConnectionString);
var connection2 = new SpannerConnection(_fixture.ConnectionString);
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
var tx1 = await connection1.BeginTransactionAsync();
// TX1 READ
using (var cmd = CreateSelectAllCommandForKey(connection1))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
var tx2 = await connection2.BeginTransactionAsync();
// TX2 READ
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
var thrownException = await Assert.ThrowsAsync <SpannerException>(
async() =>
{
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
}).ConfigureAwait(false);
tx2.Dispose();
connection2.Dispose();
Assert.True(thrownException?.IsRetryable ?? false);
}
public async Task ReadWriteTransaction()
{
decimal initialBudget1 = 1225250.00m;
decimal initialBudget2 = 2250198.28m;
_fixture.SpannerMock.AddOrUpdateStatementResult(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 1 AND AlbumId = 1",
StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = TypeCode.Numeric
}, "MarketingBudget", initialBudget1));
_fixture.SpannerMock.AddOrUpdateStatementResult(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 2 AND AlbumId = 2",
StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = TypeCode.Numeric
}, "MarketingBudget", initialBudget2));
string connectionString = $"Data Source=projects/p1/instances/i1/databases/d1;Host={_fixture.Host};Port={_fixture.Port}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
decimal firstBudget = 0;
using var connection = new SpannerConnection(connectionString, ChannelCredentials.Insecure);
await connection.OpenAsync();
using (var transaction = await connection.BeginTransactionAsync())
{
// Create statement to select the second album's data.
var cmdLookup = connection.CreateSelectCommand(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup.Transaction = transaction;
// Excecute the select query.
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
secondBudget = reader.GetNumeric(reader.GetOrdinal("MarketingBudget")).ToDecimal(LossOfPrecisionHandling.Throw);
}
}
// Read the first album's budget.
cmdLookup = connection.CreateSelectCommand(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 1 AND AlbumId = 1");
cmdLookup.Transaction = transaction;
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
firstBudget = reader.GetNumeric(reader.GetOrdinal("MarketingBudget")).ToDecimal(LossOfPrecisionHandling.Throw);
}
}
// Specify update command parameters.
var cmd = connection.CreateUpdateCommand("Albums",
new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Numeric },
});
cmd.Transaction = transaction;
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
cmd.Parameters["SingerId"].Value = 2;
cmd.Parameters["AlbumId"].Value = 2;
cmd.Parameters["MarketingBudget"].Value = secondBudget;
await cmd.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget += transferAmount;
cmd.Parameters["SingerId"].Value = 1;
cmd.Parameters["AlbumId"].Value = 1;
cmd.Parameters["MarketingBudget"].Value = firstBudget;
await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
// Assert that the correct updates were sent.
Stack <IMessage> requests = new Stack <IMessage>(_fixture.SpannerMock.Requests);
Assert.Equal(typeof(CommitRequest), requests.Peek().GetType());
CommitRequest commit = (CommitRequest)requests.Pop();
Assert.Equal(2, commit.Mutations.Count);
Mutation update1 = commit.Mutations.Last();
Assert.Equal(Mutation.OperationOneofCase.Update, update1.OperationCase);
Assert.Equal("Albums", update1.Update.Table);
Assert.Equal("1", update1.Update.Values.ElementAt(0).Values.ElementAt(0).StringValue);
Assert.Equal(
SpannerNumeric.FromDecimal(initialBudget1 + transferAmount, LossOfPrecisionHandling.Throw),
SpannerNumeric.Parse(update1.Update.Values.ElementAt(0).Values.ElementAt(2).StringValue));
Mutation update2 = commit.Mutations.First();
Assert.Equal(Mutation.OperationOneofCase.Update, update2.OperationCase);
Assert.Equal("Albums", update2.Update.Table);
Assert.Equal("2", update2.Update.Values.ElementAt(0).Values.ElementAt(0).StringValue);
Assert.Equal(
SpannerNumeric.FromDecimal(initialBudget2 - transferAmount, LossOfPrecisionHandling.Throw),
SpannerNumeric.Parse(update2.Update.Values.ElementAt(0).Values.ElementAt(2).StringValue));
}
public async Task AbortedThrownCorrectly()
{
await WriteSampleRowsAsync();
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
var connection1 = new SpannerConnection(_testFixture.ConnectionString);
var connection2 = new SpannerConnection(_testFixture.ConnectionString);
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
var tx1 = await connection1.BeginTransactionAsync();
// TX1 READ
using (var cmd = connection1.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
var tx2 = await connection2.BeginTransactionAsync();
// TX2 READ
using (var cmd = connection2.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", _key, SpannerDbType.String },
{ "Int64Value", 0, SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
SpannerException thrownException = null;
try
{
using (var cmd = connection2.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
}
catch (SpannerException ex)
{
thrownException = ex;
}
finally
{
tx2.Dispose();
connection2.Dispose();
}
Assert.True(thrownException?.IsRetryable ?? false);
}
public async Task <int> ReadWriteWithTransactionCoreAsync(string projectId, string instanceId, string databaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the Add Column and Write Data To New Column samples first,
// in that order.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
decimal firstBudget = 0;
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
using var cmdLookup1 = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup1.Transaction = transaction;
using (var reader = await cmdLookup1.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the second album's budget.
secondBudget = reader.GetFieldValue <decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if (secondBudget < transferAmount)
{
throw new Exception($"The second album's budget {secondBudget} contains less than the amount to transfer.");
}
}
}
// Read the first album's budget.
using var cmdLookup2 = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
cmdLookup2.Transaction = transaction;
using (var reader = await cmdLookup2.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
firstBudget = reader.GetFieldValue <decimal>("MarketingBudget");
}
}
// Specify update command parameters.
using var cmdUpdate = connection.CreateUpdateCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Int64 },
});
cmdUpdate.Transaction = transaction;
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
cmdUpdate.Parameters["SingerId"].Value = 2;
cmdUpdate.Parameters["AlbumId"].Value = 2;
cmdUpdate.Parameters["MarketingBudget"].Value = secondBudget;
var rowCount = await cmdUpdate.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget += transferAmount;
cmdUpdate.Parameters["SingerId"].Value = 1;
cmdUpdate.Parameters["AlbumId"].Value = 1;
cmdUpdate.Parameters["MarketingBudget"].Value = firstBudget;
rowCount += await cmdUpdate.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine("Transaction complete.");
return(rowCount);
}
