Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2003-02-13 : 09:49:13
|
| I am BCPing in from multiple clients at around the same time of day. I want to prevent concurrent loading, as there is an update statement that runs after each BCP on only the just-BCPed data.I though including the "TABLOCK" hint would place a table-level lock for the duration of the BCP and prevent concurrent loading, but I think I may be misinformed.Do I have it backwards? Do you include "TABLOCK" when you want concurrent loading and leave it off when you don't?Jay White{0} |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-13 : 10:15:49
|
| FROM SQL Server BOL:TABLOCK A table-level lock is acquired for the duration of the bulk copy operation. This hint 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 concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-13 : 10:23:21
|
| Yeah, thanks ... I thought I was the king of the RTFM response ...I don't find the quote from BOL very clear. It seems to be saying that without the hint, the operation will block all other connections to the table. With the hint, the operation will block all other connections to the table except other bcp connections.So right now all my connects are using TABLOCK. If I want to lock the table so I don't have concurrent loading, then it seems I must remove the TABLOCK hint. In other words, to lock the table, remove the TABLOCK hint .... doesn't make sense.Jay White{0} |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-13 : 10:37:08
|
| I read it as saying that if there are no indexes and you use TABLOCK, you can load from multiple clients. The caveat being no indexes. If there is even one index then you do not have that option available to you. Personally, I have no idea why you would keep an index (except the clustered index which should be a considered a constraint in this context) on a table when doing BCP. It's kind of self defeating but I guess if you're dealing with concurrency issues you need to investigate the tradeoffs and see if they're worth it. As far as w/o the hint, the locking behavior is controlled by the table option table lock on bulk load.As far as the quote from the BOL, I swear it wasn't there two weeks ago before I installed SQL2K SP3. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-02-13 : 14:38:39
|
| Jay,How about issuing a table lock on the UPDATE statement...DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
|
|
bcp -h "TABLOCK"