Chapter 5. Transaction Management

Topic

Transactions

Concurrent Update Problems

Lost Updates

Dirty Data

Non-Repeatable Reads

Phantoms

Locks

Interaction of Locks and Transactions

Modifying Default Locking Behavior

Monitoring Locks and Troubleshooting Contention Problems

Monitoring Locking and Contention

Avoiding Contention Problems

Monitoring Deadlocks

Avoiding Deadlocks

Under the Covers

sysprocesses

syslockinfo

This chapter is not about "transactions per second" or any other measurement of performance. It is about what many people would call "logical units of work." First, this chapter talks about transactions and what they are capable of. Then, the discussion focuses on how SQL Server locks resources, and how these locks interact with transactions. Finally, this chapter covers problems that may occur because of transactions and locks, and shows you some of the things you can do to troubleshoot these problems. Please have patience while reading the first two sections of this chapter that cover transactions and locks; these topics present a "chicken-and-egg" type of problem. I promise to tie it all together in the end.