Insert statment and strange locking behavior

Hi,
We were encountering table locks in the application that is hindering the
concurrency and we found that the insert into table was causing the table
lock and I can easily reproduce this from query analyzer. The statement look
s
like following:
Insert into db1..tab1 select * from db2..tab2
Table tab1 contains about 12M rows and tab2 contains 5000 rows.
Following are the stats and observations:
There is only 1 index on tab1 that is unique clustered and is on sequence
generated columns so we don’t expect lot of page splits since all the new
data will go at the end.
There is no memory pressure on SQL Server since this is the only user
connected.
If we insert only 2999 rows then SQL Sever takes row locks and IX locks on
page and table but when we try to insert >3000 rows SQL Server escalate the
row lock to table X lock so somehow there is some SQL Server algorithm that
causes it to escalate it to table lock
In SQL Server 2005, we are not able to reproduce the issue and it only takes
row locks even for 5000 rows so it seems like the issue is fixed in new
release.
Also >3000 rows insert is not true for all table since on some table SQL
Server escalate to table locks for >2000 rows so there is some other interna
l
SQL Server algorithm.
We are just wondering if someone else sees the same kind of issue and what
the solution was.
This is from Books online
“when a transaction requests rows from a table, SQL Server automatically
acquires locks on those rows affected and places higher-level intent locks o
n
the pages and table, or index, which contain those rows. When the number of
locks held by the transaction exceeds its threshold, SQL Server attempts to
change the intent lock on the table to a stronger lock (for example, an
intent exclusive (IX) would change to an exclusive (X) lock). After acquirin
g
the stronger lock, all page and row level locks held by the transaction on
the table are released, reducing lock overhead”
It is very difficult to believe that threshold is just 5000 row locks for
this type of insert since we had seen SQL Server taking millions of row lock
s
and works fine
Thanks
--HarvinderYou do not say whether you are explicitly defining a locking method on your
query/update
NOLOCK or ROWLOCK
"harvinder" wrote:

> Hi,
> We were encountering table locks in the application that is hindering the
> concurrency and we found that the insert into table was causing the table
> lock and I can easily reproduce this from query analyzer. The statement lo
oks
> like following:
> Insert into db1..tab1 select * from db2..tab2
> Table tab1 contains about 12M rows and tab2 contains 5000 rows.

Insert statment and strange locking behavior

Hi,
We were encountering table locks in the application that is hindering the
concurrency and we found that the insert into table was causing the table
lock and I can easily reproduce this from query analyzer. The statement looks
like following:
Insert into db1..tab1 select * from db2..tab2
Table tab1 contains about 12M rows and tab2 contains 5000 rows.
Following are the stats and observations:
There is only 1 index on tab1 that is unique clustered and is on sequence
generated columns so we donâ't expect lot of page splits since all the new
data will go at the end.
There is no memory pressure on SQL Server since this is the only user
connected.
If we insert only 2999 rows then SQL Sever takes row locks and IX locks on
page and table but when we try to insert >3000 rows SQL Server escalate the
row lock to table X lock so somehow there is some SQL Server algorithm that
causes it to escalate it to table lock
In SQL Server 2005, we are not able to reproduce the issue and it only takes
row locks even for 5000 rows so it seems like the issue is fixed in new
release.
Also >3000 rows insert is not true for all table since on some table SQL
Server escalate to table locks for >2000 rows so there is some other internal
SQL Server algorithm.
We are just wondering if someone else sees the same kind of issue and what
the solution was.
This is from Books online
â'when a transaction requests rows from a table, SQL Server automatically
acquires locks on those rows affected and places higher-level intent locks on
the pages and table, or index, which contain those rows. When the number of
locks held by the transaction exceeds its threshold, SQL Server attempts to
change the intent lock on the table to a stronger lock (for example, an
intent exclusive (IX) would change to an exclusive (X) lock). After acquiring
the stronger lock, all page and row level locks held by the transaction on
the table are released, reducing lock overheadâ'
It is very difficult to believe that threshold is just 5000 row locks for
this type of insert since we had seen SQL Server taking millions of row locks
and works fine
Thanks
--HarvinderYou do not say whether you are explicitly defining a locking method on your
query/update
NOLOCK or ROWLOCK
"harvinder" wrote:
> Hi,
> We were encountering table locks in the application that is hindering the
> concurrency and we found that the insert into table was causing the table
> lock and I can easily reproduce this from query analyzer. The statement looks
> like following:
> Insert into db1..tab1 select * from db2..tab2
> Table tab1 contains about 12M rows and tab2 contains 5000 rows.

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).
>
>.
>