Microsoft® SQL Server® 2008 Bible

Chapter 66. Managing Transactions, Locking, and Blocking

IN THIS CHAPTER

• Transactional integrity theory

• The transaction log and why it's important

• SQL Server locks and performance

• Handling and preventing deadlocks

• Implementing optimistic and pessimistic locking

Any route is fast at 4:00 A.M. with no traffic and all green lights. The trick is designing a route that works during rush hour. Concurrency is about contention. Contention is "a struggling together in opposition" in which two users are trying to get hold of the same thing, and it can apply to system resources or data. Managing resource contention is about writing code that uses as few resources as possible to enable as many users as possible to execute code at the same time.

With database contention, more than one user is trying to access the same resource within the database. For any complex system, as the number of users increases, performance decreases as each user competes for the same resources and the contention increases. This chapter focuses on the contention for data. Most people focus on system resource contention, not on contention for database resources. Chapter 2 defined 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.

Here's why: To ensure transactional integrity, SQL Server (by default) uses locks to protect transactions from affecting other ...