ROWLOCK hint does not seem to have any effect

Hi
Here is the situation.
Many clients simulteneously update the same table, but *never*
the same rows. Each client has its *own* subset of rows.
So there is theoretically no concurrency problem.
Yet we are having locking issues.
We use ROWLOCK hint but it looks like it does not do anything.
Here is my simple test of it.
From one connection, I begin a transaction and update a record in a table
with the ROWLOCK hint.
Then I leave it as is
and
From another connection, I try to update a different record in the same
table also with the ROWLOCK hint.
Second command does not do anything until a transaction in the first
connection is commited.
If it is being locked by row, why is this happenning ?
Thanks
Alex
Hi
If SQL Server has to do a Table or Range Scan to get to data, the 2
operations may step on each other.
Correct indexing, espacially clustered indexes can help a lot.
Post DML and DDL so that we can look at it.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex" wrote:

> Hi
> Here is the situation.
> Many clients simulteneously update the same table, but *never*
> the same rows. Each client has its *own* subset of rows.
> So there is theoretically no concurrency problem.
> Yet we are having locking issues.
> We use ROWLOCK hint but it looks like it does not do anything.
> Here is my simple test of it.
> From one connection, I begin a transaction and update a record in a table
> with the ROWLOCK hint.
> Then I leave it as is
> and
> From another connection, I try to update a different record in the same
> table also with the ROWLOCK hint.
> Second command does not do anything until a transaction in the first
> connection is commited.
> If it is being locked by row, why is this happenning ?
> Thanks
> Alex
>
>
>
sql