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 adde
d
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:
>
> 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:[vbcol=seagreen]
>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.
>
>[quoted text clipped - 14 lines]|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> 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:|||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 ref
erring to this table? If
so, did you create indexes over those foreign key columns in the other table
s?
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:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key re
ferring to this table? If
>so, did you create indexes over those foreign key columns in the other tabl
es?
>
>[quoted text clipped - 6 lines]|||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...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote: