Broken connection with Firebird database from ASP.NET Core

Question

I'm developing desktop application based on Firebird 2.5 database in WPF / .NET Framework 4.7.2.

I have API to this application in ASP.NET Core 2.2.

In the API, I'm using the same database which:

• FirebirdSql.Data.FirebirdClient 7.1.1
• FirebirdSql.EntityFrameworkCore.Firebird 7.1.1
• Microsoft.EntityFrameworkCore 2.2.4

API is running on IIS Server.

Mobile devices (up to 20) connect to this API (HTTP/JSON).

Sometimes all request with updates / deletion stop working with an error:

---------- MAIN EXCEPTION ---------------

Typ wyjątku: Microsoft.EntityFrameworkCore.DbUpdateException
Wiadomosć błędu: An error occurred while updating the entries. See the inner exception for details.
TargetSite: Void Execute(Microsoft.EntityFrameworkCore.Storage.IRelationalConnection)
Source: Microsoft.EntityFrameworkCore.Relational
HResult: -2146233088
StackTrace:    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)


---------- INNER EXCEPTION ---------------

Typ wyjątku: FirebirdSql.Data.FirebirdClient.FbException
Wiadomosć błędu: Dynamic SQL Error
SQL error code = -206
Column unknown
P0
At line 2, column 10
TargetSite: FirebirdSql.Data.FirebirdClient.FbDataReader ExecuteReader(System.Data.CommandBehavior)
Source: FirebirdSql.Data.FirebirdClient
HResult: -2147467259
StackTrace:    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)


------------ INNER EXCEPTION ------------

Typ wyjątku: FirebirdSql.Data.Common.IscException
Wiadomosć błędu: Dynamic SQL Error
SQL error code = -206
Column unknown
P0
At line 2, column 10
TargetSite: Void ProcessResponse(FirebirdSql.Data.Client.Managed.IResponse)
Source: FirebirdSql.Data.FirebirdClient
HResult: -2146233088
StackTrace:    at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ProcessResponse(IResponse response)
   at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadResponse[TResponse]()
   at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.Prepare(String commandText)
   at FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)

When this error occurs once, all update / delete requests stop working. Select statements still work.

After restarting the website, the requests start to work normally again. This situation occurs from 0 to a few times a day.

I trying to reproduce on development machine, and sometimes I noticed that another errors are showing before this error:

Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded.

An error occurred while updating the entries. See the inner exception for details.
invalid request BLR at offset 55
undefined parameter number

An error occurred while updating the entries. See the inner exception for details.
lock conflict on no wait transaction
deadlock update conflicts with concurrent update
concurrent transaction number is 5863195

I can reproduce this error with simultaneous run of the same request, but in client environment, they are editing this same table but not the same rows.

I assume that can be situations when one device can send two request at the same time to update the same data (background thread + user thread).

All queries and updates are creating with this same approach:

using (var db = DbContext(connextionString))
{
    // do changes 
    db.SaveChanges();
}

Can somebody give a hint where the problem is?

Answer 1

As Svyatoslav proposed I was upgraded asp.core to version 3.1 and Entity Framework to version 3.1.32 and Firebird libraries to version 7.10.1. After upgrade I cant't reproduce the problem.