c# lock table for bulkcopy

Question

I have a datatable in c# that get a new row insert every few milliseconds. I want this datatable to be bulkcopy in SQL server every 10 seconds but I get an exception "Collection was modified; enumeration operation might not execute" so I use the lock to bypass that error. Is the lock a good approach or there is a better option to do this?

lock (testdatatable.Rows.SyncRoot)
{
     s.WriteToServer(testdatatable);
}

Answer 1

I am not a C# guru, but do like playing around with anything SQL server related.

To me, you are locking the ADO data table in memory. Since you did not post the rest of the code, I can not tell if this is associated with a SQL Server table or not. I bet you it is.

The message is stating that the under lying data has been modified.

Is one bulk copy process stepping on another?

Check out the entry from ConcernedOfTunbridgeWells for a good coding style -

Best Practices for uploading files to database

My main question is why are you doing it every 10 secs?

At that frequency, some things come to mind.

1 - Are you using a staging table w/o indexes and integrity. Rebuilding indexes if the data size is large will take time.

2 - What is the underlying SqlBulkCopy class doing for locks. TABLOCK may cause blocking at this rate.

If you have sysadmin access to the database, here are some simple commands to look at the locks.

--
-- Locked object details
-- 

-- Old school technique
EXEC sp_lock
GO

-- Lock details
SELECT
    resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description 
    FROM sys.dm_tran_locks
    WHERE resource_database_id = DB_ID('AdventureWorks2012')
GO

-- Page/Key details
SELECT object_name(object_id) as object_nm, *
    FROM sys.partitions
    WHERE hobt_id = 72057594047037440
GO

-- Object details
SELECT object_name(1266103551)
GO

A Tech Next snippet on Bulk Copy.

http://technet.microsoft.com/en-us/library/ms130809.aspx

TABLOCK: A table-level lock is acquired for the duration of the bulk copy operation. This option significantly improves performance because holding a lock only for the duration of the bulk copy operation reduces lock contention on the table. A table can be loaded by multiple clients concurrently if the table has no indexes and TABLOCK is specified. By default, the locking behavior is determined by the table option table lock on bulk load.

In summary, try increasing the time between BULK COPY operations. See if the error goes away.

Answer 2

Maybe this question should be tagged with "c#" and not sql ?

Your problem (I think) has to do with that you modify a collection, right ? This is a general problem and I think you could find the solution here:

"Collection was modified..." Issue

If not, then we would have to se your code to investigate more.

Answer 3

This question has nothing to do with:

• SQL Server
• Locking (well, sort-of, see the bottom of the answer)

The underlying problem here is that bulk-copy will enumerate the collection. And while this is happening, you are changing the collection.

That's it.

Almost all enumerator implementations in .NET will cooperate with the collection it enumerates in such a way that if the collection changes while the enumerator is still alive, the enumerator will stop working.

Example:

• I say, for each person on this page, I want to dial the phone number listed and talk to the person
• While I'm doing this, you add a new person to the page

Question:

• Should I call that person as well, since he is now on the page, and by the time I dial the last (as it was when I started), and finish that call, the next person should be the one you added, so I should call that person?
• Disregard that person, as he/she was not on the page when I started?

What if you remove a person from the list while I'm dialing or talking to that person? Should I hang up?

Most .NET collection enumerators will stop working, with that precise exception, if the underlying collection changes while you are enumerating over it.

A common way to "fix" this is to make a copy of the collection.

Basically, instead of doing this:

foreach (var x in y)

you do this:

foreach (var x in y.ToArray())

The .ToArray() call will make a copy, into a separate array, and then the entire for-loop will enumerate over that. If y changes in the mean time, that does not matter.

In your case, however, you're nowhere near the actual for-loop or similar that is being executed.

I would ensure the DataTable in question here does not change while you bulk-copy it into the database.

A lock is not the right thing to do, at least not by itself, as any other code that doesn't lock will of course disregard the lock altogether. A lock statement does not magically prevent other threads from changing the instance, it merely places a lock on the instance that other threads that would try to lock as well would be stopped from doing so. If no other thread attempts to take a lock on that object before modifying it, your lock will basically do nothing.