SQL Server requires (READ) locks for reading from the database (select queries).
For Updates (WRITE) locks are used.
SQL Server starts with row-level locks, but often escalates these to page and table locks, causing deadlocks.
Using NOLOCK tells SQL Server to ignore locks and read directly from the tables. This means you don’t have to worry about locks, however you may read data that is not yet committed (i.e. in the Process of being updated, and not yet committed, or worse yet was int he process of being updated, but then the transaction doing the writing rolls back, so you read data that was never there). Obviously only use NOLOCK when you can deal with these consequences (e.g. it might not be a good idea in an Accouting system, but may be fine for a CMS style site).
Using ROWLOCK, means that the lock type won’t esclated up to page or table locks (which would have effected more records than just those being updated). Instead locks will always be at the ROW level.
FROM Person WITH (NOLOCK)
WHERE Name LIKE ‘Smith’
UPDATE Person WITH (ROWLOCK)
SET Name = ‘Albert’ WHERE Name= ‘Bert’