A transaction is currently active. Parallel transactions are not supported

[Norbert Saint Georges]

Hi, there,

how can I avoid this kind of error?

FirebirdSql.Data.FirebirdClient.FbConnectionInternal.EnsureNoActiveTransaction()
FirebirdSql.Data.FirebirdClient.FbConnectionInternal.<BeginTransactionAsync>d__32.MoveNext()

--
Norbert Saint Georges
http://tetrasys.fi

[Jiří Činčura]

> how can I avoid this kind of error?

Do not start new transaction while the current one is active.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Norbert Saint Georges]

>> how can I avoid this kind of error?

> Do not start new transaction while the current one is active.

thank you for your reply.
How can I test if a transaction is open? or retrieve it?

[Jiří Činčura]

> How can I test if a transaction is open? or retrieve it?

There's no API for such thing. Your code should know about its connections and transactions.

[Norbert Saint Georges]

Jirí Cincura a écrit :

>> How can I test if a transaction is open? or retrieve it?
>
> There's no API for such thing. Your code should know about its connections
> and transactions.
>

the bool FbConnectionInternal.HasActiveTransaction is used internally,
can't we publish it?

[Jiří Činčura]

> can't we publish it?

We could. But I don't think we should. What would be value of that property? Show me some legitimate use case.

[Norbert]

I'm not sure about the legitimate translation, but below is a piece of code used in a Blazor that in the case of a user refresh a page while the cache is empty.
signalr restarts the procedure faster than the cache is validated.


public class GetPaginatedNorpaAgendaHandler(IServiceProvider serviceProvider,
IOptions<NorpaSettings> settings, ICacheService cacheService)
: IRequestHandler<GetPaginatedNorpaAgendaCommand, PaginatedList<Models.NorpaAgenda_rec>>
{

public async Task<PaginatedList<Models.NorpaAgenda_rec>> Handle(GetPaginatedNorpaAgendaCommand request, CancellationToken cancellationToken)
{
if (request.Cached)
{
var cacheKey = GenerateCacheKey(request);
return (await cacheService.GetSetCachedItemAsync(cacheKey, async () => await FetchAgendaAsync(request, cancellationToken)))!;
}
return await FetchAgendaAsync(request, cancellationToken);
}

private string GenerateCacheKey(GetPaginatedNorpaAgendaCommand request)
{
var queryString = request.Cached.ToString()+request.PageIndex.ToString()+request.AmountPerPage.ToString();
var hash = SHA256.HashData(Encoding.UTF8.GetBytes(queryString));
return typeof(Models.NorpaAgenda_rec).ToCacheKey(Convert.ToBase64String(hash));
}

private async Task<PaginatedList<Models.NorpaAgenda_rec>> FetchAgendaAsync(GetPaginatedNorpaAgendaCommand request, CancellationToken cancellationToken)
{
NorpaAgenda fAgenda = new();
var fconnection = await GetConnect();
using (FbTransaction ftransaction = await fconnection.BeginTransactionAsync(cancellationToken))
{
var clauseWhere = " 1 = 1 rows " + ((request.PageIndex - 1) * request.AmountPerPage).ToString() + " to " + request.AmountPerPage.ToString();
var ftmp = await fAgenda.SelectFromWheretoListRecord(ftransaction, clauseWhere);
await ftransaction.CommitAsync();
await ftransaction.DisposeAsync();
return ftmp.ToPaginatedList(request.PageIndex, request.AmountPerPage);

}
}

private async Task<FbConnection> GetConnect()
{
return (await cacheService.GetSetCachedItemAsync("GetPaginatedNorpaAgendaCommandFbConn", async () =>
{
FbConnection fconnection = new(settings.Value.ConnectionString!);
await fconnection.OpenAsync();
return fconnection!;
}, 600))!;
}
}

-----Message d'origine-----
De : firebird-n...@googlegroups.com <firebird-n...@googlegroups.com> De la part de Jirí Cincura
Envoyé : mardi 15 avril 2025 11:51
À : 'Mr. John' via firebird-net-provider <firebird-n...@googlegroups.com>
Objet : Re: [firebird-net-provider] Re: A transaction is currently active. Parallel transactions are not supported

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate.
---
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/e0e87042-9d8e-497e-a0d2-e6340a00565f%40app.fastmail.com.

[Jiří Činčura]

> code used in a Blazor that in the case of a user refresh a page while
> the cache is empty.
> signalr restarts the procedure faster than the cache is validated.

Sounds more like a problem with your caching layer.

[Norbert Saint Georges]

Jirí Cincura a écrit :

>> code used in a Blazor that in the case of a user refresh a page while
>> the cache is empty.
>> signalr restarts the procedure faster than the cache is validated.
>
> Sounds more like a problem with your caching layer.

I don't think so but .. (I've put the code snippet below)

On the other hand by deleting the async ‘await
fconnection.BeginTransactionAsync(cancellationToken))’ with
‘fconnection.BeginTransaction()’

I don't get this error anymore, weird :-(


public async Task<T?> GetSetCachedItemAsyncEx<T>(string cacheKey,
Func<Task<T>> getCacheItemAsync, int cacheTimeInSecondes =
CacheExtensions.MemoryCacheInSecondes)
{
if (_useRedis)
{
// Use Redis cache
var cachedData = await
_distributedCache.GetAsync(cacheKey);
if (cachedData != null)
{
return Deserialize<T>(cachedData);
}

var value = await getCacheItemAsync();
var data = Serialize(value);

var options = new DistributedCacheEntryOptions()

.SetSlidingExpiration(TimeSpan.FromSeconds(cacheTimeInSecondes));

await _distributedCache.SetAsync(cacheKey, data, options);
Keys.TryAdd(cacheKey, default);

return value;
}

// Use Memory cache
if (!_memoryCache.TryGetValue(cacheKey, out T? cacheEntry))
{
cacheEntry = await getCacheItemAsync();

var cacheEntryOptions = new MemoryCacheEntryOptions()

.SetSlidingExpiration(TimeSpan.FromMinutes(cacheTimeInSecondes));

_memoryCache.Set(cacheKey, cacheEntry, cacheEntryOptions);
Keys.TryAdd(cacheKey, default);
}
return cacheEntry;

[Gerdus van Zyl]

In that code you will always have the problem since it basically creates a shared firebird connection for all requests to that endpoint for the duration of the connection cache validity.
I think it is bad idea to cache the firebird connection, using the builtin connection pooling is almost always better.
You also already cache the result of the operation so don't know if you are winning by caching the firebird connection.

Alternatively you can use a static lock to only allow one thread to process at a time.

~ GerdusVZ

[Norbert Saint Georges]

Gerdus van Zyl a écrit :

You're right, but I haven't found a way around the ‘connection pool is
full’ error otherwise.
As the object is specific to the user session, with this way of doing
things I don't get any more pool errors and by removing the async, I
don't get any more errors (currently :-) ).

[Jiří Činčura]

> but I haven't found a way around the ‘connection pool is
> full’ error otherwise.

Are you disposing connections properly?

[Norbert Saint Georges]

Jirí Cincura a écrit :

Yes, in any case, after debugging a production project and spending
hours on it, I found nothing, either in debug or in fbtrace. Just a
longer delay when the pool is larger and this seems totally random
(only observation but ... , the Firebird logs have more errors 10054).