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 |
|
fisherman_jake
Slave to the Almighty Yak
159 Posts |
 Posted - 2002-01-25 : 01:58:31
|
G'day, I've been reading about LOCKS and Locking escalations ALL DAY.. I've had about enough of it.. Basically I'm running BULK INSERT with the TABLOCK option, now one would think that this would create a TABLE LOCK on the table in question, but it doesn't, it basically builds EXTENT type locks and builds it's way towards a table lock.. quote:
...BOL...Resource Description RID = Row identifier. Used to individually lock a single row within a table. Key = Key; a row lock within an index. Used to protect key ranges in serializable transactions. Page = 8-KB data page or index page. Extent = Contiguous group of eight data pages or index pages. Table = Entire table, including all data and indexes. DB = Database. ...
I've tried setting the sp_tableoption and sp_dboption level bulk insert blah blah blah, and still amd getting thousands of EXTENT type locks and 1 or 2 TABLE locks.... I'm saying this because it's taking up all my LOCKS!!! ==================================================World War III is imminent, you know what that means... No Bag limits!!!Master Fisherman |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-25 : 03:47:07
|
| Doint a bulk insert is adding records to the table. This means it has to add extents which are not part of the table so have to be locked individually until they are added to the table.Look at the locks taken when you add records to a temp table - it will do a similar thing.Using the tablock operation on the bulk insert may reduce the number of locks taken but I don't see any way round what you are doing other than including a batch size in the insert.If you are running out of locks and it is caused by an operation locking extents you should probably increase the number of locks available.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
BULK INSERT & TABLELOCKS