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