New Posts New Posts RSS Feed: .Delete() causes error: Save failed - Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

.Delete() causes error: Save failed - Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

 Post Reply Post Reply
Author
HFloyd View Drop Down
Groupie
Groupie


Joined: 27-Jul-2007
Location: United States
Posts: 44
Post Options Post Options   Quote HFloyd Quote  Post ReplyReply Direct Link To This Post Topic: .Delete() causes error: Save failed - Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
    Posted: 12-Aug-2008 at 5:53pm
Hi, I am just using a simple Console application to run some tests on my BOs. I created  some test data using my .Create() method and it was copied to the database, now I just want to clean out my test data. I figured deleting wouldn't be a big deal, but I have been struggling with it for some time now!

I get the error "Save failed - Concurrency violation: the DeleteCommand affected 0 of the expected 1 records." right at the pm.SaveChanges() call.

Here is the code chunk:

for (int i = 0; i < iTotal - 1; i++)
            {
                ContactMethod oTest = pm.GetEntities<ContactMethod>();

                if (oTest.HouseholdBizREF == gHeatherHB)
                {
                    if (oTest.MethodType == "Website")
                    {
                        Console.WriteLine("These will be DELETED:");
                        strTest = oTest.MethodType;
                        Console.Write(strTest);
                        Console.Write(" : ");
                        strTest = oTest.NumberOrAddress;
                        Console.Write(strTest);
                        Console.Write(" ( ");
                        strTest = oTest.ContactMethodNote;
                        Console.Write(strTest);
                        Console.Write(" ) ");
                        strTest = oTest.ContactMethodGUID.ToString();
                        Console.Write(strTest);
                        Console.WriteLine();
                       
                        oTest.Delete();
                        pm.SaveChanges();

                        iTotal = pm.GetEntities<ContactMethod>().Count;
                    }
                }
            }


After browsing around on this forum I came across a post which suggested using a separate EntityList to manage deleting:
http://www.ideablade.com/Forum/forum_posts.asp?TID=359

So I edited my code thus:

EntityList<ContactMethod> oDeleteList = new EntityList<ContactMethod>();
            oDeleteList.ShouldRemoveDeletedEntities = false;

            for (int i = 0; i < iTotal - 1; i++)
            {
                ContactMethod oTest = pm.GetEntities<ContactMethod>();

                if (oTest.HouseholdBizREF == gHeatherHB)
                {
                    if (oTest.MethodType == "Website")
                    {
                        Console.WriteLine("These will be DELETED:");
                        strTest = oTest.MethodType;
                        Console.Write(strTest);
                        Console.Write(" : ");
                        strTest = oTest.NumberOrAddress;
                        Console.Write(strTest);
                        Console.Write(" ( ");
                        strTest = oTest.ContactMethodNote;
                        Console.Write(strTest);
                        Console.Write(" ) ");
                        strTest = oTest.ContactMethodGUID.ToString();
                        Console.Write(strTest);
                        Console.WriteLine();
                        
                        oDeleteList.Add(oTest);
                        oTest.Delete();
                        pm.SaveChanges(oDeleteList);


                        iTotal = pm.GetEntities<ContactMethod>().Count;
                    }
                }
            }


But I still get the same error at the same location.

Can anyone spot the reason this doesn't work as I expect it to?

Thanks!

Heather

Edited by HFloyd - 12-Aug-2008 at 6:02pm
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715
Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 13-Aug-2008 at 9:38am
I don't see how a delete could possibly cause a concurrency violation, but I do see that you are doing a pm.SaveChanges.  One suspicion that I have is that you may have other changes in your cache that you are not aware of  that are causing the real problem.  For example, there may be some newly created entities that have identical primary keys.  To check for this,  check the value of pm.HasChanges() immediately before doing the delete.  The value should be false.  If the value is true, try doing a pm.SaveChanges before doing the delete.
 
You can also look at the tutorial "Resolving Concurrency Conflicts".  This will show you how to check which record actually causing the  concurrency conflict.
Back to Top
HFloyd View Drop Down
Groupie
Groupie


Joined: 27-Jul-2007
Location: United States
Posts: 44
Post Options Post Options   Quote HFloyd Quote  Post ReplyReply Direct Link To This Post Posted: 13-Aug-2008 at 1:27pm
Thanks for your reply, David,

I added a check to pm.HasChanges(), which returns FALSE. (Which doesn't surprise me since this code is pretty simple and there isn't any other access to the database happening at the same time.)

Any other ideas?

Thanks!

Heather
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715
Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 13-Aug-2008 at 4:38pm
Per my last post, you should be able to locate the entity that is causing the concurrency exception.  You should be able to find an EntityWithErrors.  Look at the tutorial on resolving concurrency conflicts.
 
However, I think there is something else wrong with your application or database that is causing these weird errors.  For example, "sick" databases can generate misleading errors like this.  I would try to simplify things to get something simple that works.
 
You can also send a DebugLog or SQL profiler output to support@ideablade.com
 
Back to Top
HFloyd View Drop Down
Groupie
Groupie


Joined: 27-Jul-2007
Location: United States
Posts: 44
Post Options Post Options   Quote HFloyd Quote  Post ReplyReply Direct Link To This Post Posted: 14-Aug-2008 at 2:37pm
Hi David,

I hobbled together a basic try/catch to check for the Entities with Errors and then print out info about their rowstate:

...
oTest.Delete();

                        try
                        {
                          SaveResult result = pm.SaveChanges();
                          return; // success = don't try save again.
                        }
                        catch (PersistenceManagerSaveException saveException)
                        {
                            Exception innerException = saveException.InnerException;
                            if (innerException is DBConcurrencyException)
                            {
                                PersistenceManagerSaveException pException = saveException;
                                SaveResult pSaveResult= pException.SaveResult;

                                Console.WriteLine("There are Save Errors:");

                                for (int x = 0; x < pSaveResult.EntitiesWithErrors.Count; x++)
                                {
                                    Entity aEntity = pSaveResult.EntitiesWithErrors[0];
                                    Console.WriteLine(aEntity.Table);
                                    Console.WriteLine("     Row State: " + aEntity.RowState);
                                 
                                }
                           
                                Console.ReadLine(); Console.WriteLine("------------------------");
                                Console.WriteLine("Press any key to exit.");
                                Console.ReadLine();

                                return;
                            }
                            else
                            {
                                Console.WriteLine("Unexpected Save Exception");
                                Console.ReadLine(); Console.WriteLine("------------------------");
                                Console.WriteLine("Press any key to exit.");
                                Console.ReadLine();
                                return;
                            }
                       
                        }
...


What I got was this information:
Table = HS.Admin.Model.ContactMethod <== this makes some sense, since I am trying to delete ContactMethods
RowState = Detached  <== this, I am not so clear about.

I don't think I have a clear conception of what "Detached" means, and if this data was just called from the database, why is it "Detached", as opposed to "Unchanged" or something?

I added a print of the rowstate for the entity right before "oTest.Delete();" - and it does return "Unchanged".

I'm not sure what you mean by "sick databases" - do you mean some malformed data in a field? Actual corruption? An improperly Created object?  Some naughty table dependency?

I would be surprised if my db were actually corrupted, since it only has test data and has never been in Production use.

Thanks,

Heather



Edited by HFloyd - 14-Aug-2008 at 2:54pm
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715
Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 15-Aug-2008 at 3:17pm
Let's review what a concurrency violation means.  Every row that gets modified or deleted by DevForce has a CurrentVersion and an OriginalVersion of that row in the PersistenceManager Cache. When DevForce tells the database to delete a row, the database looks at the CurrentVersion of the ConcurrencyColumn from the PersistenceManager Cache and compares it with the actual value of the ConcurrencyColumn in the database,  The two numbers should be the same.  If they are not the same, the assumption is made that some other user or process must have changed that value, and a concurrency violation is reported
 
Let me give an example.  I will use the RowVersion column as the ConcurrenctColumn and the database will increment that number by 1 for every change.  When I do a GetEntities on Employee, assume that RowVersion is 100 for a particular row.  If I do a Delete on that row, the RowVersion will be equal to 100 for both the OriginalVersion and the CurrentVersion.  The database will allow the Delete as long as the RowVersion in the database is still equal to 100.
 
Now, let's take the same example, but after I do the GetEntities of Employee, some other user modifies the row I want to delete.  This will change RowVersion from 100 to 101.  Then when I try the Delete, my proposed value of 100 for RowVersion will not match the 101 value in the database,
 
You should now see that we need more information from your recent code.  Report the value of the ConcurrencyColumn from both the OriginalVersion and CurrentVersion of the EntityWithErrors.  Also, report the value of the ConcurrencyColumn in the database.  If you are not sure how to do this, look at the code in the tutorial on Resolving Concurrency Conflicts.
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715
Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 15-Aug-2008 at 3:28pm
I just got this email.  It may explain why you are having problems?

If I don’t actually do anything with rowversion I keep getting concurrency errors when I save because the rowversion column is NULL by default.  I have the default value in SQL as 1 but the object mapper is ignoring that.

The one solution I can see is go to every table in the OM and change the default value to 1.  Or is this something a fix to Ideablade can be done so that the concurrency rowversion can handle a null value and treat it as 1.

Is there another solution?  Should the OM be picking up the default value from SQL and putting it into the default value column?

 

 



Edited by davidklitzke - 15-Aug-2008 at 3:28pm
Back to Top
HFloyd View Drop Down
Groupie
Groupie


Joined: 27-Jul-2007
Location: United States
Posts: 44
Post Options Post Options   Quote HFloyd Quote  Post ReplyReply Direct Link To This Post Posted: 15-Aug-2008 at 9:38pm
David,

That was IT!

I had not fully implemented the RowVersion (that is, set the SQL Update in the OM to RowVersion+1) before I had added the test data, so all the RowVersions were null - I did add a Console.Write test to show the RowVersion Before I called .Delete() and after, and they did match - both null. So I just went into SQL Server and ran an quick update query to update all the RowVersions to 0, and then the Delete worked fine.

I also noticed that even though the Table RowVersion field was an integer, it was not automatically giving new rows a 0 value, so I changed the "SQL Insert Value" in the OM to 0, which seems to have fixed it.

Thanks for your sleuthing!

Heather
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down