SqlBulkCopy with concurrent request Deadlock issue in c#

[maulikhdave9601672767]

I m using Sqlbulkcopy with my application and trying to insert bulk 1000000 records in single table. But the approach is 10 Parallel threads will hit with bulk command on same table with 100000 record per each thread. while this thread ware running there will not be any update operation will happen on table. only read operation can happen same time from different thread.

using (SqlBulkCopy bulkCopy =
                new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the using block.
                    reader.Close();
                }

I don't want any lock on my table and i also want to allow my all threads to bulk insert. I m using default option of bulk. how i can avoided Deadlock in this situation?

[Wraith2]

That isn't a lot of rows for bulk copy and shouldn't take very long unless you're got poor latency. Why are you trying to use multiple threads at all?

[maulikhdave9601672767]

its my design requirement.

try 
        {            
            using var bulkCopy = new SqlBulkCopy(sqlconn); 

            bulkCopy.DestinationTableName = "[MyTable]"; 

            var dt = list.ToList().ToDataTable(); 

            await bulkCopy.WriteToServerAsync(dt); 
        } 
        catch (Exception ex) 
        { 
            throw ex; 
        } 

Please correct me i m using default option of SqlBulkCopy which will impose only lock rows as default nature.
and it will give flexibility to insert bulk records with parallel threads without impacting dead lock .
please correct me if my understanding is differ .help me in understanding inner implementation
@Wraith2 @cheenamalhotra

[maulikhdave9601672767]

using var bulkCopy = new SqlBulkCopy(sqlconn); impose only lock rows as default nature of library.
This will give flexibility to insert bulk records with parallel threads without impacting dead lock . if we are ot doing any operation on those rows. @Wraith2 is understanding correct? @paulmedynski

[cheenamalhotra]

@maulikhdave9601672767

If you're not specifying SQLBulkCopyOptions.TableLock as in example above, row locks will be used by default. The enum linked here has the documentation on it.

Let us know if you need any additional info.

[maulikhdave9601672767]

so just confirming

try 
        {            
            **using var bulkCopy = new SqlBulkCopy(sqlconn);** 

            bulkCopy.DestinationTableName = "[MyTable]"; 

            var dt = list.ToList().ToDataTable(); 

            await bulkCopy.WriteToServerAsync(dt); 
        } 
        catch (Exception ex) 
        { 
            throw ex; 
        } 

I m not using any option by default only row will be locked.
It will not make any difference how many parallel using var bulkcopy statement executing parallel . It will not lock @cheenamalhotra