public async Task ReadWriteTransaction_QueryFullyConsumed_AndThenError_FailsRetry() { string sql = $"SELECT Id FROM Foo WHERE Id={_fixture.RandomLong()}"; _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1)); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { Assert.Equal(1, reader.GetInt64(reader.GetOrdinal("Id"))); } } // Replace the result returned by the query on the server with an error and abort the transaction. // The retry should now fail. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateException(new RpcException(new Status(StatusCode.NotFound, "Table not found: Foo")))); _fixture.SpannerMock.AbortTransaction(transaction.TransactionId); await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync()); Assert.Equal(1, transaction.RetryCount); }
public async Task UpdateFailsIfVersionNumberChanged() { using var db = new MockServerVersionDbContextUsingMutations(ConnectionString); // Set the result of the concurrency check to an empty result set to simulate a version number that has changed. var concurrencySql = $"SELECT 1 FROM `SingersWithVersion` {Environment.NewLine}WHERE `SingerId` = @p0 AND `Version` = @p1"; _fixture.SpannerMock.AddOrUpdateStatementResult(concurrencySql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "COL1")); // Attach a singer to the context and try to update it. var singer = new SingersWithVersion { SingerId = 1L, FirstName = "Pete", LastName = "Allison", Version = 1L }; db.Attach(singer); singer.LastName = "Allison - Peterson"; await Assert.ThrowsAsync <DbUpdateConcurrencyException>(() => db.SaveChangesAsync()); // Update the concurrency check result to 1 to simulate a resolved version conflict. _fixture.SpannerMock.AddOrUpdateStatementResult(concurrencySql, StatementResult.CreateSelect1ResultSet()); Assert.Equal(1L, await db.SaveChangesAsync()); }
public async Task ReadWriteTransaction_ReadScalar_CanBeRetried(bool enableInternalRetries) { string sql = $"SELECT Id FROM Foo WHERE Id={_fixture.RandomLong()}"; _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1)); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); transaction.EnableInternalRetries = enableInternalRetries; var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; var id = await cmd.ExecuteScalarAsync(); Assert.Equal(1L, id); // Abort the transaction on the mock server. The transaction should be able to internally retry. _fixture.SpannerMock.AbortTransaction(transaction.TransactionId); if (enableInternalRetries) { await transaction.CommitAsync(); Assert.Equal(1, transaction.RetryCount); } else { var e = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync()); Assert.Equal(ErrorCode.Aborted, e.ErrorCode); } }
public async Task ReadWriteTransaction_QueryWithError_AndThenNoError_FailsRetry() { string sql = $"SELECT Id FROM Foo WHERE Id={_fixture.RandomLong()}"; _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateException(new RpcException(new Status(StatusCode.NotFound, "Table not found: Foo")))); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using (var reader = await cmd.ExecuteReaderAsync()) { // Any query error is thrown by the first call to reader.ReadAsync(); var e = await Assert.ThrowsAsync <SpannerException>(() => reader.ReadAsync()); Assert.Contains("Table not found: Foo", e.InnerException.Message); } // Remove the error returned by the query on the server and abort the transaction. // The retry should now fail. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1)); _fixture.SpannerMock.AbortTransaction(transaction.TransactionId); await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync()); Assert.Equal(1, transaction.RetryCount); }
public async Task ReadWriteTransaction_QueryAbortsHalfway_WithDifferentUnseenResults_CanBeRetried(bool enableInternalRetries) { string sql = $"SELECT Id FROM Foo WHERE Id IN ({_fixture.RandomLong()}, {_fixture.RandomLong()})"; // Create a result set with 2 rows. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1, 2)); // The following will cause the ExecuteStreamingSql method on the mock server to return an Aborted error on stream index 1 (i.e. before the row with value 2 is returned). // This simulates a transaction that is aborted while a streaming result set is still being returned to the client. var streamWritePermissions = new BlockingCollection <int> { 1 }; var executionTime = ExecutionTime.StreamException(MockSpannerService.CreateAbortedException(sql), 1, streamWritePermissions); _fixture.SpannerMock.AddOrUpdateExecutionTime(nameof(MockSpannerService.ExecuteStreamingSql), executionTime); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); transaction.EnableInternalRetries = enableInternalRetries; var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using var reader = await cmd.ExecuteReaderAsync(); // Only the first row of the reader. Assert.True(await reader.ReadAsync()); Assert.Equal(1, reader.GetInt64(reader.GetOrdinal("Id"))); // Now change the result of the query, but only for the second row which has not yet been // seen by this transaction. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1, 3)); // Try to get the second row of the result. This should succeed, even though the transaction // was aborted, retried and the reader was re-initialized under the hood. The retry succeeds // because only data that had not yet been seen by this transaction was changed. executionTime.AlwaysAllowWrite(); if (enableInternalRetries) { Assert.True(await reader.ReadAsync()); Assert.Equal(3, reader.GetInt64(reader.GetOrdinal("Id"))); // Ensure that there are no more rows in the results. Assert.False(await reader.ReadAsync()); // Check that the transaction really retried. Assert.Equal(1, transaction.RetryCount); } else { var e = await Assert.ThrowsAsync <SpannerException>(() => reader.ReadAsync()); Assert.Equal(ErrorCode.Aborted, e.ErrorCode); } }
public async Task CanInsertRowWithCommitTimestampAndComputedColumn() { using var db = new MockServerSampleDbContextUsingMutations(ConnectionString); var selectSql = $"{Environment.NewLine}SELECT `ColComputed`{Environment.NewLine}FROM `TableWithAllColumnTypes`{Environment.NewLine}WHERE TRUE AND `ColInt64` = @p0"; _fixture.SpannerMock.AddOrUpdateStatementResult(selectSql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.String }, "FOO")); db.TableWithAllColumnTypes.Add( new TableWithAllColumnTypes { ColInt64 = 1L } ); await db.SaveChangesAsync(); Assert.Empty(_fixture.SpannerMock.Requests.Where(request => request is ExecuteBatchDmlRequest)); Assert.Collection( _fixture.SpannerMock.Requests.Where(request => request is ExecuteSqlRequest).Select(request => (ExecuteSqlRequest)request), request => Assert.Equal(selectSql.Trim(), request.Sql.Trim()) ); Assert.Single(_fixture.SpannerMock.Requests.Where(request => request is CommitRequest)); // Verify the order of the requests (that is, the Select statement should be outside the implicit transaction). Assert.Collection( _fixture.SpannerMock.Requests.Where(request => request is CommitRequest || request is ExecuteSqlRequest).Select(request => request.GetType()), requestType => Assert.Equal(typeof(CommitRequest), requestType), requestType => Assert.Equal(typeof(ExecuteSqlRequest), requestType) ); Assert.Collection( _fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request), request => { Assert.Single(request.Mutations); var mutation = request.Mutations[0]; Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase); Assert.Single(mutation.Insert.Values); var row = mutation.Insert.Values[0]; var cols = mutation.Insert.Columns; Assert.Equal("spanner.commit_timestamp()", row.Values[cols.IndexOf("ColCommitTS")].StringValue); } ); }
public async Task ReadWriteTransaction_QueryAbortsHalfway_WithDifferentResults_FailsRetry() { string sql = $"SELECT Id FROM Foo WHERE Id IN ({_fixture.RandomLong()}, {_fixture.RandomLong()})"; // Create a result set with 2 rows. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1, 2)); // The following will cause the ExecuteStreamingSql method on the mock server to return an Aborted error on stream index 1 (i.e. before the row with value 2 is returned). // This simulates a transaction that is aborted while a streaming result set is still being returned to the client. var streamWritePermissions = new BlockingCollection <int> { 1 }; var executionTime = ExecutionTime.StreamException(MockSpannerService.CreateAbortedException(sql), 1, streamWritePermissions); _fixture.SpannerMock.AddOrUpdateExecutionTime(nameof(MockSpannerService.ExecuteStreamingSql), executionTime); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using var reader = await cmd.ExecuteReaderAsync(); // Only the first row of the reader. Assert.True(await reader.ReadAsync()); Assert.Equal(1, reader.GetInt64(reader.GetOrdinal("Id"))); // Now change the result of the query for the record that has already been seen. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 3, 2)); executionTime.AlwaysAllowWrite(); // Try to get the second row of the result. This will now fail. await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => reader.ReadAsync()); Assert.Equal(1, transaction.RetryCount); }
public async Task ReadWriteTransaction_QueryHalfConsumed_WithDifferentUnseenResults_CanBeRetried(bool enableInternalRetries) { string sql = $"SELECT Id FROM Foo WHERE Id IN ({_fixture.RandomLong()}, {_fixture.RandomLong()})"; // Create a result set with 2 rows. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1, 2)); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); transaction.EnableInternalRetries = enableInternalRetries; var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using (var reader = await cmd.ExecuteReaderAsync()) { // Only consume the first row of the reader. Assert.True(await reader.ReadAsync()); Assert.Equal(1, reader.GetInt64(reader.GetOrdinal("Id"))); } // Change the second row of the result of the query. That row has never been seen by the transaction // and should therefore not cause any retry to abort. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1, 3)); _fixture.SpannerMock.AbortTransaction(transaction.TransactionId); if (enableInternalRetries) { await transaction.CommitAsync(); Assert.Equal(1, transaction.RetryCount); } else { var e = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync()); Assert.Equal(ErrorCode.Aborted, e.ErrorCode); } }
public async Task ReadWriteTransaction_QueryFullyConsumed_WithModifiedResultsAfterLastRow_FailsRetry() { string sql = $"SELECT Id FROM Foo WHERE Id IN ({_fixture.RandomLong()}, {_fixture.RandomLong()})"; _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.Int64 }, "Id", 1)); using var connection = CreateConnection(); await connection.OpenAsync(); using var transaction = await connection.BeginTransactionAsync(); var cmd = connection.CreateSelectCommand(sql); cmd.Transaction = transaction; using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { Assert.Equal(1, reader.GetInt64(reader.GetOrdinal("Id"))); } } // Add a row to the result of the query on the server and abort the transaction. Even though the // original query did not see the additional row, it did see a 'false' being returned after consuming // the first row in the query, meaning that it knew that there were no more results. // The retry should now fail. _fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateResultSet( new List <Tuple <V1.TypeCode, string> > { Tuple.Create(V1.TypeCode.Int64, "Id"), }, new List <object[]> { new object[] { 1L }, new object[] { 2L }, } )); _fixture.SpannerMock.AbortTransaction(transaction.TransactionId); var e = await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync()); Assert.Equal(1, transaction.RetryCount); }
public async Task CanUpdateCommitTimestamp() { using var db = new MockServerSampleDbContextUsingMutations(ConnectionString); _fixture.SpannerMock.AddOrUpdateStatementResult($"{Environment.NewLine}SELECT `ColComputed`{Environment.NewLine}FROM `TableWithAllColumnTypes`{Environment.NewLine}WHERE TRUE AND `ColInt64` = @p0", StatementResult.CreateSingleColumnResultSet(new V1.Type { Code = V1.TypeCode.String }, "FOO")); var row = new TableWithAllColumnTypes { ColInt64 = 1L }; db.TableWithAllColumnTypes.Attach(row); row.ColBool = true; await db.SaveChangesAsync(); Assert.Collection( _fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request), request => { Assert.Single(request.Mutations); var mutation = request.Mutations[0]; Assert.Equal(Mutation.OperationOneofCase.Update, mutation.OperationCase); Assert.Single(mutation.Update.Values); var row = mutation.Update.Values[0]; var cols = mutation.Update.Columns; Assert.Equal("spanner.commit_timestamp()", row.Values[cols.IndexOf("ColCommitTS")].StringValue); } ); }
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)); }