实体框架，PostgreSQL，带有隐藏xmin列的乐观并发 [英] Entity Framework, PostgreSQL, Optimistic Concurrency with hidden xmin column
问题描述
我正在针对PostgreSQL 9.1数据库使用Entity Framework（模型优先方法）。
您可能都知道每个表都有一个名为 xmin 的隐藏列，我将使用该列来帮助EF确定执行前该行是否已更改
我知道PostgreSQL的内部结构可能会发生变化，对于生产代码来说这可能根本不是一个好主意，但我想尝试一下。 / p>
手动更新模型以使其包含一个表的该列并测试其行为将需要哪些步骤？
TIA。
编辑1 ：到目前为止，这是我使用模型优先方法和自跟踪实体的方法。
已对模型进行了以下修改：
CSDL：<属性名称= xmin类型=十进制 Nullable = false Precision = 15比例= 0 ConcurrencyMode =已修复 />
SSDL：<属性名称= xmin类型=数字 Nullable = false Precision = 15 Scale = 0 StoreGeneratedPattern =计算 />
xmin列在SELECT上有效地检索，然后在UPDATE期间使用：
更新 schema。 mytable设置 column1 = FALSE WHERE（ pk = cast（7526作为数字））AND（ xmin = cast（1185877为数字））
这里的问题是xmin列上使用的强制类型转换。它失败。到目前为止，我发现它完全不需要任何转换。但是我不确定如何告诉EF在此列的查询中完全不使用强制转换。
BTW xmin列的数据类型为'xid' 对于Npgsql ADO.NET提供程序来说是未知的。
编辑2 ：这是生成SQL文本的提供程序。查看提供者代码后，我可以确认使用整数数据类型时没有强制转换。因此，我在实体中使用了整数来存储值，并尝试选择和更新实体。失败并发并发异常。
System.Data.OptimisticConcurrencyException：存储更新，插入或删除语句影响了意外数量的行（0）。自加载实体以来，实体可能已被修改或删除。刷新ObjectStateManager条目。
在System.Data.Mapping.Update.Internal.UpdateTranslator.Update（IEntityStateManager stateManager，IEntityAdapter适配器）
在System.Data.Objects.ObjectContext.SaveChanges（SaveOptions选项）
pgsql日志如下：
CEST LOG：说明：开始；设置事务隔离级别已提交；
CEST LOG：指令：更新 schema。 mytable SET column1 = TRUE WHERE（ pk = cast（7526作为数字））AND（ xmin = 1185882）
CEST LOG ：指令：ROLLBACK
Humm，检索受影响的行可能有问题，因为如果相同查询是手工执行的，我已经更新了一行...
编辑3 ：我已经启用了npgsql调试日志。
xmin不变时，我有以下内容（ UPDATE 1 ）：
7 / 2/2012 12:00:38 PM 12380调试进入NpgsqlState.ProcessBackendResponses（）
7/2/2012 12:00:38 PM 12380 Debug输入PGUtil.ReadString（）
2012年7月2日12380下午12:00:38调试获取NpgsqlEventLog.LogLevel
2012/2/2下午12380：38 PM 12380调试字符串读取：UPDATE 1.
更改后，我有以下内容（更新0 ）：
2012年7月2日1:50:06 PM 12700调试进入NpgsqlState.ProcessBackendResponses（）
2012年7月2日1:50:06 PM 12700调试进入PGUtil.ReadString（）
2012年2月2日1:50:06 PM 12700调试获取NpgsqlEventLog.LogLevel
2012年7月2日1:50:06 PM 12700调试字符串读取：UPDATE 0.
但是不幸的是，在两种情况下，我都有OptimisticConcurrencyException ...
编辑4 ：查看完在npgsql日志中，似乎实体框架在内部使用DbCommand.ExecuteReader（CommandBehavior.SequentialAccess），然后使用reader.Read（）确定UPDATE语句影响了多少行。
我可能是错的，但是提供程序在ForwardsOnlyDataReader.Read（）期间返回假，这可能是问题的根源。
编辑5 ：这绝对是提供程序问题（版本2.0.11.93和即将发布的版本2.0.11.94）。
对于INSERT语句，提供程序仅支持计算列当基础数据类型为SERIAL（int）或BIGSERIAL（bigint）时。
对于UPDATE语句，提供程序不处理给定DbUpdateCommandTree的Returning属性。这意味着不会返回任何计算列。调用SaveChanges（）后，必须手动刷新实体对象。
到目前为止，尚不支持乐观并发。
我将尝试在提供程序中实现最低支持，并保持发布状态。
编辑6 ：现在，Returning属性在我自己的提供程序版本中处理。我很乐意分享我的代码。
xmin 不太可能问。很快就会消失，并且可以用于实现更新的乐观并发控制的简单形式。在内部请小心，它是一个 unsigned 32位数字，因此不要尝试将其存储在一个有符号的32位字段中，否则它可能会工作一段时间，然后严重损坏。
只要将其映射到适当的数据类型，请先读取它，并将其包含在 WHERE UPDATE 的c>子句，并针对未找到条件采取适当的操作，这非常简单。
I'm working with Entity Framework (Model First approach) against a PostgreSQL 9.1 database.
You probably all know that every table has a hidden column called xmin that I would use to help EF determine if the row has changed before performing an update.
I know that the internals of PostgreSQL may change and that this might not be a good idea at all for production code, but I would like to try.
What would be the required steps to manually update the model to include this column for one table and test its behavior ?
TIA.
EDIT 1 : Here is where I'm so far, using the Model First approach with Self Tracking Entities.
The model has been modified with the following :
CSDL : <Property Name="xmin" Type="Decimal" Nullable="false" Precision="15" Scale="0" ConcurrencyMode="Fixed" />
SSDL : <Property Name="xmin" Type="numeric" Nullable="false" Precision="15" Scale="0" StoreGeneratedPattern="Computed" />
The xmin column is effectively retrieved on SELECT, then used during UPDATE :
UPDATE "schema"."mytable" SET "column1"=FALSE WHERE ("pk"=cast(7526 as numeric)) AND ("xmin"=cast(1185877 as numeric))
The problem here is with the cast used on the xmin column. It fails. What I've found so far, is that it works with no cast at all. But I'm not sure how to tell EF to use no cast at all in the query for this column.
BTW the xmin column datatype is 'xid' which is unknown to the Npgsql ADO.NET provider.
EDIT 2 : This is the provider that generates the SQL text. After looking into the provider code, I can confirm that when using an integer datatype there is no cast. So, I've used an integer in my entity to store the value and tried to select and update the entity. It fails with a concurrency exception.
System.Data.OptimisticConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
the pgsql log follows :
CEST LOG: instruction : BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CEST LOG: instruction : UPDATE "schema"."mytable" SET "column1"=TRUE WHERE ("pk"=cast(7526 as numeric)) AND ("xmin"=1185882)
CEST LOG: instruction : ROLLBACK
Humm, may be there is problem with retreiving the affected rows because, if the same query is executed by hand, I have one row updated...
EDIT 3 : I've enabled the npgsql debug log. When xmin has not changed I have the following (UPDATE 1):
7/2/2012 12:00:38 PM 12380 Debug Entering NpgsqlState.ProcessBackendResponses()
7/2/2012 12:00:38 PM 12380 Debug Entering PGUtil.ReadString()
7/2/2012 12:00:38 PM 12380 Debug Get NpgsqlEventLog.LogLevel
7/2/2012 12:00:38 PM 12380 Debug String read: UPDATE 1.
When it has changed I have the following (UPDATE 0):
7/2/2012 1:50:06 PM 12700 Debug Entering NpgsqlState.ProcessBackendResponses()
7/2/2012 1:50:06 PM 12700 Debug Entering PGUtil.ReadString()
7/2/2012 1:50:06 PM 12700 Debug Get NpgsqlEventLog.LogLevel
7/2/2012 1:50:06 PM 12700 Debug String read: UPDATE 0.
But unfortunately, in both cases I have an OptimisticConcurrencyException...
EDIT 4 : After reviewing the npgsql log, it seems that entity Framework is internally using DbCommand.ExecuteReader(CommandBehavior.SequentialAccess) followed with a reader.Read() to determine how many rows were affected by the UPDATE statement.
I may be wrong but the provider is returning false during ForwardsOnlyDataReader.Read() which might be the source of the problem.
EDIT 5 : This is definitely a provider issue (version 2.0.11.93 and upcoming version 2.0.11.94).
For an INSERT Statement, the provider only support computed columns when the underlying datatype is SERIAL (int) or BIGSERIAL (bigint).
For an UPDATE statement, the provider does not handle the Returning property of a given DbUpdateCommandTree. This means that no computed column is ever returned. You have to manually refresh the entity object after the call to SaveChanges(). Optimistic concurrency is unsupported so far.
I'll try to implement minimum support in the provider and keep you posted.
EDIT 6 : The Returning property is now handled in my own version of the provider. I'd be happy to share my code. Don't hesitate to ask.
xmin is quite unlikely to go away any time soon, and can be used to implement a simple form of optimistic concurrency control for updates. Be careful in that internally it is an unsigned 32-bit number, so don't try to store it in a signed 32-bit field, or it is likely to work for a while and then break badly.
As long as you map it to an appropriate data type, read it in up front, include it in the WHERE clause of your UPDATE and take appropriate action on a "not found" condition it is pretty straightforward.
这篇关于实体框架，PostgreSQL，带有隐藏xmin列的乐观并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
