Issue Details (XML | Word | Printable)

Key: DNET-738
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Jiri Fartak
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

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

Created: 17/Feb/17 03:37 PM   Updated: 18/Jul/17 10:29 AM
Component/s: ADO.NET Provider
Affects Version/s: 5.1.1.0
Fix Version/s: 5.8.1.0

Environment: FirebirdServer 2.5.4, .NET 4.5.1, EF 6.1.3
Issue Links:
Duplicate
 


 Description  « Hide
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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.