A Tech Log

November 13, 2008

SQL Server Locking : NOLOCK and ROWLOCK

Filed under: Development — adallow @ 11:14 pm
Tags:

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.

Examples:
SELECT Name
FROM Person WITH (NOLOCK)
WHERE Name LIKE ‘Smith’

and

UPDATE Person WITH (ROWLOCK)
SET Name = ‘Albert’ WHERE Name= ‘Bert’

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: