Transaction Locking

3 4

SQL Server uses an object called a lock to prevent multiple users from making modifications to a database at the same time and to prevent a user from retrieving data that is being changed by another user. Locking helps to ensure logical integrity of transactions and data. Locks are managed internally by SQL Server software and are acquired on a per-user-connection basis. When a user acquires (or owns) a lock on a resource, the lock indicates that the user has the right to use that resource. Resources that can be locked by a user include a row of data, a page of data, an extent (8 pages), a table, or an entire database. For example, if the user holds a lock on a data page, another user cannot perform operations on that page that would affect the operations of the user owning the lock. So a user could not update a data page that is currently locked and being retrieved by another user. Nor can a user acquire a lock that would conflict with a lock already held by another user. For instance, two users could not both have locks to update the same page at the same time. The same lock cannot be used by more than one user.

SQL Server's locking management automatically acquires and releases locks, according to users' actions. No action by the DBA or the programmer is needed to manage locks. However, you can use programming hints to indicate to SQL Server which type of lock to acquire when performing a particular query or database modification; these are covered in the section "Locking Hints" later in this chapter.

In this section, we'll look at the levels of granularity of locks as well as at locking modes. But first let's examine some of the locking management features that enhance SQL Server performance.

Locking Management Features

SQL Server supports row-level locking—the ability to acquire locks on a row in a data page or an index page. Row-level locking is the finest level of locking granularity that can be acquired in SQL Server. This lower level of locking provides many online transaction processing (OLTP) applications with more concurrency. Row-level locking is especially useful when you are performing row inserts, updates, and deletes on tables and indexes.

In addition to providing the row-level locking feature, SQL Server provides ease of administration for lock configuration. You do not need to set the locks configuration parameter manually to determine the number of locks available for SQL Server use. By default, if more locks are needed, SQL Server will dynamically allocate more, up to a limit set by SQL Server memory. If locks were allocated but are no longer in use, SQL Server will deallocate them. SQL Server is also optimized to dynamically choose which type of lock to acquire on a resource—usually row-level locking for inserts, updates, and deletes, and page locking for table scans. The next section explains the levels of locking in more detail.

MORE INFO

---

See Chapter 30 for more information about the locks configuration option.

Levels of Locking

Locks can be acquired on a number of resources; the type of resource determines the granularity level of the lock. Table 19-2 lists the resources that SQL Server can lock, ordered from the finest to the coarsest level of locking granularity.

Table 19-2. Lockable resources

Resource	Type of Locking	Description
Row ID	Row level	Locks an individual row in a table
Key	Row level	Locks an individual row in an index
Page	Page level	Locks an individual 8-KB page in a table or an index
Extent	Extent level	Locks an extent, a group of 8 contiguous data pages or index pages
Table	Table level	Locks an entire table
Database	Database level	Locks an entire database

As the granularity level becomes coarser, concurrency decreases. For example, locking an entire table with a certain type of lock can block that table from being accessed by any other users. But overhead will decrease because fewer locks are used. As the granularity level becomes finer—in page-level and row-level locking, for example—concurrency increases because more users are allowed to access various pages or rows in a table at one time. In this case, overhead also increases because more locks are required when many rows or pages are being accessed individually.

SQL Server automatically chooses the type of lock that will be appropriate for the task, while minimizing the overhead of locking. SQL Server also automatically determines a lock mode for each transaction involving a locked resource; these modes are covered next.

Lock Modes

A lock mode specifies how a resource can be accessed by concurrent users (or concurrent transactions). Each type of lock is acquired in one of these modes. Six modes of locking are available: shared, update, exclusive, intent, schema, and bulk update.

Shared

Shared lock mode is used for read-only operations such as operations you perform by using the SELECT statement. This mode allows concurrent transactions to read the same resource at the same time, but it does not allow any transaction to modify that resource. Shared locks are released as soon as the read is finished unless the isolation level has been set to repeatable read or higher, or unless a locking hint that overrides this behavior was specified in the transaction.

Update

Update lock mode is used when an update might be performed on the resource. Only one transaction at a time can obtain an update lock on a resource. If the transaction does make a modification (because, for example, the search condition found rows to modify), the update lock is converted to an exclusive lock (described next); otherwise, it is converted to a shared lock.

Exclusive

Exclusive lock mode is used for operations that modify data, such as updates, inserts, and deletes. When an exclusive lock is held on a resource by a transaction, no other transaction can read or modify that resource. This lock mode prevents the same data from being updated at the same time by concurrent users, which might cause invalid data.

Intent

Intent lock mode is used to establish a locking hierarchy. For example, an intent lock at the table level indicates that SQL Server intends to acquire a lock on one or more pages or rows in that table. Generally, if a transaction needs to acquire an exclusive lock on a resource, SQL Server first checks whether any intent locks exist for that resource. If an intent lock is held by a transaction that is waiting for that resource, the second transaction cannot acquire the exclusive lock. If there is no transaction that is holding an intent lock and is waiting for that resource, the transaction can acquire the exclusive lock on it. There are three types of intent lock modes, as follows:

• Intent shared Indicates that a transaction intends to put a shared lock on a resource
• Intent exclusive Indicates that a transaction intends to put an exclusive lock on a resource
• Shared with intent exclusive Indicates that a transaction intends to put a shared lock on some resources and an exclusive lock on other resources

For details concerning these mode types, look up "shared lock mode" in the Books Online index and select "Understanding Locking in SQL Server" in the Topics Found dialog box.

Schema

Schema lock mode is used when a table schema change operation, such as the addition of a column to a table, is executed or when queries are being compiled. Two types of schema locks exist for these cases: schema modification (Sch-M) and schema stability (Sch-S). A schema modification lock is used when a table data definition language (DDL) operation is performed. A schema stability lock is used for compiling queries. When a query is compiled, other transactions can run and acquire locks on the table at the same time, even exclusive locks, but DDL statements cannot be executed on the table when there is a schema stability lock.

Bulk Update

Bulk update lock mode is used when you are bulk copying data into a table with the TABLOCK hint specified or when you set the table lock on bulk load option by using sp_tableoption. The purpose of the bulk update lock is to allow processes to bulk copy data concurrently into the same table, while preventing access to that table by any processes that are not performing a bulk copy.