How do I do row-level locking on SQL Server?

A. A. Only SQL 7.0 has full built-in row-level locking.

SQL 6.5 has limited row-level locking that only occurs for inserts to the end of the last page of a table, if "sp_tableoption 'table_name', 'Insert row lock', true" is set. See the books-online for 6.5 for more information.

SQL 6.0 and previous have no row-level locking capabilities.

You can however effectively do row level locking with version 6.5 and earlier as long as each row takes up a whole page - thus locking 1 page is the same as 1 row. You can do this by padding a row with CHAR NOT NULL fields until the row length is forced to be greater than 1024 bytes. (Rows cannot span pages so this forces one row per page).

However, you should note that although the rows on this last data page are being row-level locked, any non-clustered index pages involved are not. These can be a source of contention and even deadlock - when two logically distinct transactions need to lock one or more index pages, and pessimistically in different orders.

Contributed by Neil Pike