how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .