Record Locking in SQL Server

Pessimistic and Optimistic Concurrency

In a multi-user environment, there are two models
for updating data in a database: optimistic concurrency and pessimistic concurrency.

Pessimistic concurrency involves locking
the data at the database when you read it.  You essentially lock the database
record and dont allow anyone to touch it until you are done modifying and
saving it back to the database.  Here you have 100% assurance that nobody
will modify the record  and while you check it  have it checked out,  out.
 aAnother person will have to wait until you have made the your changes.

By default, SQL Server controls lock escalation,
but you can control it yourself by using lock optimizer hints. Here are some
lock escalation hints you may want to consider:

> · ROWLOCK This hint
guides tells SQL Server to use row-level locking instead of page locks for
INSERTS. By default, SQL Server may perform as a  page-level lock instead
of a less intrusive row-level lock when inserting data. By using this hint,
you can guide tell SQL Server to always start out using row-level locking.
But, this hint does not prevent lock escalation if the number of locks exceeds
SQL Servers lock threshold.

> · SERIALIZABLE (equivalent
to HOLDLOCK) applies only to the table specified and only for the duration
of the transaction, and it will hold a shared lock for this duration instead
of releasing it as soon as the required table, data page, row or data is no
longer required.

> · TABLOCK specifies
that a table lock to be used instead of a page or row level lock. This lock
will be remained held until the end of the statement.

> · TABLOCKX specifies
that an exclusive lock will  be applied held on the table until the end of
the statement or transaction, and will prevent others from reading or updating
the table.

> · UPDLOCK specifies
that update locks will be used instead of shared locks, and will hold the
locks until the end of the statement or transaction.

> · XLOCK specifies that
an exclusive lock be used and kept activated held until the end of the end
of the transaction on all data being processed by the statement. The granularity
of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.

SQL Server 7.0 and SQL Server 2000 Lock Escalation
Options

You can override how SQL Server
performs locking on a table by using the SP_INDEXOPTION command. Below is
an example of code you can run to guide tell SQL Server to use page locking,
not row locks, for a specific table:

SP_INDEXOPTION INDEX_NAME, ALLOWPAGELOCKS,
TRUE

When FALSE, page locks are not used. Access to
the specified indexes is obtained using row- and table-level locks only.

SP_INDEXOPTION INDEX_NAME, ALLOWROWLOCKS,
TRUE

When FALSE, row locks are not used, . aAccess
to the specified indexes is obtained using page- and table-level locks only.

SQL Server 2000 Lock Escalation Options

SP_INDEXOPTION INDEX_NAME, DISALLOWPAGELOCKS,
TRUE

When TRUE, page locks are not used,. aAccess to
the specified indexes is obtained using row- and table-level locks, only.

SP_INDEXOPTION INDEX_NAME, ALLOWROWLOCKS,
TRUE

When TRUE, row locks are not used.,  aAccess to
the specified indexes is obtained using page- and table-level locks, only.

When these commands are used, they affect all
queries that eaffect these indexes. This command should not be used unless
you know, positively, that they always produce the results you desire.

Important

The SQL Server query optimizer automatically makes
the correct determination. It is recommended that you do not override the
choices the optimizer makes. Disallowing a locking level can affect the concurrency
for a table or index adversely. For example, specifying only table-level locks
on a large table accessed heavily by many users can affect performance significantly.
Users must wait for the table-level lock to be released before accessing the
table.

Optimistic concurrency means you read the
database record, but dont lock it.  Anyone can read and modify the record
at anytime and you will take your chances that the record is not modified
by someone else before you have a chance to modify and save it.  As a developer,
the burden is on you to check for changes in the original data (collisions)
and act accordingly based on any errors that may occur during the updating
e process..

With optimistic concurrency, the application has
to check for changes to the original record to avoid overwriting changes.
 There is no guarantee that the original record has not been changed, because
no lock has been placed on that data at its source - the database.  Hence,
there is the real possibility of losing changes made by another person.

Optimistic Concurrency Strategies

If you are in a performance state-of-mind, there
are chances of your chosingchances are you will go with optimistic concurrency. 
Optimistic concurrency frees up database resources as quickly as possible
so that other users and processes can act upon that data as soon as possible.

There are four popular strategies to deal ing
with optimistic concurrency:

   1. Do Nothing.

   2. Check for changes to all fields during update.

   3. Check for changes tof modified fields during
update.

   4. Check for changes to timestamp (row version)
during update.

All of these strategies have to deal with the
shaping of the Update T-SQL Command sent to the database during the updating
of the data.  The examples below are not very detailed on purpose and assume
a basic understanding of ADO.NET.

Optimistic Concurrency on Update Strategy #1 -
Do Nothing

The simplest strategy for dealing with concurrency
issues during the updating of data is to do nothing.

The update command will not check for any changes
in the data, only specify the primary key of the record to be changed.  If
someone else changed the data, those changes will more than likely be overwritten:

Update Product

    Set

        Name = @Name

    Where

        ID = @ID

One would hope that this means either 1) the application
is a single-user application, or 2) the chance of multi-user update collisions
is very unlikely and the repercussions of overwriting data is negligible.

Optimistic Concurrency on Update Strategy #2 -
Check All Fields

With this strategy, the update command will check
that all fields in the row (usually minus BLOB fields) are equal to their
original values when performing the update to assure no changes have been
made to the original record.  A check of the return value of the ExecuteNonQuery
Command will indicatetell you if the update actually took place.  The return
value of the ExecuteNonQuery Command is typically the number of rows affected
by the query.

Update Product                                                          
                                                                                                                    

    Set

        Name = @Name,

    Where

        ID = @ID

      AND

        Name = @OriginalName

      AND

        Price = @OriginalPrice

This is essentially what CommandBuilder creates
when using DataSets and is a strategy that doesnt want to see any changes
to the data.

Optimistic Concurrency on Update Strategy #3 -
Check Only Changed Fields

Rather than checking all fields in the row to
make sure they match their original value, this strategy checks only those
fields whichthat are being updated in the command.

Update Product

    Set

        Name = @Name

    Where

        ID = @ID

      AND

        Name = @OriginalName

This strategy only cares that it is not overwriting
any data and could care less thant other fields in the record may have been
changed. This could create an interesting combination of data in the row.

Optimistic Concurrency on Update Strategy #4 -
Implement Timestamp

SQL Server has a timestamp ( alias rowversion
) field that is modified every time a change is made to a record that contains
such a field.  Therefore, if you add such a field to a table you only have
to verify the timestamp record contains the same original value to be assured
none of the fields have been changed in the record.

Update Product

    Set

        Name = @Name

    Where

        ID = @ID

      AND

        TimestampID = @TimestampID

This is the same as Strategy #2 above without
the need for checking all fields.

Optimistic concurrency has a performance component
to it that suggests a higher performing ASP.NET website.

There are other methods of achieving optimistic
concurrency, but I think the ones above are the most popular.  A developer
needs to look at the application itself to determine which strategy makes
sense.  The DataSet, Command Builder, and DataAdapter typically handle this
stuff for you using Strategy #2.  However, if you work with objects instead
of DataSets, you need to handle concurrency issues yourself.

Author:

justify>By Kalpesh Bakotiya

justify>Kalpesh Bakotiya is working as a Programmer at Semaphore Infotech Pvt. Ltd, India. You can contact on email: kalpesh@semaphore-software.com.