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?

Re: deadlock help please

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

Wolfing wrote:
> 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?
>

Re: deadlock help please

On Jun 15, 12:17 pm, Seribus Dragon wrote:
> Are those section that are getting the dead lock just doing read only or
> do the need to right?
>
> Wolfing wrote:
> > 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?

Only read, the only process that writes is the one I showed above that
runs every 30 minutes

Re: deadlock help please

Wolfing (wolfing1@gmail.com) writes:
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx