I'll try to explain the issue with a simplified console application example, however the real project is a ASP.NET MVC3 application.Having the following tables:
imagine the following scenario:[*]user creates a report (a line in \[code\]TestReport\[/code\], where \[code\]Text\[/code\] is the report \[code\]string\[/code\] content, and \[code\]Ready\[/code\] is a \[code\]bool\[/code\] flag, saying, if the report is ready to be processed); by default \[code\]Ready\[/code\] is set to \[code\]false\[/code\], i.e. not ready.[*]user wants the report to be processed, so he submits it; \[code\]Ready\[/code\] is set to \[code\]true\[/code\] here.The system gives an opportunity to recall the report back, if it has not been processed yet. So, when the report is recalled, \[code\]Ready\[/code\] is set to \[code\]false\[/code\] back. On the contrary, when the report is processed, a line in \[code\]TestReportRef\[/code\], referencing report by its \[code\]Id\[/code\], is created.Now imagine that at one and the same moment[*]user wants to recall the report;[*]the report is added to the process list;As soon as this can happen simultaneously, errors may occur. That is the report will have \[code\]Ready\[/code\] == \[code\]false\[/code\] and it'll be referenced in \[code\]TestReportRef\[/code\].Here is a simple console example of how this may happen:\[code\]var dc = new TestDataContext('my connection string');dc.TestReport.InsertOnSubmit(new TestReport{ Text = "My report content", Ready = true //ready at once});dc.SubmitChanges();Action recallReport = () =>{ var _dc = new TestDataContext(cs); var report = _dc.TestReport.FirstOrDefault(t => t.Ready); if (report != null && !report.TestReportRef.Any()) { Thread.Sleep(1000); report.Ready = false; _dc.SubmitChanges(); }};Action acceptReport = () =>{ var _dc = new TestDataContext(cs); var report = _dc.TestReport.FirstOrDefault(t => t.Ready); if (report != null && !report.TestReportRef.Any()) { Thread.Sleep(1000); _dc.TestReportRef.InsertOnSubmit(new TestReportRef { FK_ReportId = report.Id }); _dc.SubmitChanges(); }};var task1 = new Task(recallReport);var task2 = new Task(acceptReport);task1.Start();task2.Start();task1.Wait();task2.Wait();foreach (var t in dc.TestReport){ Console.WriteLine(string.Format("{0}\t{1}\t{2}", t.Id, t.Text, t.Ready));}foreach (var t in dc.TestReportRef){ Console.WriteLine("ref id:\t" + t.FK_ReportId);}\[/code\]\[code\]Thread.Sleep(1000);\[/code\] is added to be ensure, that tasks will check one and the same situation.The given example may sound awkward, however, I hope, it should explain the issue I'm dealing with.How can I avoid this? Making the repository singleton doesn't seem to be a good idea. Shall I use some shared mutex (one for all web requests) to separate write-operations only?Or is there a pattern I should use in this kind of scenario?This is only a simplified example of one of the scenarios I have. However, there are several scenarios in which it may run into a similar discrepancy. The best thing would be to make this kind of intersection impossible, I guess.