SQL Server 2000 Design and Implementation, Part X -
Locking...

This series of articles is focusing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQL Server. The main reference for this series of articles is MCSE: SQL Server 2000 Design by Israel and Jones.

By: Chris Sully Date: July 5, 2004

Introduction

This series of articles is focussing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQLServer. The main reference for this series of articles is MCSE: SQLServer 2000 Design by Israel and Jones.

Here's a quick overview of what we've covered so far as well as what we shall be covering:

Article I: we started with developing a logical data model, before looking at how we go about implementing the physical database in SQLServer 2000.

Article II: creating and maintaining databases, in particular the files that comprise the database and how they can be split into filegroups and why they should be on occasion.

Article III: creating and maintaining tables - looking at what we can store in tables, and how we do so.

Article IV: data integrity - default values, check constraints and rules, primary keys, unique constraints, as well as foreign keys and relationships.

Article V: indexes and the related area of statistics.

Article VI: creating and maintaining the various database objects that exist- views, stored procedures, transactions, user defined functions and triggers. This concludes the look at the physical design per se. The remainder of the articles look at issues relating to data access.

Article VII: accessing data

Article VIII: modifying data

Article IX: importing and exporting data

Article X: locking

Article XI: security

Article XII: analysing and optimising data access

Thus far we've reached article X: locking. Locks prevent one user from overwriting or interfering with another user's action. SQLServer has a system of locking that balances the need to protect data against conflicting modifications while allowing as much concurrency as possible.

The Lock Manager

SQLServer's lock manager controls and manages the locking process. The lock manager is responsible for acquiring locks for different processes, managing the interaction of the different lock modes, escalating these locks if necessary, and releasing them as soon as possible to avoid contention problems with other processes. The lock manager must avoid releasing locks too soon as transactional integrity must be maintained, depending on the isolation level that is set for the database. If they occur the lock manager must also resolve deadlock issues.

There are 4 levels of transaction isolation level specified by the ANSI and ISO standards groups and which are supported by SQLServer 2000, as follows in increasing order of isolation:

READ UNCOMMITTED (aka dirty read)
Allows a user to read any data on a page even if it has been changed by another transaction in progress. Thus, if that transaction failed and was rolled back the user may have used data that no longer exists on the system. A process that is reading data does not acquire shared locks.

READ COMMITTED
The default. Will never read data that is changed but not committed. A process will acquire shared locks when reading data. If a process revisits a row of data inside a transaction, it may have changed or new rows may have appeared.

REPEATABLE READ
As READ COMMITTED but ensures that if a row of data is revisited within a transaction, the data cannot be changed, but new rows may appear. This level will hold shared locks until the transaction completes.

SERIALIZABLE
SQLServer will behave as if it's a single user system for each user, at the expense of contention problems. This prevents new rows from appearing within a transaction.

You can set the isolation level for a particular transaction using SET TRANSACTION ISOLATION LEVEL <isolation level>. The setting will then stay in place for the duration of the connection unless changed.

The lock manager tries to prevent the following problems occurring:

• lost updates: a value is updated which is overwritten by someone else. All transaction isolation levels prevent this.
• uncommitted dependencies: one user reads data that is later rolled back – a dirty read. The READ COMMITTED level prevents this.
• inconsistent analysis: a 2nd data retrieval in a transaction returns a different value from the first retrieval. REPEATABLE READ prevents this.
• phantom reads: occur when a process retrieves data in two different operations, but gets a different number of rows in one of the operations due to an INSERT or DELETE operation between the two retrieval operations. SERIALIZABLE prevents this.

SQLServer implements different lock modes that prevent access to data as well as protecting users from receiving inaccurate data. Various options and hints can be included in applications or set on the server to change the way that SQLServer implements each type of lock. The isolation levels affect the way these locks are used by transactions. The lock modes are:

• shared (S)
• intent: intent shared (IS), intent exclusive (IX), shared with intent exclusive (SIX)
• update (U)
• exclusive (X)
• schema: schema stability (Sch-S), schema modification (Sch-M)
• bulk update (BU)

