Intro

Ideally transactions occur in series (one transaction completes before another begins) or in parallel (each transaction works on different data). In reality, transactions can occur concurrently on the same data, thus resulting in concurrency problems.

Locking resources can solve concurrency problems and ensure the isolation of data. However too much locking makes it difficult for transactions to occur concurrently at all. When it comes to isolating transactions, care must be made when choosing which resources to lock and how tightly those resources will be locked. Here are general examples:

Concurrency Problems

Here are different concurrency problems

Lost updates

  1. ta (transaction a) and tb both do a good read on dx (data x).
  2. Both update dx, but whichever does the last update will overwrite the previous.

Uncommitted dependency (aka dirty read)

  1. ta reads dx. dx is now "dirty" because ta intends to change dx.
  2. tb reads dx and finishes its work.
  3. ta updates dx and finishes its work.

Inconsistent analysis (aka non-repeatable read)

  1. ta reads dx. dx is now "dirty" because ta intends to change dx.
  2. tb reads dx and continues its work.
  3. ta updates dx. dx is now doubly dirty because it intends to further change dx.
  4. tb reads dx again (but dx is different) and finishes its work.
  5. ta updates dx again and finishes its work.

Phantom reads

  1. ta reads dx.
  2. tb inserts or deletes rows from dx and finishes its work.
  3. ta reads dx (but now it is missing rows or has mysterious new rows) and finishes its work.

Setting Locking Options

There are a three basic places to set locking options in SQL Server applications: Cursor Concurrency, Transaction Isolation Level, and Locking Hints

Cursor Concurrency

If an application is using cursors, then there are cursor concurrency options primarily because of the cursor as opposed to because of any transactions.

There are three basic categories of cursor concurrency:

Transact-SQL cursors specify the READ_ONLY, SCROLL_LOCK, and OPTIMISTIC keywords on the DECLARE CURSOR statement. The OPTIMISTIC keyword specifies optimistic with row versioning, Transact-SQL cursors do not support the optimistic with values concurrency option.

ADO applications specify adLockReadOnly, adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic in the LockType property of a Recordset object.

ODBC applications set the statement attribute SQL_ATTR_CONCURRENCY to SQL_CONCUR_READ_ONLY, SQL_CONCUR_ROWVER, SQL_CONCUR_VALUES, or SQL_CONCUR_LOCK.

DB-Library applications set the dbcursoropen parameter concuropt to CUR_READONLY, CUR_OPTCC (for optimistic using row versioning), CUR_OPTCCVAL, or CUR_LOCKCC.

Transaction Isolation Level

Transaction Isolation Level. Four SQL-92 isolation levels that can be set for each transaction.

Transact-SQL scripts and DB-Library applications use the SET TRANSACTION ISOLATION LEVEL statement.

SET TRANSACTION ISOLATION LEVEL
    {   READ UNCOMMITTED
      | READ COMMITTED
      | REPEATABLE READ
      | SERIALIZABLE
    }
BEGIN TRANSACTION
...
COMMIT TRANSACTION

ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

OLE DB applications call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, or ISOLATIONLEVEL_SERIALIZABLE

ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

Locking Hints

Locking hints on SELECT, INSERT, UPDATE, or DELETE statements used within a transaction for more granular control over locking than indicated by the Transaction Isolation Level. The syntax is to add WITH LockingHint after each appropriate table.

Locking hint Description
HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK Use page locks where a single table lock would usually be taken.
READCOMMITTED Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED Equivalent to NOLOCK.
REPEATABLEREAD Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
ROWLOCK Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
TABLOCK Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
UPDLOCK Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
XLOCK Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.

How SQL Server Implements Locking

When locking options are set, SQL Server implements the locking in the background. Here are a few of the details of how this is done.

Here are resources SQL Server can lock in order of increasing granularity. [This is taken right out of the MSDN Library.]

Resource Description
RID Row identifier. Used to lock a single row within a table.
Key Row lock within an index. Used to protect key ranges in serializable transactions.
Page 8 kilobyte (KB) data page or index page.
Extent Contiguous group of eight data pages or index pages.
Table Entire table, including all data and indexes.
DB Database.

Here are the different modes that determine how tightly SQL Server locks resources. [This is taken right out of the MSDN Library.]

Lock mode Description
Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU) Used when bulk-copying data into a table and the TABLOCK hint is specified.

Page Modified: (Hand noted: 2007-10-13 16:04:27Z) (Auto noted: 2010-12-24 22:47:37Z)