Insert row in DB when using multi-threaded?

Question

Here I use multithreading and linq for sql.

Here I upload my code snippet:

public class PostService { MessageRepository objFbPostRespository = new MessageRepository(); public void callthreads() { for (int i = 0; i < 100; i++) { Thread th = new Thread(postingProcess); th.Start(); } } public void postingProcess() { objFbPostRespository.AddLog("Test Multithread", DateTime.Now); } } 

Message Repository Class

 class MessageRepository { DataClassesDataContext db_Context = new DataClassesDataContext(); public void AddLog(string Message, DateTime CurrentDateTime) { FbMessgaeLog FbMessage = new FbMessgaeLog { Message = Message, Time = CurrentDateTime }; db_Context.FbMessgaeLogs.InsertOnSubmit(FbMessage); db_Context.SubmitChanges(); } } 

When I run it without threads, then it works fine after turning on the thread. I got the following error. msg:

Error: An item with the same key has already been added.

Thanks in advance...:)

Answer 1

You cannot use LINQ DataContext at the same time:

Any instance members are not guaranteed to have reliable flow.

Therefore, you need to either serialize access (blocking), which will be terribly inefficient, or it is better to use a separate context in each thread:

 public class PostService { public void callthreads() { for (int i = 0; i < 100; i++) { Thread th = new Thread(postingProcess); th.Start(); } } public void postingProcess() { using (MessageRepository objFbPostRespository = new MessageRepository()) { objFbPostRespository.AddLog("Test Multithread", DateTime.Now); } } } 

I also hope, for your own sake, that your test has real logic to wait for the test threads to complete before closing ... And, of course, correctly implement IDisposable in your repository and get rid of the context so that the DB Connection will be put back into the pool.

Answer 2

I suspect you are using time as a primary key or as a unique constraint. If so, then your problem, you should use either identity or uniqueidentifier .

Identification will be more readable, as these will be increasing numerical values. It will also be useful, as it will usually tell you in what order the records were inserted, which may be useful later.

The unique identifier has the advantage that you can select it in advance, rather than waiting to find out what the database gives you.

For an application like logging, I would recommend an identifiers column.

Answer 3

I had problems in the past with LINQ to SQL DataContexts that remembered the objects I previously presented and tried to insert them again the next time I submitted. It looks like you might run into something similar.

The solution I came across was to get rid of the old DataContext and start with a new one after each call to SubmitChanges ().