Microsoft SQL Server 2012 Bible

Chapter 47

Managing Transactions, Locking, and Blocking

In This Chapter

Using the Transactional Integrity Theory

Understanding the Transaction Log and Why It's Important

Exploring SQL Server Locks and Performance

Handling and Preventing Deadlocks

Implementing Optimistic and Pessimistic Locking

This chapter focuses on how contention occurs inside the database engine as data is read and modifications occur. Chapter 2, “Data Architecture,” defines six database architecture design goals: usability, integrity, scalability, extensibility, availability, and security. Scalability is all about concurrency — multiple users simultaneously attempting to retrieve and modify data.

To ensure transactional integrity, SQL Server uses locks to protect transactions from affecting other transactions. A lock is a mechanism used by SQL Server to synchronize access by multiple users to the same piece of data at the same time. Specifically, transactions that are reading data may lock that data, which can prevent, or block, other transactions from writing the same data. Similarly, a transaction that's writing data can prevent other transactions from writing or reading the same data, depending on the isolation level of the transaction reading the locked data.

SQL Server uses locks to maintain the isolation property of a transaction. As more transactions occur inside the database, there is potential for more resources to acquire locks as data is modified. When more resources are locked, there is a good ...