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

Hello. I am extending an existing proc and encountering a strange issue. I
hope someone can shed some light on this. Here's my problem :
insert into : table with decimal(14,2) columns ( where you see math )
sql statement :
TRUNCATE TABLE dbo.tbl_met_wos
INSERT INTO dbo.tbl_met_wos
SELECT
b.TopDivision as Division,
b.TopDivisionName as DivisionName,
b.Div as Department,
b.DivName as DepartmentName,
b.Dept as Class,
b.DeptName as ClassName,
a.fponhandTW,
a.fponhandMTD,
a.fponhandTQ,
a.fponhandTS,
a.fponhandTY,
c.mdonhandTW,
c.mdonhandMTD,
c.mdonhandTQ,
c.mdonhandTS, --STD
c.mdonhandTY,
d.FPSalesTW,
d.FPSalesMTD,
d.FPSalesTQ,
d.FPSalesSTD,
d.FPSalesTY,
e.MDSalesTW,
e.MDSalesMTD,
e.MDSalesTQ,
e.MDSalesSTD,
e.MDSalesTY,
--RH Added.
--cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in (0,null)
then 1 else d.FPSalesTW end) AS decimal(14,2)),
'1.25',
Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
then 1 else e.MDSalesTW end) AS varchar),
(a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
else d.FPSalesMTD end,
(c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
else e.MDSalesMTD end,
Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
(c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
else e.MDSalesTQ end,
(a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
else d.FPSalesSTD end,
--(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then 1
else e.MDSalesSTD end,
'1.37',
(a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
else d.FPSalesTY end,
(c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
else e.MDSalesTY end
FROM #HIERARCHY b
LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
a.Department
LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
c.Department
LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department =
d.Department
LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
e.Department
ORDER BY
b.Div,
b.Dept
as you can see, this is not the hottest proc in the world, but support sucks
sometimes :) anyhow, when this insert statement runs, It will round off to
whole numbers where you see math in the fields. Where you see '1.25' and
'1.37' they insert fine as a decimal(14,2) when you select from the table. I
have tryed converting and casting the entire field like :
Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
then 1 else e.MDSalesTQ end AS Decimal(14,2)
but it still rounds off to a whole number. if it were trying to insert
100.65 I get 101.00 when I select from the table.
Any IDEAS? I'm really with this one.
Robert H"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
>
...

> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
>
The sum or product of two decimals requires more precision than the
individual operands. As the required precision approaches 36, scale is
trimmed. So after a few operations your results quickly become converted to
decimal(36,0) unless you are diligent about converting intermediate results
to remove unneeded precision.
If you are sticking with decimal(14,2), just convert each intermediate
result to decimal(16,4), or something, to preserve your scale.
See:
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||Robert H wrote:
> Hello. I am extending an existing proc and encountering a strange
> issue. I hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW
> in(0,null) then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null)
> then 1 else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null)
> then 1 else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ
> in(0,null) then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then
> 1 else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null)
> then 1 else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null)
> then 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then
> 1 else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then
> 1 else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND
> b.Department = a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND
> b.Department = c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND
> b.Department = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND
> b.Department = e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but
> support sucks sometimes :) anyhow, when this insert statement runs,
> It will round off to whole numbers where you see math in the fields.
> Where you see '1.25' and '1.37' they insert fine as a decimal(14,2)
> when you select from the table. I have tryed converting and casting
> the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ
> in(0,null) then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
Don't use integer type values in the calculation unless you want an
integer result. Make sure all integer values are first converted/cast to
decimal (14, 2) first (you can do this inline). If it's a hard-coded
value, then use "0.00" instead of "0".
Also, the TRUNCATE TABLE operation can only be run by system
administrators, table owner, db_owner, and db_ddladmin. So you won't be
able to grant execute on the procedure to users without those rights.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Guys ! I now know a little more about sql precision! The solution
to the problem based on what you guys have said is to convert
each field to a real data type. This solved all problems. that truncate
table was allready in the proc, and was in production so Someone gave it the
proper access.
Thanks!
Robert H
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
> then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
> else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
> else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
> then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
> else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
> else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then
> 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
> else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
> else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
> a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
> c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department
> = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
> e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but support
> sucks sometimes :) anyhow, when this insert statement runs, It will round
> off to whole numbers where you see math in the fields. Where you see
> '1.25' and '1.37' they insert fine as a decimal(14,2) when you select from
> the table. I have tryed converting and casting the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
> then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
>
>|||Robert H wrote:
> Thanks Guys ! I now know a little more about sql precision! The
> solution to the problem based on what you guys have said is to convert
> each field to a real data type. This solved all problems. that
> truncate table was allready in the proc, and was in production so
> Someone gave it the proper access.
No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eySIpGYrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Robert H wrote:
> No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
>
Actually, using floating point in a calculation is a very reasonable thing
to to. It frees you from having to manually adjust the precision and scale
after each operation. At the end you can round off and convert back to a
decimal type, knowing that th accumulation of error in the calculation was
minimized by the floating point arithmetic.
David