[Firebird-net-provider] [FB-Tracker] Created: (DNET-738) EF does not detect concurrency update fail

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

EF does not detect concurrency update fail since FBClient returns row filled with null (storage generated) values instead of "nothing" (empty result)
-----------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: DNET-738
                 URL: http://tracker.firebirdsql.org/browse/DNET-738
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider
    Affects Versions: 5.1.1.0
         Environment: FirebirdServer 2.5.4, .NET 4.5.1, EF 6.1.3
            Reporter: Jiri Fartak
            Assignee: Jiri Cincura

Synopsis:

FirebirdClient uses Execute block statements wrapping UPDATE command clause having RETURNING statement to return server-generated values (e.g. identity column, version...etc) back to EF.

This is typical example of such command:
-----
EXECUTE BLOCK (
p0 TIMESTAMP = @p0, p1 VARCHAR(16) CHARACTER SET UTF8 = @p1, p2 BIGINT = @p2, p3 CHAR(16) CHARACTER SET OCTETS = @p3, p4 BIGINT = @p4, p5 INT = @p5
) RETURNS (
"created" TIMESTAMP, "creator" VARCHAR(16), "deleted" TIMESTAMP, "deletor" VARCHAR(16), "version" INT)

AS BEGIN

UPDATE "PersistentObject"
SET "createdByOid" = NULL, "modified" = :p0, "modifier" = :p1, "modifiedByOid" = :p2, "deletedByOid" = NULL, "clsid" = :p3
WHERE (("oid" = :p4) AND ("version" = :p5))
RETURNING "created", "creator", "deleted", "deletor", "version" INTO :"created", :"creator", :"deleted", :"deletor", :"version";

SUSPEND;
END

------

The problem arises when UPDATE fails - when no row is updated due to concurrency, since no rows satisfied WHERE constraints (version has changed). If so, the following SUSPEND directive returns the row having ALL columns filled with null values to the caller.

This is misleading to EF (control layer), beacuse the presence of the row (albait with null values) causes, that command issued by EF will read this row (via FBDataReader()) as valid row (and so rowsAffected is 1 and not zero) and EF will then try to update entity's properties with these server-ganerated values (calls translator.BackPropagateServerGen()). However, since the row is having only null values, then properties that require value (.Required())  will make the EF to throw exception due to inconsistency (see below).

This is excerpt of control in System\Data\Mapping\Update\Internal\UpdateTranslator.cs:

		  foreach (UpdateCommand command in orderedCommands)
                {
                    // Remember the data sources so that we can throw meaningful exception
                    source = command;

		!!!! rowsAffected will have value of 1 instead of 0, since command.Execute returned row with nulls and not empty result set

                    long rowsAffected = command.Execute(translator, connection, identifierValues, generatedValues);

		!!! The line below would throw DbConcurrencyException (as we want and expect) if rowsAffected would be zero, this does not happen

                    translator.ValidateRowsAffected(rowsAffected, source);
                }

!! Following method throws the exception informing about inconsistency in server generated value and requirements for property in model
  translator.BackPropagateServerGen(generatedValues);

The FBClient behavior makes EF to throw this exception - informing about inconsistency - even though the problem was caused by concurrency update:

A null store-generated value was returned for a non-nullable member 'Created' of type 'WMS.Altair.Service.Repository.PersistentObjectModel'.
A null store-generated value was returned for a non-nullable member 'Created' of type 'WMS.Altair.Service.Repository.PersistentObjectModel'.
Výpis: Vnitřní výpis chyby:--->Typ chyby: DbUpdateExceptionZpráva: A null store-generated value was returned for a non-nullable member 'Created' of type'WMS.Altair.Service.Repository.PersistentObjectModel'.Výpis: v WMS.Altair.Service.Repository.IPAddressManager.Update(IPAddressModel model) v WMS.Altair.Web.Controllers.IPAddressController.Edit(IPAddressEditViewModel viewModel, StoreObjectState objectStates)Vnitřní výpis chyby:--->Typ chyby: UpdateExceptionZpráva: A null store-generated value was returned for a non-nullable member 'Created' of type 'WMS.Altair.Service.Repository.PersistentObjectModel'.Výpis: v System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult.AlignReturnValue(Object value, EdmMember member) v System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult.SetServerGenValue(Object value) v System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.BackPropagateServerGen(List`1 generatedValues) v System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() v System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.b__2(UpdateTranslator ut) v System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction) v System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update() v System.Data.Entity.Core.Objects.ObjectContext.b__35() v System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) v System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) v System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.b__27() v System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation) v System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) v System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) v System.Data.Entity.Internal.InternalContext.SaveChanges() 

However, this exception would be throwed when FBClient would return empty result to EF:

--->
Typ chyby: DbUpdateConcurrencyException
Zpráva: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.
Výpis: v WMS.Altair.Service.Repository.IPAddressManager.Update(IPAddressModel model) v C:\Users\genx.INTRANET\Documents\Visual Studio 2013\Projects\AltairWebClient\AltairServices\Repository\UseCaseManager\IPAddressManager\IPAddressManager.cs:řádek 661 v WMS.Altair.Web.Controllers.IPAddressController.Edit(IPAddressEditViewModel viewModel, StoreObjectState objectStates) v C:\Users\genx.INTRANET\Documents\Visual Studio 2013\Projects\AltairWebClient\AltairWebClient\Controllers\IPAddressController.cs:řádek 485
Vnitřní výpis chyby:
--->
Typ chyby: OptimisticConcurrencyException
Zpráva: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.
Výpis: v System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int64 rowsAffected, UpdateCommand source) v System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() v System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) v System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) v System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) v System.Data.Entity.Internal.InternalContext.SaveChanges()

----

Suggested Fix:

Instead of simple unconditional calling of SUSPEND we made minor change to the DmlSqlGenerator.GenerateReturningSql() method:

...
if (row_count > 0) then SUSPEND;
commandText.AppendLine("END");

...

if UPDATE command in EXECUTE BLOCK succeeded then Firebird Server sets row_count to 1 and then we sent the row to the caller, otherwise we do nothing (caller obtains empty result set). According Firebird database manual, the row_count should be supported since FB 1.5+ and it should not be too limiting for today deployments.

EF then gets empty result set and correctly detects it as DbConecurrencyException. We did minor testing and it looks promising.

Affected provider versions (where seen): 4.7.0.0, 5.1.1.0 and maybe others if GenerateReturningSql() generates the same pattern.

Jiri Fartak, WMS s.r.o.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[Firebird-net-provider] [FB-Tracker] Created: (DNET-738) EF does not detect concurrency update fail

A powerful, cross platform, SQL database system

[Firebird-net-provider] [FB-Tracker] Created: (DNET-738) EF does not detect concurrency update fail since FBClient returns row filled with null (storage generated) values instead of "nothing" (empty result)