Blocking issue

We are experencing some serious locking issue on sql server (sql2000). Basically a method in VB component opens a recordset which loops through a table A (about 100,000 times), inside the loop, it calls a stpred proc and does an update on table A. Each update in the loop is quite small, a few records at the most. Table A has an update trigger which insert the updated records to a history table then delete this record from table a. There are some f other queries running on table a. The big update loop on table A causes locking issues. With every update in the loop, there are hundred of locks on the delete job when it's blocking other jobs. I can't fingure out why continuous small updates opens so many locks(by looking at sp_lock) therefore blocks other process that query the same table. Any help is appreciated. thanks.Ouch. That's just asking for locking problems... Why do you this using a
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.