Chapter 14
Transactions and Locks
WHAT YOU WILL LEARN IN THIS CHAPTER:
- What a transaction is, and how and when to use one
- How SQL Server uses the log to manage database concurrency
- When SQL will lock resources, and what types of locks exist
- How to manage locking
- The difference between ordinary lock waits and deadlocks
This is one of those chapters that, when you go back to work, makes you sound like you’ve had your Wheaties today. Nothing you’re going to learn about in this chapter is wildly difficult, yet transactions and locks tend to be two of the most misunderstood areas in the database world. As such, this beginning (or at least I think it’s a basic) concept is going to make you start to look like a real pro.
In this chapter, my goals are to:
- Demystify transactions
- Examine how the SQL Server log and checkpoints work
- Unlock your understanding of locks
You’ll learn why these topics are so closely tied to each other and how to minimize problems with each.
UNDERSTANDING TRANSACTIONS
Transactions are all about atomicity. Atomicity is the concept that something should act as a unit. From a database standpoint, it’s about the smallest grouping of one or more statements that should be considered to be all or nothing.
Often, when dealing with data, you want to make sure that if one thing happens, another thing happens, or that neither of them does. Indeed, this can be carried out to the degree where 20 things (or more) all have to happen together or nothing happens. Let’s look ...
