Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>>Hi sarajuma !
>>You could use a more granualar lock like ROWLOCK specified after the
>>Tablename in the Delete Statement. This will produce more overhead but
>>could solve you problem in some ways.
>>DELETE FROM SomeTable WITH (ROWLOCK)
>>HTH, Jens Suessmeyer.
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>>Hi sarajuma !
>[quoted text clipped - 14 lines]
>> regards|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
>>Hi
>>You have to have a WHERE condition in your DELETE statement . Also you can
>>remove all indexes defined on the table and re-create them after
>>deletion.
>>Hi sarajuma !
>>[quoted text clipped - 14 lines]
>> regards|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>[quoted text clipped - 6 lines]
>> regards|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
>>In addition to the other posts: Are you saying that you have foreign key referring to this table?
>>If
>>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>>[quoted text clipped - 6 lines]
>> regards|||are there any indexed views involved?|||On Tue, 13 Dec 2005 07:26:42 GMT, "sarajuma" <u16630@.uwe> wrote:
>I want to execute a delete statement for one table , which is very common
>and so many tables refers to it
>when the delete is hapening all the users will be blocked and the delete
>never happens
>Is there any way that I can delete rows without blocking other users '?
One would expect it to complete.
Can you show us the SQL for the delete, and/or the table declaration?
Are there any joins involved in the delete?
J.