public async Task ReadWriteTransaction_AbortedDml_IsAutomaticallyRetried(bool enableInternalRetries)
{
string sql = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateUpdateCount(1));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
transaction.EnableInternalRetries = enableInternalRetries;
// Abort the transaction on the mock server. The transaction should be able to internally retry.
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
if (enableInternalRetries)
{
var updateCount = await cmd.ExecuteNonQueryAsync();
Assert.Equal(1, updateCount);
Assert.Equal(1, transaction.RetryCount);
}
else
{
var e = await Assert.ThrowsAsync <SpannerException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(ErrorCode.Aborted, e.ErrorCode);
}
}
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_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 TestEntity_ConvertValuesWithFloatOverflow_Succeeds()
{
var sql = $"SELECT `t`.`Id`, `t`.`ByteCol`, `t`.`DecimalCol`, `t`.`FloatCol`" +
$"{Environment.NewLine}FROM `TestEntities` AS `t`{Environment.NewLine}" +
$"WHERE `t`.`Id` = @__p_0{Environment.NewLine}LIMIT 1";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateResultSet(
new List <Tuple <V1.Type, string> >
{
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "Id"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "ByteCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Numeric
}, "DecimalCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Float64
}, "FloatCol"),
},
new List <object[]>
{
new object[] { 1L, 1L, "3.14", float.MaxValue * 2d },
}
));
using var db = new TypeConversionDbContext(ConnectionString);
var row = await db.TestEntities.FindAsync(1L);
Assert.Equal(float.PositiveInfinity, row.FloatCol);
}
public async Task ReadWriteTransaction_Retry_GivesUp()
{
string sql = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateUpdateCount(1));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
transaction.MaxInternalRetryCount = 3;
for (var i = 0; i < transaction.MaxInternalRetryCount; i++)
{
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
var updateCount = await cmd.ExecuteNonQueryAsync();
Assert.Equal(1, updateCount);
Assert.Equal(i + 1, transaction.RetryCount);
}
// The next statement that aborts will cause the transaction to fail.
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
var cmd2 = connection.CreateDmlCommand(sql);
cmd2.Transaction = transaction;
var e = await Assert.ThrowsAsync <SpannerException>(() => cmd2.ExecuteNonQueryAsync());
Assert.Equal(ErrorCode.Aborted, e.ErrorCode);
Assert.Contains("Transaction was aborted because it aborted and retried too many times", e.Message);
}
public async Task ReadWriteTransaction_BatchDmlWithSameExceptionHalfwayAndDifferentResults_FailsRetry()
{
string sql1 = $"UPDATE Foo SET Bar='valid' WHERE Id={_fixture.RandomLong()}";
string sql2 = $"UPDATE Foo SET Bar='invalid' Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateUpdateCount(1));
_fixture.SpannerMock.AddOrUpdateStatementResult(sql2, StatementResult.CreateException(new RpcException(new Status(StatusCode.FailedPrecondition, "UPDATE statement misses WHERE clause"))));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
var cmd = connection.CreateBatchDmlCommand();
cmd.Transaction = transaction;
cmd.Add(sql1);
cmd.Add(sql2);
var e = await Assert.ThrowsAsync <SpannerBatchNonQueryException>(() => cmd.ExecuteNonQueryAsync());
Assert.Contains("UPDATE statement misses WHERE clause", e.Message);
Assert.Equal(new List <long> {
1
}, e.SuccessfulCommandResults);
// Change the result of the first statement and abort the transaction.
// The retry should now fail, even though the error code and message is the same.
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateUpdateCount(2));
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync());
Assert.Equal(1, transaction.RetryCount);
}
public async Task TestEntity_ConvertValuesWithDecimalOverflow_Fails()
{
var sql = $"SELECT `t`.`Id`, `t`.`ByteCol`, `t`.`DecimalCol`, `t`.`FloatCol`" +
$"{Environment.NewLine}FROM `TestEntities` AS `t`{Environment.NewLine}" +
$"WHERE `t`.`Id` = @__p_0{Environment.NewLine}LIMIT 1";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateResultSet(
new List <Tuple <V1.Type, string> >
{
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "Id"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "ByteCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Numeric
}, "DecimalCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Float64
}, "FloatCol"),
},
new List <object[]>
{
new object[] { 1L, 1L, $"99999999999999999999999999999", 1d },
}
));
using var db = new TypeConversionDbContext(ConnectionString);
await Assert.ThrowsAsync <OverflowException>(() => db.TestEntities.FindAsync(1L).AsTask());
}
public async Task ReadWriteTransaction_BatchDmlWithDifferentException_FailsRetry()
{
string sql1 = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateException(new RpcException(new Status(StatusCode.AlreadyExists, "Unique key constraint violation"))));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
var cmd = connection.CreateBatchDmlCommand();
cmd.Transaction = transaction;
cmd.Add(sql1);
try
{
await cmd.ExecuteNonQueryAsync();
Assert.True(false, "Missing expected exception");
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.AlreadyExists)
{
Assert.Contains("Unique key constraint violation", e.InnerException?.Message);
}
// Remove the error message for the udpate statement.
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateUpdateCount(1));
// Abort the transaction and try to commit. That will trigger a retry, but the retry
// will not receive an error for the update statement. That will fail the retry.
_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_ModifiedBatchDmlUpdateCount_FailsRetry()
{
string sql1 = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
string sql2 = $"UPDATE Foo SET Bar='baz' WHERE Id IN ({_fixture.RandomLong()},{_fixture.RandomLong()})";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateUpdateCount(1));
_fixture.SpannerMock.AddOrUpdateStatementResult(sql2, StatementResult.CreateUpdateCount(2));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
var cmd = connection.CreateBatchDmlCommand();
cmd.Transaction = transaction;
cmd.Add(sql1);
cmd.Add(sql2);
Assert.Equal(new List <long> {
1, 2
}, await cmd.ExecuteNonQueryAsync());
// Change the update count returned by one of the statements and abort the transaction.
_fixture.SpannerMock.AddOrUpdateStatementResult(sql2, StatementResult.CreateUpdateCount(1));
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync());
Assert.Equal(1, transaction.RetryCount);
}
public async Task ReadWriteTransaction_AbortedDmlWithDifferentException_FailsRetry()
{
string sql = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateException(new RpcException(new Status(StatusCode.AlreadyExists, "Unique key constraint violation"))));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
var e = await Assert.ThrowsAsync <SpannerException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(ErrorCode.AlreadyExists, e.ErrorCode);
Assert.Contains("Unique key constraint violation", e.InnerException?.Message);
// Change the error for the statement on the mock server and abort the transaction.
// The retry should now fail as the error has changed.
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateException(new RpcException(new Status(StatusCode.NotFound, "Table Foo not found"))));
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => transaction.CommitAsync());
Assert.Equal(1, transaction.RetryCount);
}
public async Task ReadWriteTransaction_AbortedDmlWithSameException_CanBeRetried(bool enableInternalRetries)
{
string sql = $"UPDATE Foo SET Bar='bar' Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateException(new RpcException(new Status(StatusCode.FailedPrecondition, "UPDATE statement misses WHERE clause"))));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
transaction.EnableInternalRetries = enableInternalRetries;
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
var e = await Assert.ThrowsAsync <SpannerException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(ErrorCode.FailedPrecondition, e.ErrorCode);
Assert.Contains("UPDATE statement misses WHERE clause", e.InnerException?.Message);
// 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 se = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync());
Assert.Equal(ErrorCode.Aborted, se.ErrorCode);
}
}
public async Task ReadWriteTransaction_ModifiedDmlUpdateCount_FailsRetry()
{
// This statement returns an update count of 1 the first time.
string sql = $"UPDATE Foo SET Bar='baz' WHERE Id IN ({_fixture.RandomLong()},{_fixture.RandomLong()})";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateUpdateCount(1));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
// Execute an update and then change the return value for the statement before the retry is executed.
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
Assert.Equal(1, await cmd.ExecuteNonQueryAsync());
// The update statement will return 2 the next time it is executed.
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateUpdateCount(2));
// Now abort the transaction and try to execute another DML statement. The retry will fail because it sees
// a different update count during the retry.
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
cmd = connection.CreateDmlCommand($"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}");
cmd.Transaction = transaction;
var e = await Assert.ThrowsAsync <SpannerAbortedDueToConcurrentModificationException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(1, transaction.RetryCount);
}
public void AddOrUpdateStatementResult(string sql, StatementResult result)
{
_results.AddOrUpdate(sql.Trim(),
result,
(string sql, StatementResult existing) => result
);
}
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);
}
}
private string AddSelectSingerFullNameResult(string fullName, int paramIndex)
{
var selectFullNameSql = $"{Environment.NewLine}SELECT `FullName`{Environment.NewLine}FROM `Singers`" +
$"{Environment.NewLine}WHERE TRUE AND `SingerId` = @p{paramIndex}";
_fixture.SpannerMock.AddOrUpdateStatementResult(selectFullNameSql, StatementResult.CreateResultSet(
new List <Tuple <V1.TypeCode, string> >
{
Tuple.Create(V1.TypeCode.Int64, "SingerId"),
Tuple.Create(V1.TypeCode.String, "FullName"),
},
new List <object[]>
{
new object[] { 1L, fullName },
}
));
return(selectFullNameSql);
}
private string AddFindSingerResult(string sql)
{
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateResultSet(
new List <Tuple <V1.TypeCode, string> >
{
Tuple.Create(V1.TypeCode.Int64, "SingerId"),
Tuple.Create(V1.TypeCode.Date, "BirthDate"),
Tuple.Create(V1.TypeCode.String, "FirstName"),
Tuple.Create(V1.TypeCode.String, "FullName"),
Tuple.Create(V1.TypeCode.String, "LastName"),
Tuple.Create(V1.TypeCode.Bytes, "Picture"),
},
new List <object[]>
{
new object[] { 1L, null, "Alice", "Alice Morrison", "Morrison", null },
}
));
return(sql);
}
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_BatchDmlWithSameExceptionHalfwayAndSameResults_CanBeRetried(bool enableInternalRetries)
{
string sql1 = $"UPDATE Foo SET Bar='valid' WHERE Id={_fixture.RandomLong()}";
string sql2 = $"UPDATE Foo SET Bar='invalid' Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql1, StatementResult.CreateUpdateCount(1));
_fixture.SpannerMock.AddOrUpdateStatementResult(sql2, StatementResult.CreateException(new RpcException(new Status(StatusCode.FailedPrecondition, "UPDATE statement misses WHERE clause"))));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
transaction.EnableInternalRetries = enableInternalRetries;
var cmd = connection.CreateBatchDmlCommand();
cmd.Transaction = transaction;
cmd.Add(sql1);
cmd.Add(sql2);
var e = await Assert.ThrowsAsync <SpannerBatchNonQueryException>(() => cmd.ExecuteNonQueryAsync());
Assert.Contains("UPDATE statement misses WHERE clause", e.Message);
Assert.Equal(new List <long> {
1
}, e.SuccessfulCommandResults);
// Abort the transaction and try to commit. That will trigger a retry, and the retry will receive
// the same error and the same results for the BatchDML call as the original attempt.
_fixture.SpannerMock.AbortTransaction(transaction.TransactionId);
if (enableInternalRetries)
{
await transaction.CommitAsync();
Assert.Equal(1, transaction.RetryCount);
}
else
{
var se = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync());
Assert.Equal(ErrorCode.Aborted, se.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 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_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_WithoutAbort_DoesNotRetry()
{
string sql = $"UPDATE Foo SET Bar='bar' WHERE Id={_fixture.RandomLong()}";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateUpdateCount(1));
using var connection = CreateConnection();
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
var cmd = connection.CreateDmlCommand(sql);
cmd.Transaction = transaction;
var updateCount = await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Assert.Equal(1, updateCount);
Assert.Equal(0, transaction.RetryCount);
}
public async Task ReadWriteTransaction_QueryWithSameException_CanBeRetried(bool enableInternalRetries)
{
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();
transaction.EnableInternalRetries = enableInternalRetries;
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.Equal(ErrorCode.NotFound, e.ErrorCode);
Assert.Contains("Table not found: Foo", e.InnerException.Message);
}
// 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 se = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync());
Assert.Equal(ErrorCode.Aborted, se.ErrorCode);
}
}
public async Task TestEntity_ConvertValuesWithoutPrecisionLossOrOverflow_Succeeds()
{
var sql = $"SELECT `t`.`Id`, `t`.`ByteCol`, `t`.`DecimalCol`, `t`.`FloatCol`" +
$"{Environment.NewLine}FROM `TestEntities` AS `t`{Environment.NewLine}" +
$"WHERE `t`.`Id` = @__p_0{Environment.NewLine}LIMIT 1";
_fixture.SpannerMock.AddOrUpdateStatementResult(sql, StatementResult.CreateResultSet(
new List <Tuple <V1.Type, string> >
{
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "Id"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Int64
}, "ByteCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Numeric
}, "DecimalCol"),
new Tuple <V1.Type, string>(new V1.Type {
Code = V1.TypeCode.Float64
}, "FloatCol"),
},
new List <object[]>
{
new object[] { 1L, 1L, "3.14", 1.0d },
}
));
using var db = new TypeConversionDbContext(ConnectionString);
var row = await db.TestEntities.FindAsync(1L);
Assert.Equal(1L, row.Id);
Assert.Equal((byte)1, row.ByteCol);
Assert.Equal(SpannerNumeric.Parse("3.14"), SpannerNumeric.FromDecimal(row.DecimalCol, LossOfPrecisionHandling.Truncate));
Assert.Equal(1.0d, row.FloatCol);
}
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 VersionNumberIsAutomaticallyGeneratedOnInsertAndUpdate()
{
using var db = new MockServerVersionDbContextUsingMutations(ConnectionString);
var singer = new SingersWithVersion {
SingerId = 1L, FirstName = "Pete", LastName = "Allison"
};
db.Singers.Add(singer);
await db.SaveChangesAsync();
Assert.Empty(_fixture.SpannerMock.Requests.Where(r => r is ExecuteBatchDmlRequest));
Assert.Collection(
_fixture.SpannerMock.Requests.Where(r => r is CommitRequest).Select(r => r as CommitRequest),
r =>
{
Assert.Collection(
r.Mutations,
mutation =>
{
Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase);
Assert.Equal("SingersWithVersion", mutation.Insert.Table);
Assert.Collection(
mutation.Insert.Columns,
column => Assert.Equal("SingerId", column),
column => Assert.Equal("FirstName", column),
column => Assert.Equal("LastName", column),
column => Assert.Equal("Version", column)
);
Assert.Collection(
mutation.Insert.Values,
row => Assert.Collection(
row.Values,
value => Assert.Equal("1", value.StringValue),
value => Assert.Equal("Pete", value.StringValue),
value => Assert.Equal("Allison", value.StringValue),
value => Assert.Equal("1", value.StringValue)
)
);
}
);
}
);
_fixture.SpannerMock.Reset();
// Update the singer and verify that the version number is first checked using a SELECT statement and then is updated in a mutation.
var concurrencySql = $"SELECT 1 FROM `SingersWithVersion` {Environment.NewLine}WHERE `SingerId` = @p0 AND `Version` = @p1";
_fixture.SpannerMock.AddOrUpdateStatementResult(concurrencySql, StatementResult.CreateSelect1ResultSet());
singer.LastName = "Peterson - Allison";
await db.SaveChangesAsync();
Assert.Empty(_fixture.SpannerMock.Requests.Where(r => r is ExecuteBatchDmlRequest));
Assert.Collection(
_fixture.SpannerMock.Requests.Where(r => r is ExecuteSqlRequest).Select(r => r as ExecuteSqlRequest),
r =>
{
Assert.Equal("1", r.Params.Fields["p0"].StringValue); // SingerId
Assert.Equal("1", r.Params.Fields["p1"].StringValue); // Version
}
);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(r => r is CommitRequest).Select(r => r as CommitRequest),
r =>
{
Assert.Collection(
r.Mutations,
mutation =>
{
Assert.Equal(Mutation.OperationOneofCase.Update, mutation.OperationCase);
Assert.Equal("SingersWithVersion", mutation.Update.Table);
Assert.Collection(
mutation.Update.Columns,
column => Assert.Equal("SingerId", column),
column => Assert.Equal("LastName", column),
column => Assert.Equal("Version", column)
);
Assert.Collection(
mutation.Update.Values,
row => Assert.Collection(
row.Values,
value => Assert.Equal("1", value.StringValue),
value => Assert.Equal("Peterson - Allison", value.StringValue),
value => Assert.Equal("2", value.StringValue)
)
);
}
);
}
);
}
public MockSpannerServerTests(SpannerMockServerFixture service)
{
_fixture = service;
// Add a simple SELECT 1 result to the mock server to be available for all test cases.
_fixture.SpannerMock.AddOrUpdateStatementResult("SELECT 1", StatementResult.CreateSelect1ResultSet());
}
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));
}
