deadlock help please

#1

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?

#2

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

Wolfing wrote:

Quote:

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?

#3

On Jun 15, 12:17 pm, Seribus Dragon <Seribus.n... (AT) seribus (DOT) com> wrote:

Quote:

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

#4

Wolfing (wolfing1 (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

deadlock help please

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

deadlock help please - 06-15-2007 , 10:43 AM

Re: deadlock help please - 06-15-2007 , 11:17 AM

Re: deadlock help please - 06-15-2007 , 11:51 AM

Re: deadlock help please - 06-15-2007 , 11:56 AM