Hi there,
it seems I found a workaround and I'll post it here for your reference.
As I noticed from the MySql log, and I really don't know why this happens, extra update queries are generated and this is causing the issue.
This does not happen on my pc but it happens on the production server.
I am using Entity Framework and this is the code I use in my DAL to update a record
Code: Select all
public void Update(MyObject myobject)
{
using (var ctx = new MyEntities())
{
ctx.MyObject.Attach(myobject);
ctx.ObjectStateManager.ChangeObjectState(myobject, System.Data.EntityState.Modified);
try
{
ctx.SaveChanges();
}
catch (OptimisticConcurrencyException ocex)
{
// do something here
ctx.Refresh(RefreshMode.StoreWins, ocex.StateEntries[0].Entity);
throw ocex;
}
}
}
An update and a select queries are fired on MySql in a transaction, for example:
Code: Select all
UPDATE mydb.mytable1
SET fk_ID_of_table2 = fk_ID_Value, field1 = value1, field2 = value2 ...
WHERE myID = IDValue
AND MyTimeStamp = '2013-04-05 08:24:28';
SELECT MyTimeStamp FROM mydb.mytable1
WHERE myID = IDValue AND ROW_COUNT() = 1;
On the server, and only when update is called on certain tables, extra queries are generated trying to update another table which is FK connected to the first table:
Code: Select all
UPDATE mydb.mytable2
SET MyTimeStamp = MyTimeStamp
WHERE fk_ID_of_table2 = fk_ID_Value
AND MyTimeStamp = '2013-04-05 08:30:01';
SELECT MyTimeStamp
FROM mydb.mytable2
WHERE fk_ID_of_table2 = fk_ID_Value AND ROW_COUNT() = 1;
MySql will return ROW_COUNT() = 0 for this second update query because no values are really changed; this means the second select query will return no records and I believe this is the point where the exception is raised.
Here comes the workaround. On the MySql site I found this
For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.
and the counterpart of CLIENT_FOUND_ROWS for dotConnect is this parameter
FoundRows - Specifies whether the provider will return the number of rows matched by the WHERE condition of the UPDATE statement instead of the rows actually changed as the number of changed rows.
Adding
FoundRows=true to my connection string in web.config did the trick; OptimisticConcurrencyException is not raised.
I am pretty sure this will not brake my code but perhaps you should consider to fix it somehow, obviously if you think it is something to do with dotConnect.
Hope this helps.
G.A.