public async Task BatchDmlAbortsTwice()
{
var spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict)
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteBatchDmlAsync_Fails(failures: 2, statusCode: StatusCode.Aborted)
.SetupCommitAsync()
.SetupRollbackAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var scheduler = (FakeScheduler)connection.Builder.SessionPoolManager.SpannerSettings.Scheduler;
var time0 = scheduler.Clock.GetCurrentDateTimeUtc();
var callee = new Callee(scheduler);
await scheduler.RunAsync(async() =>
{
var result = await connection.RunWithRetriableTransactionAsync(transaction => callee.DatabaseWorkAsync(connection, transaction));
Assert.Equal(3, result);
});
callee.AssertBackoffTimesInRange(time0);
callee.AssertLastCallTime(scheduler.Clock.GetCurrentDateTimeUtc());
}
public async Task CommitFailsOtherThanAborted()
{
var spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict)
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteBatchDmlAsync()
.SetupCommitAsync_Fails(failures: 1, StatusCode.Unknown)
.SetupRollbackAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var scheduler = (FakeScheduler)connection.Builder.SessionPoolManager.SpannerSettings.Scheduler;
var time0 = scheduler.Clock.GetCurrentDateTimeUtc();
var callee = new Callee(scheduler);
await scheduler.RunAsync(async() =>
{
var exception = await Assert.ThrowsAsync <SpannerException>(
() => connection.RunWithRetriableTransactionAsync(
transaction => callee.DatabaseWorkAsync(connection, transaction)));
Assert.Contains("Bang!", exception.InnerException.Message);
});
callee.AssertBackoffTimesInRange(time0);
callee.AssertLastCallTime(scheduler.Clock.GetCurrentDateTimeUtc());
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
numberOfPlayers = await cmd.ExecuteScalarAsync <long>();
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (int i = 0; i < 100; i++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "PlayerName", SpannerDbType.String }
});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
});
}
Console.WriteLine("Done inserting player records...");
}
public async Task <int> TransactionTagAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
return(await connection.RunWithRetriableTransactionAsync(async transaction =>
{
// Sets the transaction tag to "app=concert,env=dev".
// This transaction tag will be applied to all the individual operations inside
// the transaction.
transaction.Tag = "app=concert,env=dev";
// Sets the request tag to "app=concert,env=dev,action=update".
// This request tag will only be set on this request.
var updateCommand =
connection.CreateDmlCommand("UPDATE Venues SET Capacity = DIV(Capacity, 4) WHERE OutdoorVenue = false");
updateCommand.Tag = "app=concert,env=dev,action=update";
await updateCommand.ExecuteNonQueryAsync();
var insertCommand = connection.CreateDmlCommand(
@"INSERT INTO Venues (VenueId, VenueName, Capacity, OutdoorVenue, LastUpdateTime)
VALUES (@venueId, @venueName, @capacity, @outdoorVenue, PENDING_COMMIT_TIMESTAMP())",
new SpannerParameterCollection
{
{ "venueId", SpannerDbType.Int64, 81 },
{ "venueName", SpannerDbType.String, "Venue 81" },
{ "capacity", SpannerDbType.Int64, 1440 },
{ "outdoorVenue", SpannerDbType.Bool, true }
}
);
// Sets the request tag to "app=concert,env=dev,action=insert".
// This request tag will only be set on this request.
insertCommand.Tag = "app=concert,env=dev,action=insert";
return await insertCommand.ExecuteNonQueryAsync();
}));
}
public static async Task InsertScoresAsync(
string projectId, string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Insert 4 score records into the Scores table for each player
// in the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
Random r = new Random();
bool playerRecordsFound = false;
SpannerBatchCommand cmdBatch =
connection.CreateBatchDmlCommand();
var cmdLookup =
connection.CreateSelectCommand("SELECT * FROM Players");
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerRecordsFound = true;
for (int i = 0; i < 4; i++)
{
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-2, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-10, 1))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
SpannerCommand cmdInsert =
connection.CreateDmlCommand(
"INSERT INTO Scores "
+ "(PlayerId, Score, Timestamp) "
+ "VALUES (@PlayerId, @Score, @Timestamp)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "Score", SpannerDbType.Int64 },
{ "Timestamp",
SpannerDbType.Timestamp }
});
cmdInsert.Parameters["PlayerId"].Value =
reader.GetFieldValue <int>("PlayerId");
cmdInsert.Parameters["Score"].Value =
r.Next(1000, 1000001);
cmdInsert.Parameters["Timestamp"].Value =
randomTimestamp.ToString("o");
cmdBatch.Add(cmdInsert);
}
}
if (!playerRecordsFound)
{
Console.WriteLine("Parameter 'scores' is invalid "
+ "since no player records currently exist. First "
+ "insert players then insert scores.");
Environment.Exit((int)ExitCode.InvalidParameter);
}
else
{
await cmdBatch.ExecuteNonQueryAsync();
Console.WriteLine(
"Done inserting score records..."
);
}
}
});
}
}
public async Task WorkFails()
{
var spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict)
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupRollbackAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var scheduler = (FakeScheduler)connection.Builder.SessionPoolManager.SpannerSettings.Scheduler;
var time0 = scheduler.Clock.GetCurrentDateTimeUtc();
var callee = new Callee(scheduler);
await scheduler.RunAsync(async() =>
{
var exception = await Assert.ThrowsAsync <InvalidOperationException>(() => connection.RunWithRetriableTransactionAsync(callee.Fails));
Assert.Contains("Bang!", exception.Message);
});
callee.AssertBackoffTimesInRange(time0);
callee.AssertLastCallTime(scheduler.Clock.GetCurrentDateTimeUtc());
}
public async Task CommitAbortsAlways_RespectsOverallDeadline()
{
var spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict)
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteBatchDmlAsync()
.SetupCommitAsync_FailsAlways(statusCode: StatusCode.Aborted)
.SetupRollbackAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var scheduler = (FakeScheduler)connection.Builder.SessionPoolManager.SpannerSettings.Scheduler;
// This test needs a little bit more of real time, else it's flaky.
scheduler.RealTimeTimeout = TimeSpan.FromSeconds(60);
var time0 = scheduler.Clock.GetCurrentDateTimeUtc();
var callee = new Callee(scheduler);
await scheduler.RunAsync(async() =>
{
var exception = await Assert.ThrowsAsync <SpannerException>(() => connection.RunWithRetriableTransactionAsync(transaction => callee.DatabaseWorkAsync(connection, transaction)));
Assert.True(exception.IsRetryable && !exception.SessionExpired);
Assert.Contains("Bang!", exception.InnerException.Message);
});
// The minimum calls that can be made (assuming maximum jitter always) in that time is 60 * 60 / 32 * 2 which is 56.25,
// plus 1 because the first one has no delay.
// The maximum number of calls that can be made in 1 hour:
// - Given that 2^7 * 250 = 32000, the first 8 calls take, at a minimum 31_750ms, approx 32s.
// - The rest will happen at most (60 * 60 - 32) / 32 which is 111.5
callee.AssertCalledInRange(57, 120);
// The overall deadline is of 1 hour. The maximum backoff delay is of 32s.
// Because of jitter retries can stop at any time after 60mins - 64s, let's give it 2 minutes of range.
Assert.InRange(scheduler.Clock.GetCurrentDateTimeUtc(), time0.AddMinutes(58), time0.AddMinutes(60));
}
