Dataset Update without Locking SQL Server 40,000+

Question

I am taking a Tab Delimited file from an FTP site and doing a nightly download. I then upload it in its form using my Dataset. I simply load it into the dataset and bind it. It works, but...

I have 2 of these files, and originally I had one working and just did the Inserts of each row by manually pulling all the data out of the file like VB 6. I assumed the binding of the datatable or dataset in Visual Studio 2012 would use a better algorithm, also I thought it might be easier and more efficient. It is relatively easy at least.

I do NOT have access to the SQL Server's server file system as it is a shared environment and BULK COPY to SQL Server is not available. The bulk copy methods of the provider do not work either as advertised, due to my file size. Obviously, this would be much more efficient. This I guess is my blue collar way to do it. I do it nightly for about 100k rows in the 2 feeds remotely Both take about, 45 minutes a piece.

The issue I am having is that the table on this bind, is being locked up so no queries or access can be done while it goes through this 45 minutes of updating. Again, it does work. Is there a parameter or setting, I can invoke or set to allow it to be use during this update.

Answer 1

An UPDATE in a SQL Server will always have to take an exclusive lock - no way / no trick / no hack around this.

But this exclusive lock is just on the single row that's being updated - and it will be released after the transaction is done.

However: if you update more than 5000 rows in single transaction, SQL Server will escalate the row-level exclusive locks to a table-level lock - essentially locking the entire table until the transaction is completed.

I see two options here:

• if you can live with this approach, you could break up your import into chunks of e.g. 2000 rows in a single transaction; this way, you could avoid the table-level exclusive locks, and your system would remain more responsive during import. The downside would be: it might take a bit longer, and you might end up with some groups of rows being imported, while others fail - you're not getting a clean "all or nothing" import for the whole file anymore

• there are some trace flags / switches you can enable that would turn off this lock escalation mechanism, thus your entire import of 40000 rows would be handled by row-level locks. This on the other hand puts a lot of stress and extra work on the lock management system, so that might require more time and/or more RAM to handle all those exclusive locks, and queries running during import might be slowed down (if 39999 individual exclusive locks have to be checked)

Answer 2

100K rows really isn't that much for SQL Server to handle, unless you are doing this insert over a very slow network connection. This sounds more like a locking problem with a long-running transaction. I think you are only doing a single commit after all rows are inserted and causing a lot of blocking issues. If this is a table with a lot of reads on it, then you will have locking problems. See marc_s' post for info on lock escalation. You should modify your insert so that you are committing more often like every 100 rows. Play around with the value until you get an optimal number. You want to avoid long-running transactions.

Not sure which version of SQL Server you are running, but if it's 2005 or later you can enable snapshot isolation for the database and use that for this particular transaction. This will add a bit more pressure on the tempdb, but you get an MVCC style locking mechanism where readers don't block writers. See here for more details. http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx