SQL Server™ 2005 Bible

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

• Snapshot isolation level

• Database snapshots

I like food, and I even like grocery shopping, but I'll never figure out why stores don't open more check-out lanes when the line grows long. If they stocked tech magazines instead of gossip rags, it wouldn't be so bad.

Concurrency means contention. As the number of users increases, so will the performance hit as they fight to use the same resources.

Databases are all about transactional integrity, but the nemesis of transactional integrity is concurrency—multiple users simultaneously attempting to retrieve and modify data. While transaction isolation is less of an issue in small databases, in a production database with thousands of users, concurrency competes with transactional integrity.

Here's why: To protect a transaction from other transactions, by default, a transaction that's reading will block a transaction that's writing. Similarly, a transaction that's writing blocks both writing and reading transactions. The more transactions that occur simultaneously, especially long transactions, the more blocking, and the problems grow exponentially as waiting transactions block other transactions for an ever-increasing duration.

That's why concurrency is level four in optimization theory.

A good ...