Each type of lock is implemented when a particular event occurs. SQLServer can adjust the locking dynamically based on server conditions and its own estimates of how efficient each type of lock may be. Since locking more resources than needed can cause contention problems, each lock is held as long as it is needed to ensure transactional integrity and then released.

A shared lock protects the reading of data allowing a process to access data without data being lost or changed during the process. A resource can have any number of shared locks at one time – they do not interfere with one another. The transaction isolation level determines how long the shared lock is held. If REPEATABLE READ or SERIALIZABLE the locks are held until the end of the transaction. If READ COMMITTED the locks are held only as long as the server takes to read a page of data. If the query is scanning the table, then the lock is held until a shared lock is acquired on the next page.

An intent lock flags that a transaction needs a lock, thus preventing another process from gaining an exclusive lock on the resource while the process is waiting. This will lock at a higher level than is required by the process to prevent another process blocking it. There are three types of intent locks:

• intent shared – the transaction needs to acquire shared locks on resources at a lower level
• intent exclusive – the transaction needs to change data at a lower level and will need exclusive locks on the data being modified
• shared with intent exclusive – needs to read and modify data at the lower level

Update locks exist to prevent deadlocks on resources that are being modified. Typically the first process acquires a shared lock on the resource being modified. A second process also may acquire a shared lock on the same resource. As each attempts to convert the lock to an exclusive lock on the same resource it must wait for the other process to release its shared lock. This results in deadlock. SQLServer prevents this by acquiring an update lock on the resource and then converting this to an exclusive lock when the data modification occurs or to a shared lock if no update occurs. Only one process can hold an update lock on a resource at a time.

Update locks are used for any data modification statement that reads the data prior to a modification. Conversion to an exclusive lock is still required before the actual data modification takes place.

When an exclusive lock has been set on a resource only the process that acquired that lock may access the resource for reading and/ or modification. No other lock of any kind can be acquired on the resource for the duration of the exclusive lock.

A schema lock ensures that schema modifications cannot occur while some process needs the schema to remain constant. The schema modification lock is used when the schema for a table is being changed preventing another process from modifying the schema at the same time. A schema stability lock is used when a query is being compiled. They only prevent schema changes.

Latches are lightweight locks used when managing access to the internal data structures of SQLServer , such as pages of data, index pages and text pages. The latches ensure the physical integrity of the data by controlling the access to the actual data structures.

Each of the lock types can be applied at various levels:

• row
• page
• extent, i.e. 8 data pages
• table
• database

For example, the entire database is locked with a shared lock whenever a process has an open connection to that particular database. This shared database lock prevents major changes like a database being dropped or detached from the server.

Additionally key range locks exists, though not fitting nicely into the hierarchy above as they are held on individual rows and ranges between rows. These prevent phantom reads and allow serializable transactions. By holding a lock on a range, inserts or deletions in the range are prevented, thus ensuring no phantom reads.

Blocking is a consequence of locks and occurs when one process can prevent another from using some resource. The second process is then blocked, waiting for the resource to become accessible when the lock is removed. SQLServer attempts to minimise the amount of blocking by acquiring the lowest level of lock that it needs to complete a transaction. Further, if SQLServer decides during the activity that it did not select the most appropriate locking mechanism it can perform lock escalation, i.e. changing the lock. A single lock at a higher level is more efficient and easier to both acquire and release. Higher-level locks however cause more contention issues, as less data is available to other processes.

Occasionally two processes will deadlock – they are blocking each other with neither able to proceed. In such situations SQLServer will choose one as the deadlock victim and roll it back. You can control the deadlock priority of a process with the SET DEADLOCK_PRIORITY option which can be set to either LOW or HIGH. As you would expect if a LOW priority process becomes deadlocked with a HIGH priority process it is the former that is terminated.

The SP_LOCK system stored procedure displays lock information.

You can change the behaviour of locking on the server via lock hints. Each of these is placed in the FROM clause of a query after the table to which it should apply. Options are:

There we leave the topic of locking. In the next article we move on to consider security in SQLServer.

References

MCSE: SQLServer 2000 Design
Israel and Jones
Sybex

MSDN

Books Online