Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Feb 17, 2017 · 4 comments

Comments

@firebird-automations
Copy link

Submitted by: Jiri Fartak (jfartak_wms.cz)

Is duplicated by DNET714
Is duplicated by DNET400

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.

Commits: ce6cf54

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10811 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET714 [ DNET714 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET400 [ DNET400 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants