Bulk Insert with Parallel for each

Question

3.00/5 (1 vote)

See more:

SQL-Server-2008

, +

Hi,

I am facing following error "Transaction (Process ID 133) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Scenario :
I am using "parallel for each" to do "bulk insert during same time some other thread tries to read data from the same table and i get dead lock error.

Please let me know how to solve it.

Posted 24-Dec-12 0:19am

Aditya Magotra

Add a Solution

2 solutions

Solution 1

Hi, one of the possible variants is to execute each action in Transaction scope, for example:

C#

Parallel.ForEach(MyIEnumerableSource, insertAction);

static void insertAction(EntityItem item)
{
using (TransactionScope scope = new TransactionScope(
     Required, new TransactionOptions() {IsolationLevel = ReadCommitted))
  {   
try{
      ExecuteInsertQuery(item);
    scope.Complete ();
}
catch{scope.Rollback();}
}

Posted 24-Dec-12 0:46am

Oleksandr Kulchytskyi

Comments

[no name] 24-Dec-12 6:56am

Hi Oleksandr thanks for your reply, I am getting error when another thread tries to read same table but some other data of the table.

Oleksandr Kulchytskyi 24-Dec-12 7:07am

Sorry, obviously i inattentively read your question...

[no name] 24-Dec-12 7:16am

hi,do you know how to solve this issue.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aditya Magotra · Accepted Answer · 2013-01-01T01:31:00

Solution 2

hi,

a) Select statement in sql server locks the table to avoid deadlock issue use with(nolock) in sql statement.

b) If no index is present on table then delete statement locks the whole table to avoid this create an index.

Posted 1-Jan-13 1:31am

Aditya Magotra