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 ...