When data in a database is read or modified, the database
engine uses special types of controls, called locks, to maintain integrity in
the database. Locks basically work by making sure database records involved in
a transaction cannot be modified by other transactions until the first
transaction has committed, ensuring database consistency.

Get SQL tips in your inbox

TechRepublic’s SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

When designing database applications, you should keep in
mind the different types of locks that will be issued, and the different levels of isolation your
transactions will occur. Typically, the SQL Server defaults work fine
for what you are trying to accomplish. However, there will be times when it is
advantageous to manually make hints to how locks are issued on your tables in your SQL statements.

This article focuses on two table hints: NOLOCK and
READPAST. I’ll set up a table to use for our example queries. Execute the
script in Listing A to create the SalesHistory table and populate the table with data.

NOLOCK

This table hint, also known as READUNCOMMITTED, is
applicable to SELECT statements only. NOLOCK indicates that no shared locks are
issued against the table that would prohibit other transactions from modifying
the data in the table.

The benefit of the statement is that it allows you to keep
the database engine from issuing locks against the tables in your queries; this
increases concurrency and performance because the database engine does not have
to maintain the shared locks involved. The downside is that, because the
statement does not issue any locks against the tables being read, some “dirty,”
uncommitted data could potentially be read. A “dirty” read is one in which the
data being read is involved in a transaction from another connection. If that
transaction rolls back its work, the data read from the connection using NOLOCK
will have read uncommitted data. This type of read makes processing
inconsistent and can lead to problems. The trick is being able to know when you
should use NOLOCK.

As a side note, NOLOCK queries also run the risk of reading
“phantom” data, or data rows that are available in one database transaction
read but can be rolled back in another. (I will take a closer look at this side effect in part two of this
article series.)

The following example shows how NOLOCK works and how dirty
reads can occur. In the script below, I begin a transaction and insert a record
in the SalesHistory table.

BEGIN TRANSACTION
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)         
      VALUES           
      ('PoolTable', GETDATE(), 500)                  

The transaction is still open, which means that the record
that was inserted into the table still has locks issued against it. In a new
query window, run the following script, which uses the NOLOCK table hint in
returning the number of records in the SalesHistory
table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

The number of records returned is 301. Since the transaction
that entered the record into the SalesHistory table
has not been committed, I can undo it. I’ll roll back the transaction by
issuing the following statement:

ROLLBACK TRANSACTION

This statement removes the record from the SalesHistory table that I previously inserted. Now I run
the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

This time the record count returned is 300. My first query
read a record that was not yet committed — this is a dirty read.

READPAST

This is a much less commonly used table hint than NOLOCK.
This hint specifies that the database engine not consider any locked rows or
data pages when returning results.

The advantage of this table hint is that, like NOLOCK,
blocking does not occur when issuing queries. In addition, dirty reads are not
present in READPAST because the hint will not return locked records. The
downside of the statement is that, because records are not returned that are
locked, it is very difficult to determine if your result set, or modification
statement, includes all of the necessary rows. You may need to include some logic
in your application to ensure that all of the necessary rows are eventually
included.

The READPAST table hint example is very similar to the
NOLOCK table hint example. I’ll begin a transaction and update one record in the
SalesHistory table.

BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the
locks that were placed on the record that I updated are still in effect. In a
new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the
table.

SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)

My SalesHistory table originally had
300 records in it. The UPDATE statement is currently locking one record in the
table. The script above that uses READPAST returns 299 records, which means that
because the record I am updating is locked, it is ignored by the READPAST hint.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.