Microsoft® SQL Server® 2012 Bible

1047

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

T

his chapter focuses on how contention occurs inside the database engine as data is read and

modifi cations occur. Chapter 2, “Data Architecture,” defi nes 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. Specifi cally, 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 modifi ed.

When more resources are locked, there is a good chance that other transactions also need to modify

the data locked by other transactions. If this happens, the transactions needing to acquire locked

resources are known as “blocked.” Blocking occurs all the time inside the database and is not nec-

essarily a cause for alarm. Excessive blocking, however, is a problem.

This chapter has four goals: to detail how transactions affect other transactions, explain the data-

base theory behind transactions, illustrate how SQL Server maintains transactional integrity, and

explain how to get the best performance from a high concurrency system.

c47.indd 1047c47.indd 1047 7/31/2012 10:23:25 AM7/31/2012 10:23:25 AM

http://www.it-ebooks.info