What does the index option "Use Row Locks" mean?

[jsteph]

Hi all,
In sql-2005, there is an index option "Use Row Locks when accessing index" There is also a "Use Page Locks when accessing the index.

My question is twofold--First, *both* of these are checked by default, why is that? Second, what are the ramifications if either or both would be un-checked.

Specifically, I have a table with a fairly high trans-per-second rate. This is an insert-only transaction, the records are never updated but they are read in a Select with (nolock) statement, which uses the indexed field in the Where clause. I want to maximize the performance of the inserts while being able to read the data using this index.
Thanks for any insight into this,
--Jim

 

[mrdenny]

The ROW LOCKS option tells SQL Server that it is allowed to lock just the specific row that the user is querying for. By removing this the index will default to locking the page.

The PAGE LOCKS option tells SQL Server that it is allowed to lock just the page that contains the rows that the user is querying for. By removing this the index will default to either row locks or table locks.

It's best to leave them both on. INSERT commands require page locks or higher (table locks is PAGE locks aren't available or are to costly for some reason). SELECT statements should have there locking done at the ROW level.

To improve performance of your inserts make sure that you have the least number of indexes on the table as possible. If the writes to the table are frequent enough a RAID 10 (or 0+1) array will be your best bet for the disk array.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jsteph]

mrdenny,
Thanks very much. So the checkboxes for row and page locks are not mutually exclusive, which would explain them both being checked by default.

So let me see if I've got this right: it's basically a hierarchy of choices of how to lock, starting with table, then page, then row (depending on which of the latter two are checked)?

I'll leave them both checked for now, and go easy on adding any more indexes.
Thanks,
--Jim

 

[mrdenny]

Locking actually starts at the row level then escelates up to the page, then the table, then the database as needed.

Other than that, you've got it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog