Doing BULK Insert SUSPENDED With Wait Type LCK_M_RIn_LN

Question

I'm having awful problems with doing BULK insert. I'm actually using SqlBulkCopy to insert a number of rows into a table. At first, I would get a Timeout exception. So, I set the SqlBulkCopy's BulkCopyTimeout to a ridiculous[?] 1800 seconds. The exception wouldn't be thrown (yet). So, I checked the Activity Monitor (as suggested here: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated) from the MS Server management studio and saw that my BULK INSERT's Task Status is SUSPENDED with a Wait Type of LCK_M_RIn_LN.My code goes like this:

Using sqlCon As SqlConnection = connection.Connect()
   Dim sqlBulkCopy As New SqlBulkCopy(sqlCon, SqlBulkCopyOptions.CheckConstraints And
   SqlBulkCopyOptions.FireTriggers And
   SqlBulkCopyOptions.KeepNulls And
   SqlBulkCopyOptions.KeepIdentity, sqlTran)

   sqlBulkCopy.BulkCopyTimeout = 1800 ' is this ridiculous?
   sqlBulkCopy.BatchSize = 1000
   sqlBulkCopy.DestinationTableName = destinationTable
   sqlBulkCopy.WriteToServer(dataTableObject)

   sqlTran.Commit()
End Using

I have been searching for solutions in the web, but to no avail. Although I have found this defintion of LCK_M_RIn_LN:

Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

from http://msdn.microsoft.com/en-us/library/ms179984.aspx

But it's not helping. May someone help me out. My deepest gratitude.

Edit

I think it's because of the KeepIdentity attribute because the primary key is auto incremented. This is according to SqlBulkCopy Insert with Identity Column. I'll see if it fixes my issue.

Edit 2

I don't know what's happening but BULK insert worked fine when I tested it on the management studio (using direct transact-sql). I don't know. Maybe it's with the SqlBulkCopy. When I checked on the Activity Monitor, the query it generated was this:

insert bulk TableName ([ColumnName] Int)

Edit 3

I forgot to write that I'm actually using Entity Framework so I copied a code (translated from c# to vb, actually) that would create a DataTable from an entity object since EntityDataReader is only available for C# (which distressed me). But, anyway. I trashed the SqlBulkCopy thing and just stored the values in XML because when I look at it, I realized I did not need the values inside a database.

What's the actual error message? Also, you can add the DBMS you're using as a tag to your question, that makes it easier for other users to help you. — Josien, Jul 25 '13 at 11:41
Oh, ok thanks. At first, it threw a Timeout exception so I set the BulkCopyTimeout to 1800 seconds. But, then I found out from http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation the possibilities so I checked my Activity Monitor in MS management studio and found out that the bulk insert was suspended. I have updated my question to include this details. Thanks. — PenguinBlues, Jul 25 '13 at 11:45
Hey Phebe, thanks for the update. Last request: can you please also add the tag for the programming language you're using to your question? — Josien, Jul 25 '13 at 11:47
Btw: feel free to answer your own question and accept it if it solves your problem! That might just help out a fellow programmer some day :-) — Josien, Jul 25 '13 at 12:23
@PenguinBlues well, you didn't awser your own question... :P — Iúri dos Anjos, Apr 04 '18 at 13:20

score 1 · Answer 1 · answered Sep 16 '20 at 11:50

I hit something similar trying to bulk insert from Java but with wait type ASYNC_NETWORK_IO e.g.

+-----------+-------+-------------+---------+--------+----------------+--------------------------------------+
|  Status   | BlkBy |   Command   | CPUTime | DiskIO |   LastBatch    |             ProgramName              |
+-----------+-------+-------------+---------+--------+----------------+--------------------------------------+
| SUSPENDED | .     | BULK INSERT |      15 |      4 | 09/16 02:42:04 | Microsoft JDBC Driver for SQL Server |
+-----------+-------+-------------+---------+--------+----------------+--------------------------------------+

It's hard to say what the exact issue was, there are a few things I observed:

Either is the driver swallows errors or you only get them when the copy completes, e.g. when I tried to insert a single row I had exceptions thrown with the errors I needed to fix.
Tuning can be important, specifically the batch size (see https://dba.stackexchange.com/questions/165966/how-does-one-investigate-the-performance-of-a-bulk-insert-statement)

Once I'd addressed these then the full load worked as expected.

Some stats for batch size/rows I generated (note the data is going across the Atlantic) but the point is that the performance is very variable.

+------------+------+----------+----------+----------+
| batch size | rows |  start   |   end    | duration |
+------------+------+----------+----------+----------+
|        100 | 2500 | 09:15:45 | 09:18:17 | 00:02:32 |
|       1000 | 2500 | 09:23:34 | 09:25:35 | 00:02:00 |
|       2500 | 2500 | 09:32:53 | 09:34:55 | 00:02:01 |
|       2500 | 7500 | 10:27:18 | 10:30:49 | 00:03:31 |
|       7500 | 7500 | 10:38:10 | 10:45:57 | 00:07:47 |
+------------+------+----------+----------+----------+

Doing BULK Insert SUSPENDED With Wait Type LCK_M_RIn_LN

1 Answers1