January 16, 2008 at 9:50 pm
Hi, I'm currently working on an application that is performing bulk inserts on various tables, while these bulk inserts are run an exclusive lock is obtained on the table being inserted into. What I want is for row level locks to be used instead of a table lock (slower I know but necessary for what we are doing) but the only way I am able to achieve this is by chopping the data up and performing multiple bulk insert statements, each with only about 2000 rows of data. It seems that for anything under 2000 rows SQL decides to use row locks, but anything over this results in the lock being escalated to an exclusive lock on the entire table. Modifying the Rows_per_batch option doesn't have any effect. Is there any way to force sql to use row level locks during BULK INSERT regardless of the number of rows being inserted? (note: I do NOT have the TABLOCK hint specified and the 'table lock on bulk load' option for the table is set to 0)
Thanks
January 16, 2008 at 10:21 pm
Hi
First why would you want row level locks. A table lock will reduce lock contention and have better performance.
If you dont specify the TABLOCK hint it should be using Row level locks. Check sp_tableoptions for the default value.
Are you doing any other operation on the table during bulk insert thereby making SQL escalate locks.
"Keep Trying"
January 17, 2008 at 1:56 pm
We need to allow reads/updates on the table at the same time as a bulk insert is occuring.
The table lock option is definitely set to 0 in table options, and there are no other commands other than the bulk insert statement.
January 20, 2008 at 11:39 pm
Hi
it seems SQL thinks its better to have a table lock rather than row locks.
do u have any index on the table. check out setting index locks in BOL.
"Keep Trying"
January 20, 2008 at 11:44 pm
What if you did your bulk insert into a staging table and then, using normal T-SQL, execute the insert statement selecting from the staging table? You can then issue locking hints - specifically ROWLOCK - or even automate it using small batches and a while loop.
In fact, I suspect the small batches approach with the staging table without any locking hints might be smarter.
February 1, 2008 at 6:32 pm
Ian Yates (1/20/2008)
What if you did your bulk insert into a staging table and then, using normal T-SQL, execute the insert statement selecting from the staging table? You can then issue locking hints - specifically ROWLOCK - or even automate it using small batches and a while loop.In fact, I suspect the small batches approach with the staging table without any locking hints might be smarter.
Yep it looks like I'm gonna have to either do it this way, or perform multiple bulk insert statements (1000 rows at a time) into the real table in a while loop incrementing the firstrow and lastrow argument of BULK INSERT each time. I'll have to see which will give the best performance. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply