{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/11428","repository_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore","labels_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/11428/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/11428/comments","events_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/11428/events","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/11428","id":308384636,"number":11428,"title":"Deadlock when adding multiple entities","user":{"login":"sam-wheat","id":16885210,"avatar_url":"https://avatars0.githubusercontent.com/u/16885210?v=4","gravatar_id":"","url":"https://api.github.com/users/sam-wheat","html_url":"https://github.com/sam-wheat","followers_url":"https://api.github.com/users/sam-wheat/followers","following_url":"https://api.github.com/users/sam-wheat/following{/other_user}","gists_url":"https://api.github.com/users/sam-wheat/gists{/gist_id}","starred_url":"https://api.github.com/users/sam-wheat/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sam-wheat/subscriptions","organizations_url":"https://api.github.com/users/sam-wheat/orgs","repos_url":"https://api.github.com/users/sam-wheat/repos","events_url":"https://api.github.com/users/sam-wheat/events{/privacy}","received_events_url":"https://api.github.com/users/sam-wheat/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2018-03-25T20:53:22Z","updated_at":"2018-03-26T21:17:31Z","closed_at":null,"author_association":"NONE","body":"I don't know if this is a problem with EF or I am just doing it wrong (more often the latter).\r\n\r\nIf I am doing it wrong I would like to know why the code works when I save each entity individually as opposed to saving all of them at once outside the loop.  As is shown in the attached project, setting the isolation level appears to have no effect, nor does removing the lookup that happens for each row.\r\n\r\n\r\n\t// Save the Releases to the database\r\n\tusing (Db db = new Db(dbContextOptions))\r\n\t{\r\n\t\t// does not work:\r\n\t\t// db.Database.ExecuteSqlCommand(\"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\");\r\n\r\n\t\tforeach (Release rel in releases)\r\n\t\t{\r\n\t\t\t// omitting the following line has no effect WRT error\r\n\t\t\tawait db.Releases.AnyAsync(x => x.NativeID == rel.NativeID && x.DataProviderID == rel.DataProviderID && x.ID != rel.ID);\r\n\r\n\t\t\tdb.Entry(rel).State = EntityState.Added;\r\n\r\n\t\t\tforeach (SourceRelease sr in rel.SourceReleases)\r\n\t\t\t\tdb.Entry(sr).State = EntityState.Added;\r\n\r\n\t\t\r\n\t\t\t//await db.SaveChangesAsync();  // WORKAROUND: works if we save each entity here\r\n\t\t\t\r\n\t\t}\r\n\r\n\t\t// ...\r\n\t\t// Inner Exception 3:\r\n\t\t// SqlException: Transaction(Process ID 55) was deadlocked on lock resources with another process \r\n\t\t// and has been chosen as the deadlock victim. Rerun the transaction.\r\n\t\t\r\n\t\tawait db.SaveChangesAsync();  // error here.   WORKAROUND:  comment this out and save inside the loop\r\n\t}\r\n[InsertTest.zip](https://github.com/aspnet/EntityFrameworkCore/files/1848358/InsertTest.zip)\r\n"},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/comments/376314794","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/11428#issuecomment-376314794","issue_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/11428","id":376314794,"user":{"login":"antonioortizpola","id":11655127,"avatar_url":"https://avatars0.githubusercontent.com/u/11655127?v=4","gravatar_id":"","url":"https://api.github.com/users/antonioortizpola","html_url":"https://github.com/antonioortizpola","followers_url":"https://api.github.com/users/antonioortizpola/followers","following_url":"https://api.github.com/users/antonioortizpola/following{/other_user}","gists_url":"https://api.github.com/users/antonioortizpola/gists{/gist_id}","starred_url":"https://api.github.com/users/antonioortizpola/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/antonioortizpola/subscriptions","organizations_url":"https://api.github.com/users/antonioortizpola/orgs","repos_url":"https://api.github.com/users/antonioortizpola/repos","events_url":"https://api.github.com/users/antonioortizpola/events{/privacy}","received_events_url":"https://api.github.com/users/antonioortizpola/received_events","type":"User","site_admin":false},"created_at":"2018-03-26T21:17:31Z","updated_at":"2018-03-26T21:17:31Z","author_association":"NONE","body":"Ok, i can say for sure, while that is a short lived context in terms of code, is still a very long lived context, since is used for 550 insertions (i guess, one context per thread), where each insertion will cause EF to:\r\n\r\n- Save the entity\r\n- Cache the entity and create tracking for the database and entity\r\n- Ask for the saved id\r\n\r\nThe cache and the entity tracking will cost you a lot, and from what i see not necessary. No matter where you put that `SaveChanges`, this will cause many performance problems, you should add the instruction to set the context `AutoDetectChangesEnabled` in false.\r\n\r\nBulk inset is a hard problem, you can check an implementation a little more complete that yours in [stackoverflow](https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa).\r\n\r\nI would not recommend to follow your approach (only if you are doing a very few insertions) since will cause a lot overhead on your app and on your DB. But still, i repeat, i have no idea why your code works with the workaround and breaks without it, but i almost sure both approaches are inefficient and will lead to further problems under heavy or even normal load.\r\n\r\nTake what I say with care, I'm no expert, just a user trying to help, I hope someone with better credentials could help us clear this out."}}
{"actor":{"display_login":"antonioortizpola"}}