Controlling Transactions and Locks in SQL 2000 and 2005 – Part 3

Introduction

In the preceding articles
of this series, Lock Granularity, Transactions, and ACID were introduced.
Common lock types, such as Shared, Exclusive, and Update were explored, as well
as using SP_Lock to obtain current system lock information. In this article,
the normal internal SQL locking methods will be manipulated using Lock Hints in
order to obtain finer lock control.

Why?

Microsoft SQL does a very
good job of self-monitoring and self-adjusting lock control. The engine is
smart enough to change from row locks to page locks if the number of records
involved increases to a point where individual row locks would no longer be
optimal. It will also select a lock type specific to the type of statement
being issued. So why bother with manual lock manipulation? There are some
common situations where fine lock control will be desirable. One is a long
running batch job where you as the developer or DBA, know that the lock you
will need at the end is not a lock type SQL could have guessed looking at the
beginning statements. The other situation is where the SQL server is doing
simultaneous dual roles, such as receiving order entry input and doing
reporting at the same time. At low transaction counts, this isn’t a problem,
but as the number of users and transactions increase, performance degradation
occurs. This is especially common in real time production systems, where in
addition to typical OLTP (Online Transaction Processing) and OLAP (Online
Analytical Processing), the SQL server is issuing control instructions to some
type of machine where performance hesitation cannot be tolerated. At the same
time, it is unacceptable to have long delays in executive reporting. Ideally,
these roles would be split between several different physical machines, but that
is not always achievable due to labor or cost restraints. In these situations,
manual lock manipulation may solve some of the issues.

Lock Hints

By default, SQL Server
2000 and 2005 both employ a lock type called Read Committed. The purpose of
this lock is to ensure that any record set returned by a select only contains
committed, accurate, real data. An example of non-real data, or inaccurate
data would be if a user began a transaction to change an employee name from
Mike to Jim, while this transaction is running, but before it commits, a second
user issues a Select on employee name. Because the first transaction is not
committed, SQL does not know what the real value of employee should be. Is it
Mike, or Jim? It will be Jim if a commit is issued, but Mike if a rollback
occurs. Rather than guess at a response, SQL’s Read Committed says only
committed data will be returned, therefore, the select statement will be
blocked until the first transaction either commits or rollbacks. This type of
action is also known as "Writers always block Readers." It ensures
only accurate data is returned. The downside is the delay encountered by the
select statement. In our example, the delay would be extremely brief, but if
the database were large and our update affecting many rows, the delay may be
unacceptable. Especially if the select is for some type of real time report
that users are expecting to be returned almost instantaneously.

One of the first questions
to ask in the above scenario is whether the data being returned by the select
needs to be one hundred percent accurate. Many real time reports that display
running counts, such as "widgets made this hour," need only to be
approximate. The nature of the report is to provide a quick count and will be
refreshed in a short amount of time. In this situation, there is an easy
solution in both SQL 2000 and SQL 2005 called Read Uncommitted.

NOLOCK

Read Uncommitted, also
known as No Lock, is the opposite of the Read Committed lock described above.
With No Lock, all rows are read, regardless of any exclusive locks that may be
placed on them. In the above employee name example, it is possible to receive either
name back, Mike or Jim back, depending on the timing of the select, commit, and
rollback. The benefit of this is a very fast read that will not be blocked by
exclusive locks. For the real time report example, it is a perfect fit. NO
LOCK is enabled using the keyword WITH.


SELECT SUM(qty)AS RunningQty
FROM sales WITH (NOLOCK)

No Lock also has secondary
benefit. Not only does it ignore Exclusive locks on rows, but it does not
issue a Shared Lock on the records it reads. Therefore, it will not delay or
block a transaction trying to write, a situation known as "Readers block
writers." This can be very important in dual role systems where the
database is responsible for simultaneous reporting and data input.

READPAST

Another similar option is
called READPAST. With this hint, any rows being locked will be skipped. It is
specified using a WITH command like NOLOCK.


SELECT au_lname
FROM authors WITH (READPAST)

Unlike NOLOCK, READPAST
will skip only row locks, so if a long running transaction has a page lock, a
select issued with READPAST will be blocked until the blocking transaction
frees. In addition, READPAST will issue a Shared lock, so this reader will
block writers.

Other Hints

There are thirteen
different hints and isolation levels. Many are designed for the opposite
purpose discussed in this article; they are used to issue blocking locks in
order to guarantee a long running transaction has consistent data for the life
of a batch.

For a complete list of
Lock hints, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp
.

Conclusion

NO LOCK fit our example
perfectly. It gave a quick read by ignoring others locks. However, what if
our select data needs to be Commit accurate, we do not want to skip any rows,
and we still don’t want to wait on others locks to free. In SQL 2000, this is
not possible, but new in SQL 2005 is Snapshot. This was one of the hot topics
at the PASS Community Summit in Dallas last month. In the next article, we will focus on
this new isolation level.

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles