SQL Server Transactions and Locking – Part 2

In the first part of SQL Server Transactions
and Locking article series, you learned
the basics of SQL Server transactions, how they work and how to handle them,
and how to write procedures that use transactions. In this part, I will give an overview of SQL
Server locking and transaction isolation levels, how to set the transaction isolation level, and how some isolation levels use locking, while others use row
versioning. You also learn about what type of locks data update requires.

Locking overview

The locking is a
necessary part of the transaction process when working in multi-user online
transaction processing (OLTP) environment. SQL Server use locks to prevent
update conflicts. For example, when one user is updating the data in the table,
the SQL Server locks prevent other users from accessing the data that are being
updated. Locks help to prevent:

  • Lost
    : Occurs
    when two transactions are updating the same data simultaneously. The changes
    are saved to the last transaction that writes to the database, losing changes
    of another transaction.
  • Dirty
    Occurs when a transaction reads uncommitted data from another transaction. This
    may lead to changes to making inaccurate changes to the data. This is also
    known as an uncommitted dependency.

  • Nonrepeatable
    Occurs when row data changes between data reads. This is also referred to as
    inconsistent analysis.

  • Phantoms: A record
    appears when a transaction rereads data through after making a change

You can serialize transactions with
locks, which means that only one person can change a data item, such as a
particular row, all at once. SQL Server can issue a lock for:

  • A
    row identifier (RID), locking a single row in a table.
  • A
    key, which is a row lock within an index
  • A
    table, which locks all data rows and indexes
  • A
    database, which is used when restoring a database
  • A
    page, locking an 8-KB data or index page
  • An
    extent, locking a contiguous group of pages during space allocation

SQL Server selects
a locking level appropriate for current data manipulation or definition action.
For example, SQL Server uses a row lock to update a single row of data in a
table. SQL Server uses dynamic lock management, meaning that the level
of locking can be automatically adjusted as needed. You can use the dynamic
management view sys.dm_tran_locks for information
about active locks.

Basic locks

SQL Server supports
the following types of locks:

  • Shared
    locks (S)
    It is used when performing read-only operations against the database. Resources
    locked with a shared lock are available for SELECT, but not for modification.
  • Exclusive
    locks (X)
    Used for operations that modifies data, such as, INSERT, UPDATE and DELETE
    statements require exclusive locks. No more than one transaction can have an
    exclusive lock on a resource. If there is an exclusive lock on a resource, no
    other transaction can access that resource.
  • Intent
    Sets a lock hierarchy. For example, if a transaction has an exclusive lock on a
    row, SQL Server places an intent lock on the table. When another transaction
    requests a lock on a row in the table, SQL Server knows to check the rows to
    see if they have locks. If a table has no intent lock, it can issue the
    requested lock without checking each row for a lock.
  • Update
    lock (U)
    This type of lock usually placed on a page before performing an update. When
    SQL Server is ready to update the page, the lock will be promoted to an
    exclusive page lock.
  • Schema
    It is used to prevent a table or index that is being used in another session
    from being dropped or its schema being modified. When a resource is locked with
    a schema lock, the object cannot be accessed.
  • Bulk
    update locks (BU)
    : It is used to prevent other processes
    from accessing a table while bulk load procedure is being processed. It will,
    however, allow treatment of concurrent bulk load processes, allowing you to
    execute parallel loads. A bulk load procedure is one performed by using bulk
    copy program (bcp) or BULK INSERT.

Optimistic and
pessimistic locking

They are the two
terms that are commonly used to describe locking methods:

  • Pessimistic

    locks resources as they are acquired, holding locks on the duration of the
    transaction. It is more likely to cause deadlocks. A deadlock occurs when two
    transactions each blocks access to the resources needed for the other
  • Optimistic

    assumes that conflicts between transactions are not likely, but it could
    happen. Transactions can be executed without blocking resources. The only time
    that the resources are controlled by a conflict is when changes are made to the
    data. If a conflict occurs, the transaction is rolled back.

Row versioning

Since the release
of SQL Server 2005, Microsoft introduced row versioning as an alternative to
shared locks. With row versioning, rows are read into tempdb at the
beginning of a transaction, and the transaction uses that copy of those rows
throughout the length of the transaction. Row versioning protects the
transaction from:

  • Dirty
  • Nonrepeatable
  • Phantoms

Even when using row
versioning, SQL Server still has an exclusive lock on a row before updating it.

Row versioning
allows for optimum concurrency (multiple users access to data at the same
time), while providing good protection. However, you must ensure that tempdb
has enough disk space available. Using row versions can also degrade
performance due to the resources required to move data in and out of tempdb.


Isolation protects transactions activities performed outside the transaction,
meeting the isolation requirements of an ACID transaction. You manage
transaction isolation as a session-level setting that affects all operations in
their sessions. You can override the isolation level by using locking options
individual accounts.

To set the
transaction isolation level, you run:


Isolation levels
are defined as follows:

  • READ
    : Dirty reads are possible. A shared lock is
    not acquired and no exclusive locks are honored.
  • READ
    : Prevents
    dirty reads by using shared locks or row versioning. The method used depends on
    the configuration of the database option READ_COMMITED_SNAPSHOT. If ON,
    SQL Server uses row versioning. If OFF, SQL Server uses shared locks. Using Row
    Versioning improve concurrency because SQL Server manages row versioning at a
    statement level, nonrepeatable which are caused by data changes between reads,
    might occur.
    reads and nonrepeatable reads cannot occur. Read locks are held until the
    transaction is committed or rolled back.
  • SNAPSHOT: Data
    changes made outside the transaction after the transaction begins are not
    visible within the transaction. Uses row versioning. No shared locks are held.
    When you try to update the data, SQL Server compares the current data with
    those stored in tempdb. If they are different, the update fails and the
    transaction is rolled back. Only be used if the database option is ON ALLOW_SNAPSHOT_ISOLATION.
    transactions cannot update or insert any new rows that were read by the
    transaction until after the current transaction commits.

After changing the
isolation level, you can run DBCC USEROPTIONS to
verify that the change was made in the current connection.

Table hints

You can also use
table hints to override the locking used for a single SELECT, UPDATE, INSERT,
or DELETE statement. Table hints are specified in the FROM clause of the DML
statement and affect only the table or view referenced in that clause. The
following is the general syntax for specifying table hints:

WITH  ( <table_hint> [ [, ]...n ] )
        <table_hint> ::=
        [ NOEXPAND ] { 
    INDEX  ( index_value [ ,...n ] ) 
  | INDEX =  ( index_value )    
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | XLOCK 

<table_hint_limited> ::=
  | XLOCK 


For more
information about table hints and the hints related to locking, see Table Hints


In this two part article
series on SQL Server Transactions and Locking, you learned how to manage transactions. You
learned how to set the transaction isolation level. You
learned that some isolation levels use locking, while
others use row
You also learned that a data update requires an exclusive lock.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |