More About Transactions and Composite Transact-SQL Constructs - Locking
(Page 3 of 6 )
Let me remind you of the requirements represented by the so-called ACID test. The isolation requirement means that changes to the database made by a transaction are not visible to other transactions that are themselves in an intermediate state at the time of that transaction’s completion, and that before the transaction is committed, other transactions can see data only in the state it was in before the transaction.
To satisfy the isolation requirement, SQL Server uses locks. A lock is a restriction placed on the use of a resource in a multi-user environment. It prevents other users (that is, processes) from accessing or modifying data in the resource. SQL Server automatically acquires and releases locks on resources in accordance with the actions a user performs. For example, while the user is updating a table, nobody else can modify (and in some cases, even see) records that are already updated. As soon as all updates connected to the user action are completed, the locks are released and the records become accessible.
There is just one problem with this process. Other users have to wait for the resource to become available again—they are blocked. Such blocking can lead to performance problems or even cause a process to fail. The use of locking is a trade-off between data integrity and performance. SQL Server is intelligent enough to handle most problems, and it does a great job of preventing problems. It is also possible to control locking using transaction isolation levels and optimizer (lock) hints, both of which are described in the next section. Locks can have different levels of granularity. They can be acquired on
- Rows
- Pages
- Keys
- Ranges of keys
- Indexes
- Tables
- Databases
SQL Server automatically acquires a lock of the appropriate granularity on a resource. If SQL Server determines during execution that a lock is no longer adequate, it dynamically changes the lock’s granularity.
Locks are acquired by connection. Even if two connections are initiated from the same application, one can block the other.
The type of lock acquired by SQL Server depends on the effect that the change statement will have on the resource. For example, different locks are applied for the Select statement and the Update statement. There are five lock types:
- Shared (read) locks Usually acquired for operations that do not modify data (that is, read operations). Another transaction can also acquire a nonexclusive lock on the same record, and thus the lock is shared. The shared lock is released when the transaction moves on to read another record.
- Exclusive (write) locks Acquired for statements that modify data (such as Insert, Update, and Delete). Only one exclusive lock on a resource can be held at a time. An exclusive lock can be acquired only after other locks on the resource (including shared locks) are released.
- Update locks Resemble shared locks more than they do exclusive locks. They are used to notify SQL Server that a transaction will later modify a resource. They prevent other transactions from acquiring exclusive locks. Update locks can coexist with shared locks. Just before the resource is modified, SQL Server promotes the update lock to an exclusive lock.
- Intent locks Set on an object of higher granularity to notify SQL Server that a process has placed a lock of lower granularity inside the object. For example, if a transaction places a lock on a page in a table, it will also place an intent lock on the table. The intent lock means that SQL Server does not have to scan the whole table to find out if a process has placed a lock on some page or record inside, in order to place a table lock for another transaction. In fact, there are three different types of intent locks: IS (intent share), IX (intent exclusive), and SIX (shared with intent exclusive).
- Schema locks Prevent the dropping or modifying of a table or index while it is in use. There are two types of schema locks. Sch-S (schema stability) locks prevent table or index drops. Sch-M (schema modification) locks ensure that other transactions cannot access the resource while it is being modified.
Next: Transaction Isolation Levels and Hints >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
