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); }