public async Task SingleCommit(DbCommand command, CancellationToken cancellation)
{
NpgsqlTransaction tx = null;
try
{
tx = _connection.BeginTransaction();
command.Connection = _connection;
await command.ExecuteNonQueryAsync(cancellation).ConfigureAwait(false);
await tx.CommitAsync(cancellation).ConfigureAwait(false);
}
catch (Exception)
{
if (tx != null)
{
await tx.RollbackAsync(cancellation).ConfigureAwait(false);
}
// Let the caller deal with retries
await reopenConnectionIfNecessary(cancellation).ConfigureAwait(false);
throw;
}
finally
{
tx?.SafeDispose();
}
}
private async Task ProcessFailureAsync <TEntity>(
SqlOperation operation,
FailureStrategies currentFailureStrategy,
Exception ex,
NpgsqlTransaction transaction,
ICollection <TEntity> operatedElements,
ICollection <TEntity> notOperatedElements,
ICollection <TEntity> problemElements,
CancellationToken cancellationToken) where TEntity : class
{
if (ex == null)
{
throw new ArgumentNullException(nameof(ex));
}
_logger.LogError(ex, "Bulk command execution failed");
if (transaction != null)
{
await transaction.RollbackAsync(cancellationToken);
}
if (currentFailureStrategy == FailureStrategies.StopEverything ||
currentFailureStrategy == FailureStrategies.StopEverythingAndRollback)
{
throw new SqlBulkExecutionException <TEntity>(ex, operation, currentFailureStrategy, problemElements, notOperatedElements, operatedElements);
}
}
private async Task DeleteStreamInternal(
PostgresqlStreamId streamId,
int expectedVersion,
NpgsqlTransaction transaction,
CancellationToken cancellationToken)
{
using (var command = BuildFunctionCommand(
_schema.DeleteStream,
transaction,
Parameters.StreamId(streamId),
Parameters.ExpectedVersion(expectedVersion),
Parameters.CreatedUtc(_settings.GetUtcNow?.Invoke()),
Parameters.DeletedStreamId,
Parameters.DeletedStreamIdOriginal,
Parameters.DeletedStreamMessage(streamId)))
{
try
{
await command.ExecuteNonQueryAsync(cancellationToken).NotOnCapturedContext();
}
catch (PostgresException ex) when(ex.IsWrongExpectedVersion())
{
await transaction.RollbackAsync(cancellationToken).NotOnCapturedContext();
throw new WrongExpectedVersionException(
ErrorMessages.DeleteStreamFailedWrongExpectedVersion(streamId.IdOriginal, expectedVersion),
streamId.IdOriginal,
expectedVersion,
ex);
}
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="connection"></param>
/// <param name="executeAsync"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <TResult> TransactionalExecuteAsync <TResult>(this NpgsqlConnection connection,
Func <Task <TResult> > executeAsync,
NpgsqlTransaction transaction,
CancellationToken cancellationToken = default)
{
// Variables
var result = default(TResult);
var hasTransaction = transaction != null;
// Open
await connection.EnsureOpenAsync(cancellationToken);
// Ensure transaction
if (hasTransaction == false)
{
#if NET5_0
transaction = await connection.BeginTransactionAsync(cancellationToken);
#else
transaction = connection.BeginTransaction();
#endif
}
try
{
// Execute
if (executeAsync != null)
{
result = await executeAsync();
}
// Commit
if (hasTransaction == false)
{
await transaction.CommitAsync(cancellationToken);
}
}
catch
{
// Rollback
if (hasTransaction == false)
{
await transaction.RollbackAsync(cancellationToken);
}
// Throw
throw;
}
finally
{
// Dispose
if (hasTransaction == false)
{
await transaction.DisposeAsync();
}
}
// Return
return(result);
}
public Task RollbackAsync(CancellationToken token)
{
if (_options.OwnsTransactionLifecycle)
{
return(Transaction.RollbackAsync(token));
}
return(Task.CompletedTask);
}
public static async Task <bool> ExecTransactionAsync(List <string> sqls, string connection = null)
{
var connString = GetConnection(connection);
bool result = false;
try
{
using (var conn = new NpgsqlConnection(connString))
{
await conn.OpenAsync();
NpgsqlTransaction tran = conn.BeginTransaction();
try
{
foreach (var sql in sqls)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
result = await cmd.ExecuteNonQueryAsync() > 0;
}
}
}
catch (Exception ex0)
{
Log.WriteLog("ERROR-DB：" + ex0.Message);
result = false;
}
finally
{
if (result)
{
await tran.CommitAsync();
}
else
{
await tran.RollbackAsync();
}
}
}
}
catch (Exception ex)
{
Log.WriteLog("ERROR-DB：" + ex.Message);
}
return(result);
}
public async Task <int> WriteAsync(IEnumerable <AppMetric> appMetrics)
{
int affectedRows = 0;
if (appMetrics == null)
{
throw new ArgumentNullException(nameof(appMetrics));
}
if (appMetrics.Count() == 0)
{
return(affectedRows);
}
using (NpgsqlConnection conn = await OpenConnectionAsync())
using (NpgsqlTransaction tx = conn.BeginTransaction())
using (NpgsqlCommand upsertCmd = new NpgsqlCommand(mMetricsUpsertSql, conn, tx))
{
try
{
NpgsqlParameter pMetricId = upsertCmd.Parameters.Add("m_id",
NpgsqlDbType.Varchar);
NpgsqlParameter pMetricCategory = upsertCmd.Parameters.Add("m_category",
NpgsqlDbType.Varchar);
NpgsqlParameter pMetricValue = upsertCmd.Parameters.Add("m_value",
NpgsqlDbType.Bigint);
await upsertCmd.PrepareAsync();
foreach (AppMetric m in appMetrics)
{
pMetricId.Value = m.Id.ValueId;
pMetricCategory.Value = m.Id.ValueCategory;
pMetricValue.Value = m.Value;
affectedRows += await upsertCmd.ExecuteNonQueryAsync();
}
await tx.CommitAsync();
}
catch (Exception)
{
await tx.RollbackAsync();
throw;
}
}
return(affectedRows);
}
public bool RollbackTransaction()
{
try
{
trans.RollbackAsync(); // 异步回滚
isRollback = true;
return(true);
}
catch (Exception ex)
{
LastError = ex.Message;
Logger.WriteLogExcept(LogTitle, ex);
return(false);
}
finally
{
trans = null;
}
}
protected async Task<T> WithConnection<T> (Func<IDbConnection, IDbTransaction, Task<T>> f)
{
T result = default(T);
using (NpgsqlConnection sqlConnection = new NpgsqlConnection(_settings.Value.ConnectionString))
{
await sqlConnection.OpenAsync();
using (NpgsqlTransaction transaction = sqlConnection.BeginTransaction())
{
try
{
result = await f(sqlConnection, transaction);
transaction.Commit();
}
catch (Exception ex)
{
_logger.LogError(ex.Message, ex);
await transaction.RollbackAsync();
throw;
}
}
return result;
}
}
private async Task <object> ExecStoredProcedureAsync(ExecType execType)
{
object result = null;
NpgsqlTransaction transaction = null;
using (_command.Connection)
{
try
{
await InitCommandAsync();
transaction = _command.Connection.BeginTransaction();
using (_command)
{
if (_needPreparation)
{
await _command.PrepareAsync();
}
if (_needReloadTypes)
{
_command.Connection?.ReloadTypes();
}
switch (execType)
{
case ExecType.NonQuery:
{
await _command.ExecuteNonQueryAsync();
break;
}
case ExecType.Reader:
{
await _command.ExecuteNonQueryAsync();
var fetchAllCommand = new NpgsqlCommand($"FETCH ALL IN \"{_refCursorName}\";", _command.Connection);
fetchAllCommand.Transaction = transaction;
var reader = await fetchAllCommand.ExecuteReaderAsync();
using (reader)
{
do
{
while (await reader.ReadAsync())
{
_dataReaderAction?.Invoke(reader);
}
}while (await reader.NextResultAsync());
}
break;
}
case ExecType.Scalar:
{
await _command.ExecuteNonQueryAsync();
var fetchAllCommand = new NpgsqlCommand($"FETCH ALL IN \"{_refCursorName}\";", _command.Connection);
fetchAllCommand.Transaction = transaction;
result = await fetchAllCommand.ExecuteScalarAsync();
break;
}
}
}
transaction.Commit();
}
catch (PostgresException ex)
{
transaction?.RollbackAsync();
ProcessException(ex);
}
}
return(result);
}
public async Task <int> WriteAsync(IEnumerable <TaskPerformanceStats> executionTimeInfoBatch)
{
int affectedRows = 0;
if (executionTimeInfoBatch == null)
{
throw new ArgumentNullException(nameof(executionTimeInfoBatch));
}
if (executionTimeInfoBatch.Count() == 0)
{
return(0);
}
using (NpgsqlConnection conn = await OpenConnectionAsync())
using (NpgsqlTransaction tx = conn.BeginTransaction())
using (NpgsqlCommand upsertCmd = new NpgsqlCommand(mPerfMonInfoUpsertSql, conn, tx))
{
try
{
NpgsqlParameter pPayloadType = upsertCmd.Parameters
.Add("payload_type", NpgsqlDbType.Varchar);
NpgsqlParameter pNExecutionCycles = upsertCmd.Parameters
.Add("n_execution_cycles", NpgsqlDbType.Bigint);
NpgsqlParameter pLastExecutionTime = upsertCmd.Parameters
.Add("last_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pAvgExecutionTime = upsertCmd.Parameters
.Add("avg_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pFastestExecutionTime = upsertCmd.Parameters
.Add("fastest_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pLongestExecutionTime = upsertCmd.Parameters
.Add("longest_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pTotalExecutionTime = upsertCmd.Parameters
.Add("total_execution_time", NpgsqlDbType.Bigint);
await upsertCmd.PrepareAsync();
foreach (TaskPerformanceStats s in executionTimeInfoBatch)
{
pPayloadType.Value = s.PayloadType;
pNExecutionCycles.Value = 1;
pLastExecutionTime.Value = s.DurationMilliseconds;
pAvgExecutionTime.Value = s.DurationMilliseconds;
pFastestExecutionTime.Value = s.DurationMilliseconds;
pLongestExecutionTime.Value = s.DurationMilliseconds;
pTotalExecutionTime.Value = s.DurationMilliseconds;
affectedRows += await upsertCmd.ExecuteNonQueryAsync();
}
await tx.CommitAsync();
}
catch (Exception)
{
await tx.RollbackAsync();
throw;
}
}
return(affectedRows);
}
public async Task <IQueuedTaskToken> DequeueAsync(params string[] selectTaskTypes)
{
NpgsqlConnection conn = null;
QueuedTask dequeuedTask = null;
QueuedTaskResult dequeuedTaskResult = null;
PostgreSqlQueuedTaskToken dequeuedTaskToken = null;
MonotonicTimestamp startDequeue;
DateTimeOffset refNow = mTimestampProvider.GetNow();
CheckNotDisposedOrThrow();
try
{
mLogger.DebugFormat("Begin dequeue task. Looking for types: {0}.",
string.Join <string>(",", selectTaskTypes));
startDequeue = MonotonicTimestamp
.Now();
conn = await OpenQueueConnectionAsync();
if (conn == null)
{
return(null);
}
using (NpgsqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
//1. Dequeue means that we acquire lock on a task in the queue
// with the guarantee that nobody else did, and respecting
// the priority and static locks (basically the task_locked_until which says
// that it should not be pulled out of the queue until the
// current abstract time reaches that tick value)
dequeuedTask = await TryDequeueTaskAsync(selectTaskTypes, refNow, conn, tx);
if (dequeuedTask != null)
{
//2. Mark the task as being "Processing" and pull result info
// The result is stored separately and it's what allows us to remove
// the task from the queue at step #2,
// whils also tracking it's processing status and previous results
dequeuedTaskResult = await TryUpdateTaskResultAsync(dequeuedTask, conn, tx);
if (dequeuedTaskResult != null)
{
await tx.CommitAsync();
dequeuedTaskToken = new PostgreSqlQueuedTaskToken(dequeuedTask,
dequeuedTaskResult,
refNow);
}
}
if (dequeuedTaskToken != null)
{
IncrementDequeueCount(MonotonicTimestamp.Since(startDequeue));
}
else
{
await tx.RollbackAsync();
}
}
}
finally
{
if (conn != null)
{
await conn.CloseAsync();
conn.Dispose();
}
}
return(dequeuedTaskToken);
}