ROWLOCK hint does not seem to do anything

Hi
Here is the situation.
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
AlexAlex
SQL Server will assign ROWLOCK by default
Read Vyas's article describing row level security
http://vyaskn.tripod.com/ row_level...as
es.htm
"Alex" <alex@.nospam.com> wrote in message
news:d6edfg$cmc$1@.lust.ihug.co.nz...
> Hi
> Here is the situation.
> 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
>
>
>|||On Wed, 18 May 2005 13:40:34 +1000, Alex wrote:
(snip)
>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.
Hi Alex,
Do you have a PRIMARY KEY, UNIQUE or other INDEX on this table? Is the
WHERE clause such that this index can be used to find the row to be
updated by the seecond statement?
If the answer to either of these is "no", then SQL Server has to scan
the whole table for the UPDATE. And it will encounter the locked row
during that process. Repeat the experiment, but with the option to show
execution plan set on. After issuing commit or rollback in the first
connection, the second should finish; now you can check the execution
plan.
Also, if the indexed column itself is changed by the first UPDATE
statement, there will be a lock on that index page as well. This can
also cause a wwait in your second query, if that index is used in the
execution plan.
For more detailed info, more information is definitely needed. A repro
script (CREATE TABLE statements, INSERT statements and the statments you
executed on the two connections) would be ideal. See
www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)