The Need for Locking

In any multiuser database, there must be a consistent set of rules for making changes to data. For a true transaction-processing database, the database-management system is responsible for resolving potential conflicts between two different processes that are attempting to change the same piece of information at the same time. Such a situation cannot occur because the consistency of a transaction cannot be guaranteed. For example, if two users were to change the same data at approximately the same time, whose change would be propagated? Theoretically, the results would be unpredictable, because the answer is dependent on whose transaction completed last. Because most applications try to avoid "unpredictability" with data wherever possible (imagine your payroll systems returning "unpredictable" results, and you'll get the idea), some method must be available to guarantee sequential and consistent data changes.

Any relational database must support the ACID properties for transactions, which were previously discussed in Chapter 31, "Transaction Management and the Transaction Log." These ACID properties ensure that data changes in a database are correctly collected together and that the data is going to be left in a consistent state that corresponds with the actions being taken. The ACID properties are as follows:

  • Atomicity

  • Consistency

  • Isolation

  • Durability

The main role of locking is to provide the isolation that transactions need. Isolation ensures that individual transactions don't interfere with one another, that a given transaction does not read or modify the data being used by another transaction. In addition, the isolation that locking provides helps ensure consistency within transactions. Without locking, consistent transaction processing is impossible. Transactions are logical units of work that rely on a constant state of data, almost a "snapshot in time" of what they are modifying, to guarantee their successful completion. The highest lock isolation level that SQL Server provides allows a transaction in a multiuser system to have access to the data it is reading or modifying as if it were in a single-user system.

Although locking provides isolation for the transactions and helps ensure their integrity, it can also have a significant impact on the performance of the system. To keep your system performing well, it is necessary to keep transactions as short, concise, and non-interfering as possible. In this chapter, you'll explore the locking features of SQL Server that provide transaction isolation to transactions. You'll also come to understand the performance impact of the various levels and types of locks in SQL Server and how to define your transactions to minimize locking performance problems.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features