public async Task Save(List <AsnDescriptionCountryInfo> asnNameCountryInfos) { _log.LogInformation("Persisting asn to description and country info"); Stopwatch stopwatch = Stopwatch.StartNew(); IEnumerable <IEnumerable <AsnDescriptionCountryInfo> > batches = asnNameCountryInfos.Batch(15000); string connectionString = await _connectionInfo.GetConnectionStringAsync(); using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { await connection.OpenAsync(); using (NpgsqlTransaction transaction = connection.BeginTransaction()) { await Truncate(connection, transaction); foreach (IEnumerable <AsnDescriptionCountryInfo> batch in batches) { await Save(batch.ToList(), connection, transaction); } await transaction.CommitAsync(); await RefreshMaterializedView(connection); } connection.Close(); } _log.LogInformation($"Persisted asn to description and country info in {stopwatch.ElapsedMilliseconds} ms."); }
public async Task Save(List <IpToAsn> ipToAsns) { _log.LogInformation("Persisting ip to asn info"); Stopwatch stopwatch = Stopwatch.StartNew(); IEnumerable <IEnumerable <IpToAsn> > batches = ipToAsns.Batch(20000); string connectionString = await _connectionInfo.GetConnectionStringAsync(); using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { await connection.OpenAsync(); using (NpgsqlTransaction transaction = connection.BeginTransaction()) { await Truncate(connection, transaction); foreach (IEnumerable <IpToAsn> batch in batches) { await Save(batch.ToList(), connection, transaction); } await transaction.CommitAsync(); await RefreshMaterializedView(connection); } connection.Close(); } _log.LogInformation($"Persisted ip to asn info in {stopwatch.ElapsedMilliseconds} ms."); }
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(); } }
/// <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 async Task CommitAsync(CancellationToken token) { if (_options.OwnsTransactionLifecycle) { await Transaction.CommitAsync(token).ConfigureAwait(false); await Transaction.DisposeAsync().ConfigureAwait(false); } }
public async Task Commit() { if (_transaction == null) { return; } await _transaction.CommitAsync(); _transaction = null; Commited?.Invoke(this); }
private async ValueTask ExecuteWithTransactionAsync(string statement, object param) { if (_connection.State is ConnectionState.Closed) { await _connection.OpenAsync(); } await using NpgsqlTransaction transaction = await _connection.BeginTransactionAsync(IsolationLevel.ReadCommitted); await _connection.ExecuteAsync(statement, param, transaction); await transaction.CommitAsync(); }
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 async Task Commit(CancellationToken stoppingToken) { if (_entries != null) { var messagesToSave = _entries.Where(x => x.ProcessedUtc.HasValue); foreach (var message in messagesToSave) { var sql = $"update \"_outbox\" set " + $"{Q(nameof(OutboxEntry.ProcessedUtc))} = @processedUtc " + $"where {Q("Id")} = @id;"; await _connection.ExecuteAsync(sql, new { id = message.MessageId, processedUtc = DateTime.UtcNow }, _transaction); } } await _transaction.CommitAsync(stoppingToken); }
public async Task InsertTaskAndResultDataAsync(IEnumerable <Tuple <QueuedTask, QueuedTaskResult> > queuedTaskPairs) { using (NpgsqlConnection conn = await OpenDbConnectionAsync()) using (NpgsqlTransaction tx = conn.BeginTransaction()) { foreach (Tuple <QueuedTask, QueuedTaskResult> p in queuedTaskPairs) { if (p.Item1 != null) { await AddQueuedTaskAsync(p.Item1, conn, tx); } if (p.Item2 != null) { await AddQueuedTaskResultAsync(p.Item2, conn, tx); } } await tx.CommitAsync(); } }
public async Task Save(List <string> publicSuffixEntries) { _log.LogInformation("Persisting public suffix list"); Stopwatch stopwatch = Stopwatch.StartNew(); string connectionString = await _connectionInfo.GetConnectionStringAsync(); using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { await connection.OpenAsync(); using (NpgsqlTransaction transaction = connection.BeginTransaction()) { await Truncate(connection, transaction); await Save(publicSuffixEntries, connection, transaction); await transaction.CommitAsync(); await RefreshMaterializedView(connection); } connection.Close(); } _log.LogInformation($"Persisted public suffix list in {stopwatch.ElapsedMilliseconds} ms."); }
public async Task <BulkOperationResult <TEntity> > ExecuteOperationAsync <TEntity>( SqlOperation operation, NpgsqlConnection connection, ICollection <TEntity> elements, CancellationToken cancellationToken) where TEntity : class { if (elements == null) { throw new ArgumentNullException(nameof(elements)); } if (connection == null) { throw new ArgumentNullException(nameof(connection)); } if (_logger.IsEnabled(LogLevel.Trace)) { _logger.LogTrace($"Preparing a bulk {operation} operation"); } Stopwatch sw = null; if (_logger.IsEnabled(LogLevel.Information)) { sw = Stopwatch.StartNew(); } var entityType = typeof(TEntity); if (!_options.SupportedEntityTypes.ContainsKey(entityType)) { throw new ArgumentException($"Mapping for type '{entityType.FullName}' was not found.", nameof(elements)); } var entityProfile = _options.SupportedEntityTypes[entityType]; var maximumEntitiesPerSent = GetCurrentMaximumSentElements(entityProfile); var currentFailureStrategy = GetCurrentFailureStrategy(entityProfile); var result = currentFailureStrategy == FailureStrategies.IgnoreFailure ? new BulkOperationResult <TEntity>(elements.Count) : null; if (elements.Count == 0) { return(result); } var(needOperatedElements, needNotOperatedElements, needProblemElements) = GetExtendedFailureInformation(entityProfile); NpgsqlTransaction transaction = null; if (currentFailureStrategy == FailureStrategies.StopEverythingAndRollback) { if (connection.State != ConnectionState.Open) { await connection.OpenAsync(cancellationToken); } transaction = connection.BeginTransaction(); } var totalSqlSize = 0L; var totalSqlCommandCount = 0; if (maximumEntitiesPerSent == 0 || elements.Count <= maximumEntitiesPerSent) { try { var(sqlSize, sqlCommandCount) = await ExecutePortionAsync(operation, connection, elements, entityProfile, cancellationToken); totalSqlSize += sqlSize; totalSqlCommandCount += sqlCommandCount; if (transaction != null) { await transaction.CommitAsync(cancellationToken); } result?.Operated.AddRange(elements); if (sw != null) { var(size, suffix) = totalSqlSize.PrettifySize(); _logger.LogInformation($"{operation}ed {elements.Count} {entityProfile.EntityType.Name} elements, {totalSqlCommandCount} sql commands ({size:0.00}{suffix}) in total {sw.ElapsedMilliseconds / 1000f:0.000} s"); } } catch (Exception ex) { var notOperatedElements = needNotOperatedElements ? elements : null; var operatedElements = needOperatedElements ? new List <TEntity>() : null; var problemElements = needProblemElements ? elements : null; await ProcessFailureAsync(operation, currentFailureStrategy, ex, transaction, operatedElements, notOperatedElements, problemElements, cancellationToken); result?.NotOperated.AddRange(elements); } } else { var operated = needOperatedElements ? new List <TEntity>(elements.Count) : null; var notOperated = needNotOperatedElements ? new List <TEntity>(elements.Count) : null; var problem = needProblemElements ? new List <TEntity>(maximumEntitiesPerSent) : null; var iterations = Math.Round((decimal)elements.Count / maximumEntitiesPerSent, MidpointRounding.AwayFromZero); for (var i = 0; i < iterations; i++) { var portion = elements.Skip(i * maximumEntitiesPerSent) .Take(maximumEntitiesPerSent) .ToList(); try { var(sqlSize, sqlCommandCount) = await ExecutePortionAsync(operation, connection, portion, entityProfile, cancellationToken); totalSqlSize += sqlSize; totalSqlCommandCount += sqlCommandCount; operated?.AddRange(portion); result?.Operated.AddRange(portion); } catch (Exception ex) { if (currentFailureStrategy != FailureStrategies.IgnoreFailure) { problem?.AddRange(portion); } result?.NotOperated.AddRange(portion); switch (currentFailureStrategy) { // Ignore strategy is ignored here because it does not throw an SqlBulkExecutionException case FailureStrategies.StopEverything: { //Since i is not incremented yet, this command will add current portion to notOperated as well as the rest of elements notOperated?.AddRange(elements.Skip(i * maximumEntitiesPerSent) .ToList()); break; } case FailureStrategies.StopEverythingAndRollback: { operated?.Clear(); notOperated?.AddRange(elements); break; } } await ProcessFailureAsync(operation, currentFailureStrategy, ex, transaction, operated, notOperated, problem, cancellationToken); } } if (transaction != null) { await transaction.CommitAsync(cancellationToken); } if (sw != null) { var(size, suffix) = totalSqlSize.PrettifySize(); _logger.LogInformation($"{operation}ed {elements.Count} {entityProfile.EntityType.Name} elements in {iterations} steps, {totalSqlCommandCount} sql commands ({size:0.00}{suffix}) in total {sw.ElapsedMilliseconds / 1000f:0.000} s"); } } return(result); }
public virtual async Task Commit() { await _transaction.CommitAsync(); }
public Task CommitAsync(CancellationToken cancellationToken) => _transaction.CommitAsync(cancellationToken);
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); }
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); }