XLOCK and ROWLOCK

[slonk0]

Hello,

I need to lock only one row with exclusive lock (nobody else can see or
modify this row), but when I use "with (xlock, rowlock)" it doesn't work.
XLOCK always locks whole table.

Can anybody help me?

Thanks in advance.
Magda

[Erland Sommarskog]

[posted and mailed, please reply in news]

slonk0 (sl************@wp.pl) writes:

I need to lock only one row with exclusive lock (nobody else can see or
modify this row), but when I use "with (xlock, rowlock)" it doesn't work.
XLOCK always locks whole table.

For row locks to be possible, there must be an index that SQL Server
can use. If you say:

SELECT * FROM tbl WITH (XLOCK, ROWLOCK) WHERE col = 19

and there is no index on col, SQL Server needs to scan the whole table
to find the value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp