FirebirdSQL
"Unable to enlist in transaction, a local transaction already exists" in multithreaded environments [DNET833]
Submitted by: Daniel H (daniel90)
Votes: 1
We are developing an http://ASP.NET Core (.NET Core Version 2.1.1) web application. We are using a Firebird 3 Connection Pool with the following options in the connection string:
- Pooling: True - MinPoolSize: 0 - MaxPoolSize: 50 - Auto_Commit: false - Enlist: true
On serveral occasions we receive the following exception: "Unable to enlist in transaction, a local transaction already exists".
This happens in our minimal breaking exampple, if we call the following code with a few threads over HTTP GET:
\[HttpGet\]
public async Task<IActionResult\> GetX\(\)
\{
IEnumerable<string\> x= null;
using \(var ts = new TransactionScope\( TransactionScopeAsyncFlowOption\.Enabled\)\)
\{
using \(var connection = new FbConnection\(connectionString\)\)
\{
roles = await connection\.QueryAsync<string\>\(
sql: "SELECT strField FROM X",
commandType: CommandType\.Text\);
\}
ts\.Complete\(\);
\}
return Ok\(x\);
\}
If we encapsulate the TransactionScope block with a Semaphore it runs fine. Therefore the question: Does this library supporting multithreading or do we need to protect Firebird calls with a semaphore?
Is this is an issue with Firebird provider ? How I can make it work with TransactionScope? I will appreciate bit more information and possible workaround. Thank you.
Is this is an issue with Firebird provider ?
Likely.
How I can make it work with TransactionScope?
The issue states that locking to helps...
I will appreciate bit more information and possible workaround.
All the information I have are from this issue, so you have same information as I do. As a workaround (and even in general) I would not use TransactionScope.