Insert records while purging

Not sure if this is possible, seems like it should be with
the right locking mechanism.
I'm purging the oldest dated records from a fairly large
table, and want to be able to insert a new record. The new
record would have a current date(GetDate()). My first
tests are not going well. I can't insert the record at
all. So I am a bit confused as to why. Shouldn't I be able
to insert a record while a purge/delete is occuring if the
records are at opposite ends of the clustered index?
Any help is much appreciated.
Thanks,
JamesBefore an insert, the engine doesn't pre-determine what the value for a
column will be in order to determine whether or not it would be affected by
any existing queries. This is true for constants, variables, computed
columns and, yes, even those with defaults (because you could override the
default). So, rather than risk deciding between an insert and a delete for
a particular row that overlaps under both queries, it blocks inserts until
the delete is finished.
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||If your table is properly indexed, you should be able to control how many
rows you need to delete each time without locking up the whole table. As
long as you don't ending locking the whole while you are doing your delete,
you should be able to insert the new row.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||Quick question about this issue.
In my purge routine, I break up the purge by only deleting
a fraction of the records between commits. If an insert
has been executed, shouldn't it get to execute between the
delete statements? Do I need manually escalate a lock for
that insert statement?
>--Original Message--
>Before an insert, the engine doesn't pre-determine what
the value for a
>column will be in order to determine whether or not it
would be affected by
>any existing queries. This is true for constants,
variables, computed
>columns and, yes, even those with defaults (because you
could override the
>default). So, rather than risk deciding between an
insert and a delete for
>a particular row that overlaps under both queries, it
blocks inserts until
>the delete is finished.
>
>"James" <bigg_game_james@.hotmail.com> wrote in message
>news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
>> Not sure if this is possible, seems like it should be
with
>> the right locking mechanism.
>> I'm purging the oldest dated records from a fairly large
>> table, and want to be able to insert a new record. The
new
>> record would have a current date(GetDate()). My first
>> tests are not going well. I can't insert the record at
>> all. So I am a bit confused as to why. Shouldn't I be
able
>> to insert a record while a purge/delete is occuring if
the
>> records are at opposite ends of the clustered index?
>> Any help is much appreciated.
>> Thanks,
>> James
>
>.
>|||> In my purge routine, I break up the purge by only deleting
> a fraction of the records between commits. If an insert
> has been executed, shouldn't it get to execute between the
> delete statements?
Yes. If it's being blocked, it should be able to jump in between your
delete batches (assuming they are also committed individually).|||So I was definitely on the right track. I had the design
right, but accidently had set implicit_transactions to ON,
once I turned it off, as I originally intended, my inserts
were allowed through.
Thanks for the quick responses.
James
>--Original Message--
>> In my purge routine, I break up the purge by only
deleting
>> a fraction of the records between commits. If an insert
>> has been executed, shouldn't it get to execute between
the
>> delete statements?
>Yes. If it's being blocked, it should be able to jump in
between your
>delete batches (assuming they are also committed
individually).
>
>.
>