deadlock help please

I have a table that every 30 minutes needs to be repopulated from
another table that is recreated from scratch just before.
What I did was this:
CREATE PROCEDURE BatchUpdProducts AS
begin transaction
delete products
insert into products
select * from productsTemp
commit transaction
GO

This takes about 30 seconds to run. I tried it doing it with a cursor,
row by row, but it took like 30 minutes to run instead. The problem
is with the fast approach is, once in a while I get a deadlock error
in different areas trying to access the products table. Using SQL
Server 2000 by the way.
Any ideas?Are those section that are getting the dead lock just doing read only or
do the need to right?

Wolfing wrote:

Quote:

Originally Posted by

I have a table that every 30 minutes needs to be repopulated from
another table that is recreated from scratch just before.
What I did was this:
CREATE PROCEDURE BatchUpdProducts AS
begin transaction
delete products
insert into products
select * from productsTemp
commit transaction
GO
>
This takes about 30 seconds to run. I tried it doing it with a cursor,
row by row, but it took like 30 minutes to run instead. The problem
is with the fast approach is, once in a while I get a deadlock error
in different areas trying to access the products table. Using SQL
Server 2000 by the way.
Any ideas?
>

|||On Jun 15, 12:17 pm, Seribus Dragon <Seribus.n...@.seribus.comwrote:

Quote:

Originally Posted by

Are those section that are getting the dead lock just doing read only or
do the need to right?
>
Wolfing wrote:

Quote:

Originally Posted by

I have a table that every 30 minutes needs to be repopulated from
another table that is recreated from scratch just before.
What I did was this:
CREATE PROCEDURE BatchUpdProducts AS
begin transaction
delete products
insert into products
select * from productsTemp
commit transaction
GO

>

Quote:

Originally Posted by

This takes about 30 seconds to run. I tried it doing it with a cursor,
row by row, but it took like 30 minutes to run instead. The problem
is with the fast approach is, once in a while I get a deadlock error
in different areas trying to access the products table. Using SQL
Server 2000 by the way.
Any ideas?

Only read, the only process that writes is the one I showed above that
runs every 30 minutes|||Wolfing (wolfing1@.gmail.com) writes:

Quote:

Originally Posted by

I have a table that every 30 minutes needs to be repopulated from
another table that is recreated from scratch just before.
What I did was this:
CREATE PROCEDURE BatchUpdProducts AS
begin transaction
delete products
insert into products
select * from productsTemp
commit transaction
GO
>
This takes about 30 seconds to run. I tried it doing it with a cursor,
row by row, but it took like 30 minutes to run instead. The problem
is with the fast approach is, once in a while I get a deadlock error
in different areas trying to access the products table. Using SQL
Server 2000 by the way.

If it is OK for you that the batch process is the deadlock victim, you
can add "SET DEADLOCK_PRIORITY LOW" first in the procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx