Re: Identity Block Reserved For Inserts Issue - Are you good?
- From: "DeveloperX" <nntpDev@xxxxxxxxxxxxx>
- Date: 1 Nov 2006 07:27:36 -0800
Rob wrote:
Hi all,
I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.
Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)
Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going to
push in from BCP. We then take the block of identities and insert them into
the identity column in our files.
Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.
Why? It really sucks...
Here is a simple example:
Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry
We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)
Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad
dbcc checkident(Member, reseed, @newSeed)
Commit Tran
Assuming the current Identity was 1000, and the @range_pad was 5 at the time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5
making the @newSeed = 1007
We would then take the low value (1000) as the first identity value for the
first record and ++ for each row thereafter resulting in something like this:
identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry
This would then get BCP'd up with "Insert Identity Column" switched on.
There is a lag between getting the block and uploading using BCP of anywhere
from 1 minute to 6 hours.
What happens is that we sometimes find records in the table that have been
assigned an identity in our block.
So, if you got an answer can help us, we'd love to hear it.
BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.
Thanks BIG time folks! I know you can do it.
Rob
dbcc checkident (tablename, reseed, 1007) ?
Use lock hints to lock out the competing processes until you're
finished assigning the identities?
Alternatively manage the identity yourself, write a stored proc that
updates the nextID value in a table somewhere with a parameter that
allows you to allocate a number of rows. Use a lock hint to ensure no
other process can read the value before you've updated and force all
applications to use this stored proc. Perhaps do the same with the dbbc
checkident?
Or add a second identity column used soley by your bulk load app, it
doesn't even need to be an identity as you have sole control over it.
Just some ideas off the top of my head.
.
- References:
- Prev by Date: WinForms exception not including any code of me
- Next by Date: Re: Thread Message Loop in C#?
- Previous by thread: Identity Block Reserved For Inserts Issue - Are you good?
- Next by thread: Re: Identity Block Reserved For Inserts Issue - Are you good?
- Index(es):
Relevant Pages
|